[BigQuery]配列間での和/積集合をとってくるUDF

やりたきこと

２つarrayがあったときに、そのarray間での共通(積)の要素の集合or両方の要素(和)の集合をとってきたい
集合という型はないので配列の中身をユニークにすることで対応

SQLサンプル

CREATE TEMPORARY FUNCTION array_and(array1 ARRAY<STRING>, array2 ARRAY<STRING>)
  RETURNS ARRAY<STRING> AS ((
    SELECT 
     ARRAY_AGG(DISTINCT a1)
    FROM
       (
        SELECT
            a1
        FROM
            UNNEST(array1) as a1,UNNEST(array2) as a2
        WHERE 
            a1=a2
        )
));


CREATE TEMPORARY FUNCTION array_or(array1 ARRAY<STRING>, array2 ARRAY<STRING>)
  RETURNS ARRAY<STRING> AS ((
    SELECT 
     ARRAY_AGG(DISTINCT(a))
    FROM
       (
        SELECT
            a1 as a
        FROM
            UNNEST(array1) as a1
        UNION ALL
        SELECT 
            a2 as a
        FROM
            UNNEST(array2) as a2
        )
));

SELECT 
    # andの例
    array_and(["a", "b","c"],["a"]) as a,
    array_and(["a", "b","c"],["a","b"]) as ab,
    array_and(["b","c"],["b","c","d"]) as bc,
    array_and(["a", "b","c"],["d","e"]) as na_1,
    array_and(["a", "b","c"],[]) as na_2,
    # orの例
    array_or(["a","b","c"],["a"]) as abc,
    array_or(["a","c"],[]) as ac,
    array_or(["b","c"],["b","c","d"]) as bcd,
    array_or(["a", "b","c"],["d","e"]) as abcde

配列の型を定義しないと動かないのでstringを指定しているが、他の型でも動く