求基于给定表及告警规则的BigQuery查询逻辑

原始数据表 #

timestamp           status      name   description  
2024-06-20 11:00    SUCCESS     S1     200
2024-06-20 11:01    ERROR       S1     500
2024-06-20 11:02    ERROR       S1     500
2024-06-20 11:03    ERROR       S1     502
2024-06-20 11:00    ERROR       S2     500  
2024-06-20 11:01    ERROR       S2     505 
2024-06-20 11:02    SUCCESS     S2     200
2024-06-20 11:03    ERROR       S2     506
2024-06-20 11:04    ERROR       S2     507
2024-06-20 11:00    ERROR       S3     500  
2024-06-20 11:01    ERROR       S3     505 
2024-06-20 11:02    SUCCESS     S3     200
2024-06-20 11:00    SUCCESS     S4     200
2024-06-20 11:00    ERROR       S5     400
2024-06-20 11:01    ERROR       S5     400 
2024-06-20 11:02    ERROR       S5     400 
2024-06-20 11:03    ERROR       S5     400 
2024-06-20 11:04    ERROR       S5     400
2024-06-20 11:05    ERROR       S5     400  
2024-06-20 11:00    ERROR       S6     400 
2024-06-20 11:01    ERROR       S6     400

告警规则说明 #

• 场景1：当status为ERROR且description处于500-509区间时，标记AlertType为"High"、isActive为true；若该name的最后一条记录status为SUCCESS且description为200，则标记isActive为False、AlertType为"unknown"。
• 场景2：当status为ERROR且description为400，且连续出现次数超过5次时，标记AlertType为"High"、isActive为True；若ERROR次数≤5次，则isActive为false；同样，若最后一条记录status为SUCCESS且description为200，标记isActive为False、AlertType为"unknown"。

期望查询结果 #

name    AlertType   FirstFailedTimestamp    LastFailedTimestamp   IsActive
S1      High         2024-06-20 11:01       2024-06-20 11:03      True
S2      High         2024-06-20 11:03       2024-06-20 11:04      True
S3      High         2024-06-20 11:00       2024-06-20 11:01      False
S5      High         2024-06-20 11:00       2024-06-20 11:05      True
S6      Unknown      2024-06-20 11:00       2024-06-20 11:01      False

BigQuery 查询逻辑 #

WITH base_data AS (
  SELECT
    timestamp,
    status,
    name,
    description,
    -- 获取每个name的最后一条记录状态和描述
    LAST_VALUE(status) OVER (PARTITION BY name ORDER BY timestamp ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_status,
    LAST_VALUE(description) OVER (PARTITION BY name ORDER BY timestamp ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_description,
    -- 标记符合场景1的错误记录
    CASE WHEN status = 'ERROR' AND description BETWEEN 500 AND 509 THEN 1 ELSE 0 END AS is_scenario1_error,
    -- 标记符合场景2的错误记录
    CASE WHEN status = 'ERROR' AND description = 400 THEN 1 ELSE 0 END AS is_scenario2_error
  FROM `your-project.your-dataset.your-table` -- 替换为你的实际表路径
),
-- 计算每个name的场景1相关统计
scenario1_stats AS (
  SELECT
    name,
    MIN(CASE WHEN is_scenario1_error = 1 THEN timestamp END) AS scenario1_first_fail,
    MAX(CASE WHEN is_scenario1_error = 1 THEN timestamp END) AS scenario1_last_fail,
    COUNT(CASE WHEN is_scenario1_error = 1 THEN 1 END) AS scenario1_error_count
  FROM base_data
  GROUP BY name
),
-- 计算每个name的场景2连续错误次数（分组连续的400错误段）
scenario2_continuous AS (
  SELECT
    name,
    timestamp,
    is_scenario2_error,
    -- 用累计分组标识连续错误段
    SUM(CASE WHEN is_scenario2_error = 1 THEN 0 ELSE 1 END) OVER (PARTITION BY name ORDER BY timestamp) AS error_group
  FROM base_data
),
scenario2_stats AS (
  SELECT
    name,
    MIN(CASE WHEN is_scenario2_error = 1 THEN timestamp END) AS scenario2_first_fail,
    MAX(CASE WHEN is_scenario2_error = 1 THEN timestamp END) AS scenario2_last_fail,
    COUNT(*) AS continuous_error_count
  FROM scenario2_continuous
  GROUP BY name, error_group
  HAVING is_scenario2_error = 1
),
-- 聚合场景2的统计到每个name维度
scenario2_agg AS (
  SELECT
    name,
    MIN(scenario2_first_fail) AS scenario2_first_fail,
    MAX(scenario2_last_fail) AS scenario2_last_fail,
    MAX(continuous_error_count) AS max_continuous_400_errors
  FROM scenario2_stats
  GROUP BY name
),
-- 合并所有统计信息，排除无错误的name（如S4）
combined_stats AS (
  SELECT
    COALESCE(s1.name, s2.name) AS name,
    s1.scenario1_first_fail,
    s1.scenario1_last_fail,
    s1.scenario1_error_count,
    s2.scenario2_first_fail,
    s2.scenario2_last_fail,
    s2.max_continuous_400_errors,
    bd.last_status,
    bd.last_description
  FROM scenario1_stats s1
  FULL OUTER JOIN scenario2_agg s2 ON s1.name = s2.name
  JOIN (SELECT DISTINCT name, last_status, last_description FROM base_data) bd ON COALESCE(s1.name, s2.name) = bd.name
  WHERE COALESCE(s1.scenario1_error_count, 0) > 0 OR COALESCE(s2.max_continuous_400_errors, 0) > 0
)
-- 最终计算告警字段
SELECT
  name,
  CASE
    -- 优先判断最后一条记录是否为SUCCESS 200
    WHEN last_status = 'SUCCESS' AND last_description = 200 THEN 'Unknown'
    -- 场景1存在有效错误
    WHEN scenario1_error_count > 0 THEN 'High'
    -- 场景2连续错误超过5次
    WHEN max_continuous_400_errors > 5 THEN 'High'
    -- 其他情况（场景2错误次数≤5）
    ELSE 'Unknown'
  END AS AlertType,
  CASE
    WHEN last_status = 'SUCCESS' AND last_description = 200 THEN scenario1_first_fail
    WHEN scenario1_error_count > 0 AND (last_status != 'SUCCESS' OR last_description != 200) THEN scenario1_first_fail
    WHEN max_continuous_400_errors > 5 THEN scenario2_first_fail
    ELSE scenario2_first_fail
  END AS FirstFailedTimestamp,
  CASE
    WHEN last_status = 'SUCCESS' AND last_description = 200 THEN scenario1_last_fail
    WHEN scenario1_error_count > 0 AND (last_status != 'SUCCESS' OR last_description != 200) THEN scenario1_last_fail
    WHEN max_continuous_400_errors > 5 THEN scenario2_last_fail
    ELSE scenario2_last_fail
  END AS LastFailedTimestamp,
  CASE
    -- 最后一条是成功则置为False
    WHEN last_status = 'SUCCESS' AND last_description = 200 THEN FALSE
    -- 场景1存在有效错误且未被成功覆盖
    WHEN scenario1_error_count > 0 THEN TRUE
    -- 场景2连续错误超过5次
    WHEN max_continuous_400_errors > 5 THEN TRUE
    -- 其他情况置为False
    ELSE FALSE
  END AS IsActive
FROM combined_stats
ORDER BY name;

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