如何在BigQuery中编写SQL查询计算A事件后最近B事件的时间差

嘿，这个需求用BigQuery的窗口函数加过滤逻辑就能轻松搞定，我给你一步步拆解：

先模拟示例数据 #

首先我们创建临时表复现你的场景：

WITH event_data AS (
  SELECT 'A1' AS event_name, TIMESTAMP '2024-05-20 13:00:00' AS event_time UNION ALL
  SELECT 'B5' AS event_name, TIMESTAMP '2024-05-20 14:00:00' AS event_time UNION ALL
  SELECT 'A3' AS event_name, TIMESTAMP '2024-05-20 15:00:00' AS event_time UNION ALL
  SELECT 'B9' AS event_name, TIMESTAMP '2024-05-20 17:00:00' AS event_time
)

核心查询语句 #

这是实现需求的关键代码，用自连接加QUALIFY子句精准筛选最近的B事件：

WITH event_data AS (
  SELECT 'A1' AS event_name, TIMESTAMP '2024-05-20 13:00:00' AS event_time UNION ALL
  SELECT 'B5' AS event_name, TIMESTAMP '2024-05-20 14:00:00' AS event_time UNION ALL
  SELECT 'A3' AS event_name, TIMESTAMP '2024-05-20 15:00:00' AS event_time UNION ALL
  SELECT 'B9' AS event_name, TIMESTAMP '2024-05-20 17:00:00' AS event_time
)
SELECT
  a.event_name,
  TIMESTAMP_DIFF(b.event_time, a.event_time, HOUR) AS time_diff_hours
FROM event_data a
JOIN event_data b
  ON b.event_name LIKE 'B%' -- 只关联B类事件
  AND b.event_time > a.event_time -- 确保B在A之后发生
WHERE a.event_name LIKE 'A%' -- 只处理A类事件
QUALIFY ROW_NUMBER() OVER (PARTITION BY a.event_name ORDER BY b.event_time ASC) = 1 -- 取每个A对应的最早（最近）B事件
ORDER BY a.event_time;

语句细节解释 #

• 自连接逻辑：左边取所有A事件，右边取该A之后的所有B事件
• QUALIFY是BigQuery的实用特性，能在窗口计算后直接过滤行：这里按每个A事件分组，把对应的B事件按时间升序排序，取第一行就是最近的那个B
• TIMESTAMP_DIFF用来计算时间差，这里指定了小时单位，你也可以换成MINUTE/SECOND等其他单位适配需求

执行结果 #

运行后会得到完全符合预期的结果：

如果你的表数据量极大，自连接效率不够的话，还可以用LEAD窗口函数的变种写法，但上面的代码直观易懂，覆盖绝大多数场景完全没问题～

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