CODEWITHSUNDEEP
mysqlsqltimestamp
Johannes
Johannes

Reputation: 113

Find longest consecutive series in mysql database

I want to find the longest consecutive series in a database containing a log file. The file has the following structure:

select * from log;
+---------------+-------------------+---+
|sequence_number|timestamp          |...|
|---------------+-------------------+---+
|1              |2012-02-21 13:31:21|...|
|2              |2012-02-21 13:31:58|...|
|3              |2012-02-21 13:32:01|...|
|4              |2012-02-21 13:33:24|...|
|5              |2012-02-25 05:41:12|...|
|6              |2012-02-25 05:41:51|...|

etc...

I want to find the starting- and end-date of the period with the longest consecutive series of entries where the time difference between two rows is less than a minute. The result for the database above should be:

+-------------------+-------------------+----+
|start_date         |end_date           |size|
+-------------------+-------------------+----+
|2012-02-21 13:31:21|2012-02-21 13:32:01|3   |
|2012-02-25 05:41:21|2012-02-25 05:41:51|2   |
|2012-02-21 13:33:24|2012-02-21 13:33:24|1   |

I checked some hints, but could find a suited solution for mysql (MariaDB 10.1.23)

Hope someone can help, thanks already!

Upvotes: 2

Views: 545

Answers (2)

Strawberry
Strawberry

Reputation: 33945

Here's one idea:

DROP TABLE IF EXISTS log;

CREATE TABLE  log
    (`sequence_number` int, `timestamp` timestamp)
;

INSERT INTO  log
    (`sequence_number`, `timestamp`)
VALUES
    (1, '2012-02-21 13:31:21'),
    (2, '2012-02-21 13:31:58'),
    (3, '2012-02-21 13:32:01'),
    (4, '2012-02-21 13:33:24'),
    (5, '2012-02-25 05:41:12'),
    (6, '2012-02-25 05:41:51')
;

 SELECT MIN(timestamp) start
      , MAX(timestamp) end
      , COUNT(*) total
   FROM 
      ( SELECT l.*
             , CASE WHEN @prevx > timestamp - INTERVAL 60 SECOND THEN @ix:=@ix+1 ELSE @ix:=1 END i
             , CASE WHEN @ix=1 THEN @jx:=@jx+1 ELSE @jx:=@jx END j
             , @prevx := timestamp
          FROM log l
             , (SELECT @prevx:=null,@ix:=1,@jx:=0) vars
         ORDER  
            BY l.timestamp
      ) x
  GROUP 
     BY j
  ;

Gives the following output:

+---------------------+---------------------+-------+
| start               | end                 | total |
+---------------------+---------------------+-------+
| 2012-02-21 13:31:21 | 2012-02-21.13:32:01 |     3 |
| 2012-02-21 13:33:24 | 2012-02-21.13:33:24 |     1 |
| 2012-02-25 05:41:12 | 2012-02-25.05:41:51 |     2 |
+---------------------+---------------------+-------+

http://sqlfiddle.com/#!9/777360/19

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1270391

In pre-V8.0, you can use variables:

select min(timestamp), max(timestamp), count(*)
from (select l.*,
             (case when (@tmp_prevts := @prevts) = NULL then NULL
                   when (@prevts := timestamp) = NULL then NULL
                   when @tmp_prevts > timestamp - interval 1 minute then @grp
                   else (@grp := @grp + 1)
              end) as grp
      from (select l.*
            from log l
            order by l.timestamp
           ) l cross join
           (select @prevts := '', @grp := 0) params
     ) l
group by grp
order by count(*) desc;

Upvotes: 0

Related Questions