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

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

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临时表连接起来，以获得每个用户名的在线日期.结果将在最终查询中进行处理.注意:为了更好地演示，我在这里添加了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 |
+------------+-------+