如何通过BigQuery计算应用故障恢复时长（Failure Change Rate）

假设你的部署表名为project.dataset.deployment_history，包含以下核心字段：

• app_name: 应用名称（用于多应用分组计算）
• deployment_status: 部署状态（取值为FAILURE或SUCCESS）
• completed_at: 部署完成时间戳

以下语句会按应用分组，找到每个应用最新故障记录对应的下一条成功记录，计算两者的时间差（即故障恢复时长）：

WITH ranked_deployments AS (
  -- 按应用分组，按部署完成时间排序，获取每条故障记录的下一条部署信息
  SELECT
    app_name,
    deployment_status,
    completed_at,
    LEAD(deployment_status) OVER (PARTITION BY app_name ORDER BY completed_at) AS next_status,
    LEAD(completed_at) OVER (PARTITION BY app_name ORDER BY completed_at) AS next_completed_at,
    -- 按时间倒序给故障记录排名，最新故障排第1
    ROW_NUMBER() OVER (
      PARTITION BY app_name
      ORDER BY completed_at DESC
    ) AS failure_rank
  FROM `project.dataset.deployment_history`
  WHERE deployment_status = 'FAILURE'
),
failure_recovery AS (
  -- 筛选出下一条为成功的故障记录，仅保留每个应用的最新故障
  SELECT
    app_name,
    completed_at AS failure_time,
    next_completed_at AS recovery_time,
    -- 计算恢复时长（单位：秒，可替换为MINUTE/HOUR/DAY）
    TIMESTAMP_DIFF(next_completed_at, completed_at, SECOND) AS recovery_duration_seconds
  FROM ranked_deployments
  WHERE next_status = 'SUCCESS'
    AND failure_rank = 1
)
SELECT * FROM failure_recovery
ORDER BY app_name;

关键逻辑说明 #

• LEAD窗口函数: 用于获取同应用下当前故障记录之后的第一条部署状态和时间，确保只计算故障到首次成功的耗时。
• ROW_NUMBER(): 按应用分组对故障记录倒序排名，精准定位每个应用的最新故障。
• 时间差计算: 通过TIMESTAMP_DIFF实现时间差计算，可根据Grafana展示需求切换时间单位。

适配调整建议 #

1. 若部署状态字段取值不同（如failed/succeeded），直接修改WHERE条件中的状态值即可。
2. 若需要保留所有故障的恢复时长，去掉failure_rank = 1的过滤条件。
3. 可使用FORMAT_TIMESTAMP函数将秒数转换为时分秒格式，适配Grafana的可视化需求。

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