如何删除除首行外1小时内同模式值的数据库重复行

针对你需要保留mode和value相同分组下的首条记录、删除该记录1小时内重复行的需求，结合数据按日期重置不跨天的规则，可以用以下SQL语句实现：

1. 先验证要删除的记录ID #

先执行查询确认目标ID是否正确（避免误删）：

WITH group_first AS (
    SELECT 
        mode,
        value,
        DATE(timestamp) AS record_date,
        MIN(timestamp) AS first_timestamp
    FROM your_table
    GROUP BY mode, value, DATE(timestamp)
)
SELECT t.id
FROM your_table t
JOIN group_first gf 
    ON t.mode = gf.mode 
    AND t.value = gf.value 
    AND DATE(t.timestamp) = gf.record_date
WHERE t.timestamp > gf.first_timestamp 
  AND t.timestamp <= DATE_ADD(gf.first_timestamp, INTERVAL 1 HOUR);

这个查询会返回你需要删除的ID：2、3、5、6、8，和预期一致。

2. 执行删除操作 #

确认结果无误后，运行删除语句：

WITH group_first AS (
    SELECT 
        mode,
        value,
        DATE(timestamp) AS record_date,
        MIN(timestamp) AS first_timestamp
    FROM your_table
    GROUP BY mode, value, DATE(timestamp)
)
DELETE t
FROM your_table t
JOIN group_first gf 
    ON t.mode = gf.mode 
    AND t.value = gf.value 
    AND DATE(t.timestamp) = gf.record_date
WHERE t.timestamp > gf.first_timestamp 
  AND t.timestamp <= DATE_ADD(gf.first_timestamp, INTERVAL 1 HOUR);

逻辑说明 #

• 通过group_first公共表表达式，按mode、value和记录日期分组，提取每组的最早写入时间
• 关联原表后，筛选出同组内晚于最早时间、且在1小时窗口内的重复行，这些就是需要清理的无效数据
• 按日期分组的逻辑确保了不会跨天处理，符合“数值每日重置”的要求

注意事项 #

• 不同数据库的时间函数写法略有差异：比如PostgreSQL需要把DATE_ADD(..., INTERVAL 1 HOUR)改成gf.first_timestamp + INTERVAL '1 hour'
• 数据量极大时，建议分批次删除，或者先备份数据再执行删除操作

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