如何在BigQuery中按小时分组以统计每小时订单数？

在BigQuery中按小时分组统计订单数，核心是通过日期时间函数将时间字段截断到小时维度，再结合分组聚合完成统计，以下是几种常见场景的实现方式：

1. 基于TIMESTAMP类型时间字段的分组 #

如果你的订单时间字段是TIMESTAMP类型，使用DATE_TRUNC函数直接截断到小时：

SELECT
  DATE_TRUNC(order_time, HOUR) AS hour_start,
  COUNT(*) AS order_count
FROM
  `你的项目ID.你的数据集ID.你的表名`
GROUP BY
  hour_start
ORDER BY
  hour_start;

DATE_TRUNC(order_time, HOUR)会将每个订单的时间统一到其所在小时的起始时刻（比如2024-05-20 14:35:22会转为2024-05-20 14:00:00），以此作为分组依据统计每小时的订单量。

2. 自定义小时格式的分组 #

如果需要更易读的小时标签（比如2024-05-20 14:00），可以用FORMAT_TIMESTAMP格式化时间：

SELECT
  FORMAT_TIMESTAMP("%Y-%m-%d %H:00", order_time) AS hour_label,
  COUNT(*) AS order_count
FROM
  `你的项目ID.你的数据集ID.你的表名`
GROUP BY
  hour_label
ORDER BY
  hour_label;

3. 基于DATETIME类型时间字段的分组 #

若时间字段是DATETIME类型，只需将上述函数替换为DATE_TRUNC或FORMAT_DATETIME：

SELECT
  DATE_TRUNC(order_datetime, HOUR) AS hour_start,
  COUNT(*) AS order_count
FROM
  `你的项目ID.你的数据集ID.你的表名`
GROUP BY
  hour_start
ORDER BY
  hour_start;

4. 处理分离的日期和时间字段 #

如果订单的日期和时间是两个独立字段（比如order_date为DATE类型，order_time为TIME类型），先合并为DATETIME再分组：

SELECT
  DATE_TRUNC(DATETIME(order_date, order_time), HOUR) AS hour_start,
  COUNT(*) AS order_count
FROM
  `你的项目ID.你的数据集ID.你的表名`
GROUP BY
  hour_start
ORDER BY
  hour_start;

时区注意事项 #

如果你的数据时区与BigQuery默认时区不同，可在函数中指定时区确保分组准确，比如针对上海时区：

SELECT
  DATE_TRUNC(order_time, HOUR, "Asia/Shanghai") AS hour_start,
  COUNT(*) AS order_count
FROM
  `你的项目ID.你的数据集ID.你的表名`
GROUP BY
  hour_start
ORDER BY
  hour_start;

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