PostgreSQL：如何基于阈值计算可重置的累计求和

问题场景 #

已知数据表包含Seq_no（序号）和value（数值）列，需要计算两个衍生列：

• Cum.sum：累计求和，但当累计值达到指定阈值（示例为20）时立即重置，从下一行重新开始累计
• Bucket：分组标识，同一组对应一次从初始累计到超过阈值的区间，组内行共享相同的Bucket值

解决方案 #

普通窗口函数无法直接实现这种分段重置的累计逻辑，这里借助递归CTE来逐行判断并计算，以下是具体实现：

示例数据准备 #

先创建并插入测试数据：

CREATE TABLE sample_data (
    seq_no INT PRIMARY KEY,
    value INT
);

INSERT INTO sample_data VALUES
(1, 8),
(2, 7),
(3, 8),
(4, 9),
(5, 10),
(6, 5),
(7, 12);

核心实现代码 #

阈值设为20，执行以下SQL即可得到目标列：

WITH RECURSIVE grouped_data AS (
    -- 初始化：取第一行，累计值为自身value，Bucket从1开始
    SELECT
        seq_no,
        value,
        value AS cum_sum,
        1 AS bucket
    FROM sample_data
    WHERE seq_no = (SELECT MIN(seq_no) FROM sample_data)
    
    UNION ALL
    
    -- 递归处理后续行
    SELECT
        s.seq_no,
        s.value,
        -- 判断累加后是否超阈值，超则重置为当前value，否则继续累加
        CASE WHEN gd.cum_sum + s.value > 20 THEN s.value ELSE gd.cum_sum + s.value END AS cum_sum,
        -- 超阈值则Bucket+1，否则保持原分组
        CASE WHEN gd.cum_sum + s.value > 20 THEN gd.bucket + 1 ELSE gd.bucket END AS bucket
    FROM sample_data s
    JOIN grouped_data gd ON s.seq_no = gd.seq_no + 1
)
SELECT * FROM grouped_data ORDER BY seq_no;

输出结果验证 #

执行后得到的结果如下：

通用化适配 #

• 如果需要修改阈值，直接替换代码中的20即可
• 若seq_no不是连续整数，可先给行分配连续序号再递归：

WITH numbered_data AS (
    SELECT
        seq_no,
        value,
        ROW_NUMBER() OVER (ORDER BY seq_no) AS row_num
    FROM sample_data
),
recursive_data AS (
    SELECT
        seq_no,
        value,
        value AS cum_sum,
        1 AS bucket,
        row_num
    FROM numbered_data
    WHERE row_num = 1
    
    UNION ALL
    
    SELECT
        nd.seq_no,
        nd.value,
        CASE WHEN rd.cum_sum + nd.value > 20 THEN nd.value ELSE rd.cum_sum + nd.value END AS cum_sum,
        CASE WHEN rd.cum_sum + nd.value > 20 THEN rd.bucket + 1 ELSE rd.bucket END AS bucket,
        nd.row_num
    FROM numbered_data nd
    JOIN recursive_data rd ON nd.row_num = rd.row_num + 1
)
SELECT seq_no, value, cum_sum, bucket FROM recursive_data ORDER BY seq_no;

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