查找一周内每小时MySql并发用户数

Question

这是我的表，我想在给定的一周内找到每小时的并发用户

我正在尝试计算一个时间范围内并发用户的数量。输入看起来像下面的

表

id  user_id  login_time
1    23     2016-06-08 09:10:00
2    24     2016-06-08 08:55:00
3    25     2016-06-08 09:29:00
4    26     2016-06-08 09:40:00
5    27     2016-06-08 09:08:00
6    28     2016-06-09 13:40:00
7    31     2016-06-09 14:04:00

如何获取时间范围内的并发用户？

预期输出表

日期	小时	用户
2014-08-04	0	3
22014-08-04	1	2
2014-08-04	2	0
2014-08-05	0	1

我创建了一个DBFIDDLE

• 首先我输入了您问题的数据

• 中途，我将数据更改为此处给出的数据：http://sqlfiddle.com/#！9/67356f/2

• 首先CCD_ 1包含来自CCD_ 2的第一个和最后一个日期。

• cte2包含StartDate和EndDate之间的所有日期

• cte3包含日期的所有(24(小时。

• 这之后只是计数，看看用户是否登录。

WITH RECURSIVE cte1 AS (
SELECT 
DATE(MIN(login_time)) StartDate,
DATE(MAX(login_time)) EndDate
FROm users),
cte2 AS (
SELECT cte1.StartDate
from cte1
union all
select DATE_ADD(cte2.StartDate, INTERVAL 1 DAY)
from cte2
cross join cte1 where cte2.StartDate < cte1.EndDate
),
cte3 AS (
SELECT StartDate, 0 as H
FROM cte2
UNION ALL
SELECT StartDate, H+1 FROM cte3 WHERE H<24
)
select * from (
select 
StartDate as `Date`,
H as `hour`,
(SELECT count(*) from users 
WHERE login_time BETWEEN DATE_ADD(StartDate, interval H HOUR) AND DATE_ADD(StartDate, interval (H+1) HOUR)
) as `Count`
from cte3) x
where x.`Count` <>0
order by 1,2;

你可以从这个开始，但(在我看来(你试图得到的结果毫无意义，因为你需要计算时间：

• 如果用户输入9:30并离开9:35并重新输入9:45，则不是并发用户，但您可以在SQL中获得此信息
• 如果用户输入9:59并输入10:01，则有一个并发用户；小时
• 不同日期的并发用户(23:59和00:01登录(

在任何情况下，您要求的SQL：SQL Fiddle选择user_id，up.diff作为TimeDiff，来自(从用户u1中选择TIMESTAMPDIFF(HOUR，u1.login，u2.login(作为diff，u1.user_id加入用户u2打开u1.user_id=u2.user_idAND u1.login<u2.login(向上其中up.diff<1

没有DIFF时间(如您所要求(：

SELECT 
g.id,
g.hour,
g.datelogin,
COUNT(*) as times
FROM
(SELECT HOUR(login) as hour, DATE(login) as datelogin, id FROM users) g
GROUP BY datelogin, hour, id
HAVING COUNT(*) > 1 -- This will show only counts is bigger than 1