如何构建时间序列并计算事件报告的30天滚动平均MTTR

这个滚动MTTR的需求很典型，我来一步步帮你搞定它：

核心思路拆解 #

要实现你要的结果，我们需要做两件关键的事：

1. 生成从最早事件启动时间到当前时间的连续每小时时间序列
2. 对每个小时时间点，计算该点过去30天内符合条件事件的平均解决耗时（MTTR）

步骤1：生成连续的每小时时间序列 #

首先得构建一个覆盖目标时间范围的每小时时间戳列表，不同SQL引擎的实现方式略有不同：

BigQuery 实现 #

用GENERATE_TIMESTAMP_ARRAY生成时间数组，再展开成单独的行：

WITH hourly_timestamps AS (
  SELECT 
    timestamp AS datetime
  FROM 
    UNNEST(GENERATE_TIMESTAMP_ARRAY(
      (SELECT MIN(start_datetime) FROM incidents), -- 起始点：最早事件的启动时间
      CURRENT_TIMESTAMP(), -- 结束点：当前时间
      INTERVAL 1 HOUR -- 时间间隔：每小时
    )) AS timestamp
)

PostgreSQL 实现 #

用generate_series函数直接生成连续时间序列：

WITH hourly_timestamps AS (
  SELECT 
    generate_series(
      (SELECT MIN(start_datetime) FROM incidents),
      CURRENT_TIMESTAMP,
      '1 hour'::interval
    ) AS datetime
)

步骤2：关联事件数据计算滚动30天MTTR #

接下来我们把时间序列和事件数据关联，对每个时间点计算过去30天的平均解决耗时。这里沿用了你原查询的逻辑：统计当前时间点过去30天内启动的事件的MTTR。

完整BigQuery SQL #

WITH hourly_timestamps AS (
  SELECT 
    timestamp AS datetime
  FROM 
    UNNEST(GENERATE_TIMESTAMP_ARRAY(
      (SELECT MIN(start_datetime) FROM incidents),
      CURRENT_TIMESTAMP(),
      INTERVAL 1 HOUR
    )) AS timestamp
),
incident_metrics AS (
  SELECT
    incident_id,
    start_datetime,
    -- 把事件解决耗时转换成小时
    DATETIME_DIFF(end_datetime, start_datetime, SECOND) / 3600 AS resolve_time_hours
  FROM incidents
)
SELECT
  ht.datetime,
  -- 计算滚动30天的平均MTTR
  AVG(im.resolve_time_hours) AS mttr_last30days_in_hours
FROM hourly_timestamps ht
LEFT JOIN incident_metrics im
  -- 筛选当前时间点过去30天内启动的事件
  ON im.start_datetime BETWEEN DATETIME_SUB(ht.datetime, INTERVAL 30 DAY) AND ht.datetime
GROUP BY ht.datetime
ORDER BY ht.datetime;

完整PostgreSQL SQL #

WITH hourly_timestamps AS (
  SELECT 
    generate_series(
      (SELECT MIN(start_datetime) FROM incidents),
      CURRENT_TIMESTAMP,
      '1 hour'::interval
    ) AS datetime
),
incident_metrics AS (
  SELECT
    incident_id,
    start_datetime,
    -- 把事件解决耗时转换成小时
    EXTRACT(EPOCH FROM (end_datetime - start_datetime)) / 3600 AS resolve_time_hours
  FROM incidents
)
SELECT
  ht.datetime,
  -- 计算滚动30天的平均MTTR
  AVG(im.resolve_time_hours) AS mttr_last30days_in_hours
FROM hourly_timestamps ht
LEFT JOIN incident_metrics im
  -- 筛选当前时间点过去30天内启动的事件
  ON im.start_datetime >= (ht.datetime - INTERVAL '30 days') 
  AND im.start_datetime <= ht.datetime
GROUP BY ht.datetime
ORDER BY ht.datetime;

几个关键细节说明 #

• 左连接的作用：确保即使某个小时没有符合条件的事件，依然会返回该时间点（此时MTTR会是NULL）。如果需要把NULL替换成0，可以用COALESCE(AVG(im.resolve_time_hours), 0)。
• 时间窗口调整：如果你需要统计的是过去30天内完成的事件，只需要把关联条件里的start_datetime改成end_datetime即可。
• 耗时单位转换：我们把时间差转换成秒再除以3600，确保结果以小时为单位，和你期望的输出格式一致。

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