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 という関数がそれ