如何在PostgreSQL中按状态条件计算累计求和？

原始数据表：

no status amount 
1  2      10000 
2  3      10000
3  2       1000
4  2     -11000

当前使用的累计求和查询：

SELECT
    main.no
    , main.status
    , main.amount
    , SUM(main.amount) OVER ( 
        ORDER BY
            main.no ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_sum
from
    table AS main 

当前查询返回结果：

no status amount running_sum
1  2      10000  10000
2  3      10000  20000
3  2       1000  21000
4  2     -11000  10000

期望结果：要求在出现其他状态码（如status=3）后，仅对status=2的行进行累计求和计算。

no status amount running_sum
1  2      10000  10000
2  3      10000  20000
3  2       1000  11000
4  2     -11000  0

请问如何在现有公式中添加条件？有没有可行的实现方法？

核心思路是先以首次出现非status=2的行为分界点，将数据拆分为不同分组，再针对分组分别计算累计和：

实现代码（可读性优先） #

SELECT
    no,
    status,
    amount,
    CASE
        -- 第一组（未出现非status=2的行）：正常累计所有行的金额
        WHEN group_id = 1 THEN SUM(amount) OVER (PARTITION BY group_id ORDER BY no)
        -- 后续分组：累计当前及之前status=2的金额，加上该组内非status=2行的金额
        ELSE SUM(CASE WHEN status = 2 THEN amount ELSE 0 END) OVER (PARTITION BY group_id ORDER BY no) + 
             MAX(CASE WHEN status != 2 THEN amount ELSE 0 END) OVER (PARTITION BY group_id)
    END AS running_sum
FROM (
    SELECT
        no,
        status,
        amount,
        -- 生成分组ID：首次出现非status=2后，后续所有行归为同一组
        SUM(CASE WHEN status != 2 THEN 1 ELSE 0 END) OVER (ORDER BY no ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS group_id
    FROM table
) AS sub
ORDER BY no;

逻辑拆解 #

1. 子查询生成分组ID：通过累计每行及之前的非status=2记录数量，将数据分为两组——第一组是从开头到首次出现非status=2的行（group_id=1），之后所有行归为第二组（group_id=2）。
2. 外层分组计算累计和：
   • 对group_id=1的行，保持原逻辑，计算所有行的累计金额。
   • 对group_id=2的行，先累计当前及之前所有status=2行的金额，再加上该组内唯一非status=2行的金额（即第二行的10000），实现“非status=2行之后仅累加status=2行”的需求。

另一种简洁写法 #

如果偏好更紧凑的语句，也可以直接在窗口函数内做条件判断：

SELECT
    no,
    status,
    amount,
    SUM(
        CASE
            -- 未遇到非status=2行时，累加所有金额
            WHEN MAX(CASE WHEN status != 2 THEN 1 ELSE 0 END) OVER (ORDER BY no ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) = 0 
            THEN amount
            -- 遇到非status=2行后，仅累加status=2行的金额
            ELSE CASE WHEN status = 2 THEN amount ELSE 0 END
        END
    ) OVER (ORDER BY no) + 
    -- 加上首次非status=2行的金额（仅在该行及之后生效）
    MAX(CASE WHEN status != 2 THEN amount ELSE 0 END) OVER (ORDER BY no ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) * 
    CASE WHEN MAX(CASE WHEN status != 2 THEN 1 ELSE 0 END) OVER (ORDER BY no ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) > 0 THEN 1 ELSE 0 END
    - 
    -- 抵消非status=2行在SUM中被重复计算的金额
    CASE WHEN status != 2 THEN amount ELSE 0 END
AS running_sum
FROM table
ORDER BY no;

两种写法都能得到期望结果，第一种分组写法更易理解和维护。

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