MySQL 判断系统每小时查询的用户数

Question

我有一个使用以下审计表格式写入 MySQL 的应用程序。我需要确定一天中任何给定时间登录到系统的当前用户数。

我已经尝试过各种自连接、子查询、timediff，甚至转换为 unixtime plus一些简单的数学运算，但是我无法提出可以接受的解决方案。
非常感谢任何指点或帮助。

审计表

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

Answer 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

在 20130702 的位置，如果您认为昨天登录的用户今天活跃，则您以 yyyymmdd 格式删除 DATE_FORMAT(time, '%Y%m%d')='20130702'。