BigQuery中统计事件A前后至下一个A的特定事件发生次数

嘿，这个需求很典型——要把每个用户的事件流按'A'拆分成一个个独立区间，然后统计目标事件在每个'A'的前后区间里的出现次数对吧？我给你两种实用的实现方案，你可以根据自己的场景选。

先明确场景示例 #

假设你的表结构是这样的（我先造点样例数据方便演示）：

WITH sample_data AS (
  SELECT 'user1' AS user_id, DATE('2024-01-01') AS event_date, 'B' AS event_type UNION ALL
  SELECT 'user1', DATE('2024-01-02'), 'A' UNION ALL
  SELECT 'user1', DATE('2024-01-03'), 'B' UNION ALL
  SELECT 'user1', DATE('2024-01-04'), 'C' UNION ALL
  SELECT 'user1', DATE('2024-01-05'), 'A' UNION ALL
  SELECT 'user1', DATE('2024-01-06'), 'B' UNION ALL
  SELECT 'user2', DATE('2024-01-01'), 'A' UNION ALL
  SELECT 'user2', DATE('2024-01-02'), 'C' UNION ALL
  SELECT 'user2', DATE('2024-01-03'), 'A'
)

我们要统计每个'A'事件发生前（到上一个'A'结束）和发生后（到下一个'A'开始），事件'B'的出现次数。

方案一：用子查询+窗口函数定位前后A的边界 #

这个方案逻辑最直观，先把所有'A'事件拎出来，然后给每个'A'找到它的前一个和后一个'A'的时间点，最后统计中间的目标事件：

WITH sample_data AS (
  -- 这里放你的实际表，替换掉样例数据
  SELECT 'user1' AS user_id, DATE('2024-01-01') AS event_date, 'B' AS event_type UNION ALL
  SELECT 'user1', DATE('2024-01-02'), 'A' UNION ALL
  SELECT 'user1', DATE('2024-01-03'), 'B' UNION ALL
  SELECT 'user1', DATE('2024-01-04'), 'C' UNION ALL
  SELECT 'user1', DATE('2024-01-05'), 'A' UNION ALL
  SELECT 'user1', DATE('2024-01-06'), 'B' UNION ALL
  SELECT 'user2', DATE('2024-01-01'), 'A' UNION ALL
  SELECT 'user2', DATE('2024-01-02'), 'C' UNION ALL
  SELECT 'user2', DATE('2024-01-03'), 'A'
),
a_events AS (
  -- 筛选所有A事件，同时获取每个A的前一个/后一个A的日期
  SELECT
    user_id,
    event_date AS a_date,
    -- 前一个A的日期，第一个A的话是NULL
    LAG(event_date) OVER (PARTITION BY user_id ORDER BY event_date) AS prev_a_date,
    -- 后一个A的日期，最后一个A的话是NULL
    LEAD(event_date) OVER (PARTITION BY user_id ORDER BY event_date) AS next_a_date
  FROM sample_data
  WHERE event_type = 'A'
)
SELECT
  a.user_id,
  a.a_date AS anchor_a_date,
  -- 统计前一个A到当前A之间的B事件次数（第一个A则从最早时间开始）
  COALESCE(
    (SELECT COUNT(*) 
     FROM sample_data d 
     WHERE d.user_id = a.user_id 
       AND d.event_date > COALESCE(a.prev_a_date, DATE('1900-01-01')) 
       AND d.event_date < a.a_date 
       AND d.event_type = 'B'), -- 替换成你要统计的事件
    0
  ) AS count_target_before_anchor,
  -- 统计当前A到后一个A之间的B事件次数（最后一个A则到最晚时间结束）
  COALESCE(
    (SELECT COUNT(*) 
     FROM sample_data d 
     WHERE d.user_id = a.user_id 
       AND d.event_date > a.a_date 
       AND d.event_date < COALESCE(a.next_a_date, DATE('9999-12-31')) 
       AND d.event_type = 'B'), -- 替换成你要统计的事件
    0
  ) AS count_target_after_anchor
FROM a_events a
ORDER BY a.user_id, a.a_date;

方案一的优势 #

• 逻辑清晰，容易理解和调试
• 适合只需要每个'A'锚点的前后计数结果的场景

方案二：用窗口函数分组处理全量事件 #

如果需要同时查看每个区间内的所有事件，或者要统计多个目标事件，这个方案更灵活：

WITH sample_data AS (
  -- 替换成你的实际表
  SELECT 'user1' AS user_id, DATE('2024-01-01') AS event_date, 'B' AS event_type UNION ALL
  SELECT 'user1', DATE('2024-01-02'), 'A' UNION ALL
  SELECT 'user1', DATE('2024-01-03'), 'B' UNION ALL
  SELECT 'user1', DATE('2024-01-04'), 'C' UNION ALL
  SELECT 'user1', DATE('2024-01-05'), 'A' UNION ALL
  SELECT 'user1', DATE('2024-01-06'), 'B' UNION ALL
  SELECT 'user2', DATE('2024-01-01'), 'A' UNION ALL
  SELECT 'user2', DATE('2024-01-02'), 'C' UNION ALL
  SELECT 'user2', DATE('2024-01-03'), 'A'
),
grouped_events AS (
  SELECT
    *,
    -- 生成分组ID：每遇到一个A，分组ID递增，把事件流拆分成「上一个A到当前A」的区间
    SUM(CASE WHEN event_type = 'A' THEN 1 ELSE 0 END) OVER (
      PARTITION BY user_id 
      ORDER BY event_date 
      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS a_group_id,
    -- 标记当前事件是否是A锚点
    CASE WHEN event_type = 'A' THEN 1 ELSE 0 END AS is_a_anchor,
    -- 获取当前分组对应的锚点A的日期
    FIRST_VALUE(CASE WHEN event_type = 'A' THEN event_date END) OVER (
      PARTITION BY user_id, a_group_id 
      ORDER BY event_date 
      ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS anchor_a_date
  FROM sample_data
)
SELECT
  user_id,
  anchor_a_date,
  -- 统计当前锚点之前的目标事件次数
  SUM(CASE WHEN event_date < anchor_a_date AND event_type = 'B' THEN 1 ELSE 0 END) AS count_target_before,
  -- 统计当前锚点之后的目标事件次数
  SUM(CASE WHEN event_date > anchor_a_date AND event_type = 'B' THEN 1 ELSE 0 END) AS count_target_after
FROM grouped_events
WHERE is_a_anchor = 1 -- 只保留每个锚点A的统计结果
GROUP BY user_id, anchor_a_date, a_group_id
ORDER BY user_id, anchor_a_date;

方案二的优势 #

• 可以扩展统计多个目标事件（只要在SUM(CASE...)里加分支就行）
• 保留了全量事件的分组信息，方便后续扩展分析

注意事项 #

1. 把代码里的'B'替换成你需要统计的特定事件类型
2. 如果你的时间字段是带时分秒的TIMESTAMP，把DATE('1900-01-01')换成TIMESTAMP('1900-01-01 00:00:00')，DATE('9999-12-31')换成TIMESTAMP('9999-12-31 23:59:59')
3. 如果用户的事件流里有重复的时间点，建议再加个唯一排序字段（比如事件ID）到ORDER BY里，保证排序稳定

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