如何删除MySql select查询中的多个重复条目
如何删除MySql select查询中的多个重复条目
提问于 2021-10-23 15:05:23
我有一个远程传感器网络，通过多个网关将数据发送到一个MySql数据库。
有时，多个网关可以看到一个传感器，每个网关都会以相同的时间戳(时间戳精确到1秒)将数据添加到MySql表中。请参见下面的样表：
ID Sensor Gateway Data Time Received
96 801F126D9BDF 5410EC9AD9D8 632 2021-10-23 15:02:27
97 801F126D9BDF 5410EC9AF861 632 2021-10-23 15:02:27
98 801F126D9BDF 5410EC9ADF56 632 2021-10-23 15:02:27
99 801F126D9BDF 5410EC9AEBE3 632 2021-10-23 15:02:27 您可以看到，4个网关同时使用相同的数据看到了相同的传感器。
有一次，接收到的时间在4个网关上更改了1秒。
从同一传感器接收数据之间的时间间隔应至少为10分钟。
有没有办法运行一个查询来只产生一个结果。例如，我是否可以运行一个只返回两次接收之间的时间超过5分钟的数据的查询？或者其他关于如何实现这一点的想法？
感谢大家的期待。
回答 1
Stack Overflow用户
发布于 2021-10-23 15:50:22
MySql >= 8.0
您可以编写一个查询，为具有相同Sensor的每一行检查实际行和前一行的TimeReceived之间的差异(ORDER BY TimeReceived)，因此当差异小于5分钟(300秒)时，我将实际行标记为重复的(duplicated = true)。然后，您必须只过滤不重复的行：
WITH cte AS (SELECT *,
CASE WHEN TIMESTAMPDIFF(SECOND, LAG(TimeReceived) OVER (PARTITION BY Sensor ORDER BY TimeReceived, ID), TimeReceived) <= 300
THEN TRUE
ELSE FALSE END AS duplicated
FROM t)
SELECT *
FROM cte
WHERE ! duplicated;我将一条记录与前一条记录进行比较，因此如果传感器损坏，则每分钟通知一次，所有后续行都将作为重复行被丢弃。
也许使用更少的秒(而不是5分钟)是方便的。
MySql >= 5.5
使用变量时，查询仅在传感器更改或差异大于5分钟(300秒)时更新变量@d，因此如果传感器每分钟通知一次，报告也将每5分钟显示一次数据。
SET @s:='';
SET @d:=null;
SELECT ID, Sensor, Gateway, Data, TimeReceived
FROM (SELECT *,
CASE WHEN @s != Sensor OR TIMESTAMPDIFF(SECOND, @d, TimeReceived) > 300 THEN @d:=TimeReceived END AS valid,
CASE WHEN @s != Sensor THEN @s:=Sensor END AS sensor_change
FROM t
ORDER BY Sensor, TimeReceived, ID) AS sq
WHERE valid IS NOT NULL;页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/69689370
复制相关文章
相似问题
腾讯云开发者
