SQL Developer 同表两列名称模糊匹配/相似度比对问题咨询

第一步：字段标准化预处理（先做这一步再做匹配，能大幅降低匹配误差） #

• 先移除预设称呼关键词：遍历两个字段的内容，统一替换掉Mr.、Mrs.、Mister、Miss等预设称呼词，注意匹配时不区分大小写，避免漏处理大写开头的场景
• 再移除特殊字符：统一删除.、,、-等业务场景下无意义的特殊符号，可根据自身需求扩展要忽略的字符列表
• 内容归一化：把所有字符转成小写，连续空格合并成单个空格，避免大小写、多空格带来的无意义差异
  比如“Mr. Joe Smith”处理后是joe smith，“J. Smith”处理后是j smith，消除了称呼和特殊符号的干扰。

第二步：匹配规则设计（比纯Levenshtein距离更适配姓名匹配场景，性能更高） #

因为你只需要单条记录内的两个字段比对，不需要跨记录关联，所以即使是百万级数据，单条处理的时间复杂度都是O(n)，整体性能完全足够，不需要做额外索引优化。推荐优先用规则组合，比纯编辑距离准确率更高：

• 先做姓氏精确匹配：把处理后的两个姓名按空格拆分，取最后一段（通常是姓氏）做精确匹配，姓氏不同的直接排除，能过滤90%以上的不匹配条目
• 再做名字简化匹配：
  • 取名字部分的首字母做匹配，比如joe和j匹配通过
  • 也可以结合Levenshtein距离，设置阈值为名字长度的20%以内即判定匹配，比如3个字符的名字允许1个字符的差异
    针对你举的“Joe Smith”和“J. Smith”场景，经过预处理后姓氏smith精确匹配，名字首字母都是j，直接判定为潜在匹配即可。

第三步：不同环境的实现参考 #

SQL实现（适用于数据存在关系型数据库的场景） #

可以直接写SQL函数做预处理和匹配，示例逻辑：

-- 先创建标准化姓名的自定义函数
CREATE FUNCTION standardize_name(name_str VARCHAR(255)) RETURNS VARCHAR(255)
BEGIN
  -- 移除称呼
  SET name_str = REGEXP_REPLACE(name_str, '(Mr\\.|Mrs\\.|Mister|Miss)', '', 1, 0, 'i');
  -- 移除特殊字符
  SET name_str = REGEXP_REPLACE(name_str, '[.,]', '');
  -- 转小写 去多余空格
  SET name_str = TRIM(LOWER(name_str));
  SET name_str = REGEXP_REPLACE(name_str, '\\s+', ' ');
  RETURN name_str;
END;

-- 筛选匹配条目
SELECT * FROM 业务表
WHERE 
  -- 提取姓氏匹配
  SUBSTRING_INDEX(standardize_name(Originator), ' ', -1) = SUBSTRING_INDEX(standardize_name(Beneficiary), ' ', -1)
  -- 名字首字母匹配
  AND LEFT(SUBSTRING_INDEX(standardize_name(Originator), ' ', 1), 1) = LEFT(SUBSTRING_INDEX(standardize_name(Beneficiary), ' ', 1), 1)
  -- 可选加编辑距离阈值，根据需要调整
  AND LEVENSHTEIN(standardize_name(Originator), standardize_name(Beneficiary)) <= 3;

Python Pandas实现（适用于本地处理导出数据的场景） #

import pandas as pd
import re
from Levenshtein import distance

# 标准化函数
def standardize_name(name):
    if pd.isna(name):
        return ''
    # 移除称呼
    name = re.sub(r'(Mr\.|Mrs\.|Mister|Miss)', '', name, flags=re.IGNORECASE)
    # 移除特殊字符
    name = re.sub(r'[.,]', '', name)
    # 归一化
    return re.sub(r'\s+', ' ', name.strip().lower())

# 读取数据
df = pd.read_sql('select * from 业务表', con=你的数据库连接)
# 生成标准化字段
df['orig_std'] = df['Originator'].apply(standardize_name)
df['bene_std'] = df['Beneficiary'].apply(standardize_name)
# 筛选匹配条目
def is_match(row):
    orig_parts = row['orig_std'].split()
    bene_parts = row['bene_std'].split()
    # 空值直接排除
    if len(orig_parts) ==0 or len(bene_parts) ==0:
        return False
    # 姓氏匹配
    if orig_parts[-1] != bene_parts[-1]:
        return False
    # 名字首字母匹配
    if orig_parts[0][0] != bene_parts[0][0]:
        return False
    # 可选编辑距离校验
    if distance(row['orig_std'], row['bene_std']) > 3:
        return False
    return True

df['is_potential_match'] = df.apply(is_match, axis=1)
result = df[df['is_potential_match']]

大数据引擎实现（适用于Spark等分布式场景） #

逻辑和上面一致，用Spark UDF实现标准化和匹配函数，分布式处理百万级数据耗时通常在10秒以内。

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