基于CAPTION_START时间差删除SQLite中CAPTION_TEXT重复数据
SQLite 近时间重复CAPTION记录清理实现方案
核心判定逻辑先明确:
- 需同时满足两个条件才判定为可删除重复:① 两条记录的
CAPTION_TEXT取值完全一致;② 两条记录的CAPTION_START差值绝对值 <2 秒(和你表中CAPTION_START的单位匹配,若存储单位是毫秒就把阈值换成2000) - 同文本但时间间隔超2秒的记录不做任何修改,只清理时间邻近的重复冗余数据,默认保留同组内最早出现的记录。
第一步:操作前预检(必做,防止误删)
先不直接执行删除,先跑查询筛选出所有符合删除条件的记录,逐行核对结果:确认同文本但时间差超2秒的记录没有被纳入筛选范围,连续重复块的保留规则符合你的预期,核对没问题再做后续操作:
-- 替换target_table为你实际的表名 SELECT * FROM target_table WHERE rowid IN ( SELECT curr.rowid FROM target_table curr INNER JOIN target_table prev ON curr.CAPTION_TEXT = prev.CAPTION_TEXT -- 匹配同文本、当前行比更早的同文本记录时间差在0~2秒范围内的行 AND curr.CAPTION_START - prev.CAPTION_START BETWEEN 0 AND 2 -- 排除自身关联的无效匹配 WHERE curr.rowid != prev.rowid GROUP BY curr.rowid );
第二步:正式执行清理
预检结果无误后,先备份原表再执行删除,避免操作失误导致数据丢失:
-- 1. 先全量备份原表 CREATE TABLE target_table_backup AS SELECT * FROM target_table; -- 2. 删除符合规则的重复记录 DELETE FROM target_table WHERE rowid IN ( SELECT curr.rowid FROM target_table curr INNER JOIN target_table prev ON curr.CAPTION_TEXT = prev.CAPTION_TEXT AND curr.CAPTION_START - prev.CAPTION_START BETWEEN 0 AND 2 WHERE curr.rowid != prev.rowid GROUP BY curr.rowid );
常见适配调整
- 如果你的表是用
WITHOUT ROWID模式创建的,把代码里所有rowid替换为你表中自带的唯一主键字段(如id、caption_id)即可。 - 如果需要在重复组里保留最晚出现的记录,把JOIN条件的时间差逻辑反过来,改成
prev.CAPTION_START - curr.CAPTION_START BETWEEN 0 AND 2,就能实现保留最新记录的效果。 - 遇到连续多条同文本、互相时间差都小于2秒的重复块,上述写法会自动保留块内最早的一条,其余全部标记为待删,不会出现漏删或误删保留记录的情况。
内容的提问来源于stack exchange,提问作者y3D.stack
相关产品推荐
相关产品推荐

