mysql/mariadb：如何在基于时间的数据中找到差距？

htrmnn0y 于 2021-06-20 发布在 Mysql

关注(0)|答案(1)|浏览(377)

记录器系统每5秒保存一行数据（秒==0,5,10,15，…，55；像这样的时刻 23:00:07 不可能）。
有时，由于通信错误，记录器无法保存，表中的行也会丢失。
我需要检测这些间隙：我想读间隙前的最后一行和间隙后的第一行。
以下是演示数据：

create table #time (
    DateTime datetime not null,
    Value int not null
);

insert into #time (DateTime, Value)
    values
        ('2018-08-23 00:00:00', 123),
        ('2018-08-23 00:00:05', 152),
        ('2018-08-23 00:00:10',  37),
        ('2018-08-23 00:00:15', 141),
        ('2018-08-23 00:00:20',  41),
        ('2018-08-23 00:00:25',  35),
        ('2018-08-23 00:00:30', 143),
        ('2018-08-23 00:00:35',  67),
        ('2018-08-23 00:00:40', 111),
                                        /* gap of one minute here */
        ('2018-08-23 00:01:45', 123),
        ('2018-08-23 00:01:50', 145),
        ('2018-08-23 00:01:55', 141),
        ('2018-08-23 00:02:00',  87),
                                        /* gap of 10 seconds here */
        ('2018-08-23 00:02:15', 190),
        ('2018-08-23 00:02:20', 122),
        ('2018-08-23 00:02:25', 123);

select * from #time;

他们也在雷克斯特
我想回顾一下：

GapStart              GapEnd                Size
0   2018-08-23 00:00:40   2018-08-23 00:01:45   0000-00-00 00:01:45
1   2018-08-23 00:02:00   2018-08-23 00:02:15   0000-00-00 00:00:15

当然，不应该列出5秒的间隔
我发现的是指同一行中的开始/结束日期时间（查找某些小时之间时间表数据的间隔），或者太复杂，我无法适应我的情况（查找连续日期中的间隔）。
我使用的是mariadb 10.0.28（不可能升级）：这意味着 LAG() 不可用。
先谢谢你

mysql syntax

来源：https://stackoverflow.com/questions/51983007/mysql-mariadb-how-to-find-gaps-in-timebased-data

1条答案

按热度按时间

lyr7nygr1#

一种方法可能是先对子查询，然后将每个记录与时间戳最接近的记录配对。然后，查询它，并返回具有足够大小的间隙的所有记录。

SELECT
    DateTime AS GapStart,
    NextDateTime AS GapEnd,
    TIMESTAMPDIFF(SECOND, DateTime, NextDateTime) AS SizeInSecond
FROM
(
    SELECT DateTime, Value,
        (SELECT MIN(DateTime) FROM #time t2
         WHERE t2.DateTime > t1.DateTime) AS NextDateTime
    FROM #time t1
) t
WHERE
    TIMESTAMPDIFF(SECOND, DateTime, NextDateTime) > 5;   -- or whatever threshhold you want

演示

附加到原始答案
如果 DateTime 始终在增长，可通过更改内部选择来提高速度：

SELECT
    DateTime AS GapStart,
    NextDateTime AS GapEnd,
    TIMESTAMPDIFF(SECOND, DateTime, NextDateTime) AS SizeInSecond
FROM
(
    SELECT DateTime, Value,
        (SELECT DateTime FROM #time t2
         WHERE t2.DateTime > t1.DateTime LIMIT 1) AS NextDateTime
    FROM #time t1
) t
WHERE
    TIMESTAMPDIFF(SECOND, DateTime, NextDateTime) > 5;

赞(0）回复(0）举报 2021-06-20

我来回答

mysql/mariadb：如何在基于时间的数据中找到差距？

1条答案

演示

相关问题

热门标签

最新问答