在BigQuery中从地址提取郊区名：最长匹配问题求解

问题说明 #

现有两张表：

• property 表存储完整地址
• suburbs 表存储所有郊区名称及对应州

匹配地址中的郊区时，多词郊区名（如"North Bondi"）会同时匹配到短名（"Bondi"）和长名（"North Bondi"），需要仅保留最长的匹配郊区名。BigQuery不支持在JOIN中直接使用MAX函数，需用其他方案实现。

表结构 #

property 表

suburbs 表

当前代码及问题 #

当前关联查询会返回重复匹配结果：

Select * from  ( SELECT p.address, s.suburb 
       FROM `property` p
       JOIN `suburbs` s
         ON INITCAP(p.address) LIKE CONCAT('%', INITCAP(s.suburb),' ', INITCAP(s.state), '%')
      GROUP BY p.address, s.suburb
   ) x
join `property` p
ON p.address = x.address
where p.address is not null;

实际结果

期望结果

解决方案：使用窗口函数筛选最长匹配 #

利用ROW_NUMBER()窗口函数，按地址分组后，以郊区名称长度倒序排序，仅保留每组中排名第一的记录（即最长的郊区名）：

WITH matched_suburbs AS (
  SELECT 
    p.address, 
    s.suburb,
    -- 按地址分组，郊区名长度倒序排序，给每个匹配项编号
    ROW_NUMBER() OVER (
      PARTITION BY p.address 
      ORDER BY LENGTH(s.suburb) DESC
    ) AS rn
  FROM `property` p
  JOIN `suburbs` s
    ON INITCAP(p.address) LIKE CONCAT('%', INITCAP(s.suburb), ' ', INITCAP(s.state), '%')
)
SELECT address, suburb
FROM matched_suburbs
WHERE rn = 1; -- 仅保留最长的匹配项

逻辑说明 #

1. WITH子句先关联所有符合条件的地址和郊区，生成包含排序编号的临时表
2. ROW_NUMBER()按address分组，同一地址下的匹配郊区按名称长度从长到短排序，最长的郊区会被标记为rn=1
3. 最后筛选rn=1的记录，得到每个地址对应的唯一最长郊区名

若存在多个长度相同的郊区名（极端情况），可根据需求调整ORDER BY条件（比如按郊区名字母顺序）确定优先级。

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