sql - 根据事件数据创建 id 列

Question

我有一张表 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

我需要做的是为每个用户的每个事件周期生成一个session 字段。此外，如果用户空闲的时间等于或长于 p_timeout(在本例中为 1 小时)，则新 session 会在下一个事件开始。用户并不总是干净地注销，所以注销并不总是在那里......

注意事项:

注销总是终止 session
不必注销或登录(因为软件)
登录总是一个新 session

输出像

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

关于如何实现这一点有什么想法吗？

Answer 1

我认为这可以满足您的需要。我在输入中将“user”更改为“usr”，在输出中将“session”更改为“sess”——我从不使用保留的 Oracle 字作为对象名称。

注意:正如 Boneist 在下面指出的那样，我的解决方案将为第一个 session 分配 0 session 号，如果它是 Logout 事件(或顶部的一系列 Logout)。如果这种情况可能发生在数据中，并且即使在这种情况下所需的行为也是从 1 开始 session 计数，则必须调整 flag 的定义 - 例如，通过制作 flag = 1 当 lag(event_ts) over (partition by usr order by event_ts) 也为 null 时。

祝你好运!

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
; 

输出(时间戳使用我当前的NLS_TIMESTAMP_FORMAT 设置):

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