数据清洗时重复记录怎么批量去重

文章导读
批量去重的难点通常不在写出一条删除语句,而在于先把「什么算重复、重复时保留哪条」定清楚。整行完全一致的记录,用 DISTINCT 或 drop_duplicates 就能处理;同一业务主键下有多条不同状态记录时,直接去重会误删有效数据,需要先定保留规则,再用窗口函数或分组取最新值的方式批量标记。
📋 目录
  1. 壹 先确认当前场景和目标
  2. 贰 按这个顺序处理
  3. 叁 改完后怎么验证
  4. 肆 容易忽略的边界
A A

批量去重的难点通常不在写出一条删除语句,而在于先把「什么算重复、重复时保留哪条」定清楚。整行完全一致的记录,用 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 更稳;如果是同一订单号对应多条状态记录,说明需要按业务规则保留,不能简单整行去重。

按这个顺序处理

建议按「备份、标记、删除、加约束」的顺序推进,不要一上来就执行删除。

  1. 保留原始数据:先把待清洗表复制一份,或导出到备份表。至少在删除前保留可回退的快照。
  2. 生成保留标记:用窗口函数给每个业务主键分组编号。下面的写法以 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 字段,不能只按业务主键去重,否则可能把有效记录和已删除记录混在一起。先确定只处理有效记录,还是保留删除历史。
  • 外键和下游引用:被其他表引用的记录直接删除可能触发约束失败或留下孤儿数据,删除前要确认引用关系。
  • 大表锁和事务:一次性删除大量数据可能造成锁等待或长事务,建议分批提交,并在业务低峰期执行。回退方式通常是保留备份表、在事务中删除,或先把结果写入新表再切换。

如果无法确认保留规则,先把重复记录标记出来交给业务方确认,比直接删除更稳妥。去重之后补上唯一约束或入库前校验,才能减少重复再次进入表里的情况。