MySQL Select查询按时间和收费站过滤重复记录实现

假设存储通行记录的表名为 toll_gate_records，样例中deviceTime为毫秒级时间戳，30分钟对应的毫秒值为1800000。

需求梳理 #

• 同一ID、同一收费站名称的记录，若相邻记录时间间隔小于30分钟，仅保留1条，过滤其余重复记录
• 支持通过IN子句指定多个目标ID
• 查询固定包含Id IN ()、deviceTime between ? and ?筛选条件

适用MySQL 8.0+版本方案（支持窗口函数，性能最优） #

WITH ranked_records AS (
    SELECT 
        *,
        -- 按ID、收费站分组，取同组内上一条记录的deviceTime
        LAG(deviceTime) OVER (PARTITION BY Id, tollgateName ORDER BY deviceTime) AS prev_device_time
    FROM toll_gate_records
    -- 固定筛选条件
    WHERE 
        Id IN ('8765', '8768') -- 可替换为实际目标ID列表
        AND deviceTime BETWEEN ? AND ? -- 替换为实际时间范围参数
)
SELECT Id, tollgateName, deviceTime, latlng, dateTime
FROM ranked_records
-- 过滤规则：同组内第一条记录 或 与上一条间隔>=30分钟的记录
WHERE 
    prev_device_time IS NULL 
    OR deviceTime - prev_device_time >= 1800000
ORDER BY Id, deviceTime;

逻辑说明 #

• 先用CTE对符合基础筛选条件的记录，按ID、收费站名称分组后按时间升序排列，获取每个分组内上一条记录的时间
• 外层查询仅保留符合间隔要求的有效记录，实现去重需求

适用MySQL 5.x版本方案（无窗口函数兼容方案） #

SELECT t1.Id, t1.tollgateName, t1.deviceTime, t1.latlng, t1.dateTime
FROM toll_gate_records t1
LEFT JOIN toll_gate_records t2 
    ON t1.Id = t2.Id 
    AND t1.tollgateName = t2.tollgateName 
    AND t2.deviceTime < t1.deviceTime 
    AND t1.deviceTime - t2.deviceTime < 1800000
WHERE 
    t1.Id IN ('8765', '8768')
    AND t1.deviceTime BETWEEN ? AND ?
    AND t2.Id IS NULL
ORDER BY t1.Id, t1.deviceTime;

逻辑说明 #

• 通过自连接查找当前记录同ID同收费站下、时间更早且间隔小于30分钟的记录
• 若没有匹配到这类记录（t2.Id IS NULL），说明当前记录是该分组内符合间隔要求的有效记录，予以保留

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