基于Snowflake的JAROWINKLER_SIMILARITY实现个体数据匹配关联方案咨询

需求背景 #

我在Snowflake中有一张存储个体及其地址信息的PH_MATCH_DATA表，目标是识别并关联潜在为同一人的记录，为关联记录分配唯一的Global_ID，以此统一其交易活动，或生成主记录并停用其他记录。Snowflake提供JAROWINKLER_SIMILARITY函数用于字符串相似度比对。

表结构DDL #

create or replace TABLE PH_MATCH_DATA (
    INDIVIDUAL_ID NUMBER(38,0),
    FIRST_NAME VARCHAR(200),
    LAST_NAME VARCHAR(200),
    GLOBAL_ID NUMBER(38,0),
    ADDRESS_ID NUMBER(38,0),
    ADDRESS_LINE_1 VARCHAR(200),
    ADDRESS_LINE_2 VARCHAR(200),
    CITY VARCHAR(200),
    ADDRESS_POSTAL_AREA_1 VARCHAR(20),
    ADDRESS_POSTAL_AREA_2 VARCHAR(20),
    STATE VARCHAR(4)
);

当前Global_ID列值均为null，我正在测试的匹配查询语句如下：

select t1.individual_id as individual_id_1, t2.individual_id as individual_id_2
from ph_match_data t1, ph_match_data t2
where t1.individual_id != t2.individual_id
and t1.ADDRESS_POSTAL_AREA_1 = t2.ADDRESS_POSTAL_AREA_1
and JAROWINKLER_SIMILARITY(lower(t1.first_name), lower(t2.first_name)) >= 93
and JAROWINKLER_SIMILARITY(lower(t1.last_name), lower(t2.last_name)) >= 93
and JAROWINKLER_SIMILARITY(lower(t1.ADDRESS_LINE_1), lower(t2.ADDRESS_LINE_1)) >= 93;

优化方案建议 #

一、预处理数据，提升匹配准确性 #

在做相似度计算前，先标准化字段值，减少无意义的差异：

• 统一字符串大小写（可以提前转换为小写存储，避免每次查询重复计算）
• 去除姓名、地址中的特殊字符（比如.、-、空格）
• 标准化地址缩写（比如将St转为Street，Ave转为Avenue）
• 处理空值：比如将空的ADDRESS_LINE_2统一为特定值，避免匹配时的异常

二、优化匹配查询的性能与合理性 #

当前的笛卡尔积查询会产生重复匹配对（如id1=1,id2=2和id1=2,id2=1），可以做以下优化：

1. 调整关联条件为t1.individual_id < t2.individual_id，直接减少一半的计算量
2. 增加前置过滤条件：除了邮编匹配，可加入STATE、CITY的等值匹配，缩小需要计算相似度的记录范围
3. 差异化阈值：根据字段特性调整相似度阈值，比如姓名的阈值可设为90+，地址的阈值可适当降低（比如85+），避免过度过滤或误匹配

优化后的匹配查询示例：

SELECT 
    t1.individual_id AS individual_id_1,
    t2.individual_id AS individual_id_2
FROM ph_match_data t1
JOIN ph_match_data t2 
    ON t1.individual_id < t2.individual_id
    AND t1.STATE = t2.STATE
    AND t1.CITY = t2.CITY
    AND t1.ADDRESS_POSTAL_AREA_1 = t2.ADDRESS_POSTAL_AREA_1
    AND JAROWINKLER_SIMILARITY(LOWER(t1.first_name), LOWER(t2.first_name)) >= 90
    AND JAROWINKLER_SIMILARITY(LOWER(t1.last_name), LOWER(t2.last_name)) >= 90
    AND JAROWINKLER_SIMILARITY(LOWER(t1.ADDRESS_LINE_1), LOWER(t2.ADDRESS_LINE_1)) >= 85;

三、生成统一的Global_ID（连通组件分组） #

要给所有关联的个体分配同一个Global_ID，可以通过连通组件的方式，将所有互相匹配的个体归为一组，给每组分配唯一ID：

具体SQL实现（递归CTE）

WITH match_pairs AS (
    -- 生成去重的匹配对
    SELECT 
        t1.individual_id AS id1,
        t2.individual_id AS id2
    FROM ph_match_data t1
    JOIN ph_match_data t2 
        ON t1.individual_id < t2.individual_id
        AND t1.STATE = t2.STATE
        AND t1.CITY = t2.CITY
        AND t1.ADDRESS_POSTAL_AREA_1 = t2.ADDRESS_POSTAL_AREA_1
        AND JAROWINKLER_SIMILARITY(LOWER(t1.first_name), LOWER(t2.first_name)) >= 90
        AND JAROWINKLER_SIMILARITY(LOWER(t1.last_name), LOWER(t2.last_name)) >= 90
        AND JAROWINKLER_SIMILARITY(LOWER(t1.ADDRESS_LINE_1), LOWER(t2.ADDRESS_LINE_1)) >= 85
),
connected_components AS (
    -- 递归构建连通组，以组内最小的individual_id作为global_id
    SELECT 
        id1 AS individual_id,
        id1 AS global_id
    FROM match_pairs
    UNION ALL
    SELECT 
        CASE WHEN mp.id1 = cc.individual_id THEN mp.id2 ELSE mp.id1 END AS individual_id,
        cc.global_id
    FROM connected_components cc
    JOIN match_pairs mp 
        ON cc.individual_id IN (mp.id1, mp.id2)
    WHERE CASE WHEN mp.id1 = cc.individual_id THEN mp.id2 ELSE mp.id1 END NOT IN (SELECT individual_id FROM connected_components)
),
all_global_ids AS (
    -- 合并有匹配的个体和无匹配的个体（无匹配的用自身ID作为global_id）
    SELECT individual_id, global_id
    FROM connected_components
    UNION
    SELECT individual_id, individual_id AS global_id
    FROM ph_match_data
    WHERE individual_id NOT IN (SELECT individual_id FROM connected_components)
)
-- 更新原表的Global_ID
UPDATE ph_match_data t
SET global_id = (SELECT global_id FROM all_global_ids ai WHERE ai.individual_id = t.individual_id);

四、性能优化进阶建议 #

如果表数据量很大（千万级以上），可以做以下优化：

1. 设置聚类键：对STATE、CITY、ADDRESS_POSTAL_AREA_1设置聚类键，加速等值匹配查询：

   ALTER TABLE PH_MATCH_DATA CLUSTER BY (STATE, CITY, ADDRESS_POSTAL_AREA_1);
   

2. 分批次处理：按邮编或州分批次处理数据，避免一次性处理全量数据导致资源占用过高
3. 前置近似过滤：先用SOUNDEX或METAPHONE函数过滤发音相似的姓名，再计算Jaro-Winkler相似度，减少计算量：

   AND SOUNDEX(t1.first_name) = SOUNDEX(t2.first_name)
   

五、注意事项 #

• 先抽样测试阈值：取部分数据测试不同阈值的匹配结果，调整到符合业务需求的准确率
• 处理异常数据：比如空姓名、空地址的记录，单独做规则处理
• 定期更新：如果表数据持续新增，需要定期重新执行匹配逻辑，更新Global_ID

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