SQL Server中按优先级和类型合并重叠时间区间

需求：按type字段分组处理时间区间，高优先级区间需覆盖低优先级区间，不同type的区间完全独立、不可混合。

测试数据 #

declare @t table (type int, priority int,  start_date  datetime,  end_date datetime);

insert into @t select 1, 1, '2023-01-01', '2023-01-10'
insert into @t select 1, 1, '2023-01-08', '2023-01-14'
insert into @t select 1, 2, '2023-01-12', '2023-01-20'
insert into @t select 1, 1, '2023-01-17', '2023-01-22'
insert into @t select 1, 3, '2023-01-18', '2023-01-25'
insert into @t select 1, 2, '2023-01-23', '2023-02-05'
insert into @t select 1, 3, '2023-02-07', '2023-02-15'
insert into @t select 1, 1, '2023-02-10', NULL

insert into @t select 2, 1, '2023-01-01', '2023-01-20'
insert into @t select 2, 2, '2023-01-10', '2023-01-15'
insert into @t select 2, 3, '2023-01-18', NULL;

预期结果 #

Type  Prio    Dates
    1  |  1  |  2023-01-01 - 2023-01-11 
    1  |  2  |  2023-01-12 - 2023-01-17 
    1  |  3  |  2023-01-18 - 2023-01-25 
    1  |  2  |  2023-01-26 - 2023-02-05 
    1  |  3  |  2023-02-07 - 2023-02-15 
    1  |  1  |  2023-02-16 - NULL       
    
    2  |  1  |  2023-01-01 - 2023-01-09 
    2  |  2  |  2023-01-10 - 2023-01-15 
    2  |  1  |  2023-01-16 - 2023-01-17 
    2  |  3  |  2023-01-18 - NULL

现有代码（仅按类型合并，未考虑优先级） #

with STARTs as 
( 
    select distinct type, start_date 
    from @t as t1 
    where not exists (select * from @t as t2 
                      where t2.type = t1.type 
                        and t2.start_date < t1.start_date 
                        and t2.end_date >= t1.start_date) 
), 
ENDs as 
( 
    select distinct type, end_date 
    from @t as t1 
    where not exists (select * from @t as t2 
                      where t2.type = t1.type 
                        and t2.end_date > t1.end_date 
                        and t2.start_date <= t1.end_date) 
) 
select 
    type, start_date, 
    (select min(end_date) 
     from ENDs as e 
     where e.type = s.type 
       and end_date >= start_date) as end_date 
from 
    STARTs as s;

解决方案（支持类型分组+优先级覆盖） #

思路：先按type分区提取所有时间节点，拆分出最小时间区间后判断每个区间的最高优先级，最后合并连续的同优先级区间。

WITH AllTimePoints AS (
    -- 提取所有类型下的时间节点，将NULL替换为极大值处理永久区间
    SELECT type, start_date AS point_date FROM @t
    UNION
    SELECT type, end_date AS point_date FROM @t
    UNION
    SELECT type, '9999-12-31' AS point_date FROM @t WHERE end_date IS NULL
),
OrderedPoints AS (
    -- 对每个类型的时间节点去重、排序，生成相邻时间点的区间
    SELECT 
        type, 
        point_date,
        LEAD(point_date) OVER (PARTITION BY type ORDER BY point_date) AS next_point
    FROM (SELECT DISTINCT type, point_date FROM AllTimePoints) t
    WHERE point_date IS NOT NULL
),
IntervalPriority AS (
    -- 计算每个最小时间区间内的最高优先级
    SELECT 
        op.type,
        op.point_date AS interval_start,
        CASE WHEN op.next_point = '9999-12-31' THEN NULL ELSE op.next_point END AS interval_end,
        MAX(t.priority) AS max_priority
    FROM OrderedPoints op
    LEFT JOIN @t t 
        ON op.type = t.type
        AND (
            (t.start_date <= op.point_date AND (t.end_date >= op.next_point OR t.end_date IS NULL))
            OR (t.start_date < op.next_point AND t.end_date IS NULL)
            OR (t.start_date <= op.point_date AND t.end_date > op.point_date)
        )
    GROUP BY op.type, op.point_date, op.next_point
),
GroupedIntervals AS (
    -- 标记连续同优先级的区间分组，用于合并
    SELECT 
        type,
        max_priority,
        interval_start,
        interval_end,
        SUM(CASE WHEN prev_priority = max_priority THEN 0 ELSE 1 END) OVER (PARTITION BY type ORDER BY interval_start) AS group_id
    FROM (
        SELECT 
            type,
            max_priority,
            interval_start,
            interval_end,
            LAG(max_priority) OVER (PARTITION BY type ORDER BY interval_start) AS prev_priority
        FROM IntervalPriority
        WHERE interval_start < COALESCE(interval_end, '9999-12-31') -- 过滤无效区间
    ) t
)
SELECT 
    type AS Type,
    max_priority AS Prio,
    CONVERT(VARCHAR(10), interval_start, 23) + ' - ' + COALESCE(CONVERT(VARCHAR(10), interval_end, 23), 'NULL') AS Dates
FROM (
    SELECT 
        type,
        max_priority,
        MIN(interval_start) AS interval_start,
        MAX(interval_end) AS interval_end
    FROM GroupedIntervals
    GROUP BY type, max_priority, group_id
) t
ORDER BY type, interval_start;

步骤说明 #

1. AllTimePoints：收集所有有效时间节点，包括处理NULL的占位极大值
2. OrderedPoints：生成每个类型下的相邻时间点区间，作为最小处理单元
3. IntervalPriority：为每个最小区间计算覆盖它的最高优先级
4. GroupedIntervals：通过标记分组，识别连续的同优先级区间
5. 最终合并同组区间，格式化输出符合要求的日期字符串

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