如何在BigQuery中实现基于Lookup Table的字符串词项移除函数

你的需求是把PostgreSQL中通过游标循环替换Lookup Table单词的逻辑迁移到BigQuery，由于BigQuery UDF不支持游标和循环，我们可以通过正则表达式批量替换或JavaScript UDF循环两种方式实现，确保和原逻辑一致（长短语优先替换）。

方法一：SQL UDF（原生BigQuery SQL实现） #

这种方式利用STRING_AGG将Lookup表中的短语按长度降序拼接成正则表达式，一次性完成替换，无需循环：

CREATE OR REPLACE FUNCTION fn.remove_substring(input_str STRING)
RETURNS STRING
AS (
  REGEXP_REPLACE(
    input_str,
    -- 生成匹配所有需移除短语的正则：按长度降序，转义特殊字符，加单词边界
    (SELECT r'\b(' || STRING_AGG(REGEXP_REPLACE(word, r'([\\\^\$\.\|\?\*\(\)\[\]\{\}+-])', r'\\\1'), '|' ORDER BY LENGTH(word) DESC) || r')\b'),
    '',
    'gi' -- 忽略大小写、全局替换
  )
);

逻辑说明： #

1. 排序：按短语长度降序聚合，确保长短语先被匹配替换（修正原PostgreSQL中按字典序排序的逻辑，更贴合实际需求）
2. 转义特殊字符：用REGEXP_REPLACE转义正则元字符（如.、*等），防止这些字符干扰匹配逻辑
3. 单词边界：用\b（等价于PostgreSQL的\y）确保只匹配完整单词/短语，不会误替换字符串中间的子串

方法二：JavaScript UDF（贴近原PostgreSQL循环逻辑） #

如果更倾向于和原代码的循环逻辑对齐，可以用BigQuery的JavaScript UDF实现：

CREATE OR REPLACE FUNCTION fn.remove_substring(input_str STRING)
RETURNS STRING
LANGUAGE js
AS """
// 从Lookup表获取按长度降序排列的短语列表
const words = (SELECT ARRAY_AGG(word ORDER BY LENGTH(word) DESC) FROM lookup);
let result = input_str;

for (const word of words) {
  // 转义正则特殊字符
  const escapedWord = word.replace(/[.*+?^${}()|[\]\\]/g, '\\$&');
  // 构造带单词边界的正则，开启全局+忽略大小写
  const regex = new RegExp(`\\b${escapedWord}\\b`, 'gi');
  // 替换当前短语
  result = result.replace(regex, '');
}

return result;
""";

逻辑说明： #

完全复刻PostgreSQL的循环替换逻辑，通过BigQuery的JS UDF支持循环，同样先处理长短语，转义特殊字符，确保匹配完整单词。

测试验证 #

假设Lookup表lookup有如下数据：

调用函数测试：

SELECT fn.remove_substring('s1 s2 s3 s4 s5 s6') AS output;

输出结果：s1 s3 s6，和预期一致。

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