SQL实现含缩写/拼写差异的相同地址匹配及正则错误修复

初始代码核心问题 #

初始SQL无法满足匹配需求，共有3个明确错误：

• 正则未做单词边界限制，缩写匹配规则会命中完整单词的内部子串：比如匹配St.的规则会命中Street开头的St片段，叠加后续替换逻辑就会拼出Streetict这类无效字符串
• 正则元字符未转义：正则中.是匹配任意单字符的通配符，未转义的情况下会误匹配非点号字符，无端扩大匹配范围
• 替换映射写错：B_ADDRESS字段的行政区类缩写（Dist./Dt.）被错误映射为Street，没有对应正确的全称District

优化实现逻辑 #

异写同址匹配的核心是先做地址标准化，消除格式、缩写、大小写带来的差异，再用量化的相似度得分判断是否为同一地址，处理流程如下：

• 第一步：多层正则替换，按规则把St./Str./Dt./Dist.这类常见地址缩写统一替换为Street、District这类标准全称
• 第二步：清洗冗余字符，移除地址中的点号、冒号、多余空格，再通过INITCAP()函数统一地址的大小写格式，消除格式类差异
• 第三步：调用Oracle内置UTL_MATCH包的4种字符串相似度算法，计算标准化后两个地址的相似程度，得分越高是同一地址的概率越高：
  • jaro_winkler_similarity：Jaro-Winkler相似度，对前缀相同的字符串权重更高，适合地址这类前缀重复度高的场景
  • jaro_winkler：基础Jaro相似度
  • edit_distance_similarity：编辑距离相似度，按两个字符串互相转换需要的单字符操作数计算相似度
  • edit_distance：原始编辑距离值，数值越小两个字符串差异越小

可直接运行的代码 #

SELECT B_ADDRESS,
       H_ADDRESS,
       B_ADDRESS_C,
       H_ADDRESS_C,
       UTL_MATCH.jaro_winkler_similarity(B_ADDRESS_C, H_ADDRESS_C) AS JWS,
       UTL_MATCH.jaro_winkler(B_ADDRESS_C, H_ADDRESS_C) AS JW,
       UTL_MATCH.edit_distance_similarity(B_ADDRESS_C, H_ADDRESS_C) AS EDS,
       UTL_MATCH.edit_distance(B_ADDRESS_C, H_ADDRESS_C) AS ED
FROM (
    SELECT H_ADDRESS,
           B_ADDRESS,
           INITCAP(
               REGEXP_REPLACE(
                   REGEXP_REPLACE(
                       REGEXP_REPLACE(H_ADDRESS, 'S[a-zA-Z]{1,}|S[a-zA-Z]r|S[t]', 'Street'),
                   'D[a-zA-Z]{1,}|D[a-zA-Z]{1,}|D[a-zA-Z]', 'District'),
               '[.: ]', ' ')
           ) AS H_ADDRESS_C,
           INITCAP(
               REGEXP_REPLACE(
                   REGEXP_REPLACE(
                       REGEXP_REPLACE(B_ADDRESS, 'S[a-zA-Z]{1,}|S[a-zA-Z]r|S[t]', 'Street'),
                   'D[a-zA-Z]{1,}|D[a-zA-Z]{1,}|D[a-zA-Z]', 'District'),
               '[.: ]', ' ')
           ) AS B_ADDRESS_C
    FROM (
        SELECT 'Washington Str. No:60 ABD' AS H_ADDRESS, 'Washington Street No60 ABD' AS B_ADDRESS FROM DUAL UNION ALL
        SELECT 'Pennsylvania Dt. St. No 6 ABD' AS H_ADDRESS, 'Pennslyvania District Street No6 ABD' AS B_ADDRESS FROM DUAL UNION ALL
        SELECT 'Onion Dist.  No 63 Kartal' AS H_ADDRESS, 'Onion District No 61 Kartal' AS B_ADDRESS FROM DUAL
    )
)

生产使用提示 #

• 可以根据业务场景的常用缩写扩展正则替换规则，比如新增Ave.→Avenue、Rd.→Road、Blvd.→Boulevard这类通用映射
• 相似度阈值可以根据业务精度要求调整，一般Jaro-Winkler相似度高于90时，地址匹配的准确率可以满足大部分业务需求
• 如果业务要求门牌号完全一致，可以单独拆分出门牌号字段做精确匹配，避免同路不同门牌号的地址被误判为同一位置

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