mysql - 使用 MySQL 进行事件相关性查询

Question

MySQL菜鸟问题:

我有两个实体“foo”和“bar”，我想将它们的事件关联起来。具体来说，我想确定哪些“bar”对象与给定的“foo”对象同时处于事件状态。

为此，我建立了一个简单的 MySQL 数据库，其中包含两个表:

select * from foo;
+------+---------------------+---------------------+
| id   | arrive              | depart              |
+------+---------------------+---------------------+
|    1 | 2014-10-01 08:00:00 | 2014-10-01 09:00:00 |
|    1 | 2014-10-01 10:00:00 | 2014-10-01 11:00:00 |
|    1 | 2014-10-01 12:00:00 | 2014-10-01 13:00:00 |
|    2 | 2014-10-01 09:00:00 | 2014-10-01 10:00:00 |
|    2 | 2014-10-01 12:00:00 | 2014-10-01 13:00:00 |
+------+---------------------+---------------------+

select * from bar;

+------+---------------------+---------------------+
| id   | start               | end                 |
+------+---------------------+---------------------+
|    1 | 2014-10-01 08:05:00 | 2014-10-01 08:55:00 |
|    1 | 2014-10-01 09:05:00 | 2014-10-01 09:55:00 |
|    1 | 2014-10-01 11:05:00 | 2014-10-01 11:55:00 |
|    2 | 2014-10-01 11:05:00 | 2014-10-01 11:55:00 |
|    2 | 2014-10-01 12:05:00 | 2014-10-01 12:55:00 |
|    2 | 2014-10-01 07:05:00 | 2014-10-01 07:55:00 |
|    3 | 2014-10-01 08:05:00 | 2014-10-01 08:55:00 |
|    3 | 2014-10-01 10:05:00 | 2014-10-01 10:55:00 |
|    3 | 2014-10-01 12:05:00 | 2014-10-01 12:55:00 |
+------+---------------------+---------------------+

不用说，“到达”和“开始”列代表事件周期的开始，“出发”和“结束”列代表每个周期的结束，而 id 列是每个对象的唯一标识符.

作为第一步，对于 foo 中的每个事件周期，我想确定在同一时间范围内处于事件状态的 bar 对象集。

根据上面 foo #1 的示例数据:在 8:00 到 9:00 之间(即在 foo #1 的第一个事件期间)，bar #1 和 bar #3 都处于事件状态，10:00 至 11:00 期间，仅第 3 条酒吧处于事件状态，12:00 和 13:00 之间，#2 柱和 #3 柱都处于事件状态，等等。

一旦确定了这些集合，如果我可以确定它们的交集，我将得到我想要的答案(例如，bar #3 是唯一与 foo 对象 #1 同时处于事件状态的 bar 对象)。

我正在尝试开发一个将返回此结果的查询。理想情况下，该查询会遍历整个数据库并吐出包含一个“foo”id 和一个“bar”id 的行，其中事件一致。

作为起点，我提出了一个查询来识别在 foo 事件的每个周期内处于事件状态的所有 bar 对象:

SELECT foo.id, 
       bar.id 
FROM foo 
LEFT JOIN bar 
ON bar.start >= foo.arrive 
AND bar.end <= foo.depart;

但是，我不知道接下来该去哪里。相关子查询似乎可能有用，但我没有太多运气来制定一个没有错误的子查询。我什至不确定这是否是正确的方法。

有什么建议吗？

Answer 1

据我所知，您的 JOIN 查询是正确的。它选取完全包含在您的(到达 - 离开)间隔内的(开始 - 结束)间隔。 JOIN 是正确的选择。

将倒数添加到结果集中可以更容易地弄清楚:

SELECT foo.id AS fooid, arrive, depart,
       bar.id AS barid, start, end
  FROM foo 
  LEFT JOIN bar  
              ON bar.start >= foo.arrive 
             AND bar.end <= foo.depart

这是一个显示这一点的 SQL fiddle 。 http://sqlfiddle.com/#!2/68b7dc/3/0

然后，您要做的就是计算您拥有的不同“foo”项目的数量，并计算不同组合的数量。然后你将它们结合在一起，你就得到了你想要的结果。 http://sqlfiddle.com/#!2/ed5ac/5/0

SELECT a.fooid, b.barid
  FROM (
      SELECT COUNT(*) AS count,
             id AS fooid
        FROM foo
       GROUP BY id
        ) AS a
   JOIN (
       SELECT COUNT(*) AS count,
              foo.id AS fooid,
              bar.id AS barid
         FROM foo 
         LEFT JOIN bar  
                    ON bar.start >= foo.arrive 
                   AND bar.end <= foo.depart
        GROUP BY foo.id, bar.id
         ) AS b ON a.count=b.count AND a.fooid = b.fooid

我认为这符合你的要求。但如果没有更彻底的解释，很难确定。