SQL中实现超过24阈值时重置重新计算的累计求和（Running Total）方法求助

嘿，我来帮你搞定这个累计求和重置的问题！先把你给出的示例数据整理成清晰的表格，方便我们对齐需求：

你提到已经能计算普通的累计求和，但卡在了"超过24就重置重新计算"的逻辑上。这个需求核心是给需要重置的区间打分组标记，再在每个分组内单独计算累计，下面给你两种实用的SQL实现方案：

方案一：递归CTE逐行判断（适用于大多数SQL数据库） #

递归CTE可以逐行检查累计值是否超阈值，动态决定是否重置：

-- 先给数据加行号，确保计算顺序正确（替换成你的实际排序字段，比如主键、时间戳）
WITH numbered_data AS (
    SELECT 
        *,
        ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS row_num
    FROM your_table_name
),
recursive_running_total AS (
    -- 初始化第一行数据
    SELECT
        ID,
        Value,
        Value AS expected_running_total,
        row_num
    FROM numbered_data
    WHERE row_num = 1
    UNION ALL
    -- 递归处理后续每一行
    SELECT
        nd.ID,
        nd.Value,
        -- 判断：上一行累计值+当前值超24就从当前值开始，否则继续累加
        CASE WHEN rrt.expected_running_total + nd.Value > 24 THEN nd.Value ELSE rrt.expected_running_total + nd.Value END AS expected_running_total,
        nd.row_num
    FROM numbered_data nd
    JOIN recursive_running_total rrt ON nd.row_num = rrt.row_num + 1
)
SELECT
    ID,
    Value,
    -- 你已经实现的普通累计求和
    SUM(Value) OVER (ORDER BY row_num) AS runningtotal,
    expected_running_total
FROM recursive_running_total
ORDER BY row_num;

方案二：窗口函数生成重置分组（更简洁高效） #

通过累计"重置标记"生成分组ID，再在分组内计算累计，逻辑更直观：

WITH reset_groups AS (
    SELECT
        *,
        -- 累计重置次数：前一行累计值>=24就记1，否则0，累计后得到分组ID
        SUM(CASE WHEN prev_running >= 24 THEN 1 ELSE 0 END) OVER (ORDER BY row_num) AS group_id
    FROM (
        SELECT
            *,
            -- 计算前一行的累计值
            SUM(Value) OVER (ORDER BY row_num ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS prev_running,
            ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS row_num
        FROM your_table_name
    ) t
)
SELECT
    ID,
    Value,
    SUM(Value) OVER (ORDER BY row_num) AS runningtotal,
    -- 按分组ID计算累计，自动实现重置效果
    SUM(Value) OVER (PARTITION BY group_id ORDER BY row_num) AS expected_running_total
FROM reset_groups
ORDER BY row_num;

重要提示 #

• 代码里的your_table_name要换成你的实际表名，ORDER BY (SELECT NULL)必须替换成数据的实际排序字段（比如主键、时间戳），否则累计顺序会出错！
• 两种方案支持主流SQL数据库（MySQL 8+、PostgreSQL、SQL Server等），低版本MySQL优先用方案二。

备注：内容来源于stack exchange，提问作者Asha Jyothi Punnam