SQL Server中重叠延迟记录的时长精准计算方案问询

问题背景 #

需要针对每个延迟类型（DELAY字段）计算无重叠、无间隔的总延迟时长（单位：小时），涉及百万级数据，必须保证计算效率和精度。

示例数据 #

DELAY    STARTDATE           ENDDATE
Delay1   16-12-2023 08:36:00 24-01-2024 17:29:00
Delay1   18-12-2023 15:46:00 24-01-2024 17:29:00
Delay1   18-12-2023 15:46:00 25-01-2024 04:46:00
Delay1   18-12-2023 15:46:00 25-01-2024 04:52:00
Delay1   24-01-2024 17:29:00 25-01-2024 20:00:00
Delay1   28-01-2024 08:00:00 29-01-2024 02:00:00
Delay2   23-12-2023 00:55:00 24-01-2024 20:00:00
Delay2   23-12-2023 00:55:00 26-12-2023 16:00:00
Delay2   26-12-2023 16:00:00 24-01-2024 20:00:00
Delay2   24-01-2024 20:00:00 25-01-2024 20:00:00

预期结果 #

DELAY   TotalDelayHours  Breakdown
Delay1  990              972+18
Delay2  812              

解决方案（高效SQL实现） #

针对百万级数据，采用窗口函数合并重叠区间的方案，避免低效的自连接操作，时间复杂度为O(n log n)，性能更优。

SQL代码 #

WITH MergedIntervals AS (
    -- 标记每个区间是否为新的独立区间（非重叠）
    SELECT 
        DELAY,
        -- 若日期是字符串类型，用CONVERT转换为datetime（格式105对应dd-mm-yyyy）
        CONVERT(datetime, STARTDATE, 105) AS STARTDATE,
        CONVERT(datetime, ENDDATE, 105) AS ENDDATE,
        CASE 
            WHEN CONVERT(datetime, STARTDATE, 105) <= LAG(CONVERT(datetime, ENDDATE, 105)) OVER (PARTITION BY DELAY ORDER BY CONVERT(datetime, STARTDATE, 105))
            THEN 0  -- 与上一个区间重叠，不属于新组
            ELSE 1  -- 新的独立区间，标记为组起始
        END AS IsNewInterval
    FROM DelayRecords  -- 替换为你的表名
),
IntervalGroups AS (
    -- 给每个合并后的区间分配唯一组ID
    SELECT 
        DELAY,
        STARTDATE,
        ENDDATE,
        SUM(IsNewInterval) OVER (PARTITION BY DELAY ORDER BY STARTDATE ROWS UNBOUNDED PRECEDING) AS GroupID
    FROM MergedIntervals
),
CombinedIntervals AS (
    -- 合并同一组内的重叠区间，取最早开始、最晚结束时间
    SELECT 
        DELAY,
        MIN(STARTDATE) AS IntervalStart,
        MAX(ENDDATE) AS IntervalEnd
    FROM IntervalGroups
    GROUP BY DELAY, GroupID
)
-- 计算总延迟时长（精确到小时）
SELECT 
    DELAY,
    -- 用秒计算后转小时，避免直接用HOUR截断导致的精度丢失
    ROUND(SUM(DATEDIFF(second, IntervalStart, IntervalEnd) / 3600.0), 0) AS TotalDelayHours,
    -- 可选：展示各合并区间的时长明细
    STRING_AGG(CONCAT(ROUND(DATEDIFF(second, IntervalStart, IntervalEnd) / 3600.0, 0)), '+') AS Breakdown
FROM CombinedIntervals
GROUP BY DELAY;

代码说明 #

1. MergedIntervals：按延迟类型分组、按开始时间排序，用LAG函数对比当前区间与上一个区间的结束时间，标记是否为新的独立区间。
2. IntervalGroups：通过累计求和IsNewInterval，将重叠的区间归为同一组，非重叠区间分配新组ID。
3. CombinedIntervals：按组合并区间，得到每个延迟类型下的所有无重叠连续区间。
4. 最终汇总：用DATEDIFF(second)计算精确时长后转换为小时，再求和得到总时长，保证精度。

性能优化说明 #

• 窗口函数的排序操作是主要性能消耗，建议给DELAY和STARTDATE字段建立联合索引：

  CREATE NONCLUSTERED INDEX IX_DelayRecords_Delay_StartDate ON DelayRecords(DELAY, STARTDATE) INCLUDE(ENDDATE);
  

• 若日期字段已存储为datetime类型，可去掉CONVERT转换，进一步提升性能。

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