如何在SQL中实现超过预设值时自动重置的cumsum（累积求和）

要实现当累积求和超过预设阈值时重置并重新计算的功能，核心是先将连续不需要重置的行划分为同一组，再在组内计算累积和。以下是针对不同SQL方言的实现方案，假设你的表名为your_table，包含自增顺序列id和数值列val，阈值为25。

方案1：递归CTE（适用于PostgreSQL、SQL Server等支持递归的数据库） #

递归方式逐行处理，根据前一行的累积结果判断是否需要重置分组：

WITH RECURSIVE running_total AS (
    -- 初始化：取第一行数据
    SELECT
        id,
        val,
        val AS cumsum,
        1 AS group_id
    FROM your_table
    ORDER BY id
    LIMIT 1

    UNION ALL

    -- 递归处理后续行
    SELECT
        t.id,
        t.val,
        -- 若前一行累积和超过阈值，当前行从自身值开始累积；否则累加
        CASE WHEN rt.cumsum > 25 THEN t.val ELSE rt.cumsum + t.val END AS cumsum,
        -- 若前一行累积和超过阈值，分组ID加1；否则保持原分组
        rt.group_id + CASE WHEN rt.cumsum > 25 THEN 1 ELSE 0 END AS group_id
    FROM running_total rt
    JOIN your_table t ON t.id = rt.id + 1
)
SELECT val, cumsum
FROM running_total
ORDER BY id;

方案2：窗口函数（适用于MySQL 8.0+、PostgreSQL、SQL Server等支持窗口函数的数据库） #

通过生成重置标志划分分组，再在组内计算累积和：

WITH temp_cumsum AS (
    -- 先计算不考虑重置的累积和，用于判断后续行是否需要重置
    SELECT
        id,
        val,
        SUM(val) OVER (ORDER BY id) AS temp_total
    FROM your_table
),
reset_flags AS (
    -- 生成重置标志：前一行累积和超过阈值时，当前行标记为新组起点
    SELECT
        id,
        val,
        CASE WHEN LAG(temp_total, 1, 0) OVER (ORDER BY id) > 25 THEN 1 ELSE 0 END AS reset_flag
    FROM temp_cumsum
),
grouped_data AS (
    -- 累积重置标志得到分组ID
    SELECT
        id,
        val,
        SUM(reset_flag) OVER (ORDER BY id) AS group_id
    FROM reset_flags
)
-- 分组内计算累积和得到最终结果
SELECT
    val,
    SUM(val) OVER (PARTITION BY group_id ORDER BY id) AS cumsum
FROM grouped_data
ORDER BY id;

验证示例 #

假设your_table数据如下：

执行上述代码后，输出结果为：

完全符合需求中的示例输出。

注意：必须确保存在id这样的顺序列，SQL本身不保证行的顺序，没有顺序列的话无法正确实现累积逻辑。

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