sql - 计算 BigQuery 中数组中的匹配项数

Question

我如何计算数组中匹配项的数量？例如，对于数组 [1,2,3] 中的数字 [1,3]，将有 2 个匹配项，而对于数组 [1,2]，将有 1 个匹配项。现在我只能检查 [1,3] 是否在数组中。

  WITH `arrays` AS (
  SELECT 1 id, [1,2,3] as arr
  UNION ALL
  SELECT 2, [1,2]
  UNION ALL
  SELECT 3, [3]

)
SELECT id, arr, [1,3] as numbers,
  CASE 
   1 IN UNNEST(arr) and 
   3 IN UNNEST(arr) 
  WHEN TRUE THEN 'numbers is in array'
  ELSE 'numbers is not in array'
  END conclusion

FROM `arrays` 

我试图得到这样的结果:

Answer 1

使用数学，以下似乎是可能的:

• 如果 arr 和 numbers 的联合与 arr 相同，它将是 numbers is in array
• 如果 arr 和 numbers 的并集大于 arr，则与增加的数量一样多的元素不在 arr 中。
• 因此，numbers_len - (union_len - arr_len) 将被检查

WITH `arrays` AS (
  SELECT 1 id, [1,2,3] as arr
  UNION ALL
  SELECT 2, [1,2]
  UNION ALL
  SELECT 3, [3]
),
calculated_arrays AS (
  SELECT *, [1,3] as numbers,
         ARRAY_LENGTH(ARRAY(SELECT DISTINCT * FROM UNNEST(arr || [1, 3]))) AS union_len,
         ARRAY_LENGTH(arr) AS arr_len,
         ARRAY_LENGTH([1, 3]) AS numbers_len
    FROM `arrays`
)
SELECT id, arr, numbers,
       numbers_len - union_len + arr_len AS check, 
       IF (union_len = arr_len, 'numbers is in array', 'numbers is not in array') AS conclusion
  FROM calculated_arrays
;

输出: