MongoDB：检测所有重叠事件(开始结束日期)

发布于08月16日

对于specific事件(有开始/结束日期)，我可以check if there is an overlapping event.

我如何列出all个重叠的事件？

例如，用于:

ID	start	end	note
1	2023-08-16 10:00	2023-08-16 12:00
2	2023-08-16 12:00	2023-08-16 14:00
3	2023-08-16 14:00	2023-08-16 16:00
4	2023-08-16 11:00	2023-08-16 14:00	overlaps with 1 and 2
5	2023-08-16 12:30	2023-08-16 12:40	overlaps with 2 and 4

Should either return all affected events: 1,2,4,5
Or the highest ID's that are causing the overlaps: 4,5

注意:日期实际上是Date对象，但在此表中显示为字符串.

在MySQL this中，我将如何做到这一点:

SELECT *,
       EXISTS (SELECT 1
               FROM events AS other
               WHERE other.event_id < events.event_id
                 AND other.end > events.start
                 AND other.start < events.end) AS conflict
FROM events

如何在MongoDB中实现类似的功能？

推荐答案

类似查询的一个选项是使用$lookup:

db.events.aggregate([
  {$lookup: {
      from: "events",
      let: {id: "$ID", start: "$start",  end: "$end"},
      pipeline: [
        {$match: {$expr: {$and: [
                {$lt: ["$ID", "$$id"]},
                {$lt: ["$start", "$$end"]},
                {$gt: ["$end", "$$start"]}
        ]}}}
      ],
      as: "conflicts"
  }},
  {$match: {"conflicts.0": {$exists: true}}},
  {$project: {_id: 0, ID: 1, notes: "$conflicts.ID"}}
])

看看它在playground example号公路上是如何工作的