PostgreSQL中删除campaigns_eventfire表重复行 每个contact_id仅留一条事件记录

前置备份 #

操作前先备份全表，避免误删数据无法恢复：
CREATE TABLE campaigns_eventfire_backup AS TABLE campaigns_eventfire;

重复数据校验 #

先执行以下查询确认重复数据量级，和你提到的约600条受影响记录、以及已知的3组重复案例做核对：

SELECT 
  contact_id, 
  event_id, 
  COUNT(*) AS repeat_count
FROM campaigns_eventfire
GROUP BY contact_id, event_id
HAVING COUNT(*) > 1
ORDER BY repeat_count DESC;

批量删除方案 #

你不需要关注保留的具体记录，两种可行方案任选即可：

方案1：窗口函数实现（可读性高，适合小数据量） #

按contact_id+event_id分组，每组保留排序后的第一条记录，删除其余重复行：

DELETE FROM campaigns_eventfire
WHERE id NOT IN (
  SELECT id FROM (
    SELECT 
      id,
      ROW_NUMBER() OVER(PARTITION BY contact_id, event_id ORDER BY id) AS rn
    FROM campaigns_eventfire
  ) t
  WHERE rn = 1
);

如果需要调整保留规则，修改ORDER BY后的字段即可，比如改为ORDER BY scheduled DESC保留最新的调度记录。

方案2：基于ctid实现（执行效率更高） #

利用PostgreSQL内置的ctid行标识，同分组下仅保留ctid最大的记录：

DELETE FROM campaigns_eventfire a
USING campaigns_eventfire b
WHERE a.ctid < b.ctid
AND a.contact_id = b.contact_id
AND a.event_id = b.event_id;

结果校验 #

删除完成后重新执行重复数据校验查询，返回结果为空即代表所有重复记录清理完成。
如果需要回滚，可从备份表恢复数据：
INSERT INTO campaigns_eventfire SELECT * FROM campaigns_eventfire_backup;

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