如何在BigQuery中高效对比字符串数组并找出缺失元素？

你已经通过DECLARE声明了两个字符串数组，目标是找出list1中存在但list2中缺失的元素（比如示例中的orange），之前手动用UNION ALL逐个拼接元素的方法在数组元素较多（数百个）时效率极低，这里提供更高效的方案：

高效实现代码 #

第一种写法（左连接判空）：

DECLARE list1 ARRAY<string>;
DECLARE list2 ARRAY<string>;

SET list1 = ['orange','red','black'];
SET list2 = ['red','black'];

-- 找出list1有但list2没有的元素
SELECT element
FROM UNNEST(list1) AS element
LEFT JOIN UNNEST(list2) AS element2
ON element = element2
WHERE element2 IS NULL;

第二种写法（用EXCEPT DISTINCT简化逻辑）：

DECLARE list1 ARRAY<string>;
DECLARE list2 ARRAY<string>;

SET list1 = ['orange','red','black'];
SET list2 = ['red','black'];

SELECT element
FROM UNNEST(list1) AS element
EXCEPT DISTINCT
SELECT element
FROM UNNEST(list2) AS element;

方案优势 #

• 无需手动逐个元素拼接UNION ALL，直接用UNNEST展开数组，代码维护成本低
• 针对数百个元素的数组，BigQuery对UNNEST的处理效率远高于手动拼接的子查询，执行计划更优化
• EXCEPT DISTINCT写法还会自动处理list1中的重复元素，返回去重后的缺失值

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