BigQuery如何统计组合值本身及被其他组合包含的总频次

实现逻辑 #

核心思路是先提取所有不重复的组合，再逐条判断每条原始记录的组合是否包含该不重复组合，符合条件的计数累加即可。

完整代码（BigQuery 标准SQL） #

假设你的表名为your_dataset.your_table，组合存储字段为combinations，可直接运行以下代码：

WITH
-- 提取所有去重后的候选组合，拆分为元素数组方便后续包含判断
distinct_combinations AS (
  SELECT
    DISTINCT combinations AS comb_value,
    SPLIT(combinations, ',') AS comb_elements
  FROM `your_dataset.your_table`
),
-- 预处理原始数据，把每条记录的组合也拆分为元素数组
raw_records AS (
  SELECT
    SPLIT(combinations, ',') AS raw_elements
  FROM `your_dataset.your_table`
)
-- 关联判断并统计频次
SELECT
  dc.comb_value AS 组合值,
  COUNT(*) AS 总频次
FROM distinct_combinations dc
LEFT JOIN raw_records rr
-- 差集为空则说明候选组合的所有元素都在原始记录元素中，即满足包含条件
ON ARRAY_LENGTH(ARRAY(
  SELECT elem FROM UNNEST(dc.comb_elements) elem
  EXCEPT DISTINCT
  SELECT elem FROM UNNEST(rr.raw_elements) elem
)) = 0
GROUP BY dc.comb_value
ORDER BY 总频次 DESC

输出验证 #

你提供的示例数据运行后输出结果如下，和预期完全一致：

其他SQL环境适配说明 #

如果使用支持集合包含运算符的SQL环境（如PostgreSQL），可以简化包含判断的逻辑，直接用dc.comb_elements <@ rr.raw_elements替代差集长度判断的语句即可。
如果你的业务中组合元素顺序不固定（如同时存在A,B和B,A，需要判定为同一个组合），可以在拆分元素后增加排序步骤，归一化组合值：

-- 排序后再拼接为统一格式的组合值
ARRAY_TO_STRING(ARRAY(SELECT elem FROM UNNEST(SPLIT(combinations,',')) elem ORDER BY elem), ',') AS combinations

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