Finding groups within time series in Postgre

user_15647221 0
Viewed 21

I have a list of timestamps and i want to tag them as a group when they are close enough (less than 15 sec intervall). This is what I want to have eventually :

time group number
18:01:00 1
18:01:06 1
18:10:00 /
18:20:30 2
18:20:40 2
18:20:50 2
18:25:02 /
partition postgresql sql
1 Answers

Use lag() and date comparisons to determine where a group begins. Then use a cumulative sum. You actually only want to include rows that have multiple rows in the group, so this is a little tricker than the simple gaps-and-islands problem:

select t.*,
       (case when prev_time > time - interval '15 second' or
                  next_time < time + interval '15 second'
             then sum(case when prev_time > time - interval '15 sec' then 0 else 1 end) over (order by time)
        end) as group_number
  from (select t.*,
               lag(time) over (order by time) as prev_time,
               lead(time) over (order by time) as next_time
        from t
       ) t
user_11440350
Related