GCP BigQuery中高效统计多表ID所有出现组合的计数方案

针对4个ID表（A、B、C、D）的全组合出现次数统计需求，原多段SELECT方案在表数量增加时会出现重复扫描、效率低下、维护困难的问题，以下是基于BigQuery优化的高效解决方案：

核心思路 #

先收集所有表的唯一ID，标记每个ID在各表中的存在状态，最后按状态分组计数。这种方式仅需扫描每个表一次，完美适配BigQuery的并行计算和列存储特性，同时扩展性极强。

最优SQL实现 #

WITH table_unique_ids AS (
  -- 获取每个表的唯一ID并标记来源表
  SELECT Id, 'A' AS source FROM A GROUP BY Id
  UNION ALL
  SELECT Id, 'B' AS source FROM B GROUP BY Id
  UNION ALL
  SELECT Id, 'C' AS source FROM C GROUP BY Id
  UNION ALL
  SELECT Id, 'D' AS source FROM D GROUP BY Id
),
id_presence_flags AS (
  -- 为每个ID生成各表的存在标记（1=存在，0=不存在）
  SELECT
    Id,
    MAX(CASE WHEN source = 'A' THEN 1 ELSE 0 END) AS in_A,
    MAX(CASE WHEN source = 'B' THEN 1 ELSE 0 END) AS in_B,
    MAX(CASE WHEN source = 'C' THEN 1 ELSE 0 END) AS in_C,
    MAX(CASE WHEN source = 'D' THEN 1 ELSE 0 END) AS in_D
  FROM table_unique_ids
  GROUP BY Id
)
SELECT
  -- 生成易读的组合描述（如"A+B"表示仅在A、B中存在）
  CONCAT(
    CASE WHEN in_A = 1 THEN 'A' ELSE '' END,
    CASE WHEN in_B = 1 THEN '+B' ELSE '' END,
    CASE WHEN in_C = 1 THEN '+C' ELSE '' END,
    CASE WHEN in_D = 1 THEN '+D' ELSE '' END
  ) AS presence_combo,
  COUNT(Id) AS id_count
FROM id_presence_flags
GROUP BY presence_combo
-- 排除无任何表存在的情况（理论上不会出现）
HAVING presence_combo != ''
ORDER BY id_count DESC;

方案优势 #

1. 极致高效：每个表仅被扫描一次，避免了原方案中多次子查询的重复IO和计算开销，BigQuery的并行处理能快速完成百万级数据的统计。
2. 扩展性强：新增表时，仅需在table_unique_ids的UNION ALL中添加一行，并在id_presence_flags的CASE WHEN中增加对应标记，无需修改大量查询语句。
3. 可读性高：结果直接展示清晰的组合描述（如"A"、"A+B+C"），便于理解和后续分析。
4. 避免重复ID干扰：提前对每个表的ID去重，确保统计的是唯一ID的组合情况。

对比其他方案 #

• 原多段SELECT方案：多次重复扫描表，表数量越多，组合呈指数级增长，维护和性能都不可接受。
• LEFT JOIN方案：虽然能实现需求，但多次LEFT JOIN会带来额外的关联开销，且若表内存在重复ID，需额外处理去重，效率不如上述方案。

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