ROW_NUMBER 去重保留最新

示例说明

重复数据的治理分三步：识别、清扫、堵漏。ROW_NUMBER 按邮箱分区、按时间倒序——序号为一的就是该邮箱的最新记录；排序键里补上 id 保证同秒创建的记录也有确定序。DELETE USING 的联结删除让一条语句清完全部重复；CONCURRENTLY 建唯一索引不锁表，从写入端根治复发。

技术要点

• 分区排序让每组首行可辨
• DELETE USING 单语句联结删除
• 唯一索引 CONCURRENTLY 在线创建

代码解读

第一个查询输出全部行并标注 is_primary——清扫前先审计重复规模与形态；DELETE 语句复用同一排名逻辑删除序号大于一的行；最后唯一索引让重复插入直接失败，配合应用层的 ON CONFLICT 处理决定冲突语义。三个步骤形成完整的数据质量闭环。

使用建议

清扫前备份并审计外键引用，被删行可能挂在其他表上。唯一索引上线前先解决存量重复。软删除场景用部分唯一索引（WHERE deleted_at IS NULL）。

代码示例

-- 每个邮箱保留 created_at 最新的一行，标记其余为重复
WITH ranked AS (
  SELECT
    id,
    email,
    created_at,
    ROW_NUMBER() OVER (
      PARTITION BY email
      ORDER BY created_at DESC, id DESC
    ) AS rn
  FROM subscribers
)
SELECT id, email, created_at, (rn = 1) AS is_primary
FROM ranked
ORDER BY email, rn;

-- 一次性清扫：删除全部非首行
WITH ranked AS (
  SELECT id,
         ROW_NUMBER() OVER (
           PARTITION BY email
           ORDER BY created_at DESC, id DESC
         ) AS rn
  FROM subscribers
)
DELETE FROM subscribers s
USING ranked r
WHERE s.id = r.id
  AND r.rn > 1;

-- 上游堵漏：唯一索引阻止重复再次写入
CREATE UNIQUE INDEX CONCURRENTLY idx_subscribers_email
  ON subscribers (email);