MySQL 查询返回状态信息的总小时数

Question

我有一个 MySQL 表，每分钟捕获一个信号的状态信息，如下所示:

  ID  | state   | timestamp          |
--------------------------------------
'sig1'| 'red'   | '2017-07-10 15:30:21'
'sig1'| 'green' | '2017-07-10 15:31:26'
'sig1'| 'green' | '2017-07-10 15:32:24'
'sig1'| 'red'   | '2017-07-10 15:33:29'
'sig1'| 'red'   | '2017-07-10 15:34:30'
'sig1'| 'red'   | '2017-07-10 15:35:15'

我需要提出一个查询，其结果应为最近一次“sig1”连续超过 5 分钟处于“红色”状态，查询的输出应为

ID | state| duration | start_time | end_time

所以如果你们能帮我解决这个问题，那就太好了!
干杯!

Answer 1

你可以尝试这样的事情:

SELECT t.id,t.consecutive,t.state
       ,COUNT(*) consecutive_count
       ,MIN(timestamp) start_time
       ,MAX(timestamp) end_time
       ,TIMEDIFF(MAX(timestamp), MIN(timestamp)) AS diff /* for ckeck*/
FROM (SELECT a.* ,
      @r:= CASE WHEN @g = a.state AND @h=a.id THEN @r ELSE @r + 1 END consecutive,
      @g:= a.state g,
      @h:= a.id h    
      FROM yourtable a
      CROSS JOIN (SELECT @g:='', @r:=0, @h:='') t1
      ORDER BY id
      ) t
GROUP BY t.id,t.consecutive,t.state
HAVING (UNIX_TIMESTAMP(end_time)-UNIX_TIMESTAMP(start_time))/60>5
;

示例数据:

CREATE TABLE yourtable (
  id VARCHAR(10) NOT NULL ,
  state VARCHAR(10) NOT NULL,
  timestamp datetime
);
INSERT INTO yourtable VALUES ('sig1','red','2017-07-10 15:30:21');
INSERT INTO yourtable VALUES ('sig1','green','2017-07-10 15:31:26');
INSERT INTO yourtable VALUES ('sig1','green','2017-07-10 15:32:24');
INSERT INTO yourtable VALUES ('sig1','red','2017-07-10 15:33:29');
INSERT INTO yourtable VALUES ('sig1','red','2017-07-10 15:34:30');
INSERT INTO yourtable VALUES ('sig1','red','2017-07-10 15:39:15');
INSERT INTO yourtable VALUES ('sig2','red','2017-07-10 15:15:15');

输出:

 id  consecutive    state   consecutive_count   start_time            end_time  diff
 sig1   3           red          3              10.07.2017 15:33:29 10.07.2017 15:39:15 00:05:46