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

Question

我有一个访问表(id int，start datetime，end datetime)，您希望跟踪峰值访问次数。

示例数据：

+------+---------------------+---------------------+
| 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 

我已经注释了条件检查，以查看访问是否在某个范围之前开始并在该范围内结束，以获得与您相同的结果集，但我认为您应该考虑这一点。

Answer 2

因此，要想让它起作用，你需要了解你的经期和经期之间的重叠。我们在评论中一致认为，为了让它以正确的方式工作，你应该从第二行开始，在前一行的基础上至少增加一秒。为了理解这一点，我将在visits表中的周期下面添加一个周期图，这样您就可以看到最后的时间(因为所有周期都是相同的日和小时，所以我将在图形上只留下分钟和秒)

13:00        13:30         14:26      14:39
^            ^             ^          ^           
|------------||-----------||----------||-----------|
              |_ 13:31    |_ 14:25     |_ 14:40    |_ 20:05

--and in your table
13:00                                              20:05
^                                                  ^
|--------------------------------------------------|
             |------------|           14:39        20:05
             |_ 13:30     |_ 14:25    ^            ^
                                      |------------|

为了实现这样的periods表，我创建了一个VIEW来方便查询，下面是它的代码：

create or replace view vw_times as
  select dtstart as dt from visits
   UNION
  select dtend as dt from visits;

此视图的目的是确定给定期间的所有日期、starts和ends。

下面的查询将产生这样的周期场景：

SELECT case when cnt>1 
           then date_add(dtstart,interval 1 second) 
           else dtstart 
           end as dtstart,
         dtend
  from (SELECT dtstart, 
               dtend, 
               @ct:=@ct+1 as cnt
          FROM ( SELECT t1.dt as dtstart,
                        (select min(x.dt) 
                           from vw_times as x
                          where x.dt > t1.dt
                         ) as dtend
                   FROM vw_times t1,
                        (select @ct := 0) as cttab
                  ORDER BY t1.dt
                ) t2
          WHERE dtend is not null
        ) as t3

然后，您可以通过LEFT JOIN查找表中的重叠句点，如下所示：

SELECT times.dtstart, times.dtend, count(*)
  FROM (SELECT case when cnt>1 
                 then date_add(dtstart,interval 1 second) 
                 else dtstart 
                 end as dtstart,
               dtend
        from (SELECT dtstart, 
                     dtend, 
                     @ct:=@ct+1 as cnt
                FROM ( SELECT t1.dt as dtstart,
                              (select min(x.dt) 
                                 from vw_times as x
                                where x.dt > t1.dt
                               ) as dtend
                         FROM vw_times t1,
                              (select @ct := 0) as cttab
                        ORDER BY t1.dt
                      ) t2
                WHERE dtend is not null
              ) as t3
       ) as times 
       LEFT JOIN visits v 
              ON (    times.dtstart >= v.dtstart
                  AND times.dtend <= v.dtend)
 GROUP BY times.dtstart, times.dtend

这将导致：

dtstart                      dtend                   count(*)
July, 04 2016 19:13:00       July, 04 2016 19:13:30     1
July, 04 2016 19:13:31       July, 04 2016 19:14:25     2
July, 04 2016 19:14:26       July, 04 2016 19:14:39     1
July, 04 2016 19:14:40       July, 04 2016 19:20:05     2

在这里看到它的工作原理：http://sqlfiddle.com/#!9/3509ff/10

编辑

由于您添加了带有最终结果的注释，这将使最终查询更小：

SELECT times.dtstart, 
       case when times.dtend = vmax.maxend
            then date_add(times.dtend, interval 1 second)
            else times.dtend
            end as dtend, 
       count(*)
  FROM  (SELECT dtstart, 
               dtend
          FROM ( SELECT t1.dt as dtstart,
                        (select min(date_sub(x.dt, interval 1 second)) 
                           from vw_times as x
                          where x.dt > t1.dt
                        ) as dtend
                   FROM vw_times t1
                  ORDER BY t1.dt
               ) t2
        WHERE t2.dtend is not null
       ) as times 
       LEFT JOIN visits as v
              ON (    times.dtstart >= v.dtstart
                  AND times.dtend <= v.dtend)
       LEFT JOIN (select max(date_sub(v.dtend, interval 1 second)) as maxend
                    from visits v) vmax
              ON ( times.dtend = vmax.maxend )
 GROUP BY times.dtstart, 
          case when times.dtend = vmax.maxend
            then date_add(times.dtend, interval 1 second)
            else times.dtend
            end

这将导致：

dtstart                   dtend                 count(*)
July, 04 2016 19:13:00    2016-07-04 19:13:29    1
July, 04 2016 19:13:30    2016-07-04 19:14:24    2
July, 04 2016 19:14:25    2016-07-04 19:14:38    1
July, 04 2016 19:14:39    2016-07-04 19:20:05    2

点击这里查看：http://sqlfiddle.com/#!9/3509ff/24