如何在SQL Server中拆分表中的重叠日期区间？

需求说明 #

处理包含重叠或连续起始/结束日期的表，将所有重叠或相邻的日期区间合并为互不重叠的连续区间。

示例输入数据 #

-- 创建示例表并插入数据
CREATE TABLE DateRanges (
    StartDate DATETIME,
    EndDate DATETIME
);

INSERT INTO DateRanges VALUES
('2023-02-01 00:00:00.000', '2023-02-17 00:00:00.000'),
('2023-02-15 00:00:00.000', '2023-02-20 00:00:00.000'),
('2023-02-18 00:00:00.000', '2023-02-22 00:00:00.000'),
('2023-02-23 00:00:00.000', '2023-02-25 00:00:00.000'),
('2023-02-26 00:00:00.000', '2023-03-06 00:00:00.000');

解决方案SQL #

WITH RankedRanges AS (
    -- 按起始日期排序，计算累计最大结束日期，标记分组
    SELECT 
        StartDate,
        EndDate,
        -- 当当前起始日期 > 之前所有区间的最大结束日期时，新建分组
        SUM(CASE WHEN StartDate > COALESCE(LAG(MaxEndDate) OVER (ORDER BY StartDate), '1900-01-01') THEN 1 ELSE 0 END) OVER (ORDER BY StartDate) AS GroupId
    FROM (
        -- 计算每个位置及之前的最大结束日期
        SELECT 
            StartDate,
            EndDate,
            MAX(EndDate) OVER (ORDER BY StartDate ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS MaxEndDate
        FROM DateRanges
    ) t
)
-- 按分组聚合，得到合并后的区间
SELECT 
    MIN(StartDate) AS StartDate,
    MAX(EndDate) AS EndDate
FROM RankedRanges
GROUP BY GroupId
ORDER BY StartDate;

执行结果 #

StartDate                  EndDate
2023-02-01 00:00:00.000   2023-02-22 00:00:00.000
2023-02-23 00:00:00.000   2023-02-25 00:00:00.000
2023-02-26 00:00:00.000   2023-03-06 00:00:00.000

说明 #

1. 内层子查询通过窗口函数MAX(EndDate) OVER (...)计算从第一条到当前行的最大结束日期，用于判断当前区间是否与前面的区间重叠。
2. 中间的RankedRanges CTE通过SUM(...) OVER (...)生成分组ID：当当前区间的起始日期大于之前所有区间的最大结束日期时，说明是新的不重叠区间，分组ID加1。
3. 最后按分组ID聚合，取每组的最小起始日期和最大结束日期，得到合并后的不重叠区间。

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