我有一个应用程序用下面的审计表格式写入MySQL。我需要确定一天中任何给定时间登录系统的当前用户数量
我尝试过各种自联接、子查询、timediff,甚至转换为unixtime加上一些简单的数学,但我无法找到一个可以接受的解决方案
如有任何建议或协助,我们将不胜感激。
audit_table
user | action | time
-----|--------|------
1 | login | 2013-07-02 00:37:00
2 | login | 2013-07-02 00:38:00
1 | logout | 2013-07-02 00:39:30
3 | login | 2013-07-02 00:40:00
2 | logout | 2013-07-02 02:30:00
所需格式:一天24小时的小时数|该小时内登录的用户数
示例(请注意,用户3尚未注销,因此在第3小时的计数中仍显示活动)
hour | usersloggedin
-----|---------------
0 | 3
1 | 2
2 | 2
3 | 1
首先,您需要创建一个名为hours_table的表,其编号从00到23:
CREATE TABLE `hours_table` (`hour` char(2) DEFAULT NULL);
hour
----
00
01
02
03
04
..
执行此查询后:
SELECT hour, (count(DISTINCT(li.user)) - count(DISTINCT(lo.user))) AS usersloggedin FROM hours_table
LEFT JOIN audit_table AS li ON DATE_FORMAT(li.time, '%Y%m%d%H')<=CONCAT('20130702',hour) AND DATE_FORMAT(li.time, '%Y%m%d')='20130702' AND li.action='login'
LEFT JOIN audit_table AS lo ON DATE_FORMAT(lo.time, '%Y%m%d%H')<CONCAT('20130702',hour) AND DATE_FORMAT(lo.time, '%Y%m%d')='20130702' AND lo.action='logout'
GROUP BY hour
如果您认为昨天登录的用户今天处于活动状态,则您将另一天的格式设置为yyyymmdd remove DATE_format(时间,'%Y%m%d')='20130702,以代替20130702。