SQL 在更新语句中使用窗口函数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4358613/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Using window functions in an update statement
提问by jl6
I have a large PostgreSQL table which I access through Django. Because Django's ORM does not support window functions, I need to bake the results of a window function into the table as a regular column. I want to do something like this:
我有一个很大的 PostgreSQL 表，我可以通过 Django 访问它。因为django的ORM不支持窗口函数，所以我需要把一个窗口函数的结果作为普通列烘焙到表中。我想做这样的事情：
UPDATE table_name
SET col1 = ROW_NUMBER() OVER ( PARTITION BY col2 ORDER BY col3 );
But I get ERROR: cannot use window function in UPDATE
但我得到 ERROR: cannot use window function in UPDATE
Can anyone suggest an alternative approach? Passing the window function syntax through Django's .raw() method is not suitable, as it returns a RawQuerySet, which does not support further ORM features such as .filter(), which I need.
任何人都可以建议另一种方法吗？通过 Django 的 .raw() 方法传递窗口函数语法是不合适的，因为它返回一个 RawQuerySet，它不支持进一步的 ORM 功能，例如我需要的 .filter()。
Thanks.
谢谢。
回答by Max
The error is from postgres not django. You can rewrite this as:
错误来自 postgres 而不是 django。您可以将其重写为：
WITH v_table_name AS
(
SELECT row_number() over (partition by col2 order by col3) AS rn, primary_key
FROM table_name
)
UPDATE table_name set table_name.col1 = v_table_name.rn
FROM v_table_name
WHERE table_name.primary_key = v_table_name.primary_key;
Or alternatively:
或者：
UPDATE table_name set table_name.col1 = v_table_name.rn
FROM
(
SELECT row_number() over (partition by col2 order by col3) AS rn, primary_key
FROM table_name
) AS v_table_name
WHERE table_name.primary_key = v_table_name.primary_key;
This works. Just tested it on postgres-9.6. Here is the syntax for UPDATE(see the optional fromlist).
这有效。刚刚在 postgres-9.6 上测试过。这是UPDATE的语法（请参阅可选的fromlist）。
Hope this helps.
希望这可以帮助。
