mysql - 根据结果​​条件在 mysql 查询中插入或省略条目

Question

我有一些代码可以计算同时登录到应用程序的最大用户数量。登录表的结构如下:

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   |

找出曾经同时登录的最大用户数的代码如下(有一个部分用于处理未明确注销的用户，即如果应用程序在没有正确退出的情况下被杀死):

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

SELECT 
  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;

http://sqlfiddle.com/#!2/9a114/34

上面的代码是在下面的堆栈溢出问题中实现的: calculate most users ever online with MySQL

现在有一个问题，就是当用户登录时，登录条目有时没有写入表中，所以只有一个注销条目。这完全打乱了计算。我如何更新查询以忽略没有先前“登录”条目的条目？或者我如何在任何单独的“注销”条目之前 2 分钟添加“登录”条目，以便上述代码可以获得更合理的结果？

Answer 1

如果您需要检测哪些记录代表登录而没有匹配的注销记录，反之亦然，那么使用唯一 session ID 扩展您的表会很有帮助。添加一列 session_id，在登录时生成它的值，在 session 中记住它，并将相同的值放入 session_id 用于注销错误。它会大大简化查询。

---

如果您需要添加缺失登录记录的查询，请尝试以下操作:

SELECT 0 AS idLoginnLog, l1.username, ADDTIME(l1.time, '-0:30:0') AS time, 1 AS type
FROM logs AS l1
  LEFT JOIN logs AS l2
  ON (l1.username=l2.username AND l2.type=1 AND l2.time BETWEEN ADDTIME(l1.time, '-0:30:0') AND l1.time)
WHERE
  l1.type=0
  AND l2.idLoginLog IS NULL

( Fiddle .) 您可以将结果插入表中 (INSERT INTO logs (...) SELECT ...) 或将查询添加到原始查询中的 UNION .

---

如@OlivierCoilland 所述，查询变得相当复杂，您可以考虑在应用程序端进行分析。因为我猜日志表很大，所以您不应该依赖内存中的所有条目。您可能需要某种“滑动窗口”技术。

---

第四个选项是删除不匹配的注销记录。我的解决方案需要一个临时表，所以我没有在这里粘贴整个(很长)代码，只看 fiddle .