如何获取每日登录用户数?

Question

我有一个logged_log表，表中有用户名、login_time和logout_time列。我需要统计每日登录用户

表结构:

tbody> <<tr>

列名	类型
用户名	varchar (32)
login_time	datetime
logout_time	datetime nullable

由于您使用的是MySQL 5.0, 8.0中引入的方便的窗口函数是不可用的。所以我们必须坚持基本特征。
首先，我强烈建议创建一个名为date_list的临时表，它列出logged_log表中最小登录日期和最大注销日期之间的完整日期。我们只需要执行一次复杂查询来创建临时表，它在今天剩下的时间里是合格的。否则，如果将代码放入主查询中，则会浪费大量时间。(相信我，如果我们每次都要执行复杂的事情，那么响应时间将是非常痛苦的)此外，通过创建临时表可以提高可读性。

create temporary table date_list as
(select  selected_date as each_date from 
(select adddate('1970-01-01',t4.i*10000 + t3.i*1000 + t2.i*100 + t1.i*10 + t0.i) selected_date from
(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3,
(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4) v
where selected_date between (select min(date(login_time)) from logged_log) and curdate()) 
;
-- let's test it
select * from date_list;
+------------+
| each_date  |
+------------+
| 2023-01-05 |
| 2023-01-06 |
| 2023-01-07 |
| 2023-01-08 |
| 2023-01-09 |
| 2023-01-10 |
+------------+

接下来，我们将把logged_log表与date_list临时表连接起来，以获取每个用户名的ONLINE日期。结果将在最后的查询中处理。注意:为了更好地演示，我在这里添加了ORDER BY子句。但是在最后的查询中，我将删除它以节省文件排序时间。

select distinct username,each_date
from logged_log l
join
date_list d
on d.each_date between  date(login_time) and ifnull(date(logout_time),curdate())
order by username,each_date 
;
+----------+------------+
| username | each_date  |
+----------+------------+
| aaa      | 2023-01-06 |
| aaa      | 2023-01-08 |
| aaa      | 2023-01-09 |
| aaa      | 2023-01-10 |
| bbb      | 2023-01-06 |
| bbb      | 2023-01-07 |
| bbb      | 2023-01-09 |
| bbb      | 2023-01-10 |
| ccc      | 2023-01-07 |
| ccc      | 2023-01-09 |
| ccc      | 2023-01-10 |
| ddd      | 2023-01-05 |
| ddd      | 2023-01-06 |
| ddd      | 2023-01-07 |
| ddd      | 2023-01-08 |
| ddd      | 2023-01-09 |
| ddd      | 2023-01-10 |
+----------+------------+

在最后阶段，我们只需要使用前一个查询的结果进行聚合。

select each_date as date,count(username) as total
from
(select distinct username,each_date
from logged_log l
join
date_list d
on d.each_date between  date(login_time) and ifnull(date(logout_time),curdate()) ) t
group by each_date
;
+------------+-------+
| date       | total |
+------------+-------+
| 2023-01-05 |     1 |
| 2023-01-06 |     3 |
| 2023-01-07 |     3 |
| 2023-01-08 |     2 |
| 2023-01-09 |     4 |
| 2023-01-10 |     4 |
+------------+-------+