如何筛选无重叠的事件时间区间，实现动态1小时间隔？

原始事件时间区间数据如下：

需求说明 #

每个区间代表一个事件周期，需遵循以下规则筛选事件：

• 选取第一个事件（按开始时间排序的第一条）
• 之后依次选取与前一个选中事件无重叠的下一个最早事件

期望输出 #

最终期望得到的结果如下：

尝试的错误SQL #

以下SQL未得到期望输出：

WITH Intervalle AS (
    SELECT
        start_time,
        end_time,
        LAG(end_time) OVER (ORDER BY start_time) AS previous_end_time
    FROM
        deine_tabelle
)
SELECT
    start_time,
    end_time
FROM
    Intervalle
WHERE
    previous_end_time IS NULL
    OR start_time >= previous_end_time + INTERVAL 1 HOUR
ORDER BY
    start_time;

正确解决方案 #

原SQL的问题在于LAG函数仅能获取紧邻上一行的结束时间，无法追踪上一个被选中事件的结束时间。需要用递归CTE来逐步筛选符合条件的事件：

递归CTE实现代码 #

WITH RECURSIVE SelectedEvents AS (
    -- 初始化：选取按start_time排序后的第一个事件
    SELECT 
        time_start, 
        time_end
    FROM deine_tabelle
    ORDER BY time_start
    LIMIT 1
    
    UNION ALL
    
    -- 递归步骤：找到下一个与上一个选中事件无重叠的最早事件
    SELECT 
        t.time_start, 
        t.time_end
    FROM deine_tabelle t
    JOIN SelectedEvents se 
        ON t.time_start >= se.time_end  -- 无重叠条件：当前事件开始时间 >= 上一个选中事件的结束时间
    WHERE NOT EXISTS (
        -- 确保选取的是符合条件的最早事件，避免跳过更靠前的有效事件
        SELECT 1 
        FROM deine_tabelle t2 
        WHERE t2.time_start >= se.time_end 
          AND t2.time_start < t.time_start
    )
)
SELECT * FROM SelectedEvents ORDER BY time_start;

代码说明 #

1. 初始化部分：按事件开始时间排序，取第一条作为初始选中事件。
2. 递归部分：
   • 关联已选中事件，筛选出与上一个选中事件无重叠的候选事件。
   • 通过NOT EXISTS子句确保每次选取的是符合条件的最早事件，保证筛选顺序符合需求。
3. 最终结果按开始时间排序，得到符合要求的事件序列。

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