BigQuery中用REGEXP_CONTAINS匹配最长正则表达式避免重复行

问题分析 #

你的查询出现重复行，核心原因是短名称（比如anya）是长名称（比如anya taylor）的前缀，导致同一col1会匹配到多个符合条件的name。要解决这个问题，我们需要为每个col1筛选出匹配到的最长name。

修正后的SQL代码 #

WITH nm as (select 'anya taylor' name union all
select 'anya' name union all
select 'shawn' name union all
select 'shawn mendes' name),

dt as (select 'shawn mendes' col1 union all
select 'shawn the sheep' col1 union all
select 'anya stuart' col1 union all
select 'anya taylor joy' col1),

-- 先获取所有匹配结果，同时计算name的长度
match_results as (
  select
    name,
    col1,
    LENGTH(name) as name_length
  from nm, dt
  where REGEXP_CONTAINS(col1, CONCAT(r'(?i)', name))
)

-- 对每个col1，筛选出name长度最长的记录
select
  name,
  col1
from (
  select
    *,
    ROW_NUMBER() OVER(PARTITION BY col1 ORDER BY name_length DESC) as rn
  from match_results
)
where rn = 1

代码说明 #

1. match_results CTE：保留所有符合正则匹配的结果，新增name_length字段记录每个匹配名称的长度，为后续筛选做准备。
2. 窗口函数筛选：用ROW_NUMBER()窗口函数按col1分组，组内按name_length降序排序，每个组里最长的名称会被标记为rn=1，最后只取rn=1的记录，就能得到唯一的最长匹配结果。

最终输出结果 #

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