zk_calculate_similarity_optimized 函数使用说明

原创 已于 2025-10-24 18:08:49 修改 · 862 阅读 · 16 ·
CC 4.0 BY-SA版权
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
文章标签：

#postgresql #1024程序员节

于 2025-08-21 09:42:59 首次发布

函数概述

zk_calculate_similarity_optimized 是一个用于计算两个文本之间相似度的 PostgreSQL 函数。该函数通过多种算法综合评估文本相似度，返回 0-100 范围内的数值，数值越高表示相似度越高。

函数特点

  1. 性能优化：缓存字符串操作结果，减少重复计算
  2. 高效匹配：合并字符串包含检查，减少字符串扫描次数
  3. 综合评估：结合多种相似度计算方法得出综合评分
  4. 内置函数利用：使用 PostgreSQL 内置的 SIMILARITY 函数提高计算效率

函数定义

-- 文本相似度计算函数
-- 该函数用于计算两个文本之间的相似度得分，得分范围为0-100
-- 优化点包括：缓存字符串操作结果、合并字符串包含检查、使用PostgreSQL内置SIMILARITY函数
-- # 相似度计算结果对比
-- | 姓名   | 拼音首字母 | 搜索值 | 内置函数相似度 | 自定义函数相似度 |
-- |--------|------------|--------|----------------|------------------|
-- | 黄忠   | HZ         | HZ     | 1              | 1                |
-- | 患者1  | HZ1        | HZ     | 0.4            | 0.71000000089407 |
-- | 何中发 | HZF        | HZ     | 0.4            | 0.71000000089407 |
-- | 患者2  | HZ2        | HZ     | 0.4            | 0.71000000089407 |
-- | 冼海棠 | HT         | HZ     | 0.2            | 0.230000000447035|
-- | 黄柏睿 | HB         | HZ     | 0.2            | 0.230000000447035|
-- | 黄连娣 | HL         | HZ     | 0.2            | 0.230000000447035|
-- | 利华仔 | LHZ        | HZ     | 0.16666667     | 0.475000000745058|
-- | 丘宏中 | QHZ        | HZ     | 0.16666667     | 0.475000000745058|
-- | 具海招 | JHZ        | HZ     | 0.16666667     | 0.475000000745058|
-- | 牙侯智 | YHZ        | HZ     | 0.16666667     | 0.475000000745058|
-- | 周瑜   | Z          | HZ     | 0              | 0.4              |
-- | 严患者二| YHZE      | HZ     | 0              | 0.45             |
-- | 严患者一| YHZY      | HZ     | 0              | 0.45             |
--
------------------------
-- 测试函数
-- ```sql
-- WITH filtered_pats AS (
--     SELECT
--         id,
--         name,
--         cn_initial(name) AS name_pyszm
--     FROM pat
--     WHERE
--         name IS NOT NULL
--         AND name NOT LIKE '%*%'
-- ),
-- zk_calculate_similarity AS (
--     SELECT
--         id,
--         name,
--         name_pyszm,
--         similarity(name_pyszm, 'HZ') AS 内置对比,
--         zk_calculate_similarity_optimized('HZ', name_pyszm) / 100 as 函数对比

--     FROM filtered_pats
-- )
-- SELECT
--     id,
--     name,
--     name_pyszm AS 拼音首字母,
--     'HZ' as 搜索值,
--     内置对比,
--     函数对比
-- FROM zk_calculate_similarity
-- WHERE 内置对比 > 0.2 OR 函数对比 > 0.2
-- ORDER BY 内置对比 DESC
-- ;
-- ```

CREATE OR REPLACE FUNCTION zk_calculate_similarity_optimized(
    search_text TEXT,    -- 搜索文本
    target_text TEXT     -- 目标文本
) RETURNS NUMERIC AS $$
DECLARE
    similarity_score NUMERIC := 0;
    search_lower TEXT;
    target_lower TEXT;
    search_len INTEGER;
    target_len INTEGER;
    len_diff INTEGER;
    pos1 INTEGER;
    pos2 INTEGER;
BEGIN
    -- 检查NULL值
    IF search_text IS NULL OR target_text IS NULL THEN
        RETURN 0;
    END IF;

    -- 转换为小写进行比较并缓存结果
    search_lower := LOWER(search_text);
    target_lower := LOWER(target_text);

    -- 计算长度并缓存结果
    search_len := LENGTH(search_lower);
    target_len := LENGTH(target_lower);

    -- 检查长度是否为0
    IF search_len = 0 OR target_len = 0 THEN
        RETURN 0;
    END IF;

    -- 完全匹配得分最高
    IF search_lower = target_lower THEN
        RETURN 100;
    END IF;

    -- 合并包含关系检查，减少字符串扫描次数
    pos1 := POSITION(search_lower IN target_lower);
    pos2 := POSITION(target_lower IN search_lower);

    -- 包含关系得分
    IF pos1 > 0 THEN
        -- 在开头位置得分更高
        IF pos1 = 1 THEN
            similarity_score := similarity_score + 50;
        ELSE
            similarity_score := similarity_score + 30;
        END IF;
    END IF;

    -- 反向包含关系
    IF pos2 > 0 THEN
        similarity_score := similarity_score + 25;
    END IF;

    -- 长度相似度得分
    len_diff := ABS(search_len - target_len);
    IF len_diff = 0 THEN
        similarity_score := similarity_score + 20;
    ELSIF len_diff <= 2 THEN
        similarity_score := similarity_score + 15;
    ELSIF len_diff <= 5 THEN
        similarity_score := similarity_score + 10;
    END IF;

    -- 使用PostgreSQL内置相似度函数计算字符匹配得分
    similarity_score := similarity_score + (SIMILARITY(search_lower, target_lower) * 15);

    RETURN similarity_score;
END;
$$ LANGUAGE plpgsql;

参数说明

参数名类型说明
search_textTEXT用于比较的搜索文本
target_textTEXT用于比较的目标文本

返回值

返回 NUMERIC 类型的数值，范围在 0-100 之间：

  • 0 表示完全不相似
  • 100 表示完全相同
  • 数值越高表示相似度越高

算法说明

该函数通过以下几种方式计算相似度得分：

  1. 完全匹配：如果两个文本完全相同，直接返回 100 分
  2. 包含关系：
    • 正向包含（search_text 包含于 target_text）：
      • 开头位置包含：+50 分
      • 其他位置包含：+30 分
    • 反向包含（target_text 包含于 search_text）：+25 分
  3. 长度相似度：
    • 长度完全相同：+20 分
    • 长度差 ≤ 2：+15 分
    • 长度差 ≤ 5：+10 分
  4. 字符匹配度：使用 PostgreSQL 内置 SIMILARITY 函数计算，结果乘以 15 作为得分

使用示例

基本用法

SELECT zk_calculate_similarity_optimized('HZ', 'HZ1') AS similarity_score;
-- 返回约 71.0 分

SELECT zk_calculate_similarity_optimized('ABC', 'ABC') AS similarity_score;
-- 返回 100 分（完全匹配）

SELECT zk_calculate_similarity_optimized('ABC', 'DEF') AS similarity_score;
-- 返回较低分数（无相似性）

在查询中使用

-- 查找姓名拼音首字母与"HZ"相似的患者
WITH filtered_pats AS (
    SELECT
        id,
        name,
        cn_initial(name) AS name_pyszm
    FROM pat
    WHERE
        name IS NOT NULL
        AND name NOT LIKE '%*%'
),
similarity_scores AS (
    SELECT
        id,
        name,
        name_pyszm,
        zk_calculate_similarity_optimized('HZ', name_pyszm) AS similarity_score
    FROM filtered_pats
)
SELECT
    id,
    name,
    name_pyszm AS 拼音首字母,
    similarity_score/100 AS 相似度
FROM similarity_scores
WHERE similarity_score > 20
ORDER BY similarity_score DESC;

性能对比

与 PostgreSQL 内置的 SIMILARITY 函数相比，zk_calculate_similarity_optimized 函数在处理中文拼音首字母匹配时表现更佳：

姓名拼音首字母搜索值内置函数相似度自定义函数相似度
黄忠HZHZ11
患者1HZ1HZ0.40.71000000089407
何中发HZFHZ0.40.71000000089407
患者2HZ2HZ0.40.71000000089407
冼海棠HTHZ0.20.230000000447035
黄柏睿HBHZ0.20.230000000447035
黄连娣HLHZ0.20.230000000447035
利华仔LHZHZ0.166666670.475000000745058
丘宏中QHZHZ0.166666670.475000000745058
具海招JHZHZ0.166666670.475000000745058
牙侯智YHZHZ0.166666670.475000000745058
周瑜ZHZ00.4
严患者二YHZEHZ00.45
严患者一YHZYHZ00.45

得出以上表格的sql

WITH filtered_pats AS (
    SELECT
        id,
        name,
        cn_initial(name) AS name_pyszm
    FROM pat
    WHERE
        name IS NOT NULL
        AND name NOT LIKE '%*%'
),
zk_calculate_similarity AS (
    SELECT
        id,
        name,
        name_pyszm,
        similarity(name_pyszm, 'HZ') AS 内置对比,
        zk_calculate_similarity_optimized('HZ', name_pyszm) / 100 as 函数对比

    FROM filtered_pats
)
SELECT
    id,
    name,
    name_pyszm AS 拼音首字母,
    'HZ' as 搜索值,
    内置对比,
    函数对比
FROM zk_calculate_similarity
WHERE 内置对比 > 0.2 OR 函数对比 > 0.2
ORDER BY 内置对比 DESC
;

适用场景

  1. 患者姓名模糊搜索：根据拼音首字母查找相似姓名的患者
  2. 文本匹配：比较两个文本字符串的相似程度
  3. 数据清洗：识别可能重复或相似的文本记录
  4. 推荐系统：基于文本相似度提供相关推荐

注意事项

  1. 函数会自动将输入文本转换为小写进行比较
  2. NULL 值输入会直接返回 0
  3. 空字符串输入会直接返回 0
  4. 返回值是 0-100 范围内的数值，可根据业务需求设定阈值
评论
成就一亿技术人!
拼手气红包6.0元
还能输入1000个字符
 
红包 添加红包
表情包 插入表情
表情包 代码片
 条评论被折叠 查看
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值