谷歌BigQuery检查一个数组是否是另一个数组的超集/子集

WITH
  array1 AS (
  SELECT
    [1, 2, 3, 4, 5] AS arr1),
  array2 AS (
  SELECT
    [1, 2, 3] AS arr2)

SELECT
  arr1 AS array1,
  arr2 AS array2,
  ARRAY_LENGTH(arr1) AS array1_length,
  ARRAY_LENGTH(arr2) AS array2_length,
  ARRAY_LENGTH(arr1) >= ARRAY_LENGTH(arr2) AS is_superset,
  ARRAY_LENGTH(arr1) = ARRAY_LENGTH(arr2) AND (
  SELECT
    COUNT(DISTINCT value)
  FROM
    UNNEST(arr1) AS value
  WHERE
    value NOT IN (SELECT value FROM UNNEST(arr2))) = 0 AS is_subset
FROM
  array1,
  array2