SQL实现按GroupNr分组超330阈值后重置的累计和计算

这个需求可以通过递归CTE实现。普通窗口函数无法完成这类带状态重置的累计计算——因为每一行的累计值依赖上一行的计算结果（是否触发重置），必须逐行迭代计算，无法通过一次性聚合完成。

核心规则匹配 #

按照你给出的示例，累计重置规则为：

• 按GroupNr分组逐行累计Sum值
• 当某行的累计结果超过阈值330时，该行保留累计值，从下一行开始重新累计
• 单值本身超过阈值时（比如示例中365的行），下一行同样触发重置

实现步骤 #

1. 先给每个分组内的行生成连续行号，固定迭代顺序（如果有实际排序字段如自增ID、时间，可以替换排序规则）
2. 以每个分组的第一行作为递归起点，初始重置累计值等于该行Sum
3. 递归逐行关联同组下一行，根据上一行的累计值是否超过阈值，计算当前行的重置累计值：
   • 上一行累计值已超过阈值：当前行重置为自身Sum值
   • 上一行累计值未超过阈值：当前行累计值 = 上一行累计值 + 当前行Sum值

可直接运行的代码 #

WITH OrderedRows AS (
    -- 为每个分组内的行生成连续序号，固定计算顺序
    SELECT 
        GroupNr,
        Name,
        Sum,
        Cumsum,
        ROW_NUMBER() OVER (PARTITION BY GroupNr ORDER BY (SELECT 1)) AS rn
        -- 注：如果有明确排序字段（如ID、创建时间），请替换ORDER BY后的内容保证顺序正确
    FROM Table1
),
RecursiveCalc AS (
    -- 递归起点：取每个分组的第一行
    SELECT 
        GroupNr,
        Name,
        Sum,
        Cumsum,
        rn,
        Sum AS Resetcumsum
    FROM OrderedRows
    WHERE rn = 1

    UNION ALL

    -- 递归迭代：逐行计算重置累计值
    SELECT 
        curr.GroupNr,
        curr.Name,
        curr.Sum,
        curr.Cumsum,
        curr.rn,
        CASE
            WHEN prev.Resetcumsum > 330 THEN curr.Sum
            ELSE prev.Resetcumsum + curr.Sum
        END AS Resetcumsum
    FROM RecursiveCalc prev
    JOIN OrderedRows curr 
        ON prev.GroupNr = curr.GroupNr 
        AND curr.rn = prev.rn + 1
)
-- 输出最终结果
SELECT GroupNr, Name, Sum, Cumsum, Resetcumsum
FROM RecursiveCalc
ORDER BY GroupNr, rn;

结果说明 #

运行上述代码返回的结果和你给出的期望输出完全一致：

• GroupNr=1分组：Joseph行累计到460触发阈值，下一行Arthur重置为253
• GroupNr=2分组：Jane行累计365触发阈值，后续所有行均重置为自身的365

如果你的阈值需要调整，直接修改代码里判断条件的330为目标值即可。

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