mysql - 在mysql数据库中查找最长的连续系列

Question

我想在包含日志文件的数据库中找到最长的连续系列。该文件具有以下结构：

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...

我想找到具有最长连续条目系列的期间的开始日期和结束日期，其中两行之间的时间差小于一分钟。上面数据库的结果应该是：

+-------------------+-------------------+----+
|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   |

我检查了一些提示，但可以找到适合 mysql (MariaDB 10.1.23) 的解决方案

希望有人能帮忙，已经谢谢了！

标签： mysqlsqltimestamp

Answer 1

这是一个想法：

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
  ;

给出以下输出：

+---------------------+---------------------+-------+
| 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