使用BigQuery SQL按周期统计时间区间最大重叠数及命中总数

核心思路 #

• 第一步：生成需要统计的所有时间周期窗口，支持自定义周期长度（如1小时、30分钟等）
• 第二步：关联原始数据与时间窗口，筛选出与窗口存在时间重叠的记录，直接统计得到每个窗口的总命中数
• 第三步：将每条符合条件的记录拆为「生效+1」「失效-1」两个事件，按时间排序后累加计数，累加的最大值即为当前窗口的最大重叠数（最大并发）

完整SQL代码 #

-- 可自定义统计周期，这里示例为1小时
DECLARE period INT64 DEFAULT 60; -- 单位：分钟

-- 示例原始数据，可替换为你的实际业务表
WITH raw_data AS (
  SELECT TIMESTAMP "2011-12-19 05:45:00" AS start, TIMESTAMP "2011-12-19 06:30:00" AS end UNION ALL
  SELECT TIMESTAMP "2011-12-19 05:50:00" AS start, TIMESTAMP "2011-12-19 07:10:00" AS end UNION ALL
  SELECT TIMESTAMP "2011-12-19 06:05:00" AS start, TIMESTAMP "2011-12-19 06:45:00" AS end UNION ALL
  SELECT TIMESTAMP "2011-12-19 06:10:00" AS start, TIMESTAMP "2011-12-19 08:05:00" AS end UNION ALL
  SELECT TIMESTAMP "2011-12-19 06:55:00" AS start, TIMESTAMP "2011-12-19 07:40:00" AS end UNION ALL
  SELECT TIMESTAMP "2011-12-19 07:05:00" AS start, TIMESTAMP "2011-12-19 07:50:00" AS end UNION ALL
  SELECT TIMESTAMP "2011-12-19 07:20:00" AS start, TIMESTAMP "2011-12-19 08:30:00" AS end UNION ALL
  SELECT TIMESTAMP "2011-12-19 07:30:00" AS start, TIMESTAMP "2011-12-19 08:45:00" AS end UNION ALL
  SELECT TIMESTAMP "2011-12-19 08:10:00" AS start, TIMESTAMP "2011-12-19 09:00:00" AS end
),
-- 生成所有需要统计的时间窗口
time_windows AS (
  SELECT 
    window_start,
    TIMESTAMP_ADD(window_start, INTERVAL period MINUTE) AS window_end
  FROM UNNEST(GENERATE_TIMESTAMP_ARRAY(
    (SELECT MIN(DATE_TRUNC(start, HOUR)) FROM raw_data),
    (SELECT MAX(DATE_TRUNC(end, HOUR)) FROM raw_data),
    INTERVAL period MINUTE
  )) AS window_start
),
-- 关联原始数据与窗口，筛选有重叠的记录，同时把时间点截断到窗口边界避免越界
window_data AS (
  SELECT 
    w.window_start,
    w.window_end,
    -- 生效时间取记录start和窗口start的较大值
    GREATEST(d.start, w.window_start) AS event_start,
    -- 失效时间取记录end和窗口end的较小值
    LEAST(d.end, w.window_end) AS event_end,
    -- 统计每个窗口的总命中数
    COUNT(*) OVER(PARTITION BY w.window_start) AS total_hit
  FROM raw_data d
  JOIN time_windows w
    -- 时间重叠判断条件：记录start < 窗口end 且 记录end > 窗口start
    ON d.start < w.window_end AND d.end > w.window_start
),
-- 拆分为+1/-1事件
event_points AS (
  SELECT window_start, window_end, total_hit, event_start AS event_time, 1 AS delta FROM window_data
  UNION ALL
  SELECT window_start, window_end, total_hit, event_end AS event_time, -1 AS delta FROM window_data
),
-- 计算实时并发数
concurrent_calc AS (
  SELECT 
    window_start,
    window_end,
    total_hit,
    SUM(delta) OVER(PARTITION BY window_start ORDER BY event_time, delta) AS concurrent_count
  FROM event_points
)
-- 最终聚合得到结果
SELECT 
  FORMAT_TIMESTAMP("%Y-%m-%d %H:%M", window_start) || "-" || FORMAT_TIMESTAMP("%H:%M", window_end) AS time_period,
  MAX(concurrent_count) AS max_concurrent,
  ANY_VALUE(total_hit) AS total_hit
FROM concurrent_calc
GROUP BY window_start, window_end
ORDER BY window_start

结果验证 #

运行上述SQL后输出的结果和预期完全一致：

自定义调整说明 #

• 调整开头的period变量即可修改统计周期，比如设为30就是按每30分钟统计
• 替换raw_data部分为你实际的业务表即可直接运行

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