0

I have a table events that includes a lot of overlapping events. For instance, the following rows from the table events either fully or partially overlap with other rows:

id  start                   end                     created_at
1   2019-01-23 18:30:00.0   2019-01-23 19:00:00.0   2019-01-18 21:28:27.427612
2   2019-01-23 18:30:00.0   2019-01-23 19:00:00.0   2019-01-23 01:04:05.861876
3   2019-01-23 18:00:00.0   2019-01-23 18:45:00.0   2019-01-16 17:14:50.709552
4   2019-01-23 18:30:00.0   2019-01-23 19:30:00.0   2019-01-22 19:24:05.532491
5   2019-01-23 18:30:00.0   2019-01-23 19:30:00.0   2019-01-18 17:28:40.074205
6   2019-01-23 20:00:00.0   2019-01-23 20:30:00.0   2019-01-18 15:22:30.736888
7   2019-01-23 20:15:00.0   2019-01-23 20:45:00.0   2019-01-20 20:20:20.202020

What I need to do in this case is to keep the one meeting from the entire overlapping time block with the most recent created_at value.

id  start                   end                     created_at
2   2019-01-23 18:30:00.0   2019-01-23 19:00:00.0   2019-01-23 01:04:05.861876
7   2019-01-23 20:15:00.0   2019-01-23 20:45:00.0   2019-01-20 20:20:20.202020

I've looked around for an answer that handles any number of such overlap occurrences across a table, but haven't been able to find anything that works yet.

hov
  • 109
  • 9

1 Answers1

2

This is a form of gaps-and-islands. In this case, determine where the islands start by looking for overlaps at the beginning. Then, do a cumulative sum of the starts and aggregation:

select max(id), min(start), max(end), max(created_at)
from (select t.*,
             count(*) filter (where max_end < end) over (order by start) as grouping
      from (select t.*,
                   max(end) over (order by start rows between unbounded preceding and 1 preceding) as max_end
            from events t
           ) t
     ) t
group by grouping;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Can you please also take into account the table name ('events')? Or is it just a matter of replacing the t's with the name? I need to join to other tables and it would help greatly to see how names/aliases work in this example. – hov Jul 15 '19 at 19:44
  • id is type uuid, so max(id) will not work. is max(id::text) the solution or does it require more changes? – hov Jul 15 '19 at 20:49
  • follow up question https://stackoverflow.com/questions/57063831/removing-partially-fully-overlapping-events-with-start-and-end-timestamps-across – Juan Carlos Oropeza Jul 16 '19 at 19:17