BigQuery中如何高效将两个repeated字段按sku左外连接合并为单个repeated字段

核心思路 #

利用BigQuery原生的单行数组子查询能力，在不打散整表、不产生额外行膨胀的前提下，完成两个重复字段按SKU的左外关联，是性能最优的实现方式，尤其适合大表、数组元素较多的场景。

实现代码 #

假设你的表中product1、product2两个REPEATED字段的元素均为包含sku字段的STRUCT类型，代码示例如下：

SELECT
  -- 保留原表所有非合并字段
  * EXCEPT(product1, product2),
  -- 生成合并后的单个REPEATED字段
  ARRAY(
    SELECT AS STRUCT
      -- 保留product1的所有属性
      p1.*,
      -- 匹配product2中同SKU的所有属性，无匹配则为NULL
      (SELECT AS STRUCT p2.* FROM UNNEST(product2) p2 WHERE p2.sku = p1.sku LIMIT 1) AS product2_attr
    FROM UNNEST(product1) p1
  ) AS merged_product
FROM `your_project.your_dataset.your_table`

如果需要将product2的属性直接打平到合并后的STRUCT同一层，只需调整SELECT部分的字段即可：

SELECT
  * EXCEPT(product1, product2),
  ARRAY(
    SELECT AS STRUCT
      p1.sku,
      p1.attr1 AS product1_attr1,
      p1.attr2 AS product1_attr2,
      p2.attr1 AS product2_attr1,
      p2.attr2 AS product2_attr2
    FROM UNNEST(product1) p1
    LEFT JOIN UNNEST(product2) p2 ON p1.sku = p2.sku
  ) AS merged_product
FROM `your_project.your_dataset.your_table`

方案优势 #

• 无额外行膨胀：所有关联操作都在单行内部完成，不会因为数组元素多产生海量中间行
• 无全局Shuffle开销：不需要跨节点传输数据做聚合，性能相比传统展开再聚合的写法提升数倍到数十倍
• 写法简洁：不需要额外写GROUP BY逻辑还原原表行结构，也不需要罗列所有非数组字段做分组键

注意事项 #

如果product2中存在同一个SKU对应多条数据的场景，可以根据业务需求调整匹配逻辑，比如使用ARRAY_AGG将同SKU的多条product2数据合并为数组返回。

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