sql - 使用 SQL 检测重叠的日期/时间记录并显示它与哪些记录重叠

Question

这里的挑战是使用 SQL 来检测使用日期/时间的重叠出价，并揭示它与哪些出价重叠。

考虑表和记录:

-------------------------------------------------------------------
| person_id | bid_id  | start_at            | end_at              |
-------------------------------------------------------------------
| 1         | 10      | 2016-10-30 09:00 AM | 2016-10-30 11:00 AM |
| 1         | 20      | 2016-10-30 10:00 AM | 2016-10-30 12:00 PM |
| 1         | 30      | 2016-10-30 05:00 PM | 2016-10-30 06:00 PM |
| 1         | 40      | 2016-10-30 11:45 AM | 2016-10-30 02:00 PM |
| 2         | 50      | 2016-10-30 07:00 PM | 2016-10-30 08:00 PM |
| 2         | 60      | 2016-10-30 07:00 PM | 2016-10-30 08:00 PM |
| 2         | 70      | 2016-10-30 09:00 PM | 2016-10-30 10:00 PM |
-------------------------------------------------------------------

期望的输出:

--------------------------------------------
| person_id | bid_id | overlapping_bid_ids |
--------------------------------------------
| 1         | 10     | { 20 }              |
| 1         | 20     | { 10, 40 }          | 
| 1         | 40     | { 20 }              |
| 2         | 50     | { 60 }              |
| 2         | 60     | { 50 }              |
--------------------------------------------

Answer 1

您可以使用 array_agg() 和一个连接:

select b.person_id, b.bid_id, array_agg(b2.bid_id)
from bids b join
     bids b2
     on b2.person_id = b.person_id and
        b2.end_at > b.start_at and
        b2.start_at < b.end_at and 
        b2.bid_id <> b.bid_id 
group by b.person_id, b.bid_id;

注意两个时间段重叠的逻辑。如果第一个在第二个结束之前开始并且第一个在第一个开始之后结束，则它们会重叠。这解释了部分重叠和完全重叠。如果愿意，您还可以使用 Postgres 的 ANSI 标准范围运算符 overlap 实现。