如何用SQL对非完全匹配的相似书籍标题进行分组统计？

问题背景 #

现有books表数据如下：

执行精确分组的SQL：

SELECT title, COUNT(id) AS count FROM books GROUP BY title

得到的是每个精确标题的单独统计，但需要将拼写近似、带/不带标点的相似标题合并分组，期望结果：

可行解决方案 #

1. 标题标准化（轻量直接方案） #

通过正则表达式清理标题中的干扰字符，统一格式后再分组。针对你的场景，可处理：

• 移除所有非字母、空格的字符（如'、!、<3）
• 统一所有格格式（将Karen's和Karens归为同一标准）
• 可选：转为小写避免大小写差异

PostgreSQL/MySQL 8.0+ 示例SQL：

SELECT
  -- 取原表中最接近标准格式的标题作为分组显示值
  MIN(CASE WHEN title LIKE '%''%' THEN title ELSE NULL END) AS title,
  COUNT(id) AS count
FROM (
  SELECT
    id,
    title,
    -- 标准化处理：移除非字母空格字符，转小写
    LOWER(REGEXP_REPLACE(title, '[^a-zA-Z ]', '', 'g')) AS normalized_title
  FROM books
) AS normalized_books
GROUP BY normalized_title;

该方案简单高效，适合规则明确的相似场景（如仅标点、所有格差异）。

2. 编辑距离分组（处理拼写错误场景） #

如果标题存在拼写错误类的相似性，可使用**编辑距离（Levenshtein Distance）**判断字符串相似度，将距离小于阈值的标题归为一组。

PostgreSQL 示例SQL（需先启用fuzzystrmatch扩展）：

-- 启用模糊匹配扩展
CREATE EXTENSION IF NOT EXISTS fuzzystrmatch;

-- 按编辑距离≤2的规则分组
SELECT
  b1.title AS group_title,
  COUNT(DISTINCT b2.id) AS count
FROM books b1
JOIN books b2 ON LEVENSHTEIN(b1.title, b2.title) <= 2
-- 确保每个组仅取一个基准标题（以最小id的标题为准）
WHERE b1.id = (SELECT MIN(id) FROM books WHERE LEVENSHTEIN(b1.title, title) <=2)
GROUP BY b1.title;

此方案适合复杂拼写差异，但性能低于标准化方案，大数据量需注意优化。

3. 手动映射表（精准可控方案） #

若相似规则无法通过正则或模糊匹配覆盖，可创建映射表手动定义标题归属：

-- 创建映射表
CREATE TABLE title_mappings (
  original_title VARCHAR(255),
  standard_title VARCHAR(255)
);

-- 插入映射规则
INSERT INTO title_mappings VALUES
('Karens Diary', 'Karen''s Diary'),
('Sharon''s Diary <3', 'Sharon''s Diary'),
('Sharons Diary!', 'Sharon''s Diary');

-- 关联映射表统计
SELECT
  COALESCE(tm.standard_title, b.title) AS title,
  COUNT(b.id) AS count
FROM books b
LEFT JOIN title_mappings tm ON b.title = tm.original_title
GROUP BY COALESCE(tm.standard_title, b.title);

该方案完全可控，结果精准，适合数据量不大、相似规则明确的场景。

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