非递归实现按年龄总和阈值10分组的SQL Running Total需求

我明白你现在的痛点——递归CTE在处理大表时，因为逐行迭代的特性，会产生大量逻辑读，性能急剧下降。针对按PersonID顺序分组、每组年龄总和不超过10的需求，我们可以用两种高效的非递归方案：一种是变量累加的单行扫描方案（性能最优），另一种是纯窗口函数的CTE方案（无需临时表和变量）。

方案一：变量累加的单行扫描方案（性能最优） #

这个方案通过在SELECT中使用变量跟踪当前组号和组内累计年龄，只需要对表进行一次扫描，性能远优于递归CTE，尤其适合大表场景。

DECLARE @MaxGroupSum INT = 10; -- 每组最大年龄总和
DECLARE @CurrentGroup INT = 1;
DECLARE @RunningTotal INT = 0;

-- 创建临时表存储结果（如果不需要保留结果，也可以直接输出）
CREATE TABLE #GroupedResults (
    PersonID INT,
    PersonAge INT,
    [Group] INT,
    RunningTotal INT
);

INSERT INTO #GroupedResults
SELECT 
    PersonID,
    PersonAge,
    -- 决定是否需要新开组
    @CurrentGroup = CASE 
        WHEN @RunningTotal + PersonAge > @MaxGroupSum THEN @CurrentGroup + 1 
        ELSE @CurrentGroup 
    END,
    -- 更新组内累计年龄
    @RunningTotal = CASE 
        WHEN @RunningTotal + PersonAge > @MaxGroupSum THEN PersonAge 
        ELSE @RunningTotal + PersonAge 
    END
FROM POP
ORDER BY PersonID; -- 必须保证按PersonID顺序处理

-- 输出结果
SELECT PersonID, PersonAge, [Group], RunningTotal
FROM #GroupedResults
ORDER BY PersonID;

-- 清理临时表
DROP TABLE #GroupedResults;

方案说明：

• 用@CurrentGroup跟踪当前组号，@RunningTotal跟踪当前组内的累计年龄。
• 每处理一行，判断当前年龄加入后是否超过阈值：如果超过，就新开组并重置累计年龄为当前年龄；否则继续累加。
• 时间复杂度为O(n)，仅需一次表扫描，性能远超递归CTE。

方案二：纯窗口函数的CTE方案（无变量/临时表） #

如果不想使用变量和临时表，我们可以用窗口函数结合累计的“组起始标记”来实现。核心思路是先标记哪些行是新组的起始行，再通过累计标记得到组号，最后计算组内的累计年龄。

DECLARE @MaxGroupSum INT = 10;

WITH RunningTotals AS (
    -- 第一步：计算全局累计年龄，以及前一行的全局累计年龄
    SELECT 
        PersonID,
        PersonAge,
        SUM(PersonAge) OVER(ORDER BY PersonID) AS GlobalTotal,
        LAG(SUM(PersonAge) OVER(ORDER BY PersonID), 1, 0) OVER(ORDER BY PersonID) AS PrevGlobalTotal
    FROM POP
),
GroupStarts AS (
    -- 第二步：标记新组的起始行（第一行默认是起始行；后续行如果加入前组会超阈值则标记为起始行）
    SELECT 
        *,
        CASE 
            WHEN PersonID = 1 THEN 1 -- 第一行是第一个组的起始
            WHEN PersonAge > (@MaxGroupSum - (PrevGlobalTotal - @MaxGroupSum * FLOOR((PrevGlobalTotal - PersonAge) / @MaxGroupSum))) THEN 1
            ELSE 0
        END AS IsGroupStart
    FROM RunningTotals
),
GroupNumbers AS (
    -- 第三步：累计起始标记得到组号
    SELECT 
        *,
        SUM(IsGroupStart) OVER(ORDER BY PersonID) AS [Group]
    FROM GroupStarts
),
GroupRunningTotals AS (
    -- 第四步：计算每个组内的累计年龄
    SELECT 
        PersonID,
        PersonAge,
        [Group],
        SUM(PersonAge) OVER(PARTITION BY [Group] ORDER BY PersonID) AS RunningTotal
    FROM GroupNumbers
)
-- 输出最终结果
SELECT PersonID, PersonAge, [Group], RunningTotal
FROM GroupRunningTotals
ORDER BY PersonID;

方案说明：

1. RunningTotals：计算全局累计年龄和前一行的全局累计年龄，用于后续判断是否需要新开组。
2. GroupStarts：判断每行是否是新组的起始行：
   • 第一行直接标记为起始行。
   • 后续行通过计算「前组剩余容量」，如果当前年龄超过剩余容量，则标记为起始行。
3. GroupNumbers：通过累计起始标记的数量得到组号，每个起始行对应一个新组。
4. GroupRunningTotals：按组号分组，计算组内的累计年龄（即RunningTotal）。

这两个方案都能输出你期望的结果，对比递归CTE，它们都避免了逐行递归的开销，在大表上的性能提升非常明显。其中方案一的性能最优，适合超大规模数据集；方案二更适合需要纯SQL、无变量/临时表的场景。

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