PostgreSQL如何实现单行多列按年度的滚动累计求和

问题原因 #

你当前的SQL错误点有两个：

1. 求和逻辑错误：你直接对当年列和上一年的原始列相加，没有按需求使用累计值计算；
2. 不必要的窗口函数使用：你已经通过WHERE a.transaction = 'SUBTOTAL'过滤出了唯一一行汇总数据，不需要使用OVER()窗口函数做跨行聚合。

正确SQL写法（仅取SUBTOTAL行） #

WITH subtotal_raw AS (
    SELECT
        COALESCE("2019", 0::numeric) AS y19,
        COALESCE("2020", 0::numeric) AS y20,
        COALESCE("2021", 0::numeric) AS y21,
        COALESCE("2022", 0::numeric) AS y22
    FROM vw_blm_dashboard_iurantransaksi_anggota
    WHERE transaction = 'SUBTOTAL'
)
SELECT
    3 AS id,
    'Equity / Modal'::text AS title,
    y19 AS "2019",
    y19 + y20 AS "2020",
    y19 + y20 + y21 AS "2021",
    y19 + y20 + y21 + y22 AS "2022",
    y19 + y20 + y21 + y22 AS total
FROM subtotal_raw;

逻辑说明 #

• 先用CTE提取出SUBTOTAL行的各年度原始值，同时用COALESCE处理空值，避免后续累加出现NULL；
• 按照需求直接做逐年累计相加即可，2020列是2019原值加2020原值，2021列是前三年原值相加，以此类推，total和2022列结果一致。

如果你需要针对全表按transaction顺序做跨行滚动累计，去掉WHERE条件后调整为如下写法即可：

SELECT
    3 AS id,
    'Equity / Modal'::text AS title,
    SUM(COALESCE("2019",0)) OVER (ORDER BY transaction) AS "2019",
    SUM(COALESCE("2019",0) + COALESCE("2020",0)) OVER (ORDER BY transaction) AS "2020",
    SUM(COALESCE("2019",0) + COALESCE("2020",0) + COALESCE("2021",0)) OVER (ORDER BY transaction) AS "2021",
    SUM(COALESCE("2019",0) + COALESCE("2020",0) + COALESCE("2021",0) + COALESCE("2022",0)) OVER (ORDER BY transaction) AS "2022",
    SUM(COALESCE("2019",0) + COALESCE("2020",0) + COALESCE("2021",0) + COALESCE("2022",0)) OVER (ORDER BY transaction) AS total
FROM vw_blm_dashboard_iurantransaksi_anggota;

内容的提问来源于stack exchange，提问作者Muhammad Rafi Bahrur Rizki