如何编写PostgreSQL SQL查询返回名称相似的企业记录？

一、先标准化企业名称（处理后缀、大小写、特殊字符） #

很多重复企业只是后缀、大小写或标点不同，先统一格式能大幅减少匹配复杂度。

示例SQL生成标准化名称：

SELECT 
    name,
    -- 转小写，去掉常见后缀，清理特殊字符
    lower(
        regexp_replace(
            regexp_replace(name, '\s+(ltd|limited|holdings|inc|corp|co|llc)\s*$', '', 'i'),
            '[^a-z0-9\s]', '', 'gi'
        )
    ) AS normalized_name
FROM company;

说明：可以根据你的数据补充更多常见后缀，比如group、sas等，'i'参数表示忽略大小写匹配。

二、用Trigram相似度批量匹配相似名称 #

PostgreSQL的pg_trgm扩展支持基于 trigram（三元组字符）的相似度计算，能高效找出任意位置相似的字符串，比LIKE灵活得多。

步骤1：启用pg_trgm扩展 #

CREATE EXTENSION IF NOT EXISTS pg_trgm;

步骤2：创建索引提升查询速度 #

针对数千条数据，索引能避免全表扫描，大幅提速：

-- GIN索引适合高相似度匹配，速度优于GIST
CREATE INDEX idx_company_name_trgm ON company USING GIN (name gin_trgm_ops);

步骤3：查询所有相似企业对 #

返回相似度大于阈值的企业对，同时避免重复配对（如A-B和B-A只显示一次）：

SELECT 
    c1.name AS 企业名称1,
    c2.name AS 企业名称2,
    round(similarity(c1.name, c2.name)::numeric, 2) AS 相似度
FROM company c1
JOIN company c2 
  ON c1.name < c2.name  -- 避免重复配对
  AND similarity(c1.name, c2.name) > 0.6  -- 可调整相似度阈值，0-1之间
ORDER BY 相似度 DESC;

说明：阈值0.6是通用参考值，可根据实际需求调整——阈值越高，匹配越严格；越低，匹配范围越广。

三、结合标准化名称的分组查询（适合后缀差异场景） #

如果你的重复企业主要是后缀不同（如Wine Ltd/Wine Limited），可以先按标准化名称分组，直接列出每组的所有相似名称：

WITH normalized_companies AS (
    SELECT 
        name,
        lower(
            regexp_replace(
                regexp_replace(name, '\s+(ltd|limited|holdings|inc|corp)\s*$', '', 'i'),
                '[^a-z0-9\s]', '', 'gi'
            )
        ) AS normalized_name
    FROM company
)
SELECT 
    normalized_name AS 标准化名称,
    array_agg(DISTINCT name ORDER BY name) AS 相似企业名称列表
FROM normalized_companies
GROUP BY normalized_name
HAVING COUNT(DISTINCT name) > 1  -- 只返回有重复的组
ORDER BY normalized_name;

优化建议 #

• 持久化标准化字段：如果需要频繁查询，可给company表添加normalized_name列，插入/更新时自动维护，避免每次查询重复计算。
• 调整后缀列表：根据你的业务数据补充更多常见企业后缀，提升标准化准确性。
• 测试阈值：先小范围测试不同的相似度阈值，找到最适合你数据的数值。

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