如何删除SQL表中时间差仅1-2秒的近似重复记录

我维护一个关联ISBN图书扫描应用的SQL表，由于扫描触发的函数被多次调用，表中产生了重复数据。已经清理了同一时间点插入的重复行（比如4行时间均为2024-03-21 02:35:11.000的记录），但发现存在时间差仅1-2秒的近似重复行，示例如下：

ID    | Date
30370 | 2024-03-21 02:35:11.000
30371 | 2024-03-21 02:35:12.000
30372 | 2024-03-21 02:35:14.000
30373 | 2024-03-21 02:35:15.000

请问如何删除这些近似重复行，仅保留一行？

以下是我之前用于删除完全重复行的SQL语句：

DELETE FROM <table>
WHERE Id NOT IN (
    SELECT MIN(ID)
    FROM <table>
    GROUP BY Date
)

解决方案 #

核心思路是把时间差在阈值内（比如2秒）的记录归为同一组，然后只保留每组中的一条记录（比如最小ID的那条）。

方法1：使用窗口函数（适用于MySQL 8+/PostgreSQL/SQL Server等支持窗口函数的数据库）

WITH grouped_records AS (
    SELECT 
        ID,
        Date,
        -- 将时间差超过2秒的记录划分为新组
        SUM(CASE WHEN TIMESTAMPDIFF(SECOND, LAG(Date) OVER (ORDER BY Date), Date) > 2 THEN 1 ELSE 0 END) 
            OVER (ORDER BY Date) AS group_id
    FROM <table>
)
DELETE FROM <table>
WHERE ID NOT IN (
    SELECT MIN(ID)
    FROM grouped_records
    GROUP BY group_id
);

方法2：自关联分组（兼容旧版数据库）

DELETE t1
FROM <table> t1
JOIN <table> t2 
    ON t2.Date BETWEEN DATE_SUB(t1.Date, INTERVAL 2 SECOND) AND t1.Date
    AND t2.ID < t1.ID;

注：该方法保留每组中ID最小的记录，删除所有时间在其前后2秒内且ID更大的近似重复行；若需保留最晚插入的记录，将t2.ID < t1.ID改为t2.ID > t1.ID即可。

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