如何在Big Query中按分钟统计事件发生次数

核心思路 #

使用BigQuery内置的DATE_TRUNC函数将UTC时间戳截断到分钟粒度，再按截断后的时间维度分组聚合计数即可。 #

场景1：时间字段为TIMESTAMP类型（大多数建表时的标准类型） #

统计全量事件每分钟总发生次数

SELECT
  DATE_TRUNC(`Timestamp in UTC`, MINUTE) AS minute_start_utc,
  COUNT(*) AS total_event_count
FROM
  `你的项目ID.你的数据集.你的表名`
GROUP BY
  minute_start_utc
ORDER BY
  minute_start_utc DESC

按事件名称拆分统计每分钟各事件的发生次数

SELECT
  DATE_TRUNC(`Timestamp in UTC`, MINUTE) AS minute_start_utc,
  EventName,
  COUNT(*) AS event_count
FROM
  `你的项目ID.你的数据集.你的表名`
GROUP BY
  minute_start_utc,
  EventName
ORDER BY
  minute_start_utc DESC,
  event_count DESC

场景2：时间字段为字符串类型（匹配你给出的2021-08-11 17:27:27.916007 UTC格式） #

需要先通过PARSE_TIMESTAMP函数把字符串转换为TIMESTAMP类型再做截断：

SELECT
  DATE_TRUNC(
    PARSE_TIMESTAMP("%Y-%m-%d %H:%M:%E*S %Z", `Timestamp in UTC`),
    MINUTE
  ) AS minute_start_utc,
  EventName,
  COUNT(*) AS event_count
FROM
  `你的项目ID.你的数据集.你的表名`
GROUP BY
  minute_start_utc,
  EventName
ORDER BY
  minute_start_utc DESC

场景3：合并多张表统计 #

如果需要统计多张表的事件数据，用UNION ALL先拼接所有表数据再聚合即可：

WITH all_events AS (
  SELECT EventName, `Timestamp in UTC` FROM `你的项目ID.你的数据集.表1`
  UNION ALL
  SELECT EventName, `Timestamp in UTC` FROM `你的项目ID.你的数据集.表2`
  -- 按需添加更多需要合并的表
)
SELECT
  DATE_TRUNC(`Timestamp in UTC`, MINUTE) AS minute_start_utc,
  EventName,
  COUNT(*) AS event_count
FROM all_events
GROUP BY 1,2
ORDER BY 1 DESC

注意事项 #

• 字段名包含空格时必须用反引号包裹，避免语法报错
• DATE_TRUNC处理TIMESTAMP类型时默认保留原始时区，统计的分钟边界为UTC时区，无需额外做时区转换
• 如果需要输出其他时区的分钟统计，可在截断前用DATETIME(timestamp_expression, time_zone)转换时区即可

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