BigQuery：统计30分钟时间间隔内的记录数

我有一张预创建的表data：

id      timestamp_entry
1       "2023-01-01 04:11:24 UTC"
2       "2023-01-01 04:14:55 UTC"
...
99999   "2023-01-31 23:45:59 UTC"

其中timestamp_entry统一为UTC时区，时间范围在2023年1月内。

我想要生成30分钟的时间骨架，统计每个区间内timestamp_entry的记录数量，已编写生成时间区间的子查询：

WITH
intervals AS(
  SELECT interval AS start_time,
         TIMESTAMP_SUB(TIMESTAMP_ADD(interval, INTERVAL 30 MINUTE), INTERVAL 1 SECOND) AS end_time
  FROM UNNEST(GENERATE_TIMESTAMP_ARRAY("2023-01-01 00:00:00 UTC", "2023-01-31 23:59:59 UTC", INTERVAL 30 MINUTE)) interval
)

理想输出结果如下：

start_time                  end_time                    count
"2023-01-01 00:00:00 UTC"   "2023-01-01 00:29:59 UTC"   0
"2023-01-01 00:30:00 UTC"   "2023-01-01 00:59:59 UTC"   0
...
"2023-01-31 23:00:00 UTC"   "2023-01-31 23:29:59 UTC"   12
"2023-01-31 23:30:00 UTC"   "2023-01-31 23:59:59 UTC"   5

其中count表示data表中落入对应区间的timestamp_entry数量。尝试过用RIGHT JOIN结合BETWEEN但无法关联两张表，需要可行解决方案。

提供两种高效可行的实现方式：

方案1：左连接+区间匹配 #

直接将时间骨架与data表左连接，通过BETWEEN判断时间是否落在区间内，用COUNT(d.id)保留0值区间：

WITH
intervals AS(
  SELECT interval AS start_time,
         TIMESTAMP_SUB(TIMESTAMP_ADD(interval, INTERVAL 30 MINUTE), INTERVAL 1 SECOND) AS end_time
  FROM UNNEST(GENERATE_TIMESTAMP_ARRAY("2023-01-01 00:00:00 UTC", "2023-01-31 23:59:59 UTC", INTERVAL 30 MINUTE)) interval
)
SELECT
  i.start_time,
  i.end_time,
  COUNT(d.id) AS count
FROM intervals i
LEFT JOIN data d
ON d.timestamp_entry BETWEEN i.start_time AND i.end_time
GROUP BY i.start_time, i.end_time
ORDER BY i.start_time;

方案2：时间对齐后等值关联（性能更优） #

先将data表的timestamp_entry截断到30分钟起始点，统计各区间数量后，再与时间骨架左连接：

WITH
intervals AS(
  SELECT interval AS start_time,
         TIMESTAMP_SUB(TIMESTAMP_ADD(interval, INTERVAL 30 MINUTE), INTERVAL 1 SECOND) AS end_time
  FROM UNNEST(GENERATE_TIMESTAMP_ARRAY("2023-01-01 00:00:00 UTC", "2023-01-31 23:59:59 UTC", INTERVAL 30 MINUTE)) interval
),
data_counts AS(
  SELECT
    TIMESTAMP_TRUNC(timestamp_entry, MINUTE, 30) AS interval_start,
    COUNT(*) AS count
  FROM data
  GROUP BY interval_start
)
SELECT
  i.start_time,
  i.end_time,
  COALESCE(dc.count, 0) AS count
FROM intervals i
LEFT JOIN data_counts dc
ON i.start_time = dc.interval_start
ORDER BY i.start_time;

方案说明 #

• 方案2中TIMESTAMP_TRUNC(timestamp_entry, MINUTE, 30)会把任意时间对齐到最近的30分钟起始点，比如2023-01-01 04:11:24 UTC会被转为2023-01-01 04:00:00 UTC，刚好和时间骨架的start_time匹配，等值关联比区间匹配的性能更好。
• COALESCE(dc.count, 0)用于将无匹配记录的区间计数设为0，完全符合输出需求。

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