SQL利用窗口函数快速清理重复垃圾数据

最稳妥的去重方法是用row_number()配合cte或子查询，保留每组中排序后rn=1的行，务必指定order by（如created_at、id），避免行为不可控。

sql利用窗口函数快速清理重复垃圾数据_高效方案

用 `ROW_NUMBER()` 标记重复行再删

窗口函数本身不能直接删数据，必须配合子查询或 CTE 把“要留哪一条”明确下来。最稳的路是：给每组重复数据编号，只保留 ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...) 结果为 1 的那条。

常见错误是漏写 ORDER BY——不指定顺序时，ROW_NUMBER() 行为不确定，可能每次跑结果不同，删掉的到底是哪条根本不可控。

ORDER BY 建议用主键或时间字段（如 created_at），确保逻辑可预期
如果按业务字段排序（比如 status），注意 NULL 值默认排最前，可能意外留下脏数据
别在 WHERE 里直接用窗口函数，会报错 WINDOW function is not allowed in WHERE clause

示例（删掉 email 相同但非最新的一条）：

DELETE FROM users
WHERE id NOT IN (
  SELECT id FROM (
    SELECT id,
      ROW_NUMBER() OVER (
        PARTITION BY email
        ORDER BY created_at DESC, id DESC
      ) AS rn
    FROM users
  ) t
  WHERE rn = 1
);

用 `ctid` 或 `%%physloc%%` 快速去重（PostgreSQL / SQL Server）

当表没主键、没唯一标识，又急需清理时，得靠物理位置信息定位行。PostgreSQL 用 ctid，SQL Server 可用 %%physloc%%（需启用 DBCC TRACEON(3604)，但生产环境慎用）。

这类方案快，但危险：它不看业务语义，只按存储顺序删，容易误伤。尤其在并发写入频繁的表上，ctid 可能刚查完就被 vacuum 重用。

务必先 BEGIN; SELECT ctid, * FROM table WHERE ... LIMIT 10; 看一眼实际分布
删除时加 WHERE ctid NOT IN (...) 比用子查询更安全，避免计划器优化出错
MySQL 没等价物，别硬套；想类似效果得先加临时自增列再操作

避免 `DISTINCT ON` 被当万能解（仅 PostgreSQL）

DISTINCT ON 看起来简洁，但它只保证“每组第一个”，不提供删除能力，也不能和 DELETE 直接连用。有人写 DELETE ... USING (SELECT DISTINCT ON (...) FROM ...)，结果发现删得比预期多——因为 DISTINCT ON 的“第一行”和你想象的排序不一致。

根本问题是：它不显式暴露排序依据，容易掩盖逻辑漏洞。比如 DISTINCT ON (email) ORDER BY email, updated_at DESC，若 updated_at 有重复，数据库会任意选一条，你无法控制。