SQL query to count different values within a particular duration in timestamp-CodePudding

I've got a datastream in MySQL which looks like the example below. Any idea to count situations where i have data within a 15 second duration where i am into at least three speed zones like:

from "WALK" to "RUN" or from "RUN" to "SPRINT" or from "JOG" to "HIGH_SPEED_RUN"

only in acceleration, order is: WALK, JOG, RUN, HIGH_SPEED_RUN, SPRINT

    timestamp           Speed   
2022-01-12 14:47:43.000 WALK    
2022-01-12 14:48:08.000 WALK    
2022-01-12 14:49:07.000 JOG 
2022-01-12 14:50:07.000 WALK    
2022-01-12 14:50:12.000 WALK    
2022-01-12 14:50:17.000 WALK    
2022-01-12 15:15:13.000 WALK    
2022-01-12 15:15:23.000 WALK    
2022-01-12 15:15:28.000 WALK    
2022-01-12 15:15:33.000 WALK    
2022-01-12 15:15:38.000 WALK    
2022-01-12 15:15:43.000 WALK    
2022-01-12 15:15:53.000 WALK    
2022-01-12 15:15:58.000 WALK    
2022-01-12 15:16:08.000 WALK    
2022-01-12 15:16:33.000 WALK    
2022-01-12 15:16:42.000 WALK    
2022-01-12 15:23:20.000 WALK    
2022-01-12 15:23:25.000 WALK    
2022-01-12 15:23:35.000 WALK    
2022-01-12 15:47:17.000 WALK    
2022-01-12 15:47:21.000 WALK    
2022-01-12 15:47:26.000 WALK    
2022-01-12 15:49:47.000 WALK    
2022-01-12 15:49:52.000 WALK    
2022-01-12 15:49:57.000 JOG 
2022-01-12 15:50:07.000 JOG 
2022-01-12 15:50:12.000 JOG 
2022-01-12 15:51:12.000 WALK    
2022-01-12 15:51:22.000 WALK    
2022-01-12 15:51:42.000 JOG 
2022-01-12 15:51:52.000 JOG 
2022-01-12 15:52:02.000 JOG 
2022-01-12 15:52:21.000 JOG 
2022-01-12 15:52:31.000 JOG 
2022-01-12 15:52:51.000 JOG 
2022-01-12 15:53:11.000 JOG 
2022-01-12 15:53:41.000 JOG 
2022-01-12 15:54:11.000 WALK    
2022-01-12 15:55:00.000 JOG 
2022-01-12 15:55:45.000 WALK
    
**2022-01-12 15:59:14.000   RUN 
2022-01-12 15:59:29.000 SPRINT**                        

2022-01-12 15:59:39.000 HIGH_SPEED_RUN  
2022-01-12 15:59:49.000 RUN 
2022-01-12 15:59:54.000 HIGH_SPEED_RUN  
2022-01-12 16:02:48.000 JOG 
2022-01-12 16:02:58.000 JOG 
2022-01-12 16:03:17.000 JOG 
2022-01-12 16:03:22.000 JOG 
2022-01-12 16:03:27.000 JOG 
2022-01-12 16:03:32.000 JOG 
2022-01-12 16:03:42.000 JOG 
2022-01-12 16:03:47.000 RUN 
2022-01-12 16:03:52.000 RUN 
2022-01-12 16:04:12.000 JOG 
2022-01-12 16:04:27.000 JOG 
2022-01-12 16:04:37.000 JOG 
2022-01-12 16:04:47.000 JOG 
2022-01-12 16:06:56.000 WALK    
2022-01-12 16:07:01.000 JOG 
2022-01-12 16:07:06.000 JOG 
2022-01-12 16:07:16.000 JOG 
2022-01-12 16:08:01.000 JOG 
2022-01-12 16:08:11.000 RUN 
2022-01-12 16:08:26.000 JOG 
2022-01-12 16:08:45.000 RUN 
2022-01-12 16:09:00.000 WALK
    
**2022-01-12 16:18:52.000   JOG 
2022-01-12 16:18:57.000 HIGH_SPEED_RUN**

2022-01-12 16:19:02.000 JOG 
2022-01-12 16:19:41.000 HIGH_SPEED_RUN  
2022-01-12 16:19:51.000 RUN 
2022-01-12 16:20:01.000 JOG 
2022-01-12 16:21:01.000 JOG 
2022-01-12 16:25:19.000 JOG 
2022-01-12 16:26:14.000 RUN 
2022-01-12 16:26:24.000 JOG 
2022-01-12 16:27:09.000 JOG 
2022-01-12 16:31:52.000 JOG 
2022-01-12 16:31:57.000 JOG 
2022-01-12 16:33:41.000 RUN 
2022-01-12 16:33:46.000 RUN 
2022-01-12 16:35:06.000 WALK    
2022-01-12 16:36:06.000 JOG 
2022-01-12 16:36:11.000 RUN 
2022-01-12 16:37:20.000 JOG 
2022-01-12 16:37:45.000 WALK
    
**2022-01-12 16:38:30.000   JOG 
2022-01-12 16:38:40.000 HIGH_SPEED_RUN**

2022-01-12 16:38:50.000 RUN 
2022-01-12 16:38:55.000 RUN 
2022-01-12 16:38:59.000 RUN 
2022-01-12 16:39:09.000 RUN 
2022-01-12 16:40:04.000 JOG 
2022-01-12 16:40:49.000 RUN 
2022-01-12 16:41:53.000 RUN 
2022-01-12 16:42:58.000 JOG 
2022-01-12 16:43:03.000 JOG 
2022-01-12 16:43:08.000 JOG 
2022-01-12 16:43:13.000 RUN 
2022-01-12 16:43:23.000 RUN 
2022-01-12 16:43:33.000 JOG 
2022-01-12 16:44:18.000 SPRINT  
2022-01-12 16:44:23.000 HIGH_SPEED_RUN  
2022-01-12 16:44:32.000 WALK    
2022-01-12 16:44:37.000 JOG 
2022-01-12 16:45:07.000 JOG 
2022-01-12 16:45:12.000 JOG 
2022-01-12 16:45:17.000 JOG 
2022-01-12 16:45:37.000 JOG 

**2022-01-12 16:45:42.000   RUN 
2022-01-12 16:45:47.000 SPRINT
2022-01-12 16:45:52.000 SPRINT**    

2022-01-12 16:45:57.000 WALK    
2022-01-12 16:46:12.000 WALK    
2022-01-12 16:46:42.000 WALK    
2022-01-12 16:46:52.000 JOG 
2022-01-12 16:46:57.000 JOG 
2022-01-12 16:47:02.000 WALK
    
**2022-01-12 16:47:26.000   WALK    
2022-01-12 16:47:31.000 RUN**

2022-01-12 16:47:41.000 JOG 
2022-01-12 16:48:11.000 WALK    
2022-01-12 16:48:16.000 WALK    
2022-01-12 16:48:51.000 WALK    
2022-01-12 16:48:56.000 WALK    
2022-01-12 16:52:10.000 JOG 
2022-01-12 16:53:04.000 JOG 
2022-01-12 16:53:09.000 JOG 
2022-01-12 16:53:19.000 WALK
    
**2022-01-12 16:54:24.000   WALK    
2022-01-12 16:54:39.000 RUN**   

2022-01-12 16:54:49.000 RUN 
2022-01-12 16:54:54.000 JOG 
2022-01-12 16:54:59.000 JOG 
2022-01-12 16:56:53.000 RUN 
2022-01-12 16:57:13.000 WALK    
2022-01-12 16:58:32.000 WALK    
2022-01-12 16:58:57.000 WALK    
2022-01-12 17:04:30.000 RUN 
2022-01-12 17:04:35.000 RUN 

**2022-01-12 17:05:20.000   JOG 
2022-01-12 17:05:30.000 HIGH_SPEED_RUN**

**2022-01-12 17:05:35.000   WALK    
2022-01-12 17:05:50.000 RUN
2022-01-12 17:05:55.000 SPRINT**
    
2022-01-12 17:06:05.000 HIGH_SPEED_RUN  
2022-01-12 17:06:15.000 JOG 

**2022-01-12 17:06:40.000   WALK    
2022-01-12 17:06:49.000 JOG 
2022-01-12 17:06:54.000 HIGH_SPEED_RUN**

2022-01-12 17:06:59.000 HIGH_SPEED_RUN  
2022-01-12 17:07:39.000 WALK    
2022-01-12 17:07:44.000 WALK    
2022-01-12 17:07:49.000 JOG 
2022-01-12 17:07:54.000 JOG 
2022-01-12 17:07:59.000 JOG 
2022-01-12 17:08:04.000 JOG 

**2022-01-12 17:08:14.000   JOG 
2022-01-12 17:08:29.000 HIGH_SPEED_RUN**

2022-01-12 17:08:44.000 WALK    
2022-01-12 17:08:49.000 JOG 
2022-01-12 17:09:24.000 JOG 
2022-01-12 17:09:43.000 WALK    
2022-01-12 17:10:58.000 WALK    
2022-01-12 17:11:28.000 JOG 

**2022-01-12 17:11:53.000   WALK        
2022-01-12 17:12:03.000 RUN               
2022-01-12 17:12:08.000 RUN**   

2022-01-12 17:12:17.000 JOG 
2022-01-12 17:12:52.000 JOG 
2022-01-12 17:13:32.000 WALK    
2022-01-12 17:13:52.000 RUN 

**2022-01-12 17:13:57.000   JOG 
2022-01-12 17:14:07.000 HIGH_SPEED_RUN**    

2022-01-12 17:14:12.000 JOG 
2022-01-12 17:18:10.000 WALK    
2022-01-12 17:26:52.000 WALK    
2022-01-12 17:27:07.000 WALK    
2022-01-12 17:27:32.000 WALK    
2022-01-12 17:27:37.000 WALK    
2022-01-12 17:27:52.000 WALK    
2022-01-12 17:28:17.000 WALK    
2022-01-12 17:28:22.000 WALK    
2022-01-12 17:28:51.000 WALK    
2022-01-12 14:49:47.000 JOG 
2022-01-12 14:49:52.000 JOG 
2022-01-12 14:49:57.000 WALK    
2022-01-12 14:50:02.000 WALK    
2022-01-12 14:50:27.000 WALK    
2022-01-12 15:15:18.000 WALK    
2022-01-12 15:15:48.000 WALK    
2022-01-12 15:16:03.000 WALK    
2022-01-12 15:16:18.000 WALK    
2022-01-12 15:16:23.000 WALK    
2022-01-12 15:16:28.000 WALK    
2022-01-12 15:16:38.000 WALK    
2022-01-12 15:23:00.000 WALK    
2022-01-12 15:23:05.000 WALK    
2022-01-12 15:23:10.000 WALK    
2022-01-12 15:47:12.000 WALK    
2022-01-12 15:48:26.000 JOG 
2022-01-12 15:50:02.000 JOG 
2022-01-12 15:50:17.000 JOG 
2022-01-12 15:50:32.000 WALK
    
**2022-01-12 15:51:27.000   WALK    
2022-01-12 15:51:37.000 RUN**              

2022-01-12 15:51:47.000 JOG 
2022-01-12 15:51:57.000 JOG 
2022-01-12 15:52:06.000 JOG 
2022-01-12 15:52:11.000 JOG 
2022-01-12 15:52:16.000 JOG 
2022-01-12 15:52:41.000 JOG 
2022-01-12 15:52:46.000 JOG 
2022-01-12 15:52:56.000 RUN 
2022-01-12 15:53:01.000 JOG 
2022-01-12 15:53:06.000 JOG 
2022-01-12 15:53:16.000 JOG 
2022-01-12 15:53:36.000 JOG 
2022-01-12 15:53:56.000 JOG 
2022-01-12 15:54:16.000 JOG 
2022-01-12 15:54:41.000 JOG 
2022-01-12 15:54:46.000 JOG 
2022-01-12 15:55:05.000 WALK    
2022-01-12 15:55:50.000 WALK    
2022-01-12 15:59:19.000 SPRINT  
2022-01-12 15:59:24.000 SPRINT  
2022-01-12 15:59:34.000 SPRINT  
2022-01-12 15:59:44.000 HIGH_SPEED_RUN  
2022-01-12 16:02:43.000 JOG 
2022-01-12 16:03:03.000 JOG 
2022-01-12 16:03:07.000 JOG 
2022-01-12 16:03:37.000 JOG 
2022-01-12 16:03:57.000 JOG 
2022-01-12 16:04:02.000 JOG 
2022-01-12 16:04:07.000 JOG 
2022-01-12 16:04:17.000 JOG 
2022-01-12 16:04:22.000 JOG 
2022-01-12 16:04:32.000 JOG 
2022-01-12 16:04:42.000 WALK    
2022-01-12 16:04:52.000 JOG 
2022-01-12 16:04:57.000 JOG 
2022-01-12 16:06:51.000 JOG 
2022-01-12 16:07:11.000 JOG 
2022-01-12 16:07:21.000 JOG 
2022-01-12 16:07:56.000 JOG 
2022-01-12 16:08:06.000 JOG 
2022-01-12 16:08:16.000 JOG 
2022-01-12 16:08:21.000 JOG

CodePudding user response：

One solution is to merge consecutive rows having same speed together. In the resulting data, compare each row with next row to see (i) if next speed is two steps faster (ii) the difference is less than 15 seconds.

Here is the query (seems to return 11 rows for the sample data):

with cte1 as (
    select *, field(speed, 'walk', 'jog', 'run', 'high_speed_run', 'sprint') as speed_num
    from t
), cte2 as (
    select *, case when speed_num = lag(speed_num) over (order by timestamp) then 0 else 1 end as new_group
    from cte1
), cte3 as (
    select *, sum(new_group) over (order by timestamp) as group_num
    from cte2
), cte4 as (
    select min(timestamp) as start_time, max(timestamp) as end_time, any_value(speed_num) as speed_num
    from cte3
    group by group_num
), cte5 as (
    select *, lead(speed_num) over (order by start_time) as next_speed, lead(start_time) over (order by start_time) as next_time
    from cte4
)
select *
from cte5
where next_speed > speed_num   1
and next_time < end_time   interval 15 second
order by 1