如何将重复列表判断代码封装为BigQuery的hasAny/hasAll/hasNone函数

需求背景 #

需要封装BigQuery函数，替代重复的IN unnest()+OR/AND逻辑，实现三个核心功能：

• hasAny：判断目标数组是否包含指定列表中的任意元素（集合存在交集）
• hasAll：判断目标数组是否包含指定列表中的所有元素（指定列表是目标数组的子集）
• hasNone：判断目标数组完全不包含指定列表中的任何元素（集合无交集）

函数实现代码 #

1. 创建hasAny函数

-- 替换为你的项目和数据集ID
CREATE OR REPLACE FUNCTION `your-project.your-dataset.hasAny`(target_arr ARRAY<STRING>, check_arr ARRAY<STRING>)
RETURNS BOOLEAN
LANGUAGE SQL
AS $$
  SELECT ARRAY_LENGTH(ARRAY_INTERSECT(target_arr, check_arr)) > 0
$$;

逻辑说明：利用ARRAY_INTERSECT获取两个数组的交集，若交集长度大于0则说明存在匹配元素。

2. 创建hasAll函数

-- 替换为你的项目和数据集ID
CREATE OR REPLACE FUNCTION `your-project.your-dataset.hasAll`(target_arr ARRAY<STRING>, check_arr ARRAY<STRING>)
RETURNS BOOLEAN
LANGUAGE SQL
AS $$
  SELECT ARRAY_LENGTH(ARRAY_INTERSECT(target_arr, check_arr)) = ARRAY_LENGTH(check_arr)
$$;

逻辑说明：若两个数组的交集长度等于待检查数组的长度，说明待检查的所有元素都在目标数组中。

3. 创建hasNone函数

-- 替换为你的项目和数据集ID
CREATE OR REPLACE FUNCTION `your-project.your-dataset.hasNone`(target_arr ARRAY<STRING>, check_arr ARRAY<STRING>)
RETURNS BOOLEAN
LANGUAGE SQL
AS $$
  SELECT NOT `your-project.your-dataset.hasAny`(target_arr, check_arr)
$$;

逻辑说明：直接取反hasAny的结果，实现完全无匹配的判断。

注：如果需要处理其他类型的数组（如INT64、NUMERIC），只需将函数参数中的ARRAY<STRING>替换为对应类型即可，逻辑无需修改。

示例用法 #

用你提供的测试表验证三个函数：

WITH tbl AS (
  SELECT 1 AS id, ['yellow', 'red', 'green'] AS colors UNION ALL
  SELECT 2, ['yellow', 'blue']
)
SELECT
  id,
  colors,
  `your-project.your-dataset.hasAny`(colors, ['red', 'green', 'magenta']) AS has_any_match,
  `your-project.your-dataset.hasAll`(colors, ['yellow', 'red']) AS has_all_match,
  `your-project.your-dataset.hasNone`(colors, ['red', 'magenta']) AS has_none_match
FROM tbl;

执行结果：

内容的提问来源于stack exchange，提问作者David542