BigQuery中如何计算不同时间间隔内的最大并发购买数

核心逻辑说明 #

• 无循环、无自定义会话变量，完全基于原生SQL聚合逻辑，性能适配大表场景
• 支持单次配置任意多个时间间隔参数，一次查询返回所有间隔下的计算结果
• 以每条购买事件的发生时间作为窗口起点，严格匹配「从事件发生时间开始N秒内」的窗口规则
• 窗口内用户数按customer_id去重统计，最终取每个产品在对应间隔下的最大并发值
• 间隔设为0秒时，计算结果与原有GROUP BY product_name, timestamp + COUNT(DISTINCT customer_id)的逻辑完全一致，可直接做结果校验

可配置SQL实现 #

-- 配置需要计算的时间间隔（单位：秒），可按需增删列表内的值
WITH interval_config AS (
  SELECT interval_second
  FROM UNNEST([0, 2, 10, 30]) AS interval_second -- 按需修改间隔值，支持同时传入多个
),
-- 读取原始购买事件表，替换为实际表路径即可
purchase_events AS (
  SELECT
    timestamp,
    customer_id,
    product_name
  FROM `your_project.your_dataset.your_purchase_table`
  -- 建议加时间分区过滤缩小扫描范围，例如：WHERE timestamp BETWEEN '2024-01-01' AND '2024-06-30'
),
-- 计算每个事件作为窗口起点时，对应间隔窗口内的去重购买用户数
window_concurrent AS (
  SELECT
    ic.interval_second,
    pe1.product_name,
    pe1.timestamp AS window_start_ts,
    COUNT(DISTINCT pe2.customer_id) AS concurrent_user_cnt
  FROM interval_config ic
  CROSS JOIN purchase_events pe1
  INNER JOIN purchase_events pe2
    ON pe1.product_name = pe2.product_name
    AND pe2.timestamp >= pe1.timestamp
    AND pe2.timestamp <= TIMESTAMP_ADD(pe1.timestamp, INTERVAL ic.interval_second SECOND)
  GROUP BY ic.interval_second, pe1.product_name, pe1.timestamp
)
-- 取每个间隔、每个产品的最大并发购买值
SELECT
  interval_second,
  product_name,
  MAX(concurrent_user_cnt) AS max_concurrent_purchase
FROM window_concurrent
GROUP BY interval_second, product_name
ORDER BY interval_second, product_name

性能优化提示 #

• 必须给原始事件表的timestamp字段设置时间分区，查询时加上时间范围过滤，可将数据扫描量降低90%以上
• 关联条件强制加pe1.product_name = pe2.product_name的等值匹配，BigQuery会自动按产品做数据分片，避免全表笛卡尔积
• 如需计算的间隔值较多，不需要反复执行查询，直接在interval_config的UNNEST列表里追加值即可，一次扫描计算所有结果

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