PostgreSQL 15中如何匹配两表含子串/多词的object_name字段

1. 西里尔文转译函数创建与字段标准化 #

首先创建指定的西里尔文转译函数（修正原函数参数格式错误，确保可正常执行）：

CREATE OR REPLACE FUNCTION cyrillic_transliterate(p_string text) 
RETURNS varying 
AS $BODY$ 
SELECT replace(replace(replace(replace(replace(replace(replace(replace(
    translate(lower($1), 'абвгдеёзийклмнопрстуфхцэы', 'abvgdeezijklmnoprstufhcey'), 
    'ж','zh'),'ч', 'ch'), 'ш', 'sh'), 'щ', 'shh'), 'ъ', ''), 'ю', 'yu'), 'я', 'ya'), 'ь', ''); 
$BODY$ LANGUAGE SQL IMMUTABLE COST 100;

对两张表的object_name字段做标准化处理：判断是否包含西里尔字符（Unicode范围\u0400-\u04FF），若是则转译为拉丁文，否则直接转小写统一格式，同时拆分名称为词数组用于后续匹配：

WITH ap_normalized AS (
    SELECT 
        *,
        CASE 
            WHEN object_name ~ '[\u0400-\u04FF]' THEN cyrillic_transliterate(object_name)
            ELSE lower(object_name)
        END AS normalized_name,
        -- 拆分名称为词数组，过滤非有效字符与空元素
        array_remove(string_to_array(
            regexp_replace(CASE 
                WHEN object_name ~ '[\u0400-\u04FF]' THEN cyrillic_transliterate(object_name)
                ELSE lower(object_name)
            END, '[^a-zA-Z0-9 ]', '', 'g'), 
        ' '), '') AS name_words
    FROM architecture_permissions
),
ec_normalized AS (
    SELECT 
        *,
        CASE 
            WHEN object_name ~ '[\u0400-\u04FF]' THEN cyrillic_transliterate(object_name)
            ELSE lower(object_name)
        END AS normalized_name,
        array_remove(string_to_array(
            regexp_replace(CASE 
                WHEN object_name ~ '[\u0400-\u04FF]' THEN cyrillic_transliterate(object_name)
                ELSE lower(object_name)
            END, '[^a-zA-Z0-9 ]', '', 'g'), 
        ' '), '') AS name_words
    FROM experts_conclusion
)

2. 基于词匹配的表关联 #

在标准化数据基础上，关联两张表：比较标准化后名称的长度，用较短名称的词匹配较长名称的词（允许最多2个字符的拼写误差，可按需调整），当匹配词数超过3时输出关联记录：

SELECT 
    ap.*,
    ec.*,
    (SELECT COUNT(*) 
     FROM unnest(CASE 
         WHEN length(ap.normalized_name) >= length(ec.normalized_name) THEN ec.name_words
         ELSE ap.name_words
     END) AS short_word
     JOIN unnest(CASE 
         WHEN length(ap.normalized_name) >= length(ec.normalized_name) THEN ap.name_words
         ELSE ec.name_words
     END) AS long_word ON levenshtein(short_word, long_word) <= 2) AS matched_count
FROM ap_normalized ap
JOIN ec_normalized ec ON 
    (SELECT COUNT(*) 
     FROM unnest(CASE 
         WHEN length(ap.normalized_name) >= length(ec.normalized_name) THEN ec.name_words
         ELSE ap.name_words
     END) AS short_word
     JOIN unnest(CASE 
         WHEN length(ap.normalized_name) >= length(ec.normalized_name) THEN ap.name_words
         ELSE ec.name_words
     END) AS long_word ON levenshtein(short_word, long_word) <= 2) > 3;

关键说明 #

• 拼写误差处理：使用fuzzystrmatch扩展的levenshtein函数计算编辑距离，阈值设为2表示允许最多2个字符的差异。若未安装该扩展，需先执行CREATE EXTENSION fuzzystrmatch;。
• 词匹配逻辑：自动选择较短名称的词数组去匹配较长名称的词数组，支持词序不同、带前后缀的场景，统计符合误差要求的词数量，超过3则关联记录。

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