SQL query to group by consecutive time

user_686198 0
Viewed 86

With the following sample data, I am trying to group these records into three groups given a 'break' time.

ID Lat Lng Timestamp
1 80.1 -120.2 2021-03-01 01:00:00
2 80.1 -120.2 2021-03-01 01:01:00
3 80.1 -120.2 2021-03-01 01:02:00
4 80.1 -120.2 2021-03-01 01:03:00
5 80.1 -120.2 2021-03-01 01:04:00
6 80.1 -120.2 2021-03-01 01:15:00
7 80.1 -120.2 2021-03-01 01:16:00
8 80.1 -120.2 2021-03-01 01:17:00
9 80.1 -120.2 2021-03-01 01:18:00
10 80.1 -120.2 2021-03-01 02:10:00
11 80.1 -120.2 2021-03-01 02:11:00
12 80.1 -120.2 2021-03-01 02:12:00
13 80.1 -120.2 2021-03-01 02:13:00
14 80.1 -120.2 2021-03-01 02:14:00

So given an idle interval of 5 minutes or more, how could I group these records into 3 groups? The first group would be records 1-5, the second group would be records 6 - 9, and the third group would be records 10 - 14, because there is a break of over 5 minutes between records 5 and 6, and 9 and 10.

mysql sql
1 Answers

You can use lag() and a cumulative sum:

select t.*,
       sum( case when prev_timestamp < timestamp - interval 5 minute or
                      prev_timestamp <> prev_timestamp_ll
                 then 1
                 else 0
            end ) over (order by timestamp) as grp           
from (select t.*,
             lag(timestamp) over (partition by lat, lng order by timestamp) as prev_timestamp_ll,
             lag(timestamp) over (order by timestamp) as prev_timestamp
      from t
     ) t;

Here is a db<>fiddle.

user_11440350
Related