通过映射表与正则表达式更新BigQuery数据表

问题背景 #

需要从Table2的映射项中，匹配Table1的textWithFoundItemInIt字段内容，将匹配到的映射项更新到updated_foundItem列，支持不区分大小写匹配，多匹配项取任意一个即可。

原语句的优化点 #

你的原语句存在两处可优化的地方：

• Table2的映射项有重复值，用string_agg时先去重，减少正则匹配的冗余计算
• 子查询无需额外DISTINCT关键字，避免不必要的结果去重操作

完整UPDATE语句 #

UPDATE Table1
SET updated_foundItem = sub.foundItem
FROM (
  SELECT 
    t1.textWithFoundItemInIt,
    -- 若不需要严格匹配完整单词（比如匹配CaesarCaesar中的Caesar），去掉\b即可
    REGEXP_EXTRACT(t1.textWithFoundItemInIt, r'(?i)\b(' || mapping_items || r')\b') AS foundItem
  FROM Table1 t1
  CROSS JOIN (
    SELECT STRING_AGG(DISTINCT mappingItem, '|') AS mapping_items
    FROM Table2
  ) t2
) sub
-- WHERE子句核心逻辑
WHERE Table1.textWithFoundItemInIt = sub.textWithFoundItemInIt
  AND sub.foundItem IS NOT NULL;

关键说明 #

1. 正则匹配规则：
   • (?i)开启不区分大小写匹配
   • \b是单词边界，确保匹配完整的映射项（比如不会把Adams误识别为Adam），如果需要匹配包含映射项的子串，直接去掉\b即可
2. WHERE子句作用：
   • Table1.textWithFoundItemInIt = sub.textWithFoundItemInIt：关联主表与子查询的同一条记录
   • sub.foundItem IS NOT NULL：仅更新能匹配到有效映射项的记录，避免将未匹配的记录更新为空值
3. 多匹配项处理：REGEXP_EXTRACT会返回第一个匹配到的映射项，符合你预期的「Caesar [or Bert]」任意结果要求

预期结果验证 #

执行后会完全符合你给出的预期：

• 包含Adam的记录更新为Adam
• 包含Bert的记录更新为Bert
• 包含Caesar（包括连续出现或带引号的）的记录更新为Caesar
• 同时包含Caesar和Bert的记录，返回正则匹配到的第一个项

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