如何在BigQuery的3事件组合聚合中排除其他事件用户

你原本的代码用于统计指定事件中每3个事件组合的共同用户数，但现在需要调整逻辑：每个组合的统计用户必须仅触发了该组合内的事件，完全没有触发其他在目标列表中列出的事件。比如统计a、b、f组合时，要排除所有触发过c、d、e的用户。

原代码仅通过三次JOIN确保用户触发了三个组合事件，但未过滤同时触发其他事件的用户，因此结果会包含不符合要求的用户。

解决方案思路 #

1. 先聚合每个用户触发的所有事件，得到每个用户的完整事件集合
2. 生成所有不重复的三事件组合（避免同一组合的不同排列重复统计）
3. 匹配用户的事件集合与组合的事件集合，仅保留完全匹配的用户（即用户的事件集合恰好等于组合的事件集合）
4. 统计每个组合对应的合规用户数量

修改后的完整代码 #

-- 3 EVENT COMBO'S - 排除触发其他事件的用户
WITH 
-- 1. 统一管理目标事件列表
TargetEvents AS (
  SELECT event_name FROM UNNEST(['a','b','c','d','e','f']) AS event_name
),
-- 2. 聚合每个用户触发的所有事件，去重并排序（保证顺序一致，方便后续比较）
UserEvents AS (
  SELECT 
    user_pseudo_id,
    ARRAY_AGG(DISTINCT event_name ORDER BY event_name) AS user_event_list
  FROM `table_name`
  WHERE event_name IN ('a','b','c','d','e','f')
    AND event_date BETWEEN '20231101' AND '20231130'
  GROUP BY user_pseudo_id
),
-- 3. 生成所有不重复的三事件组合
EventCombos AS (
  SELECT 
    e1.event_name AS event1,
    e2.event_name AS event2,
    e3.event_name AS event3,
    [e1.event_name, e2.event_name, e3.event_name] AS combo_event_list
  FROM TargetEvents e1
  CROSS JOIN TargetEvents e2
  CROSS JOIN TargetEvents e3
  -- 避免同一组合的不同排列（比如a,b,f和b,a,f视为同一组合）
  WHERE e1.event_name < e2.event_name 
    AND e2.event_name < e3.event_name
)

-- 4. 统计每个组合中事件集合完全匹配的用户数
SELECT
  ec.event1,
  ec.event2,
  ec.event3,
  COUNT(DISTINCT ue.user_pseudo_id) AS common_users_count
FROM EventCombos ec
LEFT JOIN UserEvents ue
ON ec.combo_event_list = ue.user_event_list
GROUP BY ec.event1, ec.event2, ec.event3
ORDER BY common_users_count DESC;

关键逻辑说明 #

• TargetEvents：集中管理目标事件，后续修改事件只需更新此部分，避免多处改动
• UserEvents：用ARRAY_AGG聚合并排序用户的事件列表，确保同一用户的事件顺序固定，方便和组合数组做精准匹配
• EventCombos：通过CROSS JOIN生成所有合法三事件组合，利用<条件避免重复统计同一组合的不同排列
• 匹配逻辑：直接通过数组相等判断用户是否仅触发了组合内的事件，彻底排除触发其他事件的用户

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