logo标签列表

在MySQL中计算时间重叠的持续时间

mysql
3

我有一个包含事件的数据库表：

CREATE TABLE events
(event VARCHAR(32)
,down_time TIMESTAMP
,up_time TIMESTAMP
,id INT UNSIGNED NOT NULL AUTO_INCREMENT KEY
,INDEX(event(16))
);

INSERT INTO events(event, down_time, up_time) VALUES
('e1', '2015-01-01 00:00:03', '2015-01-01 00:00:08'),
('e2', '2015-01-01 00:00:05', '2015-01-01 00:00:06'),
('e3', '2015-01-01 00:00:02', '2015-01-01 00:00:09'),
('e4', '2015-01-01 00:00:01', '2015-01-01 00:00:04'),
('e5', '2015-01-01 00:00:07', '2015-01-01 00:00:10');

select * from events;
| event | down_time           | up_time             | id |
+-------+---------------------+---------------------+----+
| e1    | 2015-01-01 00:00:03 | 2015-01-01 00:00:08 |  1 |
| e2    | 2015-01-01 00:00:05 | 2015-01-01 00:00:06 |  2 |
| e3    | 2015-01-01 00:00:02 | 2015-01-01 00:00:09 |  3 |
| e4    | 2015-01-01 00:00:01 | 2015-01-01 00:00:04 |  4 |
| e5    | 2015-01-01 00:00:07 | 2015-01-01 00:00:10 |  5 |

我使用以下查询（query1）找到事件重叠：

SELECT * 
  FROM events a 
  JOIN events b 
    ON a.down_time <= b.up_time 
   AND a.up_time >= b.down_time 
 WHERE a.id < b.id 
   AND a.event != b.event 
   AND a.event regexp 'e[1-5]' 
   AND b.event regexp 'e[1-5]';

产生以下事件重叠（结果1）：
| event | down_time           | up_time             | id | event | down_time           | up_time             | id |
+-------+---------------------+---------------------+----+-------+---------------------+---------------------+----+
| e1    | 2015-01-01 00:00:03 | 2015-01-01 00:00:08 |  1 | e2    | 2015-01-01 00:00:05 | 2015-01-01 00:00:06 |  2 |
| e1    | 2015-01-01 00:00:03 | 2015-01-01 00:00:08 |  1 | e3    | 2015-01-01 00:00:02 | 2015-01-01 00:00:09 |  3 |
| e1    | 2015-01-01 00:00:03 | 2015-01-01 00:00:08 |  1 | e4    | 2015-01-01 00:00:01 | 2015-01-01 00:00:04 |  4 |
| e1    | 2015-01-01 00:00:03 | 2015-01-01 00:00:08 |  1 | e5    | 2015-01-01 00:00:07 | 2015-01-01 00:00:10 |  5 |
| e2    | 2015-01-01 00:00:05 | 2015-01-01 00:00:06 |  2 | e3    | 2015-01-01 00:00:02 | 2015-01-01 00:00:09 |  3 |
| e3    | 2015-01-01 00:00:02 | 2015-01-01 00:00:09 |  3 | e4    | 2015-01-01 00:00:01 | 2015-01-01 00:00:04 |  4 |
| e3    | 2015-01-01 00:00:02 | 2015-01-01 00:00:09 |  3 | e5    | 2015-01-01 00:00:07 | 2015-01-01 00:00:10 |  5 |
+-------+---------------------+---------------------+----+-------+---------------------+---------------------+----+

我希望在事件重叠（results1）中为每一行显示重叠持续时间，并目前使用以下PHP条件测试：

    if (a.down_time <= b.down_time && b.up_time <= a.up_time)
    {
    overlap_duration = b.up_time-b.down_time;
    }
    else if (a.down_time >= b.down_time && a.up_time <= b.up_time)
    {
    overlap_duration = a.up_time-a.down_time;
    }
    else if (a.down_time <= b.down_time)
    {
    overlap_duration = a.up_time-b.down_time;
    }
    else if (a.down_time >= b.down_time)
    {
    overlap_duration = b.up_time-a.down_time;
    }
1   2   3   4   5   6   7   8   9   10
|   |   |   |   |   |   |   |   |   |
|   |   |----------e1-------|   |   |
|   |   |   |   | e2|   |   |   |   | a.down_time <= b.down_time && b.up_time <= a.up_time
|   |------------e3-------------|   | a.down_time >= b.down_time && a.up_time <= b.up_time
|----e4-----|   |   |   |   |   |   | a.down_time >= b.down_time
|   |   |   |   |   |   |-----e5----| a.down_time <= b.down_time

然后生成以下输出（results2）：
|  event    | down_time            | up_time              | duration    |
|  e1       | 2015-01-01 00:00:03    2015-01-01 00:00:08    00:00:00:05 |
|  e2       | 2015-01-01 00:00:05    2015-01-01 00:00:06    00:00:00:01 |
|  Overlap1 | 2015-01-01 00:00:05    2015-01-01 00:00:06    00:00:00:01 |
|           |                                                           |
|  e1       | 2015-01-01 00:00:03    2015-01-01 00:00:08    00:00:00:05 |
|  e3       | 2015-01-01 00:00:02    2015-01-01 00:00:09    00:00:00:07 |
|  Overlap2 | 2015-01-01 00:00:03    2015-01-01 00:00:08    00:00:00:05 |
|           |                                                           |
|  e1       | 2015-01-01 00:00:03    2015-01-01 00:00:08    00:00:00:05 |
|  e4       | 2015-01-01 00:00:01    2015-01-01 00:00:04    00:00:00:03 |
|  Overlap3 | 2015-01-01 00:00:03    2015-01-01 00:00:04    00:00:00:01 |
|           |                                                           |
|  e1       | 2015-01-01 00:00:03    2015-01-01 00:00:08    00:00:00:05 |
|  e5       | 2015-01-01 00:00:07    2015-01-01 00:00:10    00:00:00:03 |
|  Overlap4 | 2015-01-01 00:00:07    2015-01-01 00:00:08    00:00:00:01 |
|           |                                                           |
|  e2       | 2015-01-01 00:00:05    2015-01-01 00:00:06    00:00:00:01 |
|  e3       | 2015-01-01 00:00:02    2015-01-01 00:00:09    00:00:00:07 |
|  Overlap5 | 2015-01-01 00:00:05    2015-01-01 00:00:06    00:00:00:01 |
|           |                                                           |
|  e3       | 2015-01-01 00:00:02    2015-01-01 00:00:09    00:00:00:07 |
|  e4       | 2015-01-01 00:00:01    2015-01-01 00:00:04    00:00:00:03 |
|  Overlap6 | 2015-01-01 00:00:02    2015-01-01 00:00:04    00:00:00:02 |
|           |                                                           |
|  e3       | 2015-01-01 00:00:02    2015-01-01 00:00:09    00:00:00:07 |
|  e5       | 2015-01-01 00:00:07    2015-01-01 00:00:10    00:00:00:03 |
|  Overlap7 | 2015-01-01 00:00:07    2015-01-01 00:00:09    00:00:00:02 |
+-----------+-----------------------------------------------------------+

我认为最好在MySQL中处理results1以生成results2来计算重叠持续时间，但不确定如何最好地进行。提前感谢您的帮助。
2个回答
1

这应该按预期工作

SELECT
    a.*,
    b.*,
    (least(a.up_time, b.up_time) - greatest(a.down_time, b.down_time)) as overlap_seconds
  FROM events a 
  JOIN events b 
    ON a.down_time <= b.up_time 
   AND a.up_time >= b.down_time 
 WHERE a.id < b.id 
   AND a.event != b.event
   AND a.event regexp 'e[1-5]' 
   AND b.event regexp 'e[1-5]'
GROUP BY a.id, b.id;

要获取重叠部分，您只需要比较每行的“最小” up_time 与“最大” down_time。

非常感谢Basile，那太完美了！我还想确定所有事件是否在任何一个时间重叠。 因此，在上面的数据集中，e1、e2和e3在此处都重叠： 重叠| overlap_down_time | overlap_up_time | overlap_duration 2.1 | 2015-01-01 00:00:05 | 2015-01-01 00:00:06 | 00:00:01而e1、e2、e3和e4并不全部同时重叠。 我曾考虑检查返回的重叠是否有重叠，但不确定是否有效？ - user2149346
没问题，你看到 Bernd Buffen 的回复了吗？ - Basile
不要返回我的额外要求，即在时间上相交的所有重叠部分。有任何想法吗？ - user2149346
@user2149346，不太明白你的请求......但既然你的原始问题已经得到了回答，我建议你开一个新帖子来提出你的额外请求（注意：别忘了点赞；）） - Basile
1
使用此示例，您将为每个输出块获得1行。
SELECT
  e1.event AS event1_name,
  e1.down_time AS event1_down_time,
  e1.up_time AS event1_up_time,
  TIMEDIFF(e1.up_time, e1.down_time) AS event1_duration,

  e2.event AS event2_name,
  e2.down_time AS event2_down_time,
  e2.up_time AS event2_up_time,
  TIMEDIFF(e2.up_time, e2.down_time) AS event1_duration,

  GREATEST(e1.down_time,e2.down_time) AS overlap_down_time, 
  LEAST(e1.up_time,e2.up_time) AS overlap_up_time,
  TIMEDIFF( LEAST(e1.up_time,e2.up_time),
  GREATEST(e1.down_time,e2.down_time) ) AS overlap_duration

 FROM events e1

INNER JOIN events e2 ON e1.id < e2.id
WHERE
 ( e2.down_time <= e1.up_time ) 
AND
 ( e2.up_time >= e1.down_time );

输出：

+-------------+---------------------+---------------------+-----------------+-------------+---------------------+---------------------+-----------------+---------------------+---------------------+------------------+
| event1_name | event1_down_time    | event1_up_time      | event1_duration | event2_name | event2_down_time    | event2_up_time      | event1_duration | overlap_down_time   | overlap_up_time     | overlap_duration |
+-------------+---------------------+---------------------+-----------------+-------------+---------------------+---------------------+-----------------+---------------------+---------------------+------------------+
| e1          | 2015-01-01 00:00:03 | 2015-01-01 00:00:08 | 00:00:05        | e2          | 2015-01-01 00:00:05 | 2015-01-01 00:00:06 | 00:00:01        | 2015-01-01 00:00:05 | 2015-01-01 00:00:06 | 00:00:01         |
| e1          | 2015-01-01 00:00:03 | 2015-01-01 00:00:08 | 00:00:05        | e3          | 2015-01-01 00:00:02 | 2015-01-01 00:00:09 | 00:00:07        | 2015-01-01 00:00:03 | 2015-01-01 00:00:08 | 00:00:05         |
| e1          | 2015-01-01 00:00:03 | 2015-01-01 00:00:08 | 00:00:05        | e4          | 2015-01-01 00:00:01 | 2015-01-01 00:00:04 | 00:00:03        | 2015-01-01 00:00:03 | 2015-01-01 00:00:04 | 00:00:01         |
| e1          | 2015-01-01 00:00:03 | 2015-01-01 00:00:08 | 00:00:05        | e5          | 2015-01-01 00:00:07 | 2015-01-01 00:00:10 | 00:00:03        | 2015-01-01 00:00:07 | 2015-01-01 00:00:08 | 00:00:01         |
| e2          | 2015-01-01 00:00:05 | 2015-01-01 00:00:06 | 00:00:01        | e3          | 2015-01-01 00:00:02 | 2015-01-01 00:00:09 | 00:00:07        | 2015-01-01 00:00:05 | 2015-01-01 00:00:06 | 00:00:01         |
| e3          | 2015-01-01 00:00:02 | 2015-01-01 00:00:09 | 00:00:07        | e4          | 2015-01-01 00:00:01 | 2015-01-01 00:00:04 | 00:00:03        | 2015-01-01 00:00:02 | 2015-01-01 00:00:04 | 00:00:02         |
| e3          | 2015-01-01 00:00:02 | 2015-01-01 00:00:09 | 00:00:07        | e5          | 2015-01-01 00:00:07 | 2015-01-01 00:00:10 | 00:00:03        | 2015-01-01 00:00:07 | 2015-01-01 00:00:09 | 00:00:02         |
+-------------+---------------------+---------------------+-----------------+-------------+---------------------+---------------------+-----------------+---------------------+---------------------+------------------+
7 rows in set (0.00 sec)
嗨Bernd Buffen..我也想确定是否所有事件在任何一个时间重叠。因此，在上述数据集中，e1、e2和e3在这里都重叠： Overlap |overlap_down_time | overlap_up_time | overlap_duration 2.1 |2015-01-01 00:00:05 | 2015-01-01 00:00:06 | 00:00:01 而e1、e2、e3和e4并不全部同时重叠。我曾考虑过检查返回的重叠是否有重叠，但不确定是否会实现？ - user2149346
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接