如何在BigQuery中展开时间并按小时分组统计？

需求说明 #

从start_date_time和end_date_time生成时间数组，提取区间内包含的所有小时（例如开始时间2024-03-09 12:00:18.000000 UTC、结束时间2024-03-09 15:00:18.000000 UTC时，需提取12、13、14、15），并按segment、event_type和提取出的小时分组统计数量。

原查询问题 #

以下是尝试的查询，但未得到理想结果：

with q1 as (
Select segment, event_type,hours from
`id.dataset.my_tab`,
unnest(generate_timestamp_array(end_date_time,start_date_time, interval 1 hour)) as hours
),
q2 as (
select segment, event_type,
EXTRACT(HOUR FROM hours) as hours_category from q1
)
Select segment, event_type, hours_category,
count(hour_category) as count_hours
from q2
Group by hour_category, event_type,segment

问题分析与修正 #

原查询存在几个关键问题：

• GENERATE_TIMESTAMP_ARRAY参数顺序错误，应将起始时间start_date_time放在第一个参数位置，否则无法生成正确的时间序列
• 未处理结束时间的小时包含问题：默认生成的数组不会包含结束时间对应的小时（比如15:00的结束时间，原逻辑只会生成12、13、14点）
• 统计时字段名拼写错误：count(hour_category)应为count(hours_category)

修正后的查询 #

WITH q1 AS (
  SELECT 
    segment, 
    event_type,
    hours 
  FROM `id.dataset.my_tab`,
  -- 修正参数顺序，同时将结束时间加1小时，确保结束小时被包含
  UNNEST(GENERATE_TIMESTAMP_ARRAY(start_date_time, TIMESTAMP_ADD(end_date_time, INTERVAL 1 HOUR), INTERVAL 1 HOUR)) AS hours
),
q2 AS (
  SELECT 
    segment, 
    event_type,
    EXTRACT(HOUR FROM hours) AS hours_category 
  FROM q1
)
SELECT 
  segment, 
  event_type, 
  hours_category,
  COUNT(hours_category) AS count_hours
FROM q2
GROUP BY segment, event_type, hours_category
ORDER BY segment, event_type, hours_category

修正说明 #

1. 调整GENERATE_TIMESTAMP_ARRAY的参数顺序，确保从起始时间到结束时间生成序列
2. 对end_date_time添加1小时，这样生成的时间数组会包含结束时间对应的小时（例如原结束时间15:00，加1小时后生成到16:00，数组会包含15:00）
3. 修正统计字段的拼写错误，保证统计逻辑正确
4. 增加排序语句，让结果按分组字段有序展示

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