如何使用SQL高效实现重叠时间区间的数据压缩合并

问题说明 #

待处理表包含id、start_time、end_time、SomeField四个字段，样例共5条数据：

• SomeField值为A的3条记录（id1-3）时间区间互相重叠
• SomeField值为B的2条记录（id4-5）时间区间存在明确间隔
  处理要求：同一SomeField维度下，相互重叠的时间区间合并为单条记录，存在时间间隔的区间独立保留，最终返回Start_time、End_time、SomeField三个字段。

避坑提示：直接按SomeField分组取min(start_time)、max(end_time)的写法会错误合并B类存在间隔的记录，不符合预期。正确预期结果共3条：A类重叠区间合并为1条，B类2条间隔记录独立保留。

实现逻辑 #

核心是给同维度下的连续重叠区间打统一分组标记：

1. 按SomeField分区，以start_time升序排列所有记录
2. 逐行判断当前记录的开始时间是否大于同分区内当前行之前所有记录的最大结束时间，若是则标记为区间断点
3. 累计断点值作为连续重叠区间的分组ID，最终按SomeField+分组ID聚合，取组内最小开始时间、最大结束时间即可

可运行代码（支持窗口函数的数据库通用，以MySQL 8.0+为例） #

WITH sorted_with_gap_flag AS (
    SELECT
        SomeField,
        start_time,
        end_time,
        -- 标记当前行是否和之前的连续区间断开
        CASE
            WHEN start_time > MAX(end_time) OVER (
                PARTITION BY SomeField
                ORDER BY start_time
                ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
            ) THEN 1
            ELSE 0
        END AS is_new_interval
    FROM your_origin_table
),
marked_with_group_id AS (
    SELECT
        SomeField,
        start_time,
        end_time,
        -- 累计断开标记，生成连续重叠区间的唯一分组ID
        SUM(is_new_interval) OVER (
            PARTITION BY SomeField
            ORDER BY start_time
        ) AS continuous_group_id
    FROM sorted_with_gap_flag
)
-- 按分组聚合得到合并后的区间
SELECT
    MIN(start_time) AS Start_time,
    MAX(end_time) AS End_time,
    SomeField
FROM marked_with_group_id
GROUP BY SomeField, continuous_group_id
ORDER BY SomeField, Start_time;

逻辑说明 #

该写法不会被区间嵌套、多段重叠的场景干扰：判断断点时取的是同维度下当前行之前所有记录的最大结束时间，而非仅上一条记录的结束时间，能覆盖所有重叠场景，同时自动识别间隔拆分独立区间，完全匹配需求。

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