如何用SQL查找并删除PostgreSQL中近似重复的记录？

针对你遇到的近似重复（比如song_name里多余的连续下划线、末尾下划线），可以通过字符串标准化+分组删除的方式用SQL解决，步骤如下：

1. 识别近似重复的记录 #

先创建标准化后的song_name版本，把连续下划线替换成单个，再去除末尾的下划线，以此归并近似重复项：

SELECT
  song_id,
  song_name,
  -- 标准化规则：替换连续下划线为单个，再去掉末尾所有下划线
  regexp_replace(
    regexp_replace(song_name, '__+', '_', 'g'),
    '_+$', '', 'g'
  ) AS normalized_name
FROM songs
ORDER BY normalized_name, song_id;

执行后，示例里的近似重复项会被归为同一个normalized_name，方便后续分组识别。

2. 确认待删除的重复记录 #

用窗口函数给每个标准化组内的记录排序，标记出需要删除的重复项（示例中保留每个组里song_id最小的记录，其余标记为待删除）：

WITH ranked_songs AS (
  SELECT
    song_id,
    normalized_name,
    ROW_NUMBER() OVER (
      PARTITION BY normalized_name
      ORDER BY song_id ASC -- 可替换为date_created DESC来保留最新创建的记录
    ) AS rn
  FROM (
    SELECT
      song_id,
      regexp_replace(
        regexp_replace(song_name, '__+', '_', 'g'),
        '_+$', '', 'g'
      ) AS normalized_name
    FROM songs
  ) AS normalized
)
SELECT * FROM ranked_songs WHERE rn > 1;

这条SQL会列出所有待删除的重复项，先确认结果是否符合预期。

3. 删除近似重复记录 #

删除前建议先备份数据！ 确认无误后执行删除操作：

WITH ranked_songs AS (
  SELECT
    song_id,
    ROW_NUMBER() OVER (
      PARTITION BY regexp_replace(
        regexp_replace(song_name, '__+', '_', 'g'),
        '_+$', '', 'g'
      )
      ORDER BY song_id ASC
    ) AS rn
  FROM songs
)
DELETE FROM songs
WHERE song_id IN (SELECT song_id FROM ranked_songs WHERE rn > 1);

由于fingerprints表设置了ON DELETE CASCADE，删除songs表的重复行时，关联的指纹记录会自动被删除，无需额外处理。

扩展：适配其他近似重复场景 #

如果还有其他近似重复特征，可修改标准化规则：

• 大小写统一：在标准化外层包裹LOWER()函数，如LOWER(regexp_replace(...))
• 去除多余空格：用regexp_replace(song_name, '\s+', '', 'g')清理所有空格，或TRIM()去除首尾空格

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