在更新语句中使用窗口函数 [英] Using window functions in an update statement

问题描述

我有一个很大的PostgreSQL表，可以通过Django访问。由于Django的ORM不支持窗口函数，因此我需要将窗口函数的结果作为常规列烘烤到表中。我想做这样的事情：

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:

UPDATE  table_name
SET     col1 = ROW_NUMBER() OVER ( PARTITION BY col2 ORDER BY col3 );

但我得到错误：无法在UPDATE中使用窗口功能

有人可以建议替代方法吗？通过Django的.raw（）方法传递窗口函数语法是不合适的，因为它返回RawQuerySet，它不支持其他ORM功能，例如我需要的.filter（）。

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.

谢谢。

推荐答案

错误来自postgres而不是django。您可以将其重写为：

The error is from postgres not django. You can rewrite this as:

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;  

或者：

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;

这有效。刚刚在postgres-9.6上进行了测试。这是 UPDATE 的语法（请参阅可选的 fromlist ）。

This works. Just tested it on postgres-9.6. Here is the syntax for UPDATE (see the optional fromlist).

希望这会有所帮助。

这篇关于在更新语句中使用窗口函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！