BigQuery基于数组列关联表的实现技术问题

表结构与数据说明 #

Table1 #

• 字段：item、unit_sold_item（对应item的销量）、item_2、unit_sold_item2（对应item_2的销量）
• 数据行：item='x'，unit_sold_item=1000，item_2='y'，unit_sold_item2=500

Table2 #

• 字段：bundle_items、items_in_bundle（数组类型，存储捆绑商品列表）
• 数据行：
  • bundle_items='a'，items_in_bundle=['x','y']
  • bundle_items='b'，items_in_bundle=['x','y','z']

需求 #

仅当Table1的item和item_2同时存在于Table2的items_in_bundle数组中时，关联两张表获取结果。

问题分析 #

你之前尝试的SQL写法中，直接在JOIN的ON条件里使用unnest(y.children)不符合SQL语法规范——UNNEST用于展开数组，必须放在FROM子句中，不能直接作为关联条件的一部分，这会导致语法解析错误。

可行解决方案 #

方案1：使用数组包含函数（推荐，高效简洁） #

适用于支持数组操作符/函数的SQL引擎（如PostgreSQL、BigQuery）：

-- PostgreSQL 写法
SELECT 
  t1.item,
  t1.unit_sold_item,
  t1.item_2,
  t1.unit_sold_item2,
  t2.bundle_items,
  t2.items_in_bundle
FROM Table1 t1
JOIN Table2 t2 
  ON t2.items_in_bundle @> ARRAY[t1.item, t1.item_2]::varchar[];

-- BigQuery 写法
SELECT 
  t1.item,
  t1.unit_sold_item,
  t1.item_2,
  t1.unit_sold_item2,
  t2.bundle_items,
  t2.items_in_bundle
FROM Table1 t1
JOIN Table2 t2 
  ON ARRAY_CONTAINS_ALL(t2.items_in_bundle, [t1.item, t1.item_2]);

说明：

• PostgreSQL的@>操作符表示左侧数组包含右侧数组的所有元素，直接判断Table2的数组是否同时包含item和item_2。
• BigQuery的ARRAY_CONTAINS_ALL函数作用相同，检查目标数组是否包含指定的所有元素。

方案2：使用UNNEST配合EXISTS子查询 #

如果需要通过展开数组实现，可使用EXISTS子查询分别判断两个元素是否存在：

SELECT 
  t1.item,
  t1.unit_sold_item,
  t1.item_2,
  t1.unit_sold_item2,
  t2.bundle_items,
  t2.items_in_bundle
FROM Table1 t1
JOIN Table2 t2 
  ON EXISTS (
    SELECT 1 FROM UNNEST(t2.items_in_bundle) AS bi WHERE bi = t1.item
  )
  AND EXISTS (
    SELECT 1 FROM UNNEST(t2.items_in_bundle) AS bi WHERE bi = t1.item_2
  );

说明：通过两个EXISTS子查询，分别验证item和item_2是否在Table2的数组中，只有同时满足时才关联。

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