google-bigquery - 按 T​​imestamp_Trunc 分组，包括计数为“0”的空行

Question

我有一个 BigQuery 查询，如下所示：

SELECT
  timestamp_trunc(timestamp,
    hour) hour,
  statusCode,
  CAST(AVG(durationMs) as integer) averageDurationMs,
  COUNT(*) count
FROM
  `project.dataset.table`
  WHERE timestamp > DATE_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 DAY)
GROUP BY
  hour,
  statusCode

而且效果很好，返回结果如下：

但是，我的图表组件需要空“小时”的空行（例如 18:00 应该是 0、19:00 = 0 等）

在 BigQuery SQL 中是否有一种优雅的方式来执行此操作，或者我是否必须在返回我的 UI 之前在代码中执行此操作？

标签： google-bigquery

Answer 1

尝试生成需要将其与所有状态代码交叉连接的小时数组，并与您的结果一起离开：

with mytable as (
    select timestamp '2021-10-18 19:00:00' as hour, 200 as statusCode, 1234 as averageDurationMs, 25 as count union all
    select '2021-10-18 21:00:00', 500, 4978, 6015 union all 
    select '2021-10-18 21:00:00', 404, 4987, 5984 union all 
    select '2021-10-18 21:00:00', 200, 5048, 11971 union all 
    select '2021-10-18 21:00:00', 401, 4976, 6030
)
select myhour, allCodes.statusCode, IFNULL(mytable.averageDurationMs, 0) as statusCode, IFNULL(mytable.count, 0) as averageDurationMs
from 
    UNNEST(GENERATE_TIMESTAMP_ARRAY(TIMESTAMP_SUB(TIMESTAMP_TRUNC(CURRENT_TIMESTAMP(), HOUR), INTERVAL 23 HOUR), TIMESTAMP_TRUNC(CURRENT_TIMESTAMP(), HOUR), INTERVAL 1 HOUR)) as myhour
CROSS JOIN 
    (SELECT DISTINCT statusCode FROM mytable) as allCodes
LEFT JOIN mytable ON myHour = mytable.hour AND allCodes.statusCode = mytable.statusCode