如何用SQL按时间间隔分组统计BigQuery表的产品订单数量

报错原因 #

Unrecognized name: MINUTE报错源于使用的SQL语法不符合BigQuery标准SQL规范：

1. BigQuery时间函数的时间单位参数需以字符串形式传入，不可直接使用裸常量
2. 原有查询未加入Product字段分组，无法输出每个产品的独立统计结果
3. 固定基准时间差值计算的逻辑冗余，BigQuery原生时间截断函数性能更优、可读性更强

标准实现方案 #

以下代码可直接运行，返回需要的产品、时间区间起始戳、订单量三个字段：

SELECT
  Product 产品,
  TIMESTAMP_TRUNC(Timestamp, MINUTE) 时间区间起始时间戳,
  COUNT(*) 对应订单量
FROM
  `mycompany.engagement.product_orders`
GROUP BY
  Product,
  时间区间起始时间戳
ORDER BY
  时间区间起始时间戳 DESC,
  对应订单量 DESC

任意时间间隔适配方法 #

如果需要调整统计的时间粒度，可使用通用时间对齐逻辑，仅修改interval_minute变量的值即可适配任意分钟间隔：

DECLARE interval_minute INT64 DEFAULT 1; -- 调整该数值修改统计间隔，单位为分钟
SELECT
  Product 产品,
  TIMESTAMP_SECONDS(DIV(UNIX_SECONDS(Timestamp), interval_minute * 60) * interval_minute * 60) 时间区间起始时间戳,
  COUNT(*) 对应订单量
FROM
  `mycompany.engagement.product_orders`
GROUP BY
  Product,
  时间区间起始时间戳
ORDER BY
  时间区间起始时间戳 DESC,
  对应订单量 DESC

特殊场景适配 #

如果表中Timestamp字段为DATETIME类型而非TIMESTAMP类型，将TIMESTAMP_TRUNC替换为DATETIME_TRUNC、TIMESTAMP_SECONDS替换为DATETIME_SEC即可正常运行。

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