在更新语句中使用窗口函数

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

但是我得到错误：在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.

谢谢。

1 个解决方案

#1

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.

希望这可以帮助。

#1