如何在MySQL SELECT查询中去除多条重复的传感器数据条目

一、查询语句实现（支持MySQL 8.0+） #

1. 核心逻辑 #

先合并同一传感器、时间偏差1秒内的重复上报数据，再筛选出符合时间间隔要求的有效记录。

2. 完整语句 #

假设表名为sensor_data，字段和示例一致，语句如下：

WITH step1_dedup_1s AS (
    -- 第一步：合并1秒偏差内的同传感器同数据重复上报，每组仅保留1条记录
    SELECT
        Sensor,
        Data,
        MIN(`Time Received`) AS report_time
    FROM sensor_data
    GROUP BY
        Sensor,
        Data,
        -- 用2秒窗口对齐1秒的时间偏差，可根据实际偏差调整窗口大小
        FLOOR(UNIX_TIMESTAMP(`Time Received`)/2)
),
step2_calc_diff AS (
    -- 第二步：计算同传感器相邻两条记录的时间间隔
    SELECT
        Sensor,
        Data,
        report_time,
        TIMESTAMPDIFF(MINUTE, LAG(report_time) OVER (PARTITION BY Sensor ORDER BY report_time), report_time) AS gap_minute
    FROM step1_dedup_1s
)
-- 第三步：筛选符合间隔要求的记录，首条记录默认保留
SELECT Sensor, Data, report_time
FROM step2_calc_diff
WHERE gap_minute >= 10 OR gap_minute IS NULL;

如果需要5分钟间隔，把最后一行的10改为5即可。

3. MySQL 5.x兼容版本 #

如果使用不支持窗口函数的低版本MySQL，可以用自连接实现：

SELECT
    t1.Sensor,
    t1.Data,
    t1.report_time
FROM (
    SELECT
        Sensor,
        Data,
        MIN(`Time Received`) AS report_time
    FROM sensor_data
    GROUP BY Sensor, Data, FLOOR(UNIX_TIMESTAMP(`Time Received`)/2)
) t1
LEFT JOIN (
    SELECT
        Sensor,
        Data,
        MIN(`Time Received`) AS report_time
    FROM sensor_data
    GROUP BY Sensor, Data, FLOOR(UNIX_TIMESTAMP(`Time Received`)/2)
) t2 ON t1.Sensor = t2.Sensor AND t2.report_time < t1.report_time AND TIMESTAMPDIFF(MINUTE, t2.report_time, t1.report_time) <10
WHERE t2.report_time IS NULL;

二、其他可行实现方案 #

• 写入端去重：数据写入数据库前增加校验逻辑，同一个传感器如果10分钟内已经有同数据上报则直接丢弃写入请求；也可以新增联合唯一索引uniq_sensor_time (Sensor, FLOOR(UNIX_TIMESTAMP(Time Received)/600))，使用INSERT IGNORE语句写入，重复数据会自动忽略，从源头避免脏数据入库。
• 定时清洗数据：业务允许非实时查询的场景下，可以设置定时任务（比如每小时执行一次），将符合间隔要求的有效数据同步到新表，查询直接访问新表，性能远高于实时查询计算。
• 网关侧预处理：如果网关支持自定义逻辑，可以配置同一传感器同一时间仅由信号最强的一个网关上报数据，从数据产生端减少重复上报。

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