SQL Server如何对同一列内不同连续分组的最大值进行求和

需求说明 #

现有存储日期和连续天数的表结构，需要筛选出Consec_Days大于1的连续行分组，对每个分组取Consec_Days的最大值后求和，样例数据的预期计算结果为5。

测试数据构造 #

-- 声明测试表
DECLARE @TestTable TABLE (
    Date DATE,
    Consec_Days INT
);
-- 插入样例数据
INSERT INTO @TestTable (Date, Consec_Days)
VALUES
('2015-01-01', 1),
('2015-01-03', 1),
('2015-01-06', 1),
('2015-01-07', 2),
('2015-01-09', 1),
('2015-01-12', 1),
('2015-01-13', 2),
('2015-01-14', 3),
('2015-01-17', 1);

实现查询代码 #

SELECT SUM(GroupMaxDays) AS TotalDays
FROM (
    -- 按分组取每个连续区间的最大Consec_Days
    SELECT MAX(Consec_Days) AS GroupMaxDays
    FROM (
        -- 生成连续分组ID：遇到Consec_Days=1时分隔分组
        SELECT 
            *,
            SUM(CASE WHEN Consec_Days = 1 THEN 1 ELSE 0 END) OVER (ORDER BY Date ASC) AS GroupId
        FROM @TestTable
    ) AS GroupedData
    WHERE Consec_Days > 1
    GROUP BY GroupId
) AS GroupMaxResult

逻辑说明 #

• 内层子查询GroupedData用窗口累加函数生成分组ID：每次遇到Consec_Days = 1的行时，分组ID加1，其余连续的Consec_Days > 1的行会被分配到同一个分组ID下
• 中间层子查询过滤掉Consec_Days <= 1的行，按分组ID聚合取每个连续区间的最大Consec_Days，样例中会得到两个最大值：2、3
• 最外层对所有分组的最大值求和，得到最终结果5

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