BigQuery中按user_id分组判断用户是否提交全部指定activity_id

原表结构与数据 #

需求说明 #

按user_id分组，给定活动ID列表（例如(111,222,333)），判断用户的activity_id集合是否包含列表中的所有ID，输出结果如下：

注意：无需完全匹配，用户提交额外活动不影响结果；不能使用WHERE子句过滤指定活动ID，因为原表还有其他计算需求。

解决方案（BigQuery SQL） #

方法1：关联统计法 #

WITH target_activities AS (
  SELECT activity_id FROM UNNEST([111, 222, 333]) AS activity_id
)
SELECT
  t.user_id,
  COUNT(DISTINCT CASE WHEN ta.activity_id IS NOT NULL THEN t.activity_id END) = (SELECT COUNT(*) FROM target_activities) AS submitted_minimum_activities
FROM
  `your-project.your-dataset.your-table` t
LEFT JOIN
  target_activities ta ON t.activity_id = ta.activity_id
GROUP BY
  t.user_id

代码说明

• 用UNNEST将目标活动数组转为临时表，避免硬编码目标活动数量，后续修改列表只需调整数组内容。
• 通过左关联标记用户提交的活动中属于目标列表的部分，全程不使用WHERE过滤原表数据。
• 统计用户匹配的目标活动数量，与目标活动总数对比，相等则说明用户提交了所有指定活动。

方法2：数组子集校验法 #

SELECT
  user_id,
  (SELECT COUNT(*) FROM UNNEST([111,222,333]) a WHERE a IN UNNEST(user_activities)) = 3 AS submitted_minimum_activities
FROM (
  SELECT
    user_id,
    ARRAY_AGG(DISTINCT activity_id) AS user_activities
  FROM `your-project.your-dataset.your-table`
  GROUP BY user_id
)

代码说明

• 先分组聚合每个用户的所有活动ID为数组user_activities。
• 子查询统计目标活动列表中存在于用户活动数组中的数量，等于目标活动总数（此处为3）则返回true。

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