如何用SQL统计相同地址下相似姓名对应的唯一客户ID数量

实现思路 #

首先要先对name和address两个字段做归一化处理，匹配需求里的“相似”判定规则，示例里的相似场景是「姓名大小写不同、地址末尾标点/空格差异」，你可以根据实际业务的相似规则扩展归一化逻辑：

• 姓名归一化：统一转全小写+去除首尾空格+去除多余中间空格，消除大小写、输入空格差异
• 地址归一化：统一转全小写+去除首尾空格+去除末尾的非文字类特殊字符（比如示例里的句号），消除地址输入的格式差异

归一化后，相同归一化姓名+相同归一化地址的记录就会被归为同一相似组，之后按组统计即可。

如果你的业务场景里“姓名相似”还包括拼写接近、发音相同的情况，可以在姓名归一化逻辑里追加SOUNDEX()函数，将发音相同的姓名归为同一组。

通用SQL实现（兼容大多数主流SQL引擎，特殊函数可以对应替换） #

首先用CTE做数据预处理：

WITH normalized_data AS (
    SELECT 
        id,
        name AS original_name,
        address AS original_address,
        -- 姓名归一化：转小写+去首尾空格+替换多个连续空格为单个
        LOWER(TRIM(REGEXP_REPLACE(name, '\\s+', ' '))) AS norm_name,
        -- 地址归一化：转小写+去首尾空格+去掉末尾的非字母数字字符
        LOWER(TRIM(REGEXP_REPLACE(address, '[^a-z0-9\\s]$', ''))) AS norm_address
    FROM 你的业务表名
),
similar_group AS (
    SELECT 
        norm_name,
        norm_address,
        -- 取每组出现最多的原始地址作为输出的地址（也可以按规则取第一个）
        MODE() WITHIN GROUP (ORDER BY original_address) AS output_address,
        COUNT(DISTINCT id) AS id_count,
        STRING_AGG(DISTINCT original_name, ', ') AS similar_name_list
    FROM normalized_data
    GROUP BY norm_name, norm_address
    -- 只保留有至少2个相似记录的组，匹配示例结果的规则
    HAVING COUNT(DISTINCT id) >= 2
)

结果1：每个地址对应的唯一ID计数 #

SELECT 
    output_address AS address,
    id_count AS "id count"
FROM similar_group;

结果2：每个地址关联的相似姓名列表 #

SELECT 
    output_address AS address,
    similar_name_list AS "similar name"
FROM similar_group;

不同引擎的函数替换说明 #

• 如果你用MySQL：REGEXP_REPLACE可以正常使用，字符串聚合函数替换为GROUP_CONCAT(DISTINCT original_name SEPARATOR ', ')，取众数的MODE()可以替换为SUBSTRING_INDEX(GROUP_CONCAT(original_address ORDER BY original_address DESC), ',', 1)
• 如果你用Spark SQL/Hive：字符串聚合函数替换为CONCAT_WS(', ', COLLECT_SET(DISTINCT original_name))，REGEXP_REPLACE的转义符调整为单斜杠即可

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