I am not an Oracle guru but I would like to work out the highest concurrent logins for the day for an application based on the user's Start Time and Finish Time with distinct users. Below is an example and the table.
Table scheme:
| Username | Login Activity | Start Date | Finish Date |
|---|---|---|---|
| User1 | NormalTime | 2022-12-26T15:19:41 | 2022-12-26T15:19:44 |
| User2 | AfterHours | 2023-01-09T01:29:02 | 2023-01-09T02:29:29 |
| User3 | NormalTime | 2023-01-09T14:51:36 | 2023-01-09T16:28:17 |
| User4 | AfterHours | 2023-01-10T04:38:41 | 2023-01-10T05:59:33 |
| User5 | NormalTime | 2023-01-10T12:05:52 | 2023-01-10T13:08:29 |
| User4 | AfterHours | 2023-01-26T04:45:51 | 2023-01-26T06:05:38 |
| User4 | AfterHours | 2022-11-08T04:38:19 | 2022-11-08T05:48:58 |
| User4 | AfterHours | 2022-11-09T04:40:03 | 2022-11-09T06:12:10 |
| User4 | AfterHours | 2023-01-18T05:08:48 | 2023-01-18T06:20:27 |
| User4 | AfterHours | 2023-01-20T05:40:05 | 2023-01-20T05:40:05 |
| User1 | NormalTime | 2022-12-26T15:19:44 | 2022-12-26T16:20:09 |
| User2 | AfterHours | 2023-01-09T02:47:24 | 2023-01-09T03:48:13 |
| User4 | AfterHours | 2023-01-10T04:43:55 | 2023-01-10T06:00:15 |
| User4 | AfterHours | 2023-01-23T05:21:35 | 2023-01-23T06:50:33 |
| User4 | AfterHours | 2023-01-24T05:14:31 | 2023-01-24T06:24:04 |
I would like to return something like this
Table scheme:
| Date | Max Concurrent Logins |
|---|---|
| 2022-12 | 2 |
| 2023-01 | 3 |
Example Situation
an example is there was a login at between 2023-01-30 6:00 and 23-01-30 7:00, then there is another user who logs in at 6:05 now there are 2 concurrent logins, and that same user logs out at 6:15 decreasing by 1 and 3rd user logs in at 6:20 and a 4th at 6:30 now there are 3 concurrent users. User 4 logs out at 6:45 and the 3rd user stays logged in by the time the 1st user logs out at 7:00 so the highest value for that time period is 3 concurrent users. and it would repeat that for the 3rd user who is still logged in. Hope this makes sense.
CodePudding user response：
You can count per each day firstly within the subquery, and then pick the maximum values within the main query such as
WITH t1 AS
(
SELECT TO_CHAR(Finish_Date,'yyyy-mm') AS "Date", --> Date should be quoted as a reserved keyword
COUNT(*) OVER (PARTITION BY TRUNC(Finish_Date)) AS cnt
FROM t -- your table
)
SELECT "Date", MAX(cnt) AS "Max Concurrent Logins"
FROM t1
GROUP BY "Date"
ORDER BY 1
where I observe that the Start_Date and Finish_Date has the identical day values per each line.
CodePudding user response：
Used a bit changed sample data (to test chained concurrencies):
WITH
logins (USERNAME, LOGIN_ACTIVITY, START_DATE, FINISH_DATE) AS
(
Select 'User1', 'NormalTime', To_Date('2022-12-26 15:19:41', 'yyyy-mm-dd hh24_mi:ss'), To_Date('2022-12-26 15:19:44', 'yyyy-mm-dd hh24_mi:ss') From Dual Union All
Select 'User2', 'AfterHours', To_Date('2023-01-09 01:29:02', 'yyyy-mm-dd hh24_mi:ss'), To_Date('2023-01-09 02:29:29', 'yyyy-mm-dd hh24_mi:ss') From Dual Union All
Select 'User3', 'NormalTime', To_Date('2023-01-09 01:30:36', 'yyyy-mm-dd hh24_mi:ss'), To_Date('2023-01-09 01:37:17', 'yyyy-mm-dd hh24_mi:ss') From Dual Union All
Select 'User1', 'AfterHours', To_Date('2023-01-09 01:32:24', 'yyyy-mm-dd hh24_mi:ss'), To_Date('2023-01-09 01:48:13', 'yyyy-mm-dd hh24_mi:ss') From Dual Union All
Select 'User3', 'NormalTime', To_Date('2023-01-09 14:51:36', 'yyyy-mm-dd hh24_mi:ss'), To_Date('2023-01-09 16:28:17', 'yyyy-mm-dd hh24_mi:ss') From Dual Union All
Select 'User3', 'NormalTime', To_Date('2023-01-09 16:27:36', 'yyyy-mm-dd hh24_mi:ss'), To_Date('2023-01-09 17:00:17', 'yyyy-mm-dd hh24_mi:ss') From Dual Union All
Select 'User4', 'AfterHours', To_Date('2023-01-10 04:38:41', 'yyyy-mm-dd hh24_mi:ss'), To_Date('2023-01-10 05:59:33', 'yyyy-mm-dd hh24_mi:ss') From Dual Union All
Select 'User5', 'NormalTime', To_Date('2023-01-10 12:05:52', 'yyyy-mm-dd hh24_mi:ss'), To_Date('2023-01-10 13:08:29', 'yyyy-mm-dd hh24_mi:ss') From Dual Union All
Select 'User4', 'AfterHours', To_Date('2023-01-26 04:45:51', 'yyyy-mm-dd hh24_mi:ss'), To_Date('2023-01-26 06:05:38', 'yyyy-mm-dd hh24_mi:ss') From Dual Union All
Select 'User4', 'AfterHours', To_Date('2022-11-08 04:38:19', 'yyyy-mm-dd hh24_mi:ss'), To_Date('2022-11-08 05:48:58', 'yyyy-mm-dd hh24_mi:ss') From Dual Union All
Select 'User4', 'AfterHours', To_Date('2022-11-09 04:40:03', 'yyyy-mm-dd hh24_mi:ss'), To_Date('2022-11-09 06:12:10', 'yyyy-mm-dd hh24_mi:ss') From Dual Union All
Select 'User4', 'AfterHours', To_Date('2023-01-18 05:08:48', 'yyyy-mm-dd hh24_mi:ss'), To_Date('2023-01-18 06:20:27', 'yyyy-mm-dd hh24_mi:ss') From Dual Union All
Select 'User4', 'AfterHours', To_Date('2023-01-20 05:40:05', 'yyyy-mm-dd hh24_mi:ss'), To_Date('2023-01-20 05:40:05', 'yyyy-mm-dd hh24_mi:ss') From Dual Union All
Select 'User1', 'NormalTime', To_Date('2022-12-26 15:19:44', 'yyyy-mm-dd hh24_mi:ss'), To_Date('2022-12-26 16:20:09', 'yyyy-mm-dd hh24_mi:ss') From Dual Union All
Select 'User3', 'AfterHours', To_Date('2023-01-10 04:43:55', 'yyyy-mm-dd hh24_mi:ss'), To_Date('2023-01-10 06:00:15', 'yyyy-mm-dd hh24_mi:ss') From Dual Union All
Select 'User4', 'AfterHours', To_Date('2023-01-23 05:21:35', 'yyyy-mm-dd hh24_mi:ss'), To_Date('2023-01-23 06:50:33', 'yyyy-mm-dd hh24_mi:ss') From Dual Union All
Select 'User4', 'AfterHours', To_Date('2023-01-24 05:14:31', 'yyyy-mm-dd hh24_mi:ss'), To_Date('2023-01-24 06:24:04', 'yyyy-mm-dd hh24_mi:ss') From Dual
),
Create CTE (grid) to put previous and next row data in every row:
grid AS
(
Select
ROW_NUMBER() OVER(Order By START_DATE) "RN",
LAG(USERNAME) OVER(Order By START_DATE) "PREV_USERNAME",
USERNAME,
LEAD(USERNAME) OVER(Order By START_DATE) "NEXT_USERNAME",
--
LAG(START_DATE) OVER(Order By START_DATE) "PREV_START_DATE",
START_DATE,
LEAD(START_DATE) OVER(Order By START_DATE) "NEXT_START_DATE",
--
LAG(FINISH_DATE) OVER(Order By START_DATE) "PREV_FINISH_DATE",
FINISH_DATE,
LEAD(FINISH_DATE) OVER(Order By START_DATE) "NEXT_FINISH_DATE"
From
logins
Order By START_DATE
)
G r i d :
RN PREV_USERNAME USERNAME NEXT_USERNAME PREV_START_DATE START_DATE NEXT_START_DATE PREV_FINISH_DATE FINISH_DATE NEXT_FINISH_DATE
---------- ------------- -------- ------------- --------------- ---------- --------------- ---------------- ----------- ----------------
1 User4 User4 08-NOV-22 09-NOV-22 08-NOV-22 09-NOV-22
2 User4 User4 User1 08-NOV-22 09-NOV-22 26-DEC-22 08-NOV-22 09-NOV-22 26-DEC-22
3 User4 User1 User1 09-NOV-22 26-DEC-22 26-DEC-22 09-NOV-22 26-DEC-22 26-DEC-22
4 User1 User1 User2 26-DEC-22 26-DEC-22 09-JAN-23 26-DEC-22 26-DEC-22 09-JAN-23
5 User1 User2 User3 26-DEC-22 09-JAN-23 09-JAN-23 26-DEC-22 09-JAN-23 09-JAN-23
6 User2 User3 User1 09-JAN-23 09-JAN-23 09-JAN-23 09-JAN-23 09-JAN-23 09-JAN-23
7 User3 User1 User3 09-JAN-23 09-JAN-23 09-JAN-23 09-JAN-23 09-JAN-23 09-JAN-23
8 User1 User3 User3 09-JAN-23 09-JAN-23 09-JAN-23 09-JAN-23 09-JAN-23 09-JAN-23
9 User3 User3 User4 09-JAN-23 09-JAN-23 10-JAN-23 09-JAN-23 09-JAN-23 10-JAN-23
10 User3 User4 User3 09-JAN-23 10-JAN-23 10-JAN-23 09-JAN-23 10-JAN-23 10-JAN-23
11 User4 User3 User5 10-JAN-23 10-JAN-23 10-JAN-23 10-JAN-23 10-JAN-23 10-JAN-23
12 User3 User5 User4 10-JAN-23 10-JAN-23 18-JAN-23 10-JAN-23 10-JAN-23 18-JAN-23
13 User5 User4 User4 10-JAN-23 18-JAN-23 20-JAN-23 10-JAN-23 18-JAN-23 20-JAN-23
14 User4 User4 User4 18-JAN-23 20-JAN-23 23-JAN-23 18-JAN-23 20-JAN-23 23-JAN-23
15 User4 User4 User4 20-JAN-23 23-JAN-23 24-JAN-23 20-JAN-23 23-JAN-23 24-JAN-23
16 User4 User4 User4 23-JAN-23 24-JAN-23 26-JAN-23 23-JAN-23 24-JAN-23 26-JAN-23
17 User4 User4 24-JAN-23 26-JAN-23 24-JAN-23 26-JAN-23
Main SQL
Select
TRUNC(START_DATE) "CONCURRENCY_DATE",
Count(CONCURRENCY) "MAX_CONCURRENT_USERS"
From
( SELECT RN,
USERNAME,
START_DATE,
CASE WHEN USERNAME != PREV_USERNAME And Trunc(START_DATE) = Trunc(PREV_START_DATE) And
START_DATE Between PREV_START_DATE And PREV_FINISH_DATE
THEN 1
WHEN USERNAME != NEXT_USERNAME And Trunc(START_DATE) = Trunc(NEXT_START_DATE) And
NEXT_START_DATE Between START_DATE And FINISH_DATE
THEN 1
END "CONCURRENCY"
FROM
grid g
ORDER BY RN
)
Where CONCURRENCY Is Not Null
Group By TRUNC(START_DATE)
Order By TRUNC(START_DATE)
R e s u l t :
| CONCURRENCY_DATE | MAX_CONCURRENT_USERS |
|---|---|
| 09-JAN-23 | 3 |
| 10-JAN-23 | 2 |
With a differrent grid and detailed main SQL you could have insight to the concurrecies:
grid AS
(
Select
ROW_NUMBER() OVER(Order By START_DATE) "RN",
LAG(USERNAME) OVER(Order By START_DATE) "PREV_USERNAME",
USERNAME,
LEAD(USERNAME) OVER(Order By START_DATE) "NEXT_USERNAME",
--
LAG(START_DATE) OVER(Order By START_DATE) "PREV_START_DATE",
START_DATE,
LEAD(START_DATE) OVER(Order By START_DATE) "NEXT_START_DATE",
--
LAG(START_DATE) OVER(Order By START_DATE) - LAG(Trunc(START_DATE)) OVER(Order By START_DATE) "PREV_START_DAY_TIME",
START_DATE - Trunc(START_DATE) "START_DAY_TIME",
LEAD(START_DATE) OVER(Order By START_DATE) - LEAD(Trunc(START_DATE)) OVER(Order By START_DATE) "NEXT_START_DAY_TIME",
--
LAG(FINISH_DATE) OVER(Order By START_DATE) "PREV_FINISH_DATE",
FINISH_DATE,
LEAD(FINISH_DATE) OVER(Order By START_DATE) "NEXT_FINISH_DATE",
--
Round(LAG(FINISH_DATE) OVER(Order By START_DATE) - LAG(Trunc(FINISH_DATE)) OVER(Order By START_DATE), 9) "PREV_END_DAY_TIME",
Round(FINISH_DATE - Trunc(FINISH_DATE), 9) "END_DAY_TIME",
Round(LEAD(FINISH_DATE) OVER(Order By START_DATE) - LEAD(Trunc(FINISH_DATE)) OVER(Order By START_DATE), 9) "NEXT_END_DAY_TIME",
Round(FINISH_DATE - START_DATE, 9) "LOGGED_DAY_TIME"
From
logins
Order By START_DATE
)
Select
TRUNC(START_DATE) "CONCURRENCY_DATE",
LPAD(FLOOR(CONCURRENCY_START_TIME*24*60*60/(60*60)), 2, '0') || ':' ||
LPAD(FLOOR( (CONCURRENCY_START_TIME - FLOOR(CONCURRENCY_START_TIME*24*60*60/(60*60))/24) * 24 * 60 ), 2, '0') || ':' ||
LPAD(FLOOR((((CONCURRENCY_START_TIME - FLOOR(CONCURRENCY_START_TIME*24*60*60/(60*60))/24) * 24 * 60) -
FLOOR( (CONCURRENCY_START_TIME - FLOOR(CONCURRENCY_START_TIME*24*60*60/(60*60))/24) * 24 * 60 ))*60), 2, '0') "CONCURRENCY_START_TIME",
--
LPAD(FLOOR(CONCURRENCY_END_TIME*24*60*60/(60*60)), 2, '0') || ':' ||
LPAD(FLOOR( (CONCURRENCY_END_TIME - FLOOR(CONCURRENCY_END_TIME*24*60*60/(60*60))/24) * 24 * 60 ), 2, '0') || ':' ||
LPAD(FLOOR((((CONCURRENCY_END_TIME - FLOOR(CONCURRENCY_END_TIME*24*60*60/(60*60))/24) * 24 * 60) -
FLOOR( (CONCURRENCY_END_TIME - FLOOR(CONCURRENCY_END_TIME*24*60*60/(60*60))/24) * 24 * 60 ))*60), 2, '0') "CONCURRENCY_END_TIME",
CONCURRENT_USERS
From
(
SELECT RN,
USERNAME,
START_DATE,
CASE WHEN USERNAME != PREV_USERNAME And Trunc(START_DATE) = Trunc(PREV_START_DATE) And
START_DATE Between PREV_START_DATE And PREV_FINISH_DATE
THEN PREV_USERNAME || ', ' || USERNAME
WHEN USERNAME != NEXT_USERNAME And Trunc(START_DATE) = Trunc(NEXT_START_DATE) And
NEXT_START_DATE Between START_DATE And FINISH_DATE
THEN USERNAME || ', ' || NEXT_USERNAME
END "CONCURRENT_USERS",
--
CASE WHEN USERNAME != PREV_USERNAME And Trunc(START_DATE) = Trunc(PREV_START_DATE) And
START_DATE Between PREV_START_DATE And PREV_FINISH_DATE
THEN START_DAY_TIME
WHEN USERNAME != NEXT_USERNAME And Trunc(START_DATE) = Trunc(NEXT_START_DATE) And
NEXT_START_DATE Between START_DATE And FINISH_DATE
THEN NEXT_START_DAY_TIME
END "CONCURRENCY_START_TIME",
--
CASE WHEN USERNAME != PREV_USERNAME And Trunc(START_DATE) = Trunc(PREV_START_DATE) And
START_DATE Between PREV_START_DATE And PREV_FINISH_DATE
THEN LEAST(END_DAY_TIME, PREV_END_DAY_TIME)
WHEN USERNAME != NEXT_USERNAME And Trunc(START_DATE) = Trunc(NEXT_START_DATE) And
NEXT_START_DATE Between START_DATE And FINISH_DATE
THEN LEAST(NEXT_END_DAY_TIME, END_DAY_TIME)
END "CONCURRENCY_END_TIME",
NEXT_END_DAY_TIME
FROM
grid g
ORDER BY RN
)
Where CONCURRENT_USERS Is Not Null
Order By TRUNC(START_DATE)
R e s u l t :
CONCURRENCY_DATE CONCURRENCY_START_TIME CONCURRENCY_END_TIME CONCURRENT_USERS
---------------- ---------------------- -------------------- ----------------
09-JAN-23 01:30:36 01:37:16 User2, User3
09-JAN-23 01:30:36 01:37:16 User2, User3
09-JAN-23 01:32:24 01:37:16 User3, User1
10-JAN-23 04:43:55 05:59:33 User4, User3
10-JAN-23 04:43:55 05:59:33 User4, User3