如何在两表间为含指定术语的列生成二进制标记（避免交叉连接）

AI 小北

2026-8-9

无需Cross Join实现术语匹配标记优化方案

问题背景

需要为表t2的search_terms列生成二进制标记（yes/no），判断该列值是否包含表t1的terms列中任意术语，且需避免因术语数量过多导致cross join引发的性能问题。

优化思路

将表t1中的所有术语合并为一个正则匹配模式，仅通过一次模式匹配完成判断，避免笛卡尔积式的cross join。

基础实现代码（以BigQuery为例）

-- 生成合并后的正则匹配模式，仅返回1行数据
WITH term_pattern AS (
  SELECT CONCAT(r'(?i)\b(', STRING_AGG(DISTINCT terms, '|'), r')\b') AS match_pattern
  FROM t1
)
-- 对t2的每条记录执行一次正则匹配
SELECT 
  search_terms,
  CASE 
    WHEN REGEXP_CONTAINS(search_terms, match_pattern) THEN 'yes' 
    ELSE 'no' 
  END AS flag
FROM t2
CROSS JOIN term_pattern -- 仅与1行数据关联，无性能损耗

关键细节说明

正则模式解析：
- (?i)：开启不区分大小写匹配
- \b：匹配单词边界，确保术语作为独立单词匹配（避免bananapudding被误判为包含banana）
- STRING_AGG(DISTINCT terms, '|')：将所有去重后的术语用|拼接，实现“任意术语匹配”
处理含正则特殊字符的术语：
如果t1的术语包含.、*、+等正则特殊字符，需先转义避免匹配异常，代码调整如下：

WITH term_pattern AS (
  SELECT 
    CONCAT(
      r'(?i)\b(', 
      STRING_AGG(DISTINCT REGEXP_REPLACE(terms, r'([\.\+\*\?\[\]\(\)\{\}\^\$\|])', r'\\\1'), '|'), 
      r')\b'
    ) AS match_pattern
  FROM t1
)
SELECT 
  search_terms,
  CASE WHEN REGEXP_CONTAINS(search_terms, match_pattern) THEN 'yes' ELSE 'no' END AS flag
FROM t2
CROSS JOIN term_pattern