SQLite中如何筛选名称字段近似匹配的行？

针对跨超市商品价格对比的需求，由于SQLite没有内置模糊匹配评分函数，我们可以基于关键词匹配实现近似匹配并计算得分——核心思路是提取商品名中的关键词，统计两两商品间的共同关键词数量作为匹配得分。

1. 预处理与关键词拆分 #

先统一商品名格式（转小写、去冗余空格），再用递归CTE拆分出独立关键词：

WITH split_names AS (
    SELECT 
        ROW_NUMBER() OVER () AS id,
        TRIM(LOWER(name)) AS clean_name,
        data
    FROM your_table_name  -- 替换为你的实际表名
),
keywords AS (
    SELECT 
        id,
        clean_name,
        data,
        -- 提取第一个关键词
        CASE 
            WHEN INSTR(clean_name, ' ') > 0 THEN SUBSTR(clean_name, 1, INSTR(clean_name, ' ') - 1)
            ELSE clean_name
        END AS keyword,
        -- 剩余待拆分的字符串
        CASE 
            WHEN INSTR(clean_name, ' ') > 0 THEN SUBSTR(clean_name, INSTR(clean_name, ' ') + 1)
            ELSE ''
        END AS remaining
    FROM split_names
    UNION ALL
    SELECT 
        id,
        clean_name,
        data,
        CASE 
            WHEN INSTR(remaining, ' ') > 0 THEN SUBSTR(remaining, 1, INSTR(remaining, ' ') - 1)
            ELSE remaining
        END AS keyword,
        CASE 
            WHEN INSTR(remaining, ' ') > 0 THEN SUBSTR(remaining, INSTR(remaining, ' ') + 1)
            ELSE ''
        END AS remaining
    FROM keywords
    WHERE remaining != ''
)

2. 计算两两商品的匹配得分 #

基于拆分后的关键词统计共同数量，同时排除自身匹配的重复项：

SELECT 
    a.id AS item1_id,
    a.clean_name AS item1_name,
    a.data AS item1_data,
    b.id AS item2_id,
    b.clean_name AS item2_name,
    b.data AS item2_data,
    COUNT(DISTINCT a.keyword) AS match_score
FROM keywords a
JOIN keywords b 
    ON a.keyword = b.keyword 
    AND a.id < b.id  -- 避免重复配对（如A-B和B-A只保留一次）
GROUP BY a.id, b.id
HAVING match_score >= 1  -- 仅保留有共同关键词的配对
ORDER BY match_score DESC;

3. 实用优化建议 #

• 清理特殊字符：如果商品名含括号、连字符等，可通过REPLACE预处理，比如REPLACE(REPLACE(TRIM(LOWER(name)), '(', ''), ')', '')
• 加权得分：给规格（如1kg、500ml）、核心品名等关键词更高权重，比如：

  COUNT(DISTINCT CASE WHEN a.keyword LIKE '%kg' OR a.keyword LIKE '%ml' THEN a.keyword END) * 2 +
  COUNT(DISTINCT CASE WHEN a.keyword NOT LIKE '%kg' AND a.keyword NOT LIKE '%ml' THEN a.keyword END) AS weighted_score
  

• 索引优化：给clean_name和keyword字段创建索引，提升大表查询效率。

用你提供的示例数据测试，会得到如下结果：

内容的提问来源于stack exchange，提问作者D.G. Redd