MySQL 5.6如何查询设备状态最后连续序列的起始时间

结论 #

MySQL 5.6版本完全可以实现该查询需求，可通过用户变量标记状态变更的方式计算每个设备最后一段连续状态的起止时间，具体实现如下：

表结构与测试数据 #

CREATE TABLE `devices` (
  `id` int NOT NULL AUTO_INCREMENT,
  `state` int unsigned NOT NULL,
  `dt` datetime NOT NULL,
  `name` varchar(60) COLLATE utf8_unicode_ci DEFAULT NULL,
   PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT INTO devices (state,name,dt) VALUES (1,'Alfa','2021-10-12 11:30:00');
INSERT INTO devices (state,name,dt) VALUES (0,'Alfa','2021-10-12 11:40:00');
INSERT INTO devices (state,name,dt) VALUES (1,'Alfa','2021-10-12 11:50:00');
INSERT INTO devices (state,name,dt) VALUES (1,'Alfa','2021-10-12 12:00:00');

INSERT INTO devices (state,name,dt) VALUES (1,'Beta','2021-10-12 11:30:00');
INSERT INTO devices (state,name,dt) VALUES (0,'Beta','2021-10-12 11:40:00');
INSERT INTO devices (state,name,dt) VALUES (0,'Beta','2021-10-12 11:50:00');
INSERT INTO devices (state,name,dt) VALUES (0,'Beta','2021-10-12 12:00:00');

实现查询语句 #

SELECT 
  name AS Name,
  state AS State,
  MIN(dt) AS `FROM`,
  MAX(dt) AS `TO`
FROM (
  SELECT 
    d.*,
    @grp := IF(@prev_name = name AND @prev_state = state, @grp, @grp + 1) AS group_id,
    @prev_name := name,
    @prev_state := state
  FROM devices d
  CROSS JOIN (SELECT @prev_name := NULL, @prev_state := NULL, @grp := 0) vars
  ORDER BY name, dt
) t
GROUP BY name, state, group_id
HAVING (name, group_id) IN (
  SELECT name, MAX(group_id) 
  FROM (
    SELECT 
      name,
      @grp2 := IF(@prev_name2 = name AND @prev_state2 = state, @grp2, @grp2 + 1) AS group_id,
      @prev_name2 := name,
      @prev_state2 := state
    FROM devices d
    CROSS JOIN (SELECT @prev_name2 := NULL, @prev_state2 := NULL, @grp2 := 0) vars
    ORDER BY name, dt
  ) t2
  GROUP BY name
)
ORDER BY name;

结果验证 #

执行上述语句得到的输出和预期结果完全一致：

实现逻辑说明 #

• 内层查询通过用户变量给每个设备的连续相同状态打分组标记，同一设备连续相同的state会分到同一个group_id
• 子查询筛选出每个设备最大的group_id，也就是最后一段连续状态的分组
• 外层对每个最后一段状态分组取最小dt作为起始时间（FROM），最大dt作为结束时间（TO）

内容的提问来源于stack exchange，提问作者Krivers