请求协助：在MS SQL Server中聚合连续时间间隔为单个间隔

针对连续/重叠的时间间隔合并需求，可通过**Gaps and Islands（间隙与孤岛）**算法实现，以下是具体实现步骤：

核心思路 #

通过窗口函数标记出每个独立的时间“孤岛”（连续/重叠的时间段为同一个孤岛），再按孤岛分组聚合，取每组的最早开始时间和最晚结束时间。

基础实现代码 #

假设你的数据表名为TimeIntervals，包含字段StartDate（开始时间）、EndDate（结束时间）：

WITH IntervalGroups AS (
    SELECT 
        StartDate,
        EndDate,
        -- 生成分组标识：当前记录与上一条不连续则新建分组
        SUM(CASE 
                WHEN LAG(EndDate) OVER (ORDER BY StartDate) >= DATEADD(SECOND, -1, StartDate) 
                THEN 0 
                ELSE 1 
            END) OVER (ORDER BY StartDate) AS GroupID
    FROM TimeIntervals
)
SELECT 
    MIN(StartDate) AS 聚合后开始时间,
    MAX(EndDate) AS 聚合后结束时间
FROM IntervalGroups
GROUP BY GroupID
ORDER BY 聚合后开始时间;

多维度分组场景（如按用户区分） #

如果需要按额外维度（比如UserID）分别聚合时间间隔，只需在窗口函数中添加PARTITION BY：

WITH IntervalGroups AS (
    SELECT 
        UserID,
        StartDate,
        EndDate,
        SUM(CASE 
                WHEN LAG(EndDate) OVER (PARTITION BY UserID ORDER BY StartDate) >= DATEADD(SECOND, -1, StartDate) 
                THEN 0 
                ELSE 1 
            END) OVER (PARTITION BY UserID ORDER BY StartDate) AS GroupID
    FROM TimeIntervals
)
SELECT 
    UserID,
    MIN(StartDate) AS 聚合后开始时间,
    MAX(EndDate) AS 聚合后结束时间
FROM IntervalGroups
GROUP BY UserID, GroupID
ORDER BY UserID, 聚合后开始时间;

代码说明 #

• LAG(EndDate)：获取当前记录的上一条记录的结束时间
• DATEADD(SECOND, -1, StartDate)：处理时间刚好衔接的场景（比如上一条结束于2024-01-01 10:00:00，当前开始于2024-01-01 10:00:00，视为连续）
• SUM(...) OVER (...)：累计生成分组ID，同一连续/重叠区间的记录会被分配到同一个GroupID下
• 最后按GroupID分组，取最小开始时间和最大结束时间，得到合并后的单个时间间隔

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