I have a logged_log table, the table have username, login_time and logout_time column. I need to count daily login user.

Table structure:

sample data:

expected results:

I have try to use case to replace the null logout on logged_log part, then create temp table of day list on date_period part, but when join the table to get result, only has first day and total of all days users.

SELECT
   daily_logged_log.date, count( daily_logged_log.username )
FROM (
   SELECT
      date_period.date, logged_log.username
   FROM (
      SELECT curdate() - INTERVAL (a.a   (10 * b.a)   (100 * c.a)   (1000 * d.a) ) DAY as date
      FROM (SELECT 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as a
      cross join (SELECT 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as b
      cross join (SELECT 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as c
      cross join (SELECT 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as d
   ) as date_period
   LEFT JOIN (
      SELECT 
         username, 
         DATE( login_time ) as login_at,
         CASE
            WHEN logout_time IS NULL
            THEN DATE( NOW() )
            ELSE DATE( logout_time )
            END
         as logout_at
      FROM logged_log
      WHERE DATE( login_time ) <= '2023-01-09'
      AND CASE
            WHEN logout_time IS NULL
            THEN DATE( NOW() )
            ELSE DATE( logout_time )
            END >= '2023-01-07'
   ) as logged_log
   ON date_period.date BETWEEN logged_log.login_at AND logged_log.logout_at
   WHERE date_period.date BETWEEN '2023-01-07' AND '2023-01-09'
   GROUP BY date_period.date, logged_log.username
) as daily_logged_log

CodePudding user response：

As you are using MySQL 5.0, the convenient window functions which are introduced in 8.0 are out of questions. So we have to stick to the fundamental features.
First of all, I would strongly suggest creating a temporary table named date_list which lists the complete dates between the minimum login date and the maximum logout date from the logged_log table. We only need to execute the complex query ONCE to make the temp table and it stays eligible for the rest of today. Otherwise, it wastes a lot of time if we put the code into the main query.(Trust me, the reponse time would be painstaking if we have to execute the complicated thing every time) Besides, by making a temp table it improves readability.

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 |
 ------------ 

Next, we shall join the logged_log table with the date_list temporary table to get the ONLINE date for each username. The result will be worked upon in the final query. Note: Here I added the ORDER BY clause for better demonstration. But in the final query I shall remove it to save the filesort time.

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 |
 ---------- ------------ 

At the final stage, we just need to do the aggregation using the result from the previous query.

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 |
 ------------ -------