PostgreSQL如何检测活动时段与非活动时段？

需求背景 #

我有一个存储事件的events表，每条事件包含start_ts（开始时间戳）和end_ts（结束时间戳），数据如下：

需要识别活动时段（由连续事件组成的有事件发生的时间范围）和非活动时段（无事件发生的时间范围），预期输出如下：

附建表与插入数据的SQL：

CREATE TABLE IF NOT EXISTS events(
   id INT PRIMARY KEY,   
   start_ts timestamp NOT NULL,
   end_ts timestamp NOT NULL
);

INSERT INTO events(id,start_ts,end_ts)
VALUES
(1,'2023-07-27 01:02:00','2023-07-27 01:05:00'),
(2,'2023-07-27 01:05:00','2023-07-27 01:07:00'),
(3,'2023-07-27 01:07:00','2023-07-27 01:11:00'),
(4,'2023-07-27 01:11:00','2023-07-27 01:15:00'),
(5,'2023-07-27 01:30:00','2023-07-27 01:35:00'),
(6,'2023-07-27 01:35:00','2023-07-27 01:42:00'),
(7,'2023-07-27 01:45:00','2023-07-27 01:50:00');

实现方案 #

可以通过CTE（公共表表达式）分三步实现需求，完整SQL如下：

WITH active_periods AS (
    -- 第一步：合并连续的活动时段
    SELECT
        MIN(start_ts) AS period_start,
        MAX(end_ts) AS period_end,
        'active' AS status
    FROM (
        SELECT
            start_ts,
            end_ts,
            -- 标记连续事件的分组：当前事件开始时间不等于上一个事件结束时间时，开启新组
            SUM(CASE WHEN start_ts = LAG(end_ts) OVER (ORDER BY start_ts) THEN 0 ELSE 1 END) 
                OVER (ORDER BY start_ts) AS group_id
        FROM events
    ) grouped
    GROUP BY group_id
),
inactive_periods AS (
    -- 第二步：生成非活动时段
    SELECT
        period_end AS period_start,
        LEAD(period_start) OVER (ORDER BY period_end) AS period_end,
        'inactive' AS status
    FROM active_periods
    -- 过滤掉最后一个活动时段（无后续活动时段，无法生成非活动时段）
    WHERE LEAD(period_start) OVER (ORDER BY period_end) IS NOT NULL
),
all_periods AS (
    -- 第三步：合并活动与非活动时段
    SELECT period_start, period_end, status FROM active_periods
    UNION ALL
    SELECT period_start, period_end, status FROM inactive_periods
)
-- 按时间顺序输出结果
SELECT period_start, period_end, status
FROM all_periods
ORDER BY period_start;

逻辑说明 #

1. 合并活动时段：使用LAG()窗口函数获取上一个事件的结束时间，通过判断当前事件的开始时间是否与上一个事件的结束时间衔接，将连续事件划分为同一组，最后按组取最小开始时间和最大结束时间，得到完整的活动时段。
2. 生成非活动时段：使用LEAD()窗口函数获取下一个活动时段的开始时间，以当前活动时段的结束时间作为非活动时段的开始，两个活动时段的间隔即为非活动时段。
3. 合并排序：将活动时段和非活动时段合并后按时间排序，得到最终的时段列表。

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