BigQuery SQL按1小时拆分时间区间 分配重叠分钟统计时长

源数据字段说明 #

处理前的事件表包含以下字段：

• date：事件对应日期
• event_type：事件类型
• interval_start：事件开始时间（支持TIMESTAMP/DATETIME类型）
• interval_end：事件结束时间（支持TIMESTAMP/DATETIME类型）
• duration_in_min：事件总时长，单位为分钟

核心处理规则 #

完全匹配需求逻辑：

• 以整点1小时为拆分粒度，采用[HH:00:00, HH+1:00:00)左闭右开区间规则，天然规避整点边界重复计算问题，等价于要求的59分59秒精度设置
• 新增interval字段标识记录所属的整点小时段
• 跨小时事件自动拆分，准确计算每个小时段内的有效重叠分钟数
• 拆分后保留原事件所有其余字段
• 示例逻辑对齐：09:05-11:45拆分为09点段55分钟、10点段60分钟、11点段45分钟；17:55-18:08拆分为17点段5分钟、18点段8分钟

可直接运行的SQL代码 #

注意：将代码中表路径替换为你实际的BigQuery表地址即可，如果时间字段是DATETIME类型，把SQL中所有TIMESTAMP前缀的函数替换为同名DATETIME函数即可

WITH event_with_hour_series AS (
  SELECT
    *,
    -- 生成当前事件覆盖的所有整点小时序列
    GENERATE_TIMESTAMP_ARRAY(
      TIMESTAMP_TRUNC(interval_start, HOUR),
      TIMESTAMP_TRUNC(interval_end, HOUR),
      INTERVAL 1 HOUR
    ) AS hour_buckets
  FROM `your_project.your_dataset.your_event_table`
)
SELECT
  date,
  event_type,
  interval_start,
  interval_end,
  duration_in_min,
  hour_bucket AS interval,
  -- 计算当前小时段内的实际有效分钟数
  DATETIME_DIFF(
    LEAST(interval_end, TIMESTAMP_ADD(hour_bucket, INTERVAL 1 HOUR)),
    GREATEST(interval_start, hour_bucket),
    MINUTE
  ) AS split_duration_in_min
FROM event_with_hour_series,
UNNEST(hour_buckets) AS hour_bucket
-- 过滤无有效时长的异常分段（兼容时间逻辑错误的脏数据、整点结束的边界场景）
WHERE
  LEAST(interval_end, TIMESTAMP_ADD(hour_bucket, INTERVAL 1 HOUR)) > GREATEST(interval_start, hour_bucket)

逻辑校验说明 #

以需求给出的两个测试用例验证，计算结果完全符合预期：

1. 09:05-11:45事件：
   • 09:00时段：取重叠区间09:05-10:00，时长55分钟
   • 10:00时段：取重叠区间10:00-11:00，时长60分钟
   • 11:00时段：取重叠区间11:00-11:45，时长45分钟
2. 17:55-18:08事件：
   • 17:00时段：取重叠区间17:55-18:00，时长5分钟
   • 18:00时段：取重叠区间18:00-18:08，时长8分钟

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