BigQuery中基于可变长度数组的两表关联实现方案问询

解决方案代码 #

SELECT
  zoo.cage_number,
  guidelines.task_id,
  ARRAY_CONCAT(zoo.animals, guidelines.more_animals_to_be_added) AS combined_animals
FROM zoo
JOIN guidelines
ON CARDINALITY(ARRAY_EXCEPT(guidelines.if_cage_has_all_these_animals, zoo.animals)) = 0

测试数据准备 #

首先创建zoo表：

CREATE OR REPLACE TABLE zoo AS
SELECT
  1 as cage_number,
  ['cats','parrots', 'dogs','ants'] AS animals 
UNION ALL
SELECT
  2 as cage_number,
  ['bears', 'jaguars', 'lions'] AS animals

创建guidelines表（修正原语句中字段名不一致问题，统一使用task_id）：

CREATE OR REPLACE TABLE guidelines AS
SELECT
  1 as task_id,
  ['cats', 'dogs'] AS if_cage_has_all_these_animals,
  ['rats', 'geese'] AS more_animals_to_be_added 
UNION ALL
SELECT
  2 as task_id,
  ['bears', 'jaguars', 'lions'] AS if_cage_has_all_these_animals,
  ['falcon'] AS more_animals_to_be_added 
UNION ALL
SELECT
  3 as task_id,
  ['parrots'] AS if_cage_has_all_these_animals,
  ['crow'] AS more_animals_to_be_added

逻辑说明 #

1. 关联条件：
   • 使用ARRAY_EXCEPT(guidelines.if_cage_has_all_these_animals, zoo.animals)提取规则数组中未出现在笼舍动物数组里的元素
   • 通过CARDINALITY()判断结果数组长度，长度为0时说明规则数组的所有元素都存在于笼舍数组中，满足关联要求
2. 数组合并：
   • 用ARRAY_CONCAT()替代伪代码中的CONCAT（后者仅支持字符串拼接），实现两个数组的合并

预期结果 #

执行查询后将得到以下结果：

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