JohnHC JohnHC - 1 month ago 9
SQL Question

create id column based on activity data

I have a table

EVENTS


USER EVENT_TS EVENT_TYPE
abc 2016-01-01 08:00:00 Login
abc 2016-01-01 08:25:00 Stuff
abc 2016-01-01 10:00:00 Stuff
abc 2016-01-01 14:00:00 Login
xyz 2015-12-31 18:00:00 Login
xyz 2016-01-01 08:00:00 Logout


What I need to do is produce a
session
field for each period of activity for each user. In addition, if the user has been idle for a period equal to or longer than
p_timeout
(1 hour in this case) then a new session starts at the next activity. Users don't always log out cleanly, so the logout isn't walways there...

Notes:

Logout always terminates a session

There doesn't have to be a logout or a login (because software)

Login is always a new session

Output like

USER EVENT_TS EVENT_TYPE SESSION
abc 2016-01-01 08:00:00 Login 1
abc 2016-01-01 08:25:00 Stuff 1
abc 2016-01-01 10:00:00 Stuff 2
abc 2016-01-01 14:00:00 Login 3
xyz 2015-12-31 18:00:00 Login 1
xyz 2016-01-01 08:00:00 Logout 1


Any thoughts on how to acheive this?

Answer

I think this may do what you need. I changed "user" to "usr" in the input, and "session" to "sess" in the output - I don't ever use reserved Oracle words for object names.

Note: as Boneist pointed out below, my solution will assign a session number of 0 to the first session, if it is a Logout event (or a succession of Logouts right at the top). If this situation can occur in the data, and if the desired behavior is to start session counts at 1 even in that case, then the definition of flag must be tweaked - for example, by making flag = 1 when lag(event_ts) over (partition by usr order by event_ts) is null as well.

Good luck!

with
     events ( usr, event_ts, event_type ) as (
       select 'abc', to_timestamp('2016-01-01 08:00:00', 'yyyy-mm-dd hh24:mi:ss'), 'Login' from dual union all
       select 'abc', to_timestamp('2016-01-01 08:25:00', 'yyyy-mm-dd hh24:mi:ss'), 'Stuff' from dual union all
       select 'abc', to_timestamp('2016-01-01 10:00:00', 'yyyy-mm-dd hh24:mi:ss'), 'Stuff' from dual union all
       select 'abc', to_timestamp('2016-01-01 14:00:00', 'yyyy-mm-dd hh24:mi:ss'), 'Login' from dual union all
       select 'xyz', to_timestamp('2015-12-31 18:00:00', 'yyyy-mm-dd hh24:mi:ss'), 'Login' from dual union all
       select 'xyz', to_timestamp('2016-01-01 08:00:00', 'yyyy-mm-dd hh24:mi:ss'), 'Logout' from dual
     ),
     start_of_sess ( usr, event_ts, event_type, flag ) as (
       select usr, event_ts, event_type,
              case when event_type != 'Logout' 
                    and
                        (    event_ts >= lag(event_ts) over (partition by usr 
                                                             order by event_ts) + 1/24 
                          or event_type = 'Login'
                          or lag(event_type) over (partition by usr 
                                                   order by event_ts) = 'Logout'
                        )
                   then 1 end
       from   events
     )
select usr, event_ts, event_type,
       count(flag) over (partition by usr order by event_ts) as sess
from   start_of_sess
; 

Output (timestamps use my current NLS_TIMESTAMP_FORMAT setting):

USR EVENT_TS                          EVENT_TYPE   SESS
--- --------------------------------- ---------- ------
abc 01-JAN-2016 08.00.00.000000000 AM Login           1
abc 01-JAN-2016 08.25.00.000000000 AM Stuff           1
abc 01-JAN-2016 10.00.00.000000000 AM Stuff           2
abc 01-JAN-2016 02.00.00.000000000 PM Login           3
xyz 31-DEC-2015 06.00.00.000000000 PM Login           1
xyz 01-JAN-2016 08.00.00.000000000 AM Logout          1

 6 rows selected