如何用SQL检测BigQuery中表的列是否为另一表列的子集？

假设表结构（elems为数组类型） #

两张表均包含id（数值型）和elems（数组类型，如ARRAY<INT64>）字段：

• table1：待检测表，存储需要验证的元素集合
• table2：基准表，存储对应id下的完整元素集合

解决方案1：使用LEFT JOIN + UNNEST #

SELECT t1.id, elem
FROM table1 t1
CROSS JOIN UNNEST(t1.elems) AS elem
LEFT JOIN table2 t2
ON t1.id = t2.id AND elem IN UNNEST(t2.elems)
WHERE t2.id IS NULL

解决方案2：使用NOT EXISTS子查询 #

SELECT t1.id, elem
FROM table1 t1
CROSS JOIN UNNEST(t1.elems) AS elem
WHERE NOT EXISTS (
  SELECT 1
  FROM table2 t2
  WHERE t2.id = t1.id AND elem IN UNNEST(t2.elems)
)

逻辑说明 #

1. 用CROSS JOIN UNNEST(t1.elems)将table1中每个id对应的数组拆分为单个元素的行
2. 通过匹配id，检查每个元素是否存在于table2对应id的elems数组中
3. 筛选出不存在于table2的元素，即为需要返回的结果

特殊情况：elems为逗号分隔的字符串类型 #

如果elems是用逗号分隔的字符串（如"1001,1002,1004"），需要先将字符串转为数组再处理：

SELECT t1.id, elem
FROM (
  SELECT 
    id, 
    SAFE_CAST(elem AS INT64) AS elem
  FROM table1
  CROSS JOIN UNNEST(SPLIT(elems, ',')) AS elem
) t1
LEFT JOIN (
  SELECT 
    id, 
    SAFE_CAST(elem AS INT64) AS elem
  FROM table2
  CROSS JOIN UNNEST(SPLIT(elems, ',')) AS elem
) t2
ON t1.id = t2.id AND t1.elem = t2.elem
WHERE t2.id IS NULL

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