I want to divide user clicks into sessions. A session is defined to include all of a certain user's clicks that occur within 60 seconds of one another:
create table clickstream
( t date
, userid number(10) );
insert into clickstream values (to_date('5-9-2009 10:00:00','dd-mm-yyyy hh24:mi:ss'), 2);
insert into clickstream values (to_date('5-9-2009 10:00:24','dd-mm-yyyy hh24:mi:ss') , 2);
insert into clickstream values (to_date('5-9-2009 10:01:23','dd-mm-yyyy hh24:mi:ss') , 2);
insert into clickstream values (to_date('5-9-2009 10:02:40','dd-mm-yyyy hh24:mi:ss') , 2);
insert into clickstream values (to_date('5-9-2009 0:58:24','dd-mm-yyyy hh24:mi:ss') , 7);
insert into clickstream values (to_date('5-9-2009 2:30:33','dd-mm-yyyy hh24:mi:ss') , 7);
commit;
The output should be:
Time t Userid Session
10:00:00 2 0
10:00:24 2 0
10:01:23 2 0
10:02:40 2 1
00:58:24 7 0
02:30:33 7 1
This query divides the clickstream into sessions:
select to_char(t,'hh24:mi:ss') t
, userid
, count(*) over
( partition by userid order by t RANGE between (1/(24*60)) preceding and current row) count
from clickstream
order by userid,t
T USERID COUNT
-------- ---------- ----------
10:00:00 2 1
10:00:24 2 2
10:01:23 2 2
10:02:40 2 1
00:58:24 7 1
02:30:33 7 1
Because when COUNT = 1 a new session is started for a certain user but I don't know how to proceed?