BigQuery StandardSQL の SQL UDF で書いた、文字列配列の包含判定関数

connotation.sql

 CREATE TEMP FUNCTION connotation(a ARRAY<STRING>, b ARRAY<STRING>)
RETURNS BOOL AS ((SELECT COUNT(b_) = 0
                    FROM UNNEST(b) AS b_
         LEFT OUTER JOIN UNNEST(a) AS a_ ON b_ = a_
                   WHERE a_ IS NULL));
                   
SELECT connotation(['a', 'b'], ['a']) --> true
     , connotation(['a'], ['a', 'b']) --> false
     , connotation(['a', 'b', 'c'], ['c']) --> true