SQL Server基于双列分组生成连续日期范围的需求求助

这个需求属于SQL中典型的连续相同分组合并问题，核心是识别同一ID下连续的Grouping Col 2分组，而非所有同ID同分组的记录。可以通过窗口函数生成分组标识来实现，具体SQL代码如下：

WITH RankedData AS (
    SELECT 
        ID,
        [Grouping Col 2],
        [Census date],
        -- 按ID全局排序的行号
        ROW_NUMBER() OVER (PARTITION BY ID ORDER BY [Census date]) AS GlobalRowNum,
        -- 按ID+Grouping Col2分组排序的行号
        ROW_NUMBER() OVER (PARTITION BY ID, [Grouping Col 2] ORDER BY [Census date]) AS GroupRowNum
    FROM InputTable -- 替换为你的实际表名
)
SELECT 
    ID,
    [Grouping Col 2],
    MIN([Census date]) AS [Start Date],
    MAX([Census date]) AS [End Date]
FROM RankedData
-- 用两个行号的差值作为连续分组的唯一标识
GROUP BY ID, [Grouping Col 2], (GlobalRowNum - GroupRowNum)
ORDER BY ID, [Start Date];

代码逻辑说明 #

1. 生成行号：
   • GlobalRowNum：在每个ID内，按Census date从小到大生成连续行号，确保记录的顺序正确。
   • GroupRowNum：在每个ID+Grouping Col 2的组合内，按Census date生成行号。
2. 创建连续分组标识：
   当Grouping Col 2的值连续相同时，GlobalRowNum - GroupRowNum的结果保持不变；一旦分组切换（比如从Block-1变到Block-2），这个差值会发生变化，以此区分不同的连续分组。
3. 聚合生成起止日期：
   按ID、Grouping Col 2和差值分组，取每组的最小/最大Census date作为起止日期，最后按ID和起始日期排序，得到目标结果。

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