MySQL - 按日期时间段的峰值访问计数

Question

我有一个访问表(id int、开始日期时间、结束日期时间)，并且我希望跟踪峰值访问计数。

示例数据:

+------+---------------------+---------------------+
| id   | date-time-1         | date-time-2         |
+------+---------------------+---------------------+
| 1059 | 2016-07-04 19:13:00 | 2016-07-04 19:20:05 |
| 1060 | 2016-07-04 19:13:30 | 2016-07-04 19:14:25 |
| 1061 | 2016-07-04 19:14:39 | 2016-07-04 19:20:05 |
| 1062 | 2016-07-05 02:34:40 | 2016-07-05 02:45:23 |
| 1063 | 2016-07-05 02:34:49 | 2016-07-05 02:45:34 |
+------+---------------------+---------------------+

预期结果应该是:

+-------------------------------------------+-------+ 
| date-time-1         | date-time-2         | count | 
+-------------------------------------------+-------+ 
| 2016-07-04 19:13:00 | 2016-07-04 19:13:29 | 1     | 
| 2016-07-04 19:13:30 | 2016-07-04 19:14:25 | 2     | 
| 2016-07-04 19:14:26 | 2016-07-04 19:14:38 | 1     | 
| 2016-07-04 19:14:39 | 2016-07-04 19:20:05 | 2     | 
| 2016-07-04 19:20:06 | 2016-07-05 02:34:39 | 0     | 
| 2016-07-05 02:34:40 | 2016-07-05 02:34:48 | 1     | 
| 2016-07-05 02:34:49 | 2016-07-05 02:45:23 | 2     | 
| 2016-07-05 02:45:24 | 2016-07-05 02:45:34 | 1     | 
+------+------------------------------------+-------+

Answer 1

这不是很有效，但它会给你结果:

select U.dt1 as date-time-1, DATE_ADD(U.dt2,INTERVAL -1 SECOND) as date-time-2, 
    (select count(id) from Visits where 
    (dt1 >= u.dt1 and dt1<U.dt2)  --(dt1)dt2
    or (dt1<u.dt1 and dt2>=u.dt2)   -- dt1()dt2
    --or (dt2 >= u.dt1 and dt2<U.dt2) -- dt1(dt2) (comment this line to get your result which I believe is incorrect)
    ) as count 
from (
    select A.dt1 as dt1, (
        select min(M.dt) from ( select min(dt2) as dt from Visits where dt2 > A.dt1 union select min(dt1) as dt from Visits where dt1 > A.dt1) M
    ) as dt2 from Visits A
union 
    select B.dt2 as dt1, (
        select min(M.dt) from ( select min(dt2) as dt from Visits where dt2 > b.dt2 union select min(dt1) as dt from Visits where dt1 > b.dt2) M 
    ) as dt2 from Visits b where B.dt2 <> (select max(dt2) from Visits)
) U 

我对条件检查进行了评论，以查看访问是否在某个范围之前开始并在该范围内结束，以获得与您相同的结果集，但我相信您应该考虑这一点。