如何按组内累计值分组？SQL新增TotalGrouping字段需求咨询

我有如下数据集，记录由ID标识，按GroupID分组，组内按GroupOrder排序，包含不同的Value属性：

;WITH CTE AS
( SELECT *
  FROM (VALUES
(1, 1, 1, 20),
(2, 1, 2, 31),
(3, 1, 3, 20),
(4, 2, 1, 51),
(5, 2, 2, 20),
(6, 2, 3, 10),
(7, 3, 1, 15),
(8, 3, 2, 15)
) AS MyValues(ID, GroupID, GroupOrder, Value)
)
SELECT *
FROM CTE

需求：新增TotalGrouping字段，规则为组内按GroupOrder累计总和小于50时标记为A；一旦累计总和超过50，当前及后续同组记录均标记为B。尝试使用SUM OVER()未得到预期结果，期望输出如下：

解决方案 #

普通的SUM(Value) OVER(PARTITION BY GroupID ORDER BY GroupOrder)只能计算累计和，但无法实现“一旦超过阈值，后续均标记为B”的逻辑。需要结合MAX()窗口函数来追踪是否已经触发阈值，具体SQL如下：

;WITH CTE AS
( SELECT *
  FROM (VALUES
(1, 1, 1, 20),
(2, 1, 2, 31),
(3, 1, 3, 20),
(4, 2, 1, 51),
(5, 2, 2, 20),
(6, 2, 3, 10),
(7, 3, 1, 15),
(8, 3, 2, 15)
) AS MyValues(ID, GroupID, GroupOrder, Value)
),
CTE_Cumulative AS (
    SELECT 
        ID,
        GroupID,
        GroupOrder,
        Value,
        -- 计算组内按顺序的累计和
        SUM(Value) OVER (PARTITION BY GroupID ORDER BY GroupOrder) AS CumulativeSum,
        -- 标记从组首到当前记录是否出现过累计和超过50的情况
        MAX(CASE WHEN SUM(Value) OVER (PARTITION BY GroupID ORDER BY GroupOrder) > 50 THEN 1 ELSE 0 END) 
        OVER (PARTITION BY GroupID ORDER BY GroupOrder ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS ExceedsThreshold
    FROM CTE
)
SELECT 
    ID,
    CASE WHEN ExceedsThreshold = 1 THEN 'B' ELSE 'A' END AS TotalGrouping
FROM CTE_Cumulative
ORDER BY ID;

逻辑说明 #

1. 计算累计和：通过SUM(Value) OVER(PARTITION BY GroupID ORDER BY GroupOrder)得到每组内按GroupOrder排序的累计值；
2. 追踪阈值触发状态：用MAX() OVER()窗口函数，判断当前记录及之前的所有累计和是否有超过50的情况，若有则标记为1；
3. 生成分组标记：根据ExceedsThreshold的值，将1转为'B'，0转为'A'，实现“一旦超过阈值，后续同组记录均为B”的要求。

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