This is my table, i want to find concurrent user per hour for a given week

I am trying to calculate number of concurrent users in a time range. The input looks something like the below

Table

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

How to get the concurrent users in time range ?

Expected Output Table

Similar question concurrent users sql

CodePudding user response：

You can begin with this, but (from my opinion) it has no sense the result you are trying to get because you need to calculate the time:

• If a user enters 9:30 and left 9:35 and re-enter 9:45 is not a concurrent user but you get this in the SQL.
• If a user enters 9:59 and enter 10:01 you have a concurrent user but you won't see this with this logic of "hour"
• Concurrent user with different day (23:59 and 00:01 logins)

In any case, the SQL you are asking for:

SELECT
up.id,
up.diff as Hours,
COUNT(*) as times
FROM
(
SELECT TIMESTAMPDIFF(HOUR,u1.login,u2.login) as diff, u1.id FROM users u1
JOIN users u2 --join same table to get every record with every record for the same userid, careless if the same day or not (23:59 to 00:01 jump) 
ON u1.id = u2.id
AND u1.login < u2.login ) up 
WHERE up.diff < 1 -- with more than 1 hour diff time
GROUP BY up.id
HAVING COUNT(*) > 1 --with more than 1 concurrence

And without DIFF time (as you requested):

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

CodePudding user response：

I created a DBFIDDLE

• first I entered the data from your question

• half-way I changed data to what was given here: http://sqlfiddle.com/#!9/67356f/2

• first the cte1 contains the first and last date from users.

• cte2 contains all the dates between StartDate and EndDate

• cte3 contains all (24) hours for the dates.

• After this is is just counting to see if a user is logged in.

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;