如何在BigQuery中创建30分钟滑动窗口计算事件总数

需求说明 #

在BigQuery中实现30分钟增量滑动窗口统计，计算每个窗口内的事件总数，需满足：

• 窗口包含当前时间段内的事件，以及持续时间覆盖该窗口的历史事件
• 支持跨日期统计，后续日期的窗口需包含前序日期中持续时间覆盖该窗口的事件

样本数据 #

输出规则示例 #

• 2025-03-06 11:30-12:00窗口总和：2+33+7=42
• 2025-03-07 12:00-12:30窗口总和：33+7+1+25+72+119=257

原代码问题分析 #

你提供的SQL存在以下核心问题：

1. 窗口生成冗余：生成每秒间隔的窗口数组，会产生海量数据，导致性能极差
2. 关联逻辑错误：用字符串类型的CREATE_DATE和timestamp类型的窗口时间比较，类型不匹配且逻辑错误，未正确判断事件与窗口的时间重叠关系
3. 聚合逻辑错误：按CREATE_DATE分区求和，无法实现按窗口统计的需求

优化后的SQL代码 #

WITH events AS (
  -- 将事件的日期和时间合并为完整timestamp，并计算事件结束时间
  SELECT
    PARSE_TIMESTAMP('%m/%d/%Y %H:%M:%S', CREATE_DATE || ' ' || START_TIME) AS event_start,
    TIMESTAMP_ADD(
      PARSE_TIMESTAMP('%m/%d/%Y %H:%M:%S', CREATE_DATE || ' ' || START_TIME),
      INTERVAL DURATIONMIN MINUTE
    ) AS event_end,
    TOTAL_COUNT
  FROM mytable
),
time_windows AS (
  -- 生成连续的30分钟滑动窗口，步长为1分钟（可根据需求调整）
  SELECT
    window_start,
    TIMESTAMP_ADD(window_start, INTERVAL 30 MINUTE) AS window_end,
    FORMAT_TIMESTAMP('%Y-%m-%d %H:%M', window_start) || '-' || FORMAT_TIMESTAMP('%H:%M', TIMESTAMP_ADD(window_start, INTERVAL 30 MINUTE)) AS window_name
  FROM UNNEST(GENERATE_TIMESTAMP_ARRAY(
    -- 覆盖所有可能的重叠事件，从最早事件前30分钟开始
    (SELECT TIMESTAMP_SUB(MIN(event_start), INTERVAL 30 MINUTE) FROM events),
    -- 覆盖所有可能的窗口，到最晚事件结束时间
    (SELECT MAX(event_end) FROM events),
    INTERVAL 1 MINUTE -- 步长：如需每30分钟一个窗口，改为INTERVAL 30 MINUTE
  )) AS window_start
)
-- 关联事件和窗口，统计每个窗口的总事件数
SELECT
  window_name,
  SUM(TOTAL_COUNT) AS window_sum
FROM time_windows w
JOIN events e
  -- 判断事件与窗口是否重叠：事件开始早于窗口结束，且事件结束晚于窗口开始
  ON e.event_start < w.window_end
  AND e.event_end > w.window_start
GROUP BY window_name
ORDER BY window_name;

代码说明 #

1. events CTE：将原表的日期和时间字段合并为标准timestamp，计算每个事件的结束时间（开始时间+持续分钟数），统一时间格式便于后续计算
2. time_windows CTE：根据事件的时间范围生成连续的30分钟窗口，步长设为1分钟（可调整为30分钟、5分钟等），确保覆盖所有需要统计的时间段
3. 关联逻辑：使用时间重叠的判断条件，确保所有与窗口有交集的事件都被计入该窗口，包括跨日期的事件
4. 聚合统计：按窗口名称分组求和，得到每个30分钟窗口的事件总数

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