删除具有多个值的开始和结束时间戳记的部分/完全重叠的事件 [英] Removing partially/fully overlapping events with start and end timestamps across multiple values

问题描述

基于此基础问题：

如果事件表中包含房间列，则可接受的答案将如何变化有多个值，您想按房间参加重叠的会议吗？该表可能如下所示：

How would the accepted answer change if the events table contained a "Room" column with multiple values and you wanted to take out the overlapping meetings by room? The table could look like:

id   start                   end                     created_at                  room
1    2019-01-23 18:30:00.0   2019-01-23 19:00:00.0   2019-01-18 21:28:27.427612  Room1
2    2019-01-23 18:30:00.0   2019-01-23 19:00:00.0   2019-01-23 01:04:05.861876  Room1
3    2019-01-23 18:00:00.0   2019-01-23 18:45:00.0   2019-01-16 17:14:50.709552  Room1
4    2019-01-23 18:30:00.0   2019-01-23 19:30:00.0   2019-01-22 19:24:05.532491  Room1
5    2019-01-23 18:30:00.0   2019-01-23 19:30:00.0   2019-01-18 17:28:40.074205  Room1
6    2019-01-23 20:00:00.0   2019-01-23 20:30:00.0   2019-01-18 15:22:30.736888  Room1
7    2019-01-23 20:15:00.0   2019-01-23 20:45:00.0   2019-01-20 20:20:20.202020  Room1
8    2019-01-23 18:30:00.0   2019-01-23 19:00:00.0   2019-01-18 21:28:27.427612  Room2
9    2019-01-23 18:30:00.0   2019-01-23 19:00:00.0   2019-01-23 01:04:05.861877  Room2
10   2019-01-23 18:00:00.0   2019-01-23 18:45:00.0   2019-01-16 17:14:50.709552  Room2
11   2019-01-23 18:30:00.0   2019-01-23 19:30:00.0   2019-01-22 19:24:05.532491  Room2
12   2019-01-23 18:30:00.0   2019-01-23 19:30:00.0   2019-01-18 17:28:40.074205  Room2
13   2019-01-23 20:00:00.0   2019-01-23 20:30:00.0   2019-01-18 15:22:30.736888  Room2
14   2019-01-23 20:15:00.0   2019-01-23 20:45:00.0   2019-01-20 20:20:20.202020  Room2
15   2019-01-23 20:00:00.0   2019-01-23 20:30:00.0   2019-01-18 15:22:30.736888  Room3
16   2019-01-23 20:15:00.0   2019-01-23 20:45:00.0   2019-01-20 20:20:20.202021  Room3

最终结果为：

id   start                   end                     created_at                  room
2    2019-01-23 18:30:00.0   2019-01-23 19:00:00.0   2019-01-23 01:04:05.861876  Room1
7    2019-01-23 20:15:00.0   2019-01-23 20:45:00.0   2019-01-20 20:20:20.202020  Room1
9    2019-01-23 18:30:00.0   2019-01-23 19:00:00.0   2019-01-23 01:04:05.861877  Room2
14   2019-01-23 20:15:00.0   2019-01-23 20:45:00.0   2019-01-20 20:20:20.202020  Room2
16   2019-01-23 20:15:00.0   2019-01-23 20:45:00.0   2019-01-20 20:20:20.202021  Room3

下面上一个问题的答案只能在所有房间的时间范围内提供最新创建的事件，而不能按房间显示。

The answer from the previous question below can only give the latest created event in the time block across ALL rooms, not by room.

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;

推荐答案

加入条件：

• 尝试找到同一房间的另一个活动


• 但需要使用与您不同的ID


• 必须重叠的日期确定两个日期范围是否重叠


• 最后必须有一个以后的创建日期。

• Try to found another event with same room
• But need to have a different id than you
• Must overlap dates Determine Whether Two Date Ranges Overlap
• Finally must have a later created date.

如果找不到其他行，则表示您要么是因为不重叠，要么是因为您是最新日期。

If you can't find any other row mean you are alone either because don't overlap or because you are the latest date.

SQL DEMO

SQL DEMO

SELECT a.*
FROM "events" a
LEFT JOIN "events" b
  ON a.room = b.room 
 AND a.id <> b.id
 AND a."start" <= b."end"
 AND a."end"   >= b."start"
 AND a.created_at < b.created_at
WHERE b.id IS NULL;

输出

| id |                start |                  end |                  created_at |  room |
|----|----------------------|----------------------|-----------------------------|-------|
|  2 | 2019-01-23T18:30:00Z | 2019-01-23T19:00:00Z | 2019-01-23T01:04:05.861876Z | Room1 |
|  7 | 2019-01-23T20:15:00Z | 2019-01-23T20:45:00Z |  2019-01-20T20:20:20.20202Z | Room1 |
|  9 | 2019-01-23T18:30:00Z | 2019-01-23T19:00:00Z | 2019-01-23T01:04:05.861877Z | Room2 |
| 14 | 2019-01-23T20:15:00Z | 2019-01-23T20:45:00Z |  2019-01-20T20:20:20.20202Z | Room2 |
| 16 | 2019-01-23T20:15:00Z | 2019-01-23T20:45:00Z | 2019-01-20T20:20:20.202021Z | Room3 |

这篇关于删除具有多个值的开始和结束时间戳记的部分/完全重叠的事件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！