如何在PostgreSQL中合并连续相同状态的时间区间

要实现将连续相同状态的记录合并为包含状态、开始时间、结束时间的结果表，可以借助PostgreSQL的窗口函数来分组连续的同状态记录，具体实现步骤如下：

1. 测试表准备（已有表可跳过） #

假设原始表名为status_log，先创建表并插入测试数据：

CREATE TABLE status_log (
    status VARCHAR(10),
    date TIMESTAMP
);

INSERT INTO status_log VALUES
('ok', '2023-05-01 00:00:00'),
('ok', '2023-05-01 00:01:00'),
('ok', '2023-05-01 00:02:00'),
('no', '2023-05-01 00:03:00'),
('no', '2023-05-01 00:04:00'),
('ok', '2023-05-01 00:04:00'),
('ok', '2023-05-01 00:05:00');

2. 核心查询语句 #

通过窗口函数生成连续同状态的分组标识，再聚合每组的时间范围：

SELECT
    status,
    MIN(date) AS start,
    MAX(date) AS end
FROM (
    SELECT
        status,
        date,
        -- 计算分组ID：全局行号与按状态分组行号的差值，连续同状态的行差值一致
        ROW_NUMBER() OVER (ORDER BY date) - 
        ROW_NUMBER() OVER (PARTITION BY status ORDER BY date) AS group_id
    FROM status_log
) t
GROUP BY status, group_id
ORDER BY start;

3. 查询逻辑说明 #

• 内层查询中：
  • ROW_NUMBER() OVER (ORDER BY date)：生成按时间顺序排列的全局行号，确保记录按时间顺序处理。
  • ROW_NUMBER() OVER (PARTITION BY status ORDER BY date)：按status分组后生成组内行号。
  • 两者的差值作为group_id：连续相同status的记录，该差值保持不变；当status变化时，差值会跳变，以此区分不同的连续状态段。
• 外层查询按status和group_id分组，取每组的最小时间作为start，最大时间作为end，最后按start排序得到最终结果。

执行结果 #

执行上述查询后，将得到目标结果：

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