sql - BigQuery 基于(数组包含在数组中)条件连接 2 个表

Question

我正在努力实现以下目标。假设我有两个表:

WITH table_1 as (
SELECT
* FROM UNNEST([
  STRUCT([1] as A, [2,3,4] as B),
  STRUCT([2],[6,7])
  ])
)

表 2:

WITH example as (
SELECT
* FROM UNNEST([
  STRUCT([1,2] as C, [77] as D),
  STRUCT([3,4],[88]),
  STRUCT([4],[99])
  ])
)

我想根据以下条件合并 table_1 和 table_2，即 C 的所有值都必须在 B 中:
SELECT A, C, D FROM table_1 LEFT JOIN table_2 ON C CONTAINED IN B
这将导致下表:



我的问题是是否/如何获得所需的结果。我写不出来 CONTAINED IN两个数组的语句作为 LEFT JOIN 的条件陈述。另一个要求是表 1 包含 1 亿行，表 2 包含 2.5 万行。因此，该解决方案必须是有效的。我知道这会增加问题的难度...:P

您的帮助将不胜感激!

Answer 1

WITH table_1 as (
SELECT
* FROM UNNEST([
  STRUCT([1] as A, [2,3,4] as B),
  STRUCT([2],[6,7])
  ])
),
table_2 as (
SELECT
* FROM UNNEST([
  STRUCT([1,2] as C, [77] as D),
  STRUCT([3,4],[88]),
  STRUCT([4],[99])
  ])
)

SELECT table_1.A, table_2.C, table_2.D
FROM table_1 , table_2 , UNNEST([
                      (SELECT ARRAY_LENGTH(table_2.C) - COUNT(1) 
                      FROM UNNEST(table_2.C) AS col_c 
                      JOIN UNNEST(table_1.B)  AS col_b 
                      ON col_c = col_b)]) AS x
WHERE x = 0

这会产生所需的输出。