如何链式合并重叠时间周期？SQL实现方案咨询

问题背景 #

现有四个时间周期a、b、c、d，重叠关系如下：

• a与b重叠
• b与c重叠
• a与c不重叠
• d与a、b、c均不重叠

需求：将所有可链式连接的时间周期合并为单个时间周期（哪怕是间接关联的，比如a、b、c），同时保留完全独立的周期（比如d），不能直接全局使用MIN()/MAX()聚合。

当前尝试代码 #

DROP TABLE IF EXISTS #test
CREATE TABLE 
    #test (id VARCHAR, start_time DATETIME2, end_time DATETIME2)
INSERT INTO
    #test
VALUES 
    ('a', '2024-08-03 12:35:37', '2024-08-07 12:36:42'),
    ('b', '2024-08-06 18:35:53', '2024-08-10 13:12:50'),
    ('c', '2024-08-09 12:40:13', '2024-08-11 00:00:00'),
    ('d', '2024-01-09 12:40:13', '2024-02-11 00:00:00');


WITH overlaps AS (
    SELECT
        a.id,
        a.start_time AS orig_start,
        a.end_time AS orig_end,
        LEAST(a.start_time, b.start_time) AS overlap_start,
        GREATEST(a.end_time, b.end_time) AS overlap_end
    FROM
        #test AS a
    LEFT JOIN
        #test AS b
            ON b.id != a.id -- Don't overlap with self
            AND GREATEST(a.start_time, b.start_time) < LEAST(a.end_time, b.end_time) -- catch any overlap time periods
),
merged AS (
    SELECT
        a.id,
        a.orig_start,
        a.orig_end,
        MIN(overlap_start) AS merged_start,
        MAX(overlap_end) AS merged_end
    FROM
        overlaps AS a
    GROUP BY
        a.id,
        a.orig_start,
        a.orig_end
)
SELECT DISTINCT
    id,
    merged_start,
    merged_end
FROM
    merged
ORDER BY
    merged_start;

问题分析 #

当前代码仅能处理直接重叠的周期对，无法识别链式传递的关联（比如a和c通过b间接关联），且最终结果仍保留了每个原始周期的记录，达不到合并成两个大周期的目标。

正确解法 #

使用窗口函数标记分组，再按分组聚合的方式，可完美处理链式重叠的场景：

DROP TABLE IF EXISTS #test
CREATE TABLE 
    #test (id VARCHAR, start_time DATETIME2, end_time DATETIME2)
INSERT INTO
    #test
VALUES 
    ('a', '2024-08-03 12:35:37', '2024-08-07 12:36:42'),
    ('b', '2024-08-06 18:35:53', '2024-08-10 13:12:50'),
    ('c', '2024-08-09 12:40:13', '2024-08-11 00:00:00'),
    ('d', '2024-01-09 12:40:13', '2024-02-11 00:00:00');

WITH sorted_intervals AS (
    -- 按开始时间排序，获取上一个区间的结束时间
    SELECT 
        start_time, 
        end_time,
        LAG(end_time) OVER (ORDER BY start_time) AS prev_end
    FROM #test
),
grouped_intervals AS (
    -- 标记每个区间所属的分组：当前区间开始时间 > 上一个区间结束时间则为新分组
    SELECT 
        start_time, 
        end_time,
        SUM(CASE WHEN start_time > prev_end THEN 1 ELSE 0 END) OVER (ORDER BY start_time) AS group_id
    FROM sorted_intervals
),
adjusted_groups AS (
    -- 处理第一个区间的NULL值，确保分组ID从0开始
    SELECT 
        start_time, 
        end_time,
        COALESCE(group_id, 0) AS group_id
    FROM grouped_intervals
)
-- 按分组聚合，得到合并后的时间周期
SELECT 
    ROW_NUMBER() OVER (ORDER BY MIN(start_time)) AS rn,
    MIN(start_time) AS start_time,
    MAX(end_time) AS end_time
FROM adjusted_groups
GROUP BY group_id
ORDER BY rn;

执行结果 #

rn | start_time               | end_time
---|--------------------------|--------------------------
1  | 2024-01-09 12:40:13.000 | 2024-02-11 00:00:00.000
2  | 2024-08-03 12:35:37.000 | 2024-08-11 00:00:00.000

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