如何在BigQuery SQL中统计特定事件在事件'A'前后的发生次数？

针对你的需求，我分两种常见场景给出具体的SQL实现，你可以根据实际业务情况选择：

场景1：以用户第一次发生'A'的时间为分界点，统计全局前后次数 #

如果你的需求是以每个用户第一次触发事件'A'的时间为基准，统计该用户所有X.Y.前缀事件在这个时间点之前/之后的总次数，可以用下面的SQL：

-- 第一步：获取每个用户第一次发生事件'A'的时间
WITH user_first_a AS (
  SELECT
    user_id,
    MIN(event_time) AS first_a_timestamp  -- 取第一次A事件的时间，若要最后一次则用MAX
  FROM `your_project.your_dataset.your_table`
  WHERE event_name = 'A'
  GROUP BY user_id
),
-- 第二步：关联原表，标记每个X.Y.前缀事件相对于A事件的位置
tagged_events AS (
  SELECT
    t.user_id,
    t.event_name,
    -- 判断事件是在A之前、之后还是同时发生
    CASE
      WHEN t.event_time < u.first_a_timestamp THEN 'before_A'
      WHEN t.event_time > u.first_a_timestamp THEN 'after_A'
      ELSE 'same_time_as_A'  -- 可根据需求删除或调整这一分支
    END AS relative_position
  FROM `your_project.your_dataset.your_table` t
  -- 用INNER JOIN过滤掉从未触发过A事件的用户；若要包含这些用户，改用LEFT JOIN并处理NULL
  INNER JOIN user_first_a u ON t.user_id = u.user_id
  WHERE t.event_name LIKE 'X.Y.%'  -- 匹配X.Y.开头的事件
)
-- 第三步：统计各区间的事件次数
SELECT
  relative_position,
  COUNT(*) AS event_count
FROM tagged_events
GROUP BY relative_position
ORDER BY relative_position;

场景2：以每一次'A'事件为分界点，统计单条A事件前后的次数 #

如果你的需求是针对用户触发的每一次'A'事件，分别统计该事件发生前、后X.Y.前缀事件的次数（比如分析每次A事件前后的行为规律），可以用窗口函数来实现分组：

-- 第一步：给每个用户的事件按时间排序，并标记所属的A事件分组
WITH ordered_events AS (
  SELECT
    user_id,
    event_time,
    event_name,
    -- 用累加器标记分组：每遇到一次A事件，分组ID加1
    SUM(CASE WHEN event_name = 'A' THEN 1 ELSE 0 END) OVER (
      PARTITION BY user_id 
      ORDER BY event_time 
      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS a_group_id
  FROM `your_project.your_dataset.your_table`
),
-- 第二步：提取所有A事件及其对应的分组ID和时间
a_event_groups AS (
  SELECT
    user_id,
    a_group_id,
    event_time AS a_event_timestamp
  FROM ordered_events
  WHERE event_name = 'A'
),
-- 第三步：将X.Y.前缀事件与对应的A事件分组关联
linked_events AS (
  SELECT
    o.user_id,
    o.event_name,
    o.event_time,
    a.a_event_timestamp
  FROM ordered_events o
  INNER JOIN a_event_groups a 
    ON o.user_id = a.user_id 
    AND o.a_group_id = a.a_group_id
  WHERE o.event_name LIKE 'X.Y.%'
)
-- 第四步：统计每个A事件前后的次数
SELECT
  CASE
    WHEN event_time < a_event_timestamp THEN 'before_this_A'
    WHEN event_time > a_event_timestamp THEN 'after_this_A'
    ELSE 'same_time_as_A'
  END AS relative_position,
  COUNT(*) AS event_count
FROM linked_events
GROUP BY relative_position
ORDER BY relative_position;

关键注意事项 #

• 时间字段类型：确保event_time是TIMESTAMP/DATETIME类型，如果是字符串格式，需要先用PARSE_TIMESTAMP()或PARSE_DATETIME()转换为可比较的时间类型。
• 未触发A事件的用户：如果需要统计从未触发过A事件的用户的X.Y.前缀事件，把场景1中的INNER JOIN改为LEFT JOIN，并在CASE中处理u.first_a_timestamp IS NULL的情况（比如标记为never_had_A）。
• 前缀匹配精度：如果要严格匹配X.Y.开头且后面有内容的事件，可把LIKE 'X.Y.%'改为REGEXP_CONTAINS(event_name, r'^X\.[Y]\..+')（正则表达式更精准）。
• 同时发生的事件：根据业务需求决定是否将与A事件同时发生的事件计入前/后，或者单独统计。

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