根据结果条件在mysql查询中插入或省略条目 [英] insert or omit entries in mysql query based on conditions of results
问题描述
我有一些代码可以计算同时登录到应用程序的最大用户数量.登录表的结构如下:
I have some code to calculate the max amount of users to ever be logged on to an application simultaneously. The login table is structured as follows:
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 |
找出同时登录的最大用户数的代码如下(其中有一个部分用于处理未明确注销的用户，即是否在未正确退出的情况下杀死了应用程序):
The code to find out the max users ever logged on simultaneously is as follows (there is a section to deal with users who do not explicitly log out i.e. if the application is killed without exiting properly):
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
上面的代码是在以下堆栈溢出问题中实现的: 使用MySQL在线计算大多数用户
The above code was acheived in the following stack overflow question: calculate most users ever online with MySQL
现在存在一个问题，即用户登录后有时登录条目有时未写入表中，因此只有一个注销条目.这完全弄乱了计算.如何更新查询以忽略没有先前登录"条目的条目?或者，如何在任何单独的注销"条目之前2分钟添加登录"条目，例如，这样上面的代码可以获得更合理的结果?
There is now a problem whereby the login entry has sometimes not been written to the table when users have logged on, so there is only a log out entry. This messes up the calculation completely. How can I update the query to ignore entries where there is not a prior "log in" entry? OR how can I add in "log-in" entries for say, 2 mins before any lone "log-out" entries, so that the above code can achieve a more reasonable result?
推荐答案
如果您需要检测哪些记录代表没有匹配注销记录的登录，反之亦然，那么使用唯一的会话ID扩展表将很有帮助.
添加列session_id，在登录时生成其值，在会话中记住该值，并为注销错误将相同的值添加到session_id.这样可以大大简化查询.
If you need to detect which records represent a login without a matching logout record and vice versa, then it would be helpful to extended your tables with a unique session ID.
Add a column session_id, generate its value on login, remember it in the session and put the same value to session_id for the logout error. It would simplify the queries a lot.
如果您需要一个查询来添加缺少的登录记录，请尝试以下操作:
If you need a query that would add missing login records, try the following:
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.) You can either insert the result to the table (INSERT INTO logs (...) SELECT ...) or add the query to the UNION in your original query.
正如@OlivierCoilland所指出的，查询变得相当复杂，您可以考虑在应用程序侧进行分析.因为我猜想日志表很大，所以您不应该依赖于内存中的所有条目.您可能需要某种滑动窗口"技术.
As noted by @OlivierCoilland, the query is getting pretty complex and you can consider analysis on the application side. Because I guess the log table is pretty big, you shouldn't rely you'll fit all entries in memory. You would probably need some kind of "sliding window" technique.
第四个选项是删除不匹配的注销记录.我的解决方案需要一个临时表，因此我不会在此处粘贴整个(相当长的)代码，只是看到小提琴.
The fourth option is deleting unmatched logout records. My solution needs a temporary table, so I do not paste the whole (pretty long) code here, just see the fiddle.
这篇关于根据结果条件在mysql查询中插入或省略条目的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
