BigQueryでスペース区切りのキーワードで部分一致AND検索をする方法

#standardSQL

"hoge huga" で
"hogehage" は false
"hogehuga" は true
を返すような検索エンジンっぽい挙動をするテキスト一致を実現したい
残念ながらUDFを利用

#standardSQL
CREATE  TEMPORARY FUNCTION
  ALL_MATCH(text STRING, keywords ARRAY<STRING>)
    RETURNS BOOL AS ((
    SELECT
      ARRAY_LENGTH(keywords)=SUM(match_flg)
    FROM (
      SELECT
         CAST(STRPOS(text, kw)>0 AS INT64) as match_flg
      FROM
        UNNEST(keywords) kw    
    )
    ));

WITH
  a AS (
  SELECT
    ["a a", "a aa", "aa a", "a aa aaa"] as aa
  ),

  b AS (
  SELECT
    SPLIT("a aa" ," ") as bb1,
    SPLIT("a a" ," ") as bb2
  )

SELECT
  aaa,
  bb1,
  ALL_MATCH(aaa, bb1) as match1,
  bb2,
  ALL_MATCH(aaa, bb2) as match2
 FROM
  a,UNNEST(aa) aaa, b

ALL_MATCH という関数がそれ