使用MySQL计算在线的大多数用户 [英] calculate most users ever online with MySQL

问题描述

我有一张表，其中记录了用户的登录和注销时间(他们登录的应用程序是与MySQL服务器通信的VB).该表如下例所示:

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   |

因此，每次用户登录时，都会在表中添加一个新行，其中包含用户名和时间戳.用于登录的类型为"1".注销时，相同的情况仅是类型为"0".

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".

有一些小问题，如果用户强制退出该应用程序，他们似乎永远都不会注销，因为这显然绕过了提交注销查询(类型"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.

我想知道什么查询(该查询可能每周运行一次)来计算一次一次登录的最多用户.这有可能吗?对我来说，这似乎是一个巨大的数学/SQL挑战！该表目前大约有3万行.

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.

哇！谢谢你们！我已根据最短的代码修改了mifeet的答案，以完成需要完成的工作.不敢相信我可以用这段代码来完成它，我想我必须蛮力或者重新设计我的数据库！

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;

推荐答案

您可以使用MySQL变量计算当前记录的访问者的运行总和，然后获取最大值:

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小提琴)

编辑:我还建议如何处理未明确注销的用户.假设您认为某个用户在30分钟后自动退出:

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;

(小提琴)

这篇关于使用MySQL计算在线的大多数用户的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！