BigQuery Standard SQLで配列の差集合を求める方法 #GoogleCloud

$A$と$B$を配列として時に、$A \setminus B$となる配列を返すUDF関数を作ってみました。

#standardSQL
CREATE TEMPORARY FUNCTION ARRAY_INT64_INCLUDE(arr ARRAY<INT64>, val INT64)
RETURNS BOOL AS ((
  SELECT LOGICAL_OR(elem = val) FROM UNNEST(arr) as elem
));

CREATE TEMPORARY FUNCTION ARRAY_INT64_SUB(arr1 ARRAY<INT64>, arr2 ARRAY<INT64>)
RETURNS ARRAY<INT64> AS ((
  SELECT ARRAY(SELECT val FROM UNNEST(arr1) AS val WHERE NOT(ARRAY_INT64_INCLUDE(arr2, val)))
));

SELECT ARRAY_INT64_SUB([1, 2, 3, 4, 5], [2, 3, 4])