CODEWITHSUNDEEP
mysqlsql
pedromillers
pedromillers

Reputation: 237

calculate most users ever online with MySQL

I have a table with captures user log-on and log-off times (the application they log on to is VB which communicates with a MySQL server). The table looks like the example:

idLoginLog |  username  |        Time         |  Type  |
--------------------------------------------------------
     1     |  pauljones | 2013-01-01 01:00:00 |    1   |
     2     |  mattblack | 2013-01-01 01:00:32 |    1   |
     3     |  jackblack | 2013-01-01 01:01:07 |    1   |
     4     |  mattblack | 2013-01-01 01:02:03 |    0   |
     5     |  pauljones | 2013-01-01 01:04:27 |    0   |
     6     |  sallycarr | 2013-01-01 01:06:49 |    1   |

So each time a user logs in it adds a new row to the table with their username and the time stamp. The type is "1" for logging in. When they log out the same happens only type is "0".

There are slight issues whereby users will not ever appear to have logged out if they force quit the application, as this obviously bypasses the procedure that submits the logging out query (type "0"). But please ignore that and assume I figure out a way out of that issue.

I want to know what query (that I will run perhaps once weekly) to calculate the most ever users that were logged in at any one time. Is this even possible? It seems like an immense mathmateical/SQL challenge to me! The table currently has about 30k rows.

Wow! Thank you all! I have adapted mifeet's answer to the shortest code that gets what I need done. Cannot believe I can get it done with just this code, I thought I'd have to brute force or redesign my db!

set @mx := 0;
select time,(@mx := @mx + IF(type,1,-1)) as mu from log order by mu desc limit 1;

Upvotes: 5

Views: 660

Answers (3)

flaschenpost
flaschenpost

Reputation: 2235

[ removed: count the logins per user ]

EDIT: after clarification of the question and just for fun (that little table doesn't need much real optimization) I would do it that way:

create table NumberUsers (Time datetime, number int, key(Time));

You can keep that table since old logins don't change. Please initialize that table with a Time before your start and a number of 0 (zero) users: 

insert into NumberUsers ('2010-01-01 00:00:00', 0);

That was the work once. Now for the every-week-job:

set @count := (select number from NumberUsers order by Time desc limit 1);
set @lastCalculate := (select max(Time) from NumberUsers);

insert into NumberUsers select Time, @count := @count + if(Type=0, -1, 1) 
from loginList where Time > @lastCalculate order by Time;

That should be fast, since it makes a single scan.

Upvotes: 0

Paul
Paul

Reputation: 27503

If Type were +1 for login, and -1 for log out, and you add log out entries, you could do something like this:

CREATE TABLE usage
SELECT a.Time AS Time, SUM(b.Type) AS Users
FROM logons AS a, logons AS b
WHERE b.Time < a.Time;

In some SQLs you have to break this up into multiple statements. I believe MySQL has INSERT SELECT not so sure about CREATE... SELECT.

This table gives you the usage at any time.

To get the max, Bill suggested:

SELECT * from Usage
[WHERE if you want a time range]
ORDER BY Users DESC LIMIT 1;

Note that MAXIMA are NOT UNIQUE, which raises various issues. There might be more than one time when you had the maximum number of users.... you can explore this by changing LIMIT 1 to LIMIT 5 or LIMIT 10 and looking at the table.

Upvotes: 1

Mifeet
Mifeet

Reputation: 13648

You can use MySQL variables to calculate the running sum of currently logged visitors and then get the maximum:

SET @logged := 0;
SET @max := 0;

SELECT 
     idLoginLog, type, time,
    (@logged := @logged + IF(type, 1, -1)) as logged_users,
    (@max := GREATEST(@max, @logged))
FROM logs
ORDER BY time;

SELECT @max AS max_users_ever;

(SQL Fiddle)

Edit: I have also a suggestion how to deal with users not explicitly logged out. Say you consider a user automatically logged out after 30 minutes:

SET @logged := 0;
SET @max := 0;

SELECT 
     -- Same as before
     idLoginLog, type, time,
    (@logged := @logged + IF(type, 1, -1)) AS logged_users,
    (@max := GREATEST(@max, @logged)) AS max_users
FROM ( -- Select from union of logs and records added for users not explicitely logged-out
  SELECT * from logs
  UNION
  SELECT 0 AS idLoginnLog, l1.username, ADDTIME(l1.time, '0:30:0') AS time, 0 AS type
  FROM -- Join condition matches log-out records in l2 matching a log-in record in l1
    logs AS l1
    LEFT JOIN logs AS l2
    ON (l1.username=l2.username AND l2.type=0 AND l2.time BETWEEN l1.time AND ADDTIME(l1.time, '0:30:0'))
  WHERE
    l1.type=1
    AND l2.idLoginLog IS NULL -- This leaves only records which do not have a matching log-out record
) AS extended_logs 
ORDER BY time;

SELECT @max AS max_users_ever;

(Fiddle)

Upvotes: 3

Related Questions