在 MySQL 5.7 中对'islands'进行分组

Question

我正在努力解决一个我认为很简单的"缺口和孤岛"问题。

打开这个结果集:

7:18    WORK
7:19    WORK
7:20    WORK
7:29    BREAK
7:30    BREAK
7:31    WORK
7:32    WORK
7:33    IDLE
7:34    IDLE
7:35    IDLE
7:36    IDLE

:

from_timestamp  until_timestamp status  records_in_island
7:18    7:24    WORK    6
7:29    7:32    BREAK   3
7:31    7:32    WORK    2
7:33    7:36    IDLE    4

或者基本上显示:

• 每个岛屿开始和结束的时间戳
• 各岛屿记录数

下面是对数据的修改:https://dbfiddle.uk/?rdbms=mysql_5.7&小提琴= a15cc8325ef4bc743df3208f8f328b9a

我在空白和孤岛中找不到任何解决方案。我正在使用MySQL 5.7(由于AWS Aurora的限制)，所以不能使用WITH或其他MySQL 8.0语法。

任何帮助都将是非常感激的。

Answer 1

另一种方法是在SELECT语句中使用变量:

SELECT event, MIN(event_time) AS event_time, COUNT(*) AS cnt
FROM (
SELECT 
CASE WHEN @e = event THEN @c ELSE @c := @c + 1 END AS id,
event_time,  @e := event AS event
FROM events, (SELECT @c := 0, @e := '') vars
ORDER BY event_time
) t  
GROUP BY id, event
ORDER BY event_time

小提琴

但是由于MySQL 8在表达式中设置用户变量是不赞成的，并且将在将来的版本中删除。

Answer 2

也许最简单的方法是使用where来过滤掉"先前"的值。Value与列2具有相同的值。假设这些值是日期/时间值，并精确地在一分钟内对齐:

select t.*
from (select t.*,
(select t2.status
from test t2
where t2.timestamp < t.timestamp
order by t2.timestamp desc
limit 1
) as prev_status
from test t
) t
where not prev_status <=> status;

这是一个db<>小提琴