基于主客户表清理视图相似姓名并修复拼写至新表

步骤说明 #

由于无法使用Levenshtein等编辑距离函数，我们通过不区分大小写的分组去重+模糊前缀/后缀匹配实现需求：

1. 对视图中相同地址、事件的相似姓名（不区分大小写）去重，保留每组ID最小的记录
2. 将去重后的数据与主客户表关联，匹配并替换为标准姓名
3. 将最终结果插入true_customer表

具体SQL实现 #

1. 创建目标表（若未存在）

CREATE TABLE true_customer (
    ID VARCHAR(10) PRIMARY KEY,
    Name VARCHAR(50) NOT NULL
);

2. 去重+匹配+插入的完整语句

WITH deduplicated_records AS (
    -- 按地址、事件、大写姓名分组，合并同地址同事件的相似姓名，保留每组最小ID的记录
    SELECT
        MIN(ID) AS ID,
        Name,
        Address,
        Event
    FROM customer_view
    GROUP BY Address, Event, UPPER(Name)
),
standardized_names AS (
    -- 关联主表，通过大小写统一+前缀/后缀匹配修正姓名
    SELECT
        dr.ID,
        mc.Name AS standard_name
    FROM deduplicated_records dr
    JOIN main_customer mc
        -- 覆盖完全匹配（不区分大小写）、前缀匹配两种场景
        ON UPPER(dr.Name) = UPPER(mc.Name)
        OR UPPER(dr.Name) LIKE UPPER(mc.Name) || '%'
        OR UPPER(mc.Name) LIKE UPPER(dr.Name) || '%'
)
INSERT INTO true_customer (ID, Name)
SELECT ID, standard_name
FROM standardized_names;

逻辑解释 #

• 去重阶段：通过UPPER(Name)统一姓名大小写后分组，确保Mark和marc被归为同一组，用MIN(ID)保留最早的有效记录（如B3）
• 匹配阶段：三种规则覆盖不同拼写场景：
  • 完全不区分大小写匹配（如BILLy→BILLY）
  • 视图姓名以主表姓名为前缀（如Jacobus→JACOB）
  • 主表姓名以视图姓名为前缀（如Jon→JOHN）
• 最终将匹配后的标准姓名插入目标表，得到预期结果

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