PostgreSQL中如何基于前序行值计算Total累计列并返回全量结果表

问题核心原因 #

你现有的函数存在3个核心问题导致无法正常展示完整表格数据：

1. 返回类型不匹配：函数声明返回SETOF refcursor，但实际返回的是逗号拼接的字符串，客户端无法识别为结构化的表格列
2. 游标查询加了LIMIT 1000，最多只能返回1000条记录
3. 逻辑冗余：完全不需要用游标循环计算累积值，PostgreSQL的窗口函数可以原生实现你要的计算规则，性能远高于自定义游标遍历，适配200万级数据量毫无压力

最优解决方案 #

你要的Total列本质是按ID、Group分组后，按YEAR排序截止到上一行的Comp_year累积和，直接用单行SQL就能实现：

SELECT 
    "ID",
    "Group",
    "YEAR",
    "Comp_year",
    COALESCE(SUM("Comp_year") OVER (
        PARTITION BY "ID", "Group" 
        ORDER BY "YEAR" 
        ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
    ), 0) AS "Total"
FROM xyz
ORDER BY "ID", "Group", "YEAR";

上述SQL直接查询就能得到完整的结构化结果，客户端网格可以直接展示所有列和全部200万条记录。

优化建议 #

针对200万数据量场景，建议给xyz表创建("ID", "Group", "YEAR")的联合B树索引，可避免开窗函数的排序开销，大幅提升查询效率。

若需要封装为函数的正确写法 #

如果需要封装为函数调用，直接返回结构化表结构即可：

CREATE OR REPLACE FUNCTION TEST3() 
RETURNS TABLE (
    v_id TEXT, -- 请和实际xyz表的ID列类型保持一致
    v_group TEXT, -- 请和实际xyz表的Group列类型保持一致
    v_year INT, -- 请和实际xyz表的YEAR列类型保持一致
    v_comp_year INT, -- 请和实际xyz表的Comp_year列类型保持一致
    v_total INT
)
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY
    SELECT 
        "ID",
        "Group",
        "YEAR",
        "Comp_year",
        COALESCE(SUM("Comp_year") OVER (
            PARTITION BY "ID", "Group" 
            ORDER BY "YEAR" 
            ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
        ), 0) AS "Total"
    FROM xyz
    ORDER BY "ID", "Group", "YEAR";
EXCEPTION WHEN OTHERS THEN
    RAISE NOTICE '% %', SQLERRM, SQLSTATE;
END;
$$;

调用方式：

SELECT * FROM TEST3();

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