如何编写PostgreSQL查询提取Error前连续Warning组的首尾数据

需求说明 #

现有test_data表存储日志数据，包含id、message、datetimestamp、server、system字段。需编写查询提取后续紧跟 Error 消息的连续 Warning 消息分组的首尾数据，输出字段如下：

• Start_Datetime：分组首条 Warning 的datetimestamp
• End_Datetime：分组末条 Warning 的datetimestamp
• Server_Start：首条 Warning 的server值
• Server_End：末条 Warning 的server值
• System_Start：首条 Warning 的system值
• System_End：末条 Warning 的system值

若 Warning 分组后未出现 Error 消息，则忽略该分组。

示例数据 #

输入数据（部分） #

输出数据 #

建表与测试数据 SQL #

CREATE TABLE test_data (
  id integer PRIMARY KEY,
  message varchar(10),
  datetimestamp timestamp NOT NULL,
  server varchar(10),
  system varchar(10)
);

INSERT INTO test_data VALUES
  (09, 'Normal' , '2022-07-13 08:59:09', 'Server 1' , 'System 1'),
  (10, 'Normal' , '2022-07-13 08:59:10', 'Server 4' , 'System 2'),
  (11, 'Normal' , '2022-07-13 08:59:11', 'Server 3' , 'System 3'),
  (12, 'Warning', '2022-07-13 08:59:12', 'Server 35', 'System 27'),
  (13, 'Warning', '2022-07-13 08:59:13', 'Server 5' , 'System 5'),
  (14, 'Warning', '2022-07-13 08:59:14', 'Server 9' , 'System 6'),
  (15, 'Warning', '2022-07-13 08:59:15', 'Server 8' , 'System 7'),
  (16, 'Error'  , '2022-07-13 08:59:16', 'Server 12', 'System 8'),
  (17, 'Error'  , '2022-07-13 08:59:17', 'Server 15', 'System 9'),
  (18, 'Warning', '2022-07-13 08:59:18', 'Server 29', 'System 10'),
  (19, 'Warning', '2022-07-13 08:59:19', 'Server 22', 'System 11'),
  (20, 'Warning', '2022-07-13 08:59:20', 'Server 13', 'System 12'),
  (21, 'Normal' , '2022-07-13 08:59:21', 'Server 16', 'System 13'),
  (22, 'Normal' , '2022-07-13 08:59:22', 'Server 19', 'System 14'),
  (23, 'Normal' , '2022-07-13 08:59:23', 'Server 21', 'System 15'),
  (24, 'Warning', '2022-07-13 08:59:24', 'Server 24', 'System 16'),
  (25, 'Warning', '2022-07-13 08:59:25', 'Server 27', 'System 17'),
  (26, 'Warning', '2022-07-13 08:59:26', 'Server 25', 'System 18'),
  (27, 'Error'  , '2022-07-13 08:59:27', 'Server 30', 'System 23'),
  (28, 'Error'  , '2022-07-13 08:59:28', 'Server 31', 'System 20');

解决方案 SQL #

WITH log_groups AS (
    -- 标记连续消息分组：非Warning记录作为分组分隔符
    SELECT 
        *,
        SUM(CASE WHEN message != 'Warning' THEN 1 ELSE 0 END) OVER (ORDER BY datetimestamp) AS group_id
    FROM test_data
),
warning_groups AS (
    -- 聚合Warning分组，并检查后续是否存在Error
    SELECT 
        group_id,
        MIN(datetimestamp) AS start_datetime,
        MAX(datetimestamp) AS end_datetime,
        FIRST_VALUE(server) OVER (PARTITION BY group_id ORDER BY datetimestamp) AS server_start,
        LAST_VALUE(server) OVER (PARTITION BY group_id ORDER BY datetimestamp ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS server_end,
        FIRST_VALUE(system) OVER (PARTITION BY group_id ORDER BY datetimestamp) AS system_start,
        LAST_VALUE(system) OVER (PARTITION BY group_id ORDER BY datetimestamp ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS system_end,
        EXISTS (
            SELECT 1 
            FROM test_data t
            WHERE t.datetimestamp > MAX(log_groups.datetimestamp)
              AND t.message = 'Error'
        ) AS has_following_error
    FROM log_groups
    WHERE message = 'Warning'
    GROUP BY group_id
)
-- 筛选出后续有Error的分组并输出
SELECT DISTINCT
    start_datetime,
    end_datetime,
    server_start,
    server_end,
    system_start,
    system_end
FROM warning_groups
WHERE has_following_error = true
ORDER BY start_datetime;

思路解释 #

1. 分组标记：通过窗口函数SUM() OVER (ORDER BY datetimestamp)，将非Warning记录作为分组分隔点，给连续的Warning记录分配统一的group_id。
2. 分组聚合与校验：对每个Warning分组，计算首尾时间戳、首尾的server和system值；同时通过EXISTS子查询验证分组之后是否存在Error记录。
3. 筛选输出：仅保留后续存在Error的Warning分组，去重后按起始时间排序输出结果。

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