SQL Server 带0-100范围限制的按人员分组cumulative sum实现方法

实现逻辑 #

由于每一步累积计算都需要将结果限制在0~100区间，且后续计算要以钳位后的边界值为基准，普通窗口累加函数无法直接实现该需求，因此采用递归CTE方案实现，步骤如下：

1. 对所有记录按人员分组，组内按考核日期升序排序生成唯一行号
2. 取每个人员的第一条记录，以初始值100为基准计算当期得分，结果钳位到0~100区间作为首条累积值
3. 递归遍历每个人员后续的记录，以上一条计算得到的钳位后累积值为基准，加当期得分后再次钳位，直到所有记录计算完成

完整实现代码 #

-- 示例输入表构造，实际使用时替换为你的业务表即可
WITH SourceData AS (
    SELECT Person, EvaluationDate, Score 
    FROM (VALUES
        ('Jane', 2012, -12),
        ('Hubert', 2014, -5),
        ('Jane', 2020, -5),
        ('Jane', 2015, 16),
        ('Hubert', 2011, -100)
    ) AS t(Person, EvaluationDate, Score)
),
-- 为每个人员的考核记录按日期排序生成行号
RankedRecords AS (
    SELECT 
        *,
        ROW_NUMBER() OVER(PARTITION BY Person ORDER BY EvaluationDate ASC) AS rn
    FROM SourceData
),
-- 递归计算带钳位的累积得分
CumulativeCalc AS (
    -- 锚点成员：每个人员第一条记录计算
    SELECT 
        Person,
        EvaluationDate,
        Score,
        rn,
        -- 结果钳位到0~100区间
        CASE
            WHEN 100 + Score > 100 THEN 100
            WHEN 100 + Score < 0 THEN 0
            ELSE 100 + Score
        END AS CumulativeScore
    FROM RankedRecords
    WHERE rn = 1

    UNION ALL

    -- 递归成员：后续记录基于上一步钳位结果计算
    SELECT 
        curr.Person,
        curr.EvaluationDate,
        curr.Score,
        curr.rn,
        CASE
            WHEN prev.CumulativeScore + curr.Score > 100 THEN 100
            WHEN prev.CumulativeScore + curr.Score < 0 THEN 0
            ELSE prev.CumulativeScore + curr.Score
        END AS CumulativeScore
    FROM CumulativeCalc prev
    INNER JOIN RankedRecords curr 
        ON prev.Person = curr.Person 
        AND curr.rn = prev.rn + 1
)
-- 输出最终结果
SELECT Person, EvaluationDate, Score, CumulativeScore
FROM CumulativeCalc
ORDER BY Person, EvaluationDate

输出结果验证 #

运行代码后得到的结果与预期完全一致：

兼容说明 #

如果单人员的考核记录数超过100条，需要在查询末尾添加OPTION (MAXRECURSION 0)取消递归层数限制。 #

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