基于PostgreSQL提取后续含Error的Warning时段起止时间的查询需求

需求分析 #

从test_data表中仅提取后续直接紧跟Error状态的连续Warning时段的起止时间，忽略那些后续没有Error或中间插入其他状态的Warning时段。

实现SQL查询 #

WITH status_groups AS (
    -- 为连续相同状态的记录分配分组ID，重点标记连续的Warning组
    SELECT
        id,
        message,
        datetimestamp,
        SUM(
            CASE 
                WHEN message = 'Warning' 
                     AND LAG(message) OVER (ORDER BY datetimestamp) = 'Warning' 
                THEN 0 
                ELSE 1 
            END
        ) OVER (ORDER BY datetimestamp) AS group_id
    FROM test_data
),
warning_blocks AS (
    -- 筛选出所有Warning分组，并计算每组的起止时间
    SELECT
        group_id,
        MIN(datetimestamp) AS start_time,
        MAX(datetimestamp) AS end_time
    FROM status_groups
    WHERE message = 'Warning'
    GROUP BY group_id
),
block_next_status AS (
    -- 获取每个Warning分组结束后，最早出现的下一条记录的状态
    SELECT
        start_time,
        end_time,
        (
            SELECT message 
            FROM test_data 
            WHERE datetimestamp > w.end_time 
            ORDER BY datetimestamp ASC 
            LIMIT 1
        ) AS next_status
    FROM warning_blocks w
)
-- 仅保留后续紧跟Error的Warning时段
SELECT start_time, end_time
FROM block_next_status
WHERE next_status = 'Error'
ORDER BY start_time;

代码解释 #

1. status_groups CTE：

   • 使用LAG()窗口函数获取前一条记录的状态，结合累加函数SUM() OVER()为连续的Warning记录分配相同的group_id，非Warning记录会被单独分组（后续会被过滤）。

2. warning_blocks CTE：

   • 筛选出所有Warning分组，通过MIN()和MAX()计算每个连续Warning时段的开始和结束时间。

3. block_next_status CTE：

   • 对每个Warning分组，查询其结束时间之后最早出现的记录状态，以此判断该Warning时段是否紧跟Error。

4. 最终查询：

   • 筛选出next_status为Error的记录，得到符合要求的Warning时段。

测试示例 #

建表语句

CREATE TABLE test_data (
  id integer PRIMARY KEY,
  message VARCHAR(10),
  datetimestamp timestamp without time zone NOT NULL
);

插入测试数据（需先设置datestyle = 'DMY'）

INSERT INTO test_data VALUES
(1, 'Normal', '13/07/2022 08:59:10'),
(2, 'Warning', '13/07/2022 08:59:12'),
(3, 'Warning', '13/07/2022 08:59:15'),
(4, 'Error', '13/07/2022 08:59:16'),
(5, 'Warning', '13/07/2022 08:59:18'),
(6, 'Warning', '13/07/2022 08:59:20'),
(7, 'Normal', '13/07/2022 08:59:22'),
(8, 'Warning', '13/07/2022 08:59:24'),
(9, 'Warning', '13/07/2022 08:59:26'),
(10, 'Error', '13/07/2022 08:59:27');

查询结果

start_time       |        end_time
-----------------------+-----------------------
 2022-07-13 08:59:12   | 2022-07-13 08:59:15
 2022-07-13 08:59:24   | 2022-07-13 08:59:26

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