CODEWITHSUNDEEP
sqloracle-database
Mhd H_Nahhas
Mhd H_Nahhas

Reputation: 27

Finding max conrurrent users in sessions log table

I need to find the max concurrent users (with the datetime if applicable)

I have table t_sessions contains 10,800,000 rows like the following:

sesion_start      duration_in_sec  user_name
01/01/2019 01:00  280              a
01/01/2019 01:02  380              b
01/01/2019 01:01  1250             c

I need to find the max concurrent users (with showing its datetime if applicable) I created a temp table t_times contains all the times in seconds:

f_time
01/01/2019 00:00:01
01/01/2019 00:00:02
01/01/2019 00:00:03

select max(c) from
(select f_time, count(*) c
 from t_times a
 join t_sessions b 
     on a.f_time between sesion_start and
                         (sesion_start  + (duration_in_sec /86400))
 group by f_time)

is there any less cost and better way to run such query ?

Upvotes: 0

Views: 115

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269763

Yes, you can use "in/out" logic:

with t as (
      select session_start as td, 1 as inc
      from t_sessions t
      union all
      select session_start + (duration_in_sec / (24*60*60)), inc  -- old-fashioned method.  I'm being lazy
              -1 as inc
      from t_sessions t
     )
select t.*
from (select t.*,
             row_number() over (order by num_concurrent desc) as seqnum
      from (select t.td, sum(inc) over (order by t.td) as num_concurrent
            from t
            order by num_concurrent desc
           ) t
      ) t
where seqnum = 1;

This could have slight off-by-1 discrepancies, depending on whether the find second is included as a concurrent user or not.

This also assumes that sessions for a single user do not overlap, which is implied by your query.

Upvotes: 1

Related Questions