BigQuery中如何高效行外连接数组列，无需Unnest与Group？

可以直接利用BigQuery的数组内联操作，在单一行级完成数组的全外连接与编辑类型计算，无需拆分多次UNNEST和后续的GROUP BY聚合，逻辑更紧凑且性能更优：

WITH source_data AS (
  SELECT
    1 AS pkField,
    [
      STRUCT(1 AS id, 1 AS fieldHash),
      STRUCT(2 AS id, 2 AS fieldHash),
      STRUCT(3 AS id, 3 AS fieldHash)
    ] AS before,
    [
      STRUCT(1 AS id, 1 AS fieldHash),
      STRUCT(2 AS id, 0 AS fieldHash),  -- record 2 edited
      -- record 3 deleted
      STRUCT(4 AS id, 4 AS fieldHash), -- record 4 added
      STRUCT(5 AS id, 5 AS fieldHash)  -- record 5 added
    ] AS after
)
SELECT
  pkField,
  ARRAY(
    SELECT AS STRUCT
      COALESCE(b.id, a.id) AS id,
      CASE
        WHEN a.id IS NULL THEN 'DELETE'
        WHEN b.id IS NULL THEN 'ADD'
        WHEN b.fieldHash != a.fieldHash THEN 'EDIT'
        ELSE 'NONE'
      END AS editType
    FROM UNNEST(before) b
    FULL OUTER JOIN UNNEST(after) a
    ON b.id = a.id
  ) AS edits
FROM source_data

核心优化点： #

• 行级直接处理：在ARRAY()构造器内部对两个数组执行FULL OUTER JOIN，无需拆分多个子查询生成临时表
• 减少中间数据：避免了原方案中两次UNNEST+全外连接+GROUP BY的繁琐流程，减少临时数据的生成与计算开销
• 逻辑更直观：直接在数组层面完成对比与类型标记，代码结构更清晰，易于维护

这个方案的输出结果和原代码完全一致，但执行效率更高，尤其在处理大表时，能显著降低资源消耗。

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