批量去重的难点通常不在写出一条删除语句,而在于先把「什么算重复、重复时保留哪条」定清楚。整行完全一致的记录,用 DISTINCT 或 drop_duplicates 就能处理;同一业务主键下有多条不同状态记录时,直接去重会误删有效数据,需要先定保留规则,再用窗口函数或分组取最新值的方式批量标记。
如果重复只是整行完全一致,用 DISTINCT 或 drop_duplicates 即可;如果同一业务主键下有多条不同状态记录,要先定保留规则(例如按更新时间留最新),再用窗口函数或临时表批量删除。操作前保留原始表或在副本上执行,删除后核对总行数与唯一键数量,并给业务主键补唯一约束,否则清洗后还会再次产生重复。
先确认当前场景和目标
去重前建议先确认四件事,这四件事决定了后面用哪种写法:
- 重复的定义:是整行字段完全一致,还是业务主键(订单号、手机号、证件号等)相同就算重复。前者可以用整行去重,后者需要指定主键列。
- 保留规则:同一主键下保留哪一条。常见规则是按更新时间取最新、按创建时间取最早、按状态优先级取有效记录。没有明确规则时,不要直接删。
- 数据规模和表结构:大表一次性 DELETE 可能长时间持锁;有外键引用、触发器或软删除标记的表,删除动作会带来额外影响。
- 下游是否依赖行数:如果报表、对账或同步任务按行数取数,去重后行数变化需要提前通知或做对账。
可以先跑一条分组统计,确认重复规模和样例,再决定处理方式:
SELECT order_no, COUNT(*) AS cnt
FROM ods_orders
GROUP BY order_no
HAVING COUNT(*) > 1
ORDER BY cnt DESC
LIMIT 20;
如果查出来只是少量整行重复,用临时表加 DISTINCT 更稳;如果是同一订单号对应多条状态记录,说明需要按业务规则保留,不能简单整行去重。
按这个顺序处理
建议按「备份、标记、删除、加约束」的顺序推进,不要一上来就执行删除。
- 保留原始数据:先把待清洗表复制一份,或导出到备份表。至少在删除前保留可回退的快照。
- 生成保留标记:用窗口函数给每个业务主键分组编号。下面的写法以 order_no 为业务主键、按 updated_at 保留最新记录,id 用于兜底排序:
-- 先把要保留的主键落到临时表,便于分批删除和核对
CREATE TABLE tmp_keep_order AS
SELECT id FROM (
SELECT id,
ROW_NUMBER() OVER (
PARTITION BY order_no
ORDER BY updated_at DESC, id DESC
) AS rn
FROM ods_orders
) t
WHERE rn = 1;
然后把临时表放到删除语句里。MySQL、PostgreSQL 对方言支持不同,下面这种「先落临时表、再按主键删除」的写法通用性较好:
DELETE FROM ods_orders
WHERE id NOT IN (SELECT id FROM tmp_keep_order);
数据量大时不要一次删完,可以按主键范围或批次循环,每批几千行,观察执行时间和锁等待。删除完成后,建议给业务主键补唯一约束,把去重结果固定下来:
ALTER TABLE ods_orders
ADD CONSTRAINT uk_order_no UNIQUE (order_no);
如果数据在 pandas 里处理,逻辑相同:先按保留规则排序,再按业务主键去重。下面示例保留每个 order_no 的最新一条:
df = df.sort_values(['updated_at', 'id'], ascending=[False, False])
df = df.drop_duplicates(subset=['order_no'], keep='first')
如果只想标记整行完全重复、不删除,可以把 keep 设为 False,先筛出重复行人工确认,再决定删除范围。
改完后怎么验证
删除语句执行成功不等于去重完成,至少要做三类核对:
- 重复是否归零:再跑一次按业务主键的分组统计,预期返回空结果,或者重复分组数为 0。
- 行数是否对得上:用备份表行数减去当前表行数,结果应等于删除的行数;当前表的总行数应等于业务主键去重后的数量。
- 保留记录是否正确:从备份表和当前表各抽几条同一业务主键的记录,比对更新时间、状态字段,确认留下的是规则指定的那一条。
SELECT COUNT(*) AS total,
COUNT(DISTINCT order_no) AS uniq_order
FROM ods_orders;
如果 total 和 uniq_order 一致,说明当前表在 order_no 上已无重复。之后可以跑一次下游任务或抽样 join,观察结果行数是否还存在膨胀。给业务主键加唯一约束时,如果约束创建失败,通常说明还有未处理干净的重复,需要回到分组查询继续排查。
容易忽略的边界
去重最容易出问题的地方,是「看起来一样、实际不相等」和「删掉后影响别的表」。需要留意这些情况:
- 大小写、空格、全半角:订单号前后有空格、字母大小写不同,分组时会被当成不同记录。是否要先 trim 或统一大小写,需要结合业务确认,不能默认处理。
- NULL 值:多数数据库中唯一索引允许多个 NULL,按业务主键去重时也要确认主键列是否可能为空。
- 软删除标记:如果表里有 is_deleted 字段,不能只按业务主键去重,否则可能把有效记录和已删除记录混在一起。先确定只处理有效记录,还是保留删除历史。
- 外键和下游引用:被其他表引用的记录直接删除可能触发约束失败或留下孤儿数据,删除前要确认引用关系。
- 大表锁和事务:一次性删除大量数据可能造成锁等待或长事务,建议分批提交,并在业务低峰期执行。回退方式通常是保留备份表、在事务中删除,或先把结果写入新表再切换。
如果无法确认保留规则,先把重复记录标记出来交给业务方确认,比直接删除更稳妥。去重之后补上唯一约束或入库前校验,才能减少重复再次进入表里的情况。