PostgreSQL中创建带累计求和列的视图的正确SQL写法

问题需求 #

基于原始表mytable创建视图，保留原表的Rank、Value列，新增SumUpTo列存储从Rank=1到当前Rank的所有Value累计和。

最优解法：使用窗口函数 #

PostgreSQL原生支持窗口函数，这是实现累计和最高效的方式：

CREATE VIEW myview AS
SELECT 
    rank,
    value,
    SUM(value) OVER (ORDER BY rank) AS SumUpTo
FROM mytable;

SUM(value) OVER (ORDER BY rank)会按照rank的顺序，逐行计算从第一行到当前行的value总和，完全匹配需求。

子查询修正写法 #

如果需要兼容更早版本（PostgreSQL 8.4及以上已支持窗口函数，此写法为修正你之前的思路），可以通过表别名区分内外层查询：

CREATE VIEW myview AS
SELECT 
    t1.rank,
    t1.value,
    (SELECT SUM(t2.value) FROM mytable t2 WHERE t2.rank <= t1.rank) AS SumUpTo
FROM mytable t1;

你之前查询失败的原因 #

• 第一个查询WHERE (rank<rank)：两个rank都指向子查询内的mytable，条件永远不成立，导致SumUpTo全为NULL。
• 第二个查询WHERE (rank<mytable.rank)：子查询内的rank未指定别名，默认和外层的mytable是同一个表，等价于mytable.rank < mytable.rank，同样永远不成立，结果全为NULL。必须通过表别名区分内外层的表引用。

内容的提问来源于stack exchange，提问作者Michel