PostgreSQL中带条件的running_total与overage_total计算实现问题

你需要的不低于0的截断累计和、负向超额累计值属于逐行状态递推场景，可直接通过PostgreSQL的递归CTE在数据库层实现，无需依赖业务代码处理。
注意：默认按txn_id字典序排序，如你实际业务有其他排序规则（如交易时间），替换ordered_txns子句中的排序字段即可。 #

示例测试代码 #

1. 构造测试表与数据 #

-- 示例交易表
CREATE TABLE txn_records (
    txn_id VARCHAR(10),
    amount INT
);
-- 插入你给出的示例数据
INSERT INTO txn_records VALUES
('a', 1),
('b', 2),
('c', -4),
('d', 2),
('e', -1);

2. 核心查询语句 #

WITH RECURSIVE ordered_txns AS (
    -- 对交易按规则排序并生成行号，保证累计顺序正确
    SELECT 
        txn_id,
        amount,
        ROW_NUMBER() OVER (ORDER BY txn_id) AS rn
    FROM txn_records
),
recursive_calc AS (
    -- 初始化第一行的计算结果
    SELECT 
        rn,
        txn_id,
        amount,
        GREATEST(amount, 0) AS running_total,
        GREATEST(-amount, 0) AS overage_total
    FROM ordered_txns
    WHERE rn = 1
    UNION ALL
    -- 逐行递推计算后续每一行的结果
    SELECT 
        ot.rn,
        ot.txn_id,
        ot.amount,
        GREATEST(rc.running_total + ot.amount, 0) AS running_total,
        rc.overage_total + GREATEST(-(rc.running_total + ot.amount), 0) AS overage_total
    FROM recursive_calc rc
    INNER JOIN ordered_txns ot ON rc.rn + 1 = ot.rn
)
-- 输出最终结果
SELECT txn_id, amount, running_total, overage_total
FROM recursive_calc
ORDER BY rn;

输出结果 #

查询返回结果和你要求的示例完全一致：

性能说明 #

万级及以下数据量使用该方案性能足够，如面对百万级以上大表，可通过自定义聚合函数、预分区预计算等方式进一步优化。

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