MySQL-按日期时间段的访问高峰 [英] MySQL - Peak visit counts by datetime period
问题描述
我有一个访问表(id int，开始日期时间，结束日期时间)，我希望跟踪高峰访问次数.
I have a visits table (id int, start datetime, end datetime), and I you wish to track peak visit counts.
示例数据:
+------+---------------------+---------------------+
| 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 |
+------+------------------------------------+-------+
推荐答案
因此，要进行此项工作，您需要了解您的月经及其之间的重叠.我们在评论中同意，为了使它以正确的方式工作，您应该从第二行开始，至少在上一行添加一秒钟.为了理解这一点，我将添加一张有关您的期间的图表，并在visits表的右下方显示各期间，以便最终您可以看到时间(由于所有期间都是同一天和同一天，因此我将离开在图表上只有几分钟和几秒钟)
So to make this work you need to understand your periods and the overlapping between then. We agreed in comments that to make it work in the right way you should have from the second row on, at least one second added to the prior end. In order to understand that I will add a graph of how your periods will be and right bellow the periods from the visits table so you will see in the end that the times (since all periods are same day and hour, I will leave just minutes and seconds on the graph)
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 ^ ^
|------------|
为实现此期间表，我创建了VIEW以便于查询，这是它的代码:
To achieve such periods table I've created a VIEW to facilitate the query, here is the code to it:
create or replace view vw_times as
select dtstart as dt from visits
UNION
select dtend as dt from visits;
此视图的目的是确定给定期间的所有日期starts和ends.
The purpose of this view is to identify all dates starts and ends of your given periods.
这是将产生此类期间情况的查询:
And here is the query that will produce such periods scenarios:
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您的表中找到重叠的句点，如下所示:
And from it you can LEFT JOIN your table to find the overlapping periods like this:
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
See it working here: http://sqlfiddle.com/#!9/3509ff/10
编辑
由于您添加了带有最终结果的评论，因此最终查询将变得更小:
Since you added a comment with the final result, it would make the final query even smaller:
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
这篇关于MySQL-按日期时间段的访问高峰的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
