含REPEATED字段的大表ID配对查询优化方案咨询

问题背景 #

现有一张约70万行的表my_table，每行包含REPEATED类型的value字段，每个ID平均对应150个value值。需求是找出所有存在共同value的ID对，仅保留id1 < id2的唯一组合（避免(A,B)和(B,A)重复）。

示例表结构 #

期望输出 #

当前方案及痛点 #

当前通过UNNEST展开REPEATED字段生成约1亿行数据，再执行自连接查询，SQL如下：

WITH
-- 生成约1亿行数据
flattened_table AS (
  SELECT 
    id,
    value,
  FROM
    my_table,
    UNNEST(value) AS value
)

SELECT 
  t1.id AS id1,
  t2.id AS id2,
FROM
  flattened_table t1,
  flattened_table t2
WHERE
  t1.id < t2.id      -- 仅保留单向组合，避免重复
  AND t1.value = t2.value

该方案因全量自连接涉及1亿行数据，计算量过大，运行耗时极长。

优化方案 #

核心思路：先按value分组聚合关联的ID列表，再在每个分组内生成合法ID对，彻底避免全量自连接的巨大计算开销。

方案1：通用SQL实现 #

WITH
-- 按value分组，收集关联的唯一ID列表，过滤掉无配对可能的分组
value_to_ids AS (
  SELECT
    value,
    ARRAY_AGG(DISTINCT id) AS id_list
  FROM
    my_table,
    UNNEST(value) AS value
  GROUP BY
    value
  HAVING ARRAY_LENGTH(id_list) >= 2
),
-- 在每个ID列表内生成id1 < id2的组合
id_pairs AS (
  SELECT
    id1,
    id2
  FROM
    value_to_ids,
    UNNEST(id_list) AS id1,
    UNNEST(id_list) AS id2
  WHERE
    id1 < id2
)
-- 去重（同一ID对可能因多个共享value重复出现）
SELECT DISTINCT id1, id2 FROM id_pairs

方案2：针对BigQuery的高效优化版 #

利用GENERATE_ARRAY生成有序索引，直接生成合法ID对，无需额外过滤：

WITH
value_to_ids AS (
  SELECT
    value,
    ARRAY_AGG(DISTINCT id ORDER BY id) AS sorted_ids
  FROM
    my_table,
    UNNEST(value) AS value
  GROUP BY
    value
  HAVING ARRAY_LENGTH(sorted_ids) >= 2
),
id_pairs AS (
  SELECT
    sorted_ids[OFFSET(i)] AS id1,
    sorted_ids[OFFSET(j)] AS id2
  FROM
    value_to_ids,
    -- 生成第一个ID的索引范围（从0到倒数第二个元素）
    UNNEST(GENERATE_ARRAY(0, ARRAY_LENGTH(sorted_ids)-2)) AS i,
    -- 生成第二个ID的索引范围（从i+1到最后一个元素，确保id1 < id2）
    UNNEST(GENERATE_ARRAY(i+1, ARRAY_LENGTH(sorted_ids)-1)) AS j
)
SELECT DISTINCT id1, id2 FROM id_pairs

优化原理 #

1. 数据量前置过滤：直接排除仅关联单个ID的value，避免无效计算；
2. 缩小计算范围：仅在每个value的ID列表内部生成组合，而非1亿行的全量自连接；
3. 去重提前：聚合阶段对ID去重，减少后续组合的数量；
4. 有序组合生成：方案2通过索引直接生成id1 < id2的组合，省去额外过滤步骤，进一步提升效率。

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