如何用Oracle SQL/PL/SQL或Excel识别自由文本客户重复记录

嘿，针对你手里百万级客户数据的去重难题，我结合Oracle SQL/PLSQL和Excel工具，整理了一套能覆盖大部分场景的方案，尤其是针对自由文本格式带来的各种混乱问题：

由于你的数据量达到百万级，Oracle的处理效率会比Excel高很多，而且能通过正则、内置函数和自定义逻辑处理自由文本的模糊匹配。

1. 基础重复识别：姓名（清洗后）+ 出生日期 #

出生日期是唯一的结构化字段，我们可以以此为核心，搭配清洗后的姓名来定位潜在重复。这个方法能覆盖大部分姓名格式混乱（大小写、标点、空格）以及单字段缺失的情况：

WITH cleaned_clients AS (
    SELECT 
        ch.id,
        -- 清洗名：去掉非字母字符、转小写，处理空值
        REGEXP_REPLACE(LOWER(NVL(ch.Client_First_Name, '')), '[^a-z]', '') AS clean_first_name,
        -- 清洗姓：同上
        REGEXP_REPLACE(LOWER(NVL(ch.Client_Last_Name, '')), '[^a-z]', '') AS clean_last_name,
        ch.Client_Date_Of_Birth,
        -- 拼接完整清洗姓名（处理单字段缺失）
        CASE 
            WHEN ch.Client_First_Name IS NULL THEN REGEXP_REPLACE(LOWER(ch.Client_Last_Name), '[^a-z]', '')
            WHEN ch.Client_Last_Name IS NULL THEN REGEXP_REPLACE(LOWER(ch.Client_First_Name), '[^a-z]', '')
            ELSE REGEXP_REPLACE(LOWER(ch.Client_First_Name || ' ' || ch.Client_Last_Name), '[^a-z ]', '')
        END AS full_clean_name
    FROM Client_Header ch
)
SELECT 
    full_clean_name,
    Client_Date_Of_Birth,
    LISTAGG(id, ', ') WITHIN GROUP (ORDER BY id) AS duplicate_ids,
    COUNT(*) AS duplicate_count
FROM cleaned_clients
WHERE full_clean_name <> '' -- 题目说明不会同时缺失名和姓，所以排除全空
GROUP BY full_clean_name, Client_Date_Of_Birth
HAVING COUNT(*) > 1;

2. 地址模糊匹配：结合Jaro-Winkler相似度 #

地址的自由文本问题更复杂（缩写、标点、顺序混乱），Oracle的UTL_MATCH包提供了JARO_WINKLER_SIMILARITY函数，可以计算字符串的相似度（0-100，值越高越相似）。我们可以先清洗地址，再结合姓名+出生日期做相似度匹配：

WITH cleaned_clients AS (
    SELECT 
        ch.id,
        CASE 
            WHEN ch.Client_First_Name IS NULL THEN REGEXP_REPLACE(LOWER(ch.Client_Last_Name), '[^a-z]', '')
            WHEN ch.Client_Last_Name IS NULL THEN REGEXP_REPLACE(LOWER(ch.Client_First_Name), '[^a-z]', '')
            ELSE REGEXP_REPLACE(LOWER(ch.Client_First_Name || ' ' || ch.Client_Last_Name), '[^a-z ]', '')
        END AS full_clean_name,
        ch.Client_Date_Of_Birth
    FROM Client_Header ch
),
cleaned_addresses AS (
    SELECT 
        ca.Client_Id,
        -- 清洗地址：转小写、替换常见州缩写、去掉标点、拼接所有地址字段
        REGEXP_REPLACE(
            LOWER(
                REPLACE(
                    REPLACE(
                        REPLACE(
                            ca.Address_Line1 || ' ' || NVL(ca.Address_Line2, '') || ' ' || NVL(ca.Address_Line3, '') || ' ' || ca.Suburb || ' ' || ca.State || ' ' || ca.Country,
                            'nsw', 'new south wales'),
                        'vic', 'victoria'),
                    'qld', 'queensland')
            ),
            '[^a-z0-9 ]', ''
        ) AS full_clean_address
    FROM Client_Address ca
),
client_full_clean AS (
    SELECT 
        cc.id,
        cc.full_clean_name,
        cc.Client_Date_Of_Birth,
        ca.full_clean_address
    FROM cleaned_clients cc
    JOIN cleaned_addresses ca ON cc.id = ca.Client_Id
)
-- 匹配姓名+出生日期一致，且地址相似度≥80的记录（阈值可根据实际情况调整）
SELECT 
    c1.id AS record_id_1,
    c2.id AS record_id_2,
    c1.full_clean_name,
    c1.Client_Date_Of_Birth,
    UTL_MATCH.JARO_WINKLER_SIMILARITY(c1.full_clean_address, c2.full_clean_address) AS address_similarity_score
FROM client_full_clean c1
JOIN client_full_clean c2 ON c1.id < c2.id -- 避免重复配对（比如1-2和2-1）
    AND c1.full_clean_name = c2.full_clean_name
    AND c1.Client_Date_Of_Birth = c2.Client_Date_Of_Birth
    AND UTL_MATCH.JARO_WINKLER_SIMILARITY(c1.full_clean_address, c2.full_clean_address) >= 80
ORDER BY address_similarity_score DESC;

3. 自定义PLSQL函数：处理复杂拼写/缩写 #

如果有大量常见的姓名/地址拼写变体（比如Dave→David，St→Street），可以写一个自定义函数来标准化这些内容，减少误判：

CREATE OR REPLACE FUNCTION standardize_text(p_input IN VARCHAR2) RETURN VARCHAR2 IS
    v_clean_text VARCHAR2(500);
BEGIN
    -- 基础清洗：转小写、去掉非字母数字空格
    v_clean_text := REGEXP_REPLACE(LOWER(NVL(p_input, '')), '[^a-z0-9 ]', '');
    
    -- 替换常见姓名缩写
    v_clean_text := REPLACE(v_clean_text, 'dave', 'david');
    v_clean_text := REPLACE(v_clean_text, 'johnny', 'john');
    v_clean_text := REPLACE(v_clean_text, 'liz', 'elizabeth');
    
    -- 替换常见地址缩写
    v_clean_text := REPLACE(v_clean_text, ' st ', ' street ');
    v_clean_text := REPLACE(v_clean_text, ' rd ', ' road ');
    v_clean_text := REPLACE(v_clean_text, ' apt ', ' apartment ');
    
    -- 可以继续添加更多业务相关的替换规则
    RETURN TRIM(v_clean_text);
END;
/

之后在SQL中直接调用这个函数即可，比如standardize_text(ch.Client_First_Name)。

4. 性能优化建议 #

针对百万级数据，直接跑全表连接会很慢，建议：

• 创建临时表存储清洗后的客户/地址数据，给full_clean_name、Client_Date_Of_Birth字段建索引；
• 分批次处理：按出生日期范围（比如按年份）或姓氏首字母分组，每次处理一部分数据；
• 避免在WHERE子句中对原始字段做函数运算，尽量用清洗后的临时表数据。

如果需要快速验证部分数据，或者处理导出后的小批量样本，Excel的工具也能满足需求：

1. 数据清洗 #

先把Client_Header和Client_Address关联后的完整数据导出到Excel，然后用函数清洗姓名和地址：

• 清洗姓名：=LOWER(REGEXREPLACE(A2, "[^a-z]", ""))（Excel 365支持REGEXREPLACE，旧版本可以嵌套多个SUBSTITUTE去掉标点）
• 清洗地址：=LOWER(SUBSTITUTE(SUBSTITUTE(B2, "nsw", "new south wales"), "st ", " street "))，按需添加更多缩写替换

2. 识别重复 #

• 精确匹配：用COUNTIFS针对清洗后的姓名+出生日期：=COUNTIFS($C:$C, C2, $D:$D, D2)，结果大于1的就是潜在重复；
• 模糊匹配：安装微软官方的Fuzzy Lookup插件，设置相似度阈值（比如80%），选择清洗后的地址、姓名、出生日期字段进行匹配，自动找出相似记录；
• 大数据量建议用Power Query：它的批量处理性能比普通Excel函数好，支持正则清洗和模糊匹配。

• 缺失名/姓：我们的SQL已经通过CASE语句处理了单字段缺失的情况，只使用非空的字段进行分组匹配；
• 地址为场所名称：这种确实很难100%识别，但可以尝试提取场所名称中的关键信息（比如学校名称的核心词），或者如果地址中包含邮编（比如示例中的2044），可以提取邮编作为额外匹配条件；
• 多种格式问题并存：通过多层清洗（先转小写、去标点，再替换缩写，最后用相似度匹配），能覆盖大部分混合格式问题。

内容的提问来源于stack exchange，提问作者E. L.