MySQL/MariaDB:如何找到基于时间的数据的差距? [英] MySQL / MariaDB: how to find gaps in timebased data?

问题描述

记录器系统每 5 秒保存一行数据(秒==0,5,10,15,...,55；像 23:00:07 这样的时间是不可能的).

有时记录器不会因为通信错误而保存，并且表中的行只是缺失.

我需要检测这些间隙:我想读取间隙前的最后一行和间隙后的第一行.

这些是演示数据:

创建表#time (日期时间日期时间不为空，值 int 不为空);插入 #time (DateTime, Value)价值观('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),/* 此处间隔一分钟 */('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),/* 此处间隔 10 秒 */('2018-08-23 00:02:15', 190),('2018-08-23 00:02:20', 122),('2018-08-23 00:02:25', 123);从#time 中选择*；

他们也在

演示

添加原始答案

如果DateTime一直在增长，可以通过改变内部SELECT来获得速度提升:

SELECTDateTime AS GapStart，NextDateTime AS GapEnd,TIMESTAMPDIFF(SECOND, DateTime, NextDateTime) AS SizeInSecond从(选择日期时间、值、(从 #time t2 中选择日期时间WHERE t2.DateTime >t1.DateTime LIMIT 1) 作为 NextDateTime从#time t1) t在哪里TIMESTAMPDIFF(SECOND, DateTime, NextDateTime) >5;

A logger system saves every 5 seconds a row of data (seconds==0,5,10,15,...,55; a time like 23:00:07 is not possible).

Sometimes the logger doesn't save because of a communication error, and rows are simply missing from the table.

I need to detect these gaps: I want to read the last line before the gap and the first line after the gap.

These are the demo data:

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;

They are also at RexTester

I would like to read back:

    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

Of course gaps of 5 seconds shouldn't be listed

What I found is referred to start/end DateTimes in the same row (Find gaps in timesheet data between certain hours), or is too complicated for me to be adapted to my case (find gaps in sequential dates).

I'm using MariaDB 10.0.28 (it is not possible to upgrade it): it means LAG() is not available.

Thank you in advance

解决方案

One approach might be to first subquery and pair each record with the record having the nearest greater timestamp. Then, query that, and return all records having a gap of a sufficient size.

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

Demo

ADD ON to original answer

If DateTime is always growing, speed improvement can be obtained by changing the internal SELECT:

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;

这篇关于MySQL/MariaDB:如何找到基于时间的数据的差距?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！