寻求在BigQuery中高效统计用户类别组合重叠情况的解决方案

嘿，我完全懂你面对40+类别还要手动写SQL的痛苦——简直是重复劳动到崩溃！别担心，咱们用BigQuery的UNPIVOT和数组聚合就能完美搞定这个问题，再也不用写一大堆繁琐的WHERE条件了。

核心思路 #

咱们先把原来的宽表（每个用户一行，附带多个类别列）转成窄表（每个用户的每个关联类别单独一行），然后对每个用户的关联类别做排序聚合，最后按聚合后的类别组合统计用户数。不管类别数量有多少，这个逻辑都能自动适配。

具体SQL实现 #

假设你的表叫user_categories，结构是user_id + 40多个类别字段（比如cat1、cat2...cat40），字段值为1表示用户关联该类别，0表示不关联。

第一步：转窄表（UNPIVOT）

先把每个用户的类别列拆成单独的行，只保留用户实际关联的类别：

WITH unpivoted_categories AS (
  SELECT 
    user_id,
    category_name
  FROM 
    `your-project.your-dataset.user_categories`
  -- 把所有类别列转成(category_name, is_associated)的键值对
  UNPIVOT (
    is_associated FOR category_name IN (cat1, cat2, cat3, ..., cat40)
  )
  WHERE is_associated = 1 -- 只保留用户关联的类别
)

第二步：聚合用户的类别组合

对每个用户的类别排序后聚合成数组（避免[10,30]和[30,10]被当成不同组合）：

, user_category_groups AS (
  SELECT 
    user_id,
    -- 按类别名称排序后聚合成数组，保证组合的唯一性
    ARRAY_AGG(category_name ORDER BY category_name) AS category_combination
  FROM unpivoted_categories
  GROUP BY user_id
)

第三步：统计每个组合的用户数

最后按类别组合分组，统计用户数量：

SELECT 
  category_combination,
  COUNT(user_id) AS user_count -- 这里不用DISTINCT，因为每个user_id只对应一个组合
FROM user_category_groups
GROUP BY category_combination
ORDER BY user_count DESC;

优化小技巧 #

如果类别列太多，手动写IN (cat1, cat2...)太麻烦，可以用BigQuery的元数据自动生成列名：

-- 查询所有类别列的名称，复制结果到UNPIVOT的IN里
SELECT STRING_AGG(column_name, ', ') 
FROM `your-project.your-dataset.INFORMATION_SCHEMA.COLUMNS`
WHERE table_name = 'user_categories' 
  AND column_name LIKE 'cat%'; -- 替换成你的类别列命名规则

适配不同表结构 #

如果你的类别字段不是1/0标记，而是直接存储类别名称（比如有的用户cat1是D，cat2是10，未关联的字段为NULL），只需要把WHERE is_associated = 1改成WHERE category_name IS NOT NULL就行。

这样不管你有多少个类别，这个SQL都能自动处理所有组合，再也不用手动写那些繁琐的条件啦！

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