BigQuery按Timestamp_Trunc分组时如何补全计数为0的空小时行

完全可以通过BigQuery原生SQL优雅实现，不需要额外在UI层做代码处理，核心思路是先生成覆盖目标时段的完整小时维度序列，再和原有统计结果做左连接补全空缺值。

实现逻辑 #

1. 用BigQuery内置的GENERATE_TIMESTAMP_ARRAY函数生成你需要的完整小时序列
2. 如果需要按statusCode拆分维度，先拿到所有需要覆盖的statusCode取值，和小时序列做笛卡尔积得到所有「小时+statusCode」的全量组合
3. 将全量维度表和你原有统计结果左连接，空缺的count字段用IFNULL填充为0即可

完整实现代码 #

WITH
-- 生成过去24小时的完整小时序列，时间范围可按需调整
hour_series AS (
  SELECT hour
  FROM UNNEST(GENERATE_TIMESTAMP_ARRAY(
    DATE_TRUNC(DATE_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 DAY), HOUR),
    DATE_TRUNC(CURRENT_TIMESTAMP(), HOUR),
    INTERVAL 1 HOUR
  )) AS hour
),
-- 提取所有需要覆盖的statusCode，若已知固定取值可直接手动枚举无需查表
distinct_status AS (
  SELECT DISTINCT statusCode
  FROM `project.dataset.table`
  WHERE timestamp > DATE_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 DAY)
  -- 固定枚举写法示例：FROM UNNEST([200, 400, 404, 500]) AS statusCode
),
-- 生成小时+statusCode的全量维度组合，若不需要按statusCode拆分可跳过此CTE直接用hour_series
full_dimensions AS (
  SELECT h.hour, s.statusCode
  FROM hour_series h
  CROSS JOIN distinct_status s
),
-- 你原有统计逻辑保持不变
original_stats AS (
  SELECT
    TIMESTAMP_TRUNC(timestamp, HOUR) hour,
    statusCode,
    CAST(AVG(durationMs) AS INT64) averageDurationMs,
    COUNT(*) count
  FROM `project.dataset.table`
  WHERE timestamp > DATE_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 DAY)
  GROUP BY hour, statusCode
)
-- 左连接补全空缺值
SELECT
  f.hour,
  f.statusCode,
  -- 无数据时段的平均时长可按需填0或保留NULL
  IFNULL(o.averageDurationMs, 0) averageDurationMs,
  IFNULL(o.count, 0) count
FROM full_dimensions f
LEFT JOIN original_stats o
ON f.hour = o.hour AND f.statusCode = o.statusCode
ORDER BY f.hour, f.statusCode

可选调整项 #

• 如果不需要按statusCode拆分维度，只需要每个小时生成一行总统计值，删掉distinct_status和full_dimensions的CROSS JOIN逻辑，直接用hour_series左连original_stats，关联条件仅保留f.hour = o.hour即可
• GENERATE_TIMESTAMP_ARRAY的时间范围可按需调整，比如要统计自然日数据可以将起始时间改为对应日期的零点
• 无数据时段的averageDurationMs如果不需要填0，直接移除IFNULL包裹保留NULL即可

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