BigQuery SQL需求：按规则统计服务错误次数及时间戳

需求规则 #

• ErrorCount：仅统计最后一次Success之后的ERROR数量；若服务最后状态为Success，则该值为0
• FirstFailedTimestamp：最后一次Success之后首次出现ERROR的时间戳，无符合条件的错误则为空
• LastFailedTimestamp：服务最后一次出现ERROR的时间戳，无错误记录则为空

示例数据 #

timestamp           status      Service
2024-06-20 11:00    ERROR       S1
2024-06-20 11:02    Success     S1
2024-06-20 11:03    ERROR       S1
2024-06-20 11:04    ERROR       S1
2024-06-20 11:05    ERROR       S1
2024-06-20 11:06    ERROR       S1
2024-06-20 11:07    ERROR       S1

2024-06-20 11:00    ERROR       S2
2024-06-20 11:01    ERROR       S2

2024-06-20 11:01    ERROR       S3
2024-06-20 11:02    ERROR       S3
2024-06-20 11:03    ERROR       S3
2024-06-20 11:04    ERROR       S3
2024-06-20 11:05    ERROR       S3
2024-06-20 11:06    Success     S3

预期统计结果 #

name    ErrorCount   FirstFailedTimestamp    LastFailedTimestamp
S1      5            2024-06-20 11:03        2024-06-20 11:07
S2      2            2024-06-20 11:00        2024-06-20 11:01
S3      0

原错误SQL #

WITH data AS (
    SELECT TIMESTAMP("2024-06-20 11:00:00") AS timestamp, "ERROR" AS status, "S1" AS service UNION ALL
    SELECT TIMESTAMP("2024-06-20 11:02:00"), "SUCCESS", "S1" UNION ALL
    SELECT TIMESTAMP("2024-06-20 11:03:00"), "ERROR", "S1" UNION ALL
    SELECT TIMESTAMP("2024-06-20 11:04:00"), "ERROR", "S1" UNION ALL
    SELECT TIMESTAMP("2024-06-20 11:05:00"), "ERROR", "S1" UNION ALL
    SELECT TIMESTAMP("2024-06-20 11:06:00"), "ERROR", "S1" UNION ALL
    SELECT TIMESTAMP("2024-06-20 11:07:00"), "ERROR", "S1" UNION ALL

    SELECT TIMESTAMP("2024-06-20 11:00:00"), "ERROR", "S2" UNION ALL
    SELECT TIMESTAMP("2024-06-20 11:01:00"), "ERROR", "S2" UNION ALL

    SELECT TIMESTAMP("2024-06-20 11:01:00"), "ERROR", "S3" UNION ALL
    SELECT TIMESTAMP("2024-06-20 11:02:00"), "ERROR", "S3" UNION ALL
    SELECT TIMESTAMP("2024-06-20 11:03:00"), "ERROR", "S3" UNION ALL
    SELECT TIMESTAMP("2024-06-20 11:04:00"), "ERROR", "S3" UNION ALL
    SELECT TIMESTAMP("2024-06-20 11:05:00"), "ERROR", "S3" UNION ALL
    SELECT TIMESTAMP("2024-06-20 11:06:00"), "SUCCESS", "S3"
)

SELECT
    service AS name,
    CASE WHEN MAX(status) = 'SUCCESS' THEN 0 ELSE COUNTIF(status = 'ERROR' AND SuccessFlag = 1) END AS ErrorCount,
    MIN(IF(status = 'ERROR' AND SuccessFlag = 1, FORMAT_TIMESTAMP('%Y-%m-%d %H:%M:%S', timestamp), NULL)) AS FirstFailedTimestamp,
    MAX(IF(status = 'ERROR', FORMAT_TIMESTAMP('%Y-%m-%d %H:%M:%S', timestamp), NULL)) AS LastFailedTimestamp
FROM (
    SELECT
        service,
        timestamp,
        status,
        MAX(IF(status = 'SUCCESS', 1, 0)) OVER (PARTITION BY service ORDER BY timestamp DESC) AS SuccessFlag
    FROM
        data
)
GROUP BY
    service

修正后的SQL及说明 #

问题分析 #

原SQL核心问题：

1. 用MAX(status) = 'SUCCESS'判断最后状态不准确，字符串比较无法确定是否为最后一条记录的状态
2. SuccessFlag逻辑错误：按timestamp DESC排序时，MAX(...) OVER()会把更早时间的Success也标记为1，导致错误统计最后一次Success之前的ERROR
3. LastFailedTimestamp未过滤最后一次Success之后的错误，不符合需求定义

修正代码 #

WITH data AS (
    SELECT TIMESTAMP("2024-06-20 11:00:00") AS timestamp, "ERROR" AS status, "S1" AS service UNION ALL
    SELECT TIMESTAMP("2024-06-20 11:02:00"), "SUCCESS", "S1" UNION ALL
    SELECT TIMESTAMP("2024-06-20 11:03:00"), "ERROR", "S1" UNION ALL
    SELECT TIMESTAMP("2024-06-20 11:04:00"), "ERROR", "S1" UNION ALL
    SELECT TIMESTAMP("2024-06-20 11:05:00"), "ERROR", "S1" UNION ALL
    SELECT TIMESTAMP("2024-06-20 11:06:00"), "ERROR", "S1" UNION ALL
    SELECT TIMESTAMP("2024-06-20 11:07:00"), "ERROR", "S1" UNION ALL

    SELECT TIMESTAMP("2024-06-20 11:00:00"), "ERROR", "S2" UNION ALL
    SELECT TIMESTAMP("2024-06-20 11:01:00"), "ERROR", "S2" UNION ALL

    SELECT TIMESTAMP("2024-06-20 11:01:00"), "ERROR", "S3" UNION ALL
    SELECT TIMESTAMP("2024-06-20 11:02:00"), "ERROR", "S3" UNION ALL
    SELECT TIMESTAMP("2024-06-20 11:03:00"), "ERROR", "S3" UNION ALL
    SELECT TIMESTAMP("2024-06-20 11:04:00"), "ERROR", "S3" UNION ALL
    SELECT TIMESTAMP("2024-06-20 11:05:00"), "ERROR", "S3" UNION ALL
    SELECT TIMESTAMP("2024-06-20 11:06:00"), "SUCCESS", "S3"
),
service_metadata AS (
    SELECT
        service,
        -- 获取最后一次Success的时间戳
        MAX(IF(status = 'SUCCESS', timestamp, NULL)) OVER (PARTITION BY service) AS last_success_ts,
        -- 获取服务最后一条记录的状态
        LAST_VALUE(status) OVER (PARTITION BY service ORDER BY timestamp ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS final_status,
        timestamp,
        status
    FROM data
)
SELECT
    service AS name,
    -- 若最后状态是Success则为0，否则统计最后一次Success之后的ERROR数量
    CASE WHEN final_status = 'SUCCESS' THEN 0 
         ELSE COUNTIF(status = 'ERROR' AND timestamp > last_success_ts) 
    END AS ErrorCount,
    -- 最后一次Success之后的首次ERROR时间
    MIN(IF(status = 'ERROR' AND timestamp > last_success_ts, FORMAT_TIMESTAMP('%Y-%m-%d %H:%M', timestamp), NULL)) AS FirstFailedTimestamp,
    -- 最后一次ERROR的时间（无论是否在Success之后，符合需求定义）
    MAX(IF(status = 'ERROR', FORMAT_TIMESTAMP('%Y-%m-%d %H:%M', timestamp), NULL)) AS LastFailedTimestamp
FROM service_metadata
GROUP BY service, final_status
ORDER BY name;

关键修正点 #

1. 新增service_metadataCTE，计算每个服务的last_success_ts（最后一次Success的时间）和final_status（最后一条记录的状态）
2. ErrorCount通过timestamp > last_success_ts过滤出最后一次Success之后的ERROR，同时结合final_status判断是否直接返回0
3. FirstFailedTimestamp同样用timestamp > last_success_ts过滤后取最小时间，确保是首次错误
4. 保持LastFailedTimestamp取所有ERROR中的最大时间，符合需求定义

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