我有一个包含事件的数据库表:
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]';
产生以下事件重叠(results1):
| 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来计算重叠持续时间可能更好但不确定如何最好地继续…如果有的话?
应该可以正常工作
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…
在这个示例中,每个输出块得到一行。
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)