sql - Google BigQuery 检查一个数组是否是另一个数组的超集/子集

Question

给定 Google BigQuery 中的两个数组，我需要弄清楚一个数组中的所有元素是否都包含在另一个数组中。

例如，我正在尝试完成以下查询:

WITH
  ArrayA AS (
  SELECT
    [1, 2, 3] arrA,
  UNION ALL
  SELECT
    [4, 5, 6])
  ArrayB AS (
  SELECT
    [1, 2, 3, 4, 5] arrB)
SELECT
  *
FROM
  ArrayA
CROSS JOIN
  ArrayB
WHERE
  <your code goes here>

这样的结果看起来像

arrA    | arrB
[1,2,3] | [1,2,3,4,5]

，因为 [1,2,3,4,5] 是 [1,2,3] 的超集，但不是 [4,5,6] 的超集。

非常感谢。

Answer 1

您可以检查 arrA 中的每个项目，然后获取最少的项目。如果arrB中arrA的所有项，就会有3个true，所以最小的为true。如果其中至少一个不在 arrB 中，则将有 2 个 true 和 1 个 false，因此最小值为 false。

WITH
ArrayA AS (
    SELECT [1, 2, 3] arrA,
    UNION ALL
    SELECT [4, 5, 6]
),
ArrayB AS (
  SELECT [1, 2, 3, 4, 5] arrB
)
SELECT
  *, 
  (
    SELECT min(a in UNNEST(arrB))
    FROM UNNEST(arrA) as a
  ) as is_a_in_b
FROM ArrayA
CROSS JOIN ArrayB

你也可以把它做成一个函数，用在很多地方。抱歉命名错误:)

CREATE TEMP FUNCTION is_array_in_array(subset ARRAY<int64>, main ARRAY<int64>) AS ((SELECT min(a in UNNEST(main)) FROM UNNEST(subset) as a));

WITH
ArrayA AS (
    SELECT [1, 2, 3] arrA,
    UNION ALL
    SELECT [4, 5, 6]
),
ArrayB AS (
  SELECT [1, 2, 3, 4, 5] arrB
)
SELECT
  *, 
  is_array_in_array(arrA, arrB) as is_a_in_b
FROM ArrayA
CROSS JOIN ArrayB