MySQL 8+中如何拆分事件表为重叠与非重叠区间？

问题背景 #

现有事件表 events(id, start, end)，需将表中事件的时间线拆分为所有连续且不重叠的区间（包括图示中的I1至I5）：

I1    I2    I3     I4       I5
EVENT 1    <---|-----|----|-------->
EVENT 2              <----|--------|------->
EVENT 3        <-----|---|>

当前使用的SQL仅能查询事件的重叠部分，无法获取非重叠区间：

SELECT greatest(E1.start, E2.start) as O1, least(E1.end, E2.end) as O2
FROM events E1, events E2
WHERE E1.id <> E2.id
HAVING O1 < O2

需基于MySQL 8+实现能返回全部目标区间的查询。

解决方案 #

通过提取所有事件的起止时间点，排序后配对生成完整区间：

WITH all_time_points AS (
    -- 收集所有事件的开始和结束时间点，自动去重
    SELECT start AS point FROM events
    UNION
    SELECT end AS point FROM events
),
sorted_points AS (
    -- 对时间点按顺序编号
    SELECT point, ROW_NUMBER() OVER (ORDER BY point) AS rn
    FROM all_time_points
)
-- 将每个时间点与下一个时间点配对，生成有效区间
SELECT 
    p1.point AS interval_start,
    p2.point AS interval_end
FROM sorted_points p1
JOIN sorted_points p2 ON p1.rn = p2.rn - 1
WHERE p1.point < p2.point;

逻辑说明 #

1. all_time_points：收集所有事件的start和end时间，利用UNION自动去重；
2. sorted_points：给去重后的时间点按时间顺序分配行号；
3. 自连接配对相邻时间点，过滤掉时间相同的无效区间，最终得到所有连续的目标区间。

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