How does one count the number of online users per hour ?, Count the number of online users
The system has a table that records user logon and logout logs. Now, the number of online users is measured in an hour. This is a mathematical problem.
Case 1:
| ___________ One hour ____________ |
<= Logon time logout time <=
Case 2:
| ___________ One hour ____________ |
Logon Time <logout time
Case 3:
| ___________ One hour ____________ |
Logon Time <= logout time <=
Case 4:
| ___________ One hour ____________ |
<= Logon time <logout time
With dd as (-- simulate a 24-hour period
SELECT (trunc (to_date ('1970-02-09 ', 'yyyy-mm-dd') + (LEVEL-1)/24) one_hour_1,
(Trunc (to_date ('1970-02-09 ', 'yyyy-mm-dd') + LEVEL/24) one_hour_2
FROM dual
Connect by level <= 24
)
SELECT dd. one_hour_1, COUNT (1)
FROM log_user_login d,
Dd
WHERE (d. login_time> = dd. one_hour_1 AND d. exit_time <= dd. one_hour_2)
OR (d. login_time <dd. one_hour_1 AND d. exit_time BETWEEN dd. one_hour_1 AND dd. one_hour_2)
OR (d. login_time BETWEEN dd. one_hour_1 AND dd. one_hour_2 AND d. exit_time> dd. one_hour_2)
OR (d. login_time <dd. one_hour_1 AND d. exit_time> dd. one_hour_2)
Group by dd. one_hour_1
Order by dd. one_hour_1;