在GCP的BigQuery中查找最常见水果二元组合及唯一用户数

需求说明 #

给定如下结构的用户-水果数据集：

需要生成统计结果：每对水果二元组合对应的唯一用户数量，输出如下：

实现SQL #

假设你的数据集表名为your-project.your-dataset.user_fruits，执行以下SQL即可得到目标结果：

SELECT
  CONCAT(f1.fruit, ', ', f2.fruit) AS combination,
  COUNT(DISTINCT f1.`person#`) AS unique_person_count
FROM
  `your-project.your-dataset.user_fruits` f1
JOIN
  `your-project.your-dataset.user_fruits` f2
ON
  f1.`person#` = f2.`person#`
  AND f1.fruit < f2.fruit  -- 避免重复组合（如apple,orange和orange,apple视为同一组合）
GROUP BY
  combination
ORDER BY
  unique_person_count DESC;

代码解释 #

• 自连接表：通过person#关联同一用户的两条水果记录，确保只统计同一用户下的水果组合。
• 过滤重复组合：使用f1.fruit < f2.fruit的条件，保证每个二元组合只以固定顺序出现一次，避免重复统计。
• 拼接组合字符串：用CONCAT函数将两个水果名称按固定格式拼接成组合字段。
• 统计唯一用户：通过COUNT(DISTINCT f1.person#)统计每个组合对应的唯一用户数量。

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