SQLite3按指定列子集删除重复行 保留首行的高效SQL方法

核心原理 #

SQLite 每个表默认自带隐藏列rowid，对应记录插入时的自增行标识，最早插入的记录（即需要保留的每组第一条）拥有同组内最小的rowid值，直接在数据库端通过rowid筛选删除重复数据，不需要全量导出数据到Python进程，数据量越大性能优势越明显。

操作步骤 #

• 第一步：操作前备份原表，避免误操作无法回滚

-- 生成原表备份
CREATE TABLE TR_INFO_BAK AS SELECT * FROM TR_INFO;

• 第二步：执行去重删除，仅保留每组[date, name]下的第一条记录

DELETE FROM TR_INFO
WHERE rowid NOT IN (
    SELECT MIN(rowid)
    FROM TR_INFO
    GROUP BY date, name
);

该语句执行效果和之前pandas方案中drop_duplicates(subset=['date', 'name'], keep='first')的处理结果完全一致，最终会保留目标结果：20220107日期下A_company对应价格10000、B_company对应价格500。

• 第三步（大表可选优化）：回收删除操作释放的磁盘空间，同时建立唯一索引从根源避免后续写入重复数据

-- 回收磁盘碎片
VACUUM;

-- 建立联合唯一索引，后续重复写入[date,name]相同的数据会直接报错，从源头避免冗余数据产生
CREATE UNIQUE INDEX IF NOT EXISTS idx_tr_date_name ON TR_INFO(date, name);

特殊场景适配 #

如果需要保留的“第一条记录”不是按插入顺序，而是有明确的排序规则（比如按写入时间戳、自增主键排序取首条），只需要把删除语句子查询中的MIN(rowid)替换为对应排序字段的最小值即可，例如表中有自增主键id时，将子查询改为SELECT MIN(id) FROM TR_INFO GROUP BY date, name即可。

内容的提问来源于stack exchange，提问作者김태한