根据活动数据创建ID列 [英] create id column based on activity data
问题描述
我有一张桌子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小时)，则新的会话将从下一个活动开始.用户并非总是干净退出，所以退出并不总是在那里……
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...
注意:
注销始终会终止会话
不必注销或登录(由于软件)
登录始终是一个新会话
Logout always terminates a session
There doesn't have to be a logout or a login (because software)
Login is always a new 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
关于如何实现这一目标的任何想法?
Any thoughts on how to acheive this?
推荐答案
我认为这可以满足您的需求.我在输入中将"user"更改为"usr"，在输出中将"session"更改为"sess"-我从不使用保留的Oracle单词作为对象名.
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.
注意:正如Boneist在下面指出的那样，如果是Logout事件(或连续的Logout s在顶部).如果数据中可能出现这种情况，并且即使在这种情况下，如果所需的行为是将会话计数从1开始，那么也必须调整flag的定义-例如，通过将flag = 1设置为出色地.
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.
祝你好运！
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设置):
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
这篇关于根据活动数据创建ID列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
