SQL Server 2016 T-SQL：按属性分组获取连续日期对应的起止范围

解决思路 #

这是典型的连续序列分组（岛屿问题），直接按ID、Name、City聚合会把同一个城市非连续的记录合并，所以需要先给同一用户下连续相同的城市打上唯一分组标记：

• 先对每个用户（按ID+Name分组）按日期升序排序得到全局行号
• 再对每个用户下的每个城市按日期升序排序得到城市内行号
• 两者的差值就是连续相同城市的唯一分组标记，同一个连续城市段的差值固定不变
• 最后按分组标记聚合取最小、最大日期即可得到每个连续城市段的起止日期

实现代码 #

WITH CTE AS (
    SELECT 
        ID,
        Name,
        City,
        ContinuousDates,
        -- 计算分组标记：同用户全局行号 - 同用户同城市行号，连续同城市差值一致
        ROW_NUMBER() OVER(PARTITION BY ID, Name ORDER BY ContinuousDates) 
        - ROW_NUMBER() OVER(PARTITION BY ID, Name, City ORDER BY ContinuousDates) AS GroupID
    FROM #dataset
)
SELECT 
    ID,
    Name,
    City,
    MIN(ContinuousDates) AS StartDate,
    MAX(ContinuousDates) AS EndDate
FROM CTE
GROUP BY ID, Name, City, GroupID
ORDER BY ID, StartDate

兼容说明 #

• 支持多用户场景，自动按ID、Name隔离不同用户的记录
• 自动处理City为NULL的情况，空值也会被视为同一分组
• 非连续出现的同一个城市会被拆分为独立的日期段，完全匹配预期输出

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