如何从查询输出中删除带有开始和结束时间戳记的部分/全部重叠事件？

本文介绍了如何从查询输出中删除带有开始和结束时间戳记的部分/全部重叠事件？的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有一个表 events ，其中包含许多重叠的事件。例如，表事件中的以下行与其他行完全或部分重叠：

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

在这种情况下，我需要做的是从整个重叠的时间段中以最新的created_at值阻止一次会议。 p>

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.

推荐答案

这是一种空白和孤岛的形式。在这种情况下，请通过查找开头的重叠部分来确定岛的起点。然后，对开始次数和聚合次数进行累积总和：

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;