For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!
Hey Folks,
I'm trying to wrap my head around all of this but I'm stuck on how to scaffold the context to an oracle DB where I'm not seeing the ability to use "UseOracle".
Any assistance would be greatly appreciated.
Thanks,
Wayne
MHO%xe> select t 2 , userid 3 , case when trunc( mod( (t-lag(t) over ( partition by userid order by userid, t))*24*60, 60 ) ) >= 1 4 then 1 5 else 0 6 end cnt 7 from clickstream; T USERID CNT ------------------- ---------- ---------- 05-09-2009 10:00:00 2 0 05-09-2009 10:00:24 2 0 05-09-2009 10:01:23 2 0 05-09-2009 10:02:40 2 1 05-09-2009 00:58:24 7 0 05-09-2009 02:30:33 7 1 6 rijen zijn geselecteerd.
T USERID CNT ------------------- ---------- ---------- 05-09-2009 10:00:00 2 0 05-09-2009 10:00:24 2 0 05-09-2009 10:01:23 2 0 05-09-2009 10:02:40 2 1 05-09-2009 10:02:50 2 1 05-09-2009 10:05:40 2 2 05-09-2009 00:58:24 7 0 05-09-2009 02:30:33 7 1
MHO%xe> select t 2 , userid 3 , nvl(last_value(nullif(sessionid, 0) ignore nulls) over ( partition by userid order by userid, t),0) sessionid 4 from ( select t 5 , userid 6 -- , trunc( mod( (t-lag(t) over ( partition by userid order by userid, t))*24*60, 60 ) ) minutes 7 , case when trunc( mod( (t-lag(t) over ( partition by userid order by userid, t))*24*60, 60 )) >= 1 8 then row_number() over ( partition by userid order by userid, t) 9 else 0 10 end sessionid 11 from clickstream 12 ); T USERID SESSIONID ------------------- ---------- ---------- 05-09-2009 10:00:00 2 0 05-09-2009 10:00:24 2 0 05-09-2009 10:01:23 2 0 05-09-2009 10:02:40 2 4 05-09-2009 10:02:50 2 4 05-09-2009 10:05:40 2 6 05-09-2009 00:58:24 7 0 05-09-2009 02:30:33 7 2 8 rijen zijn geselecteerd.
select t , userid , -1 + (dense_rank() over (partition by userid order by sessionid)) sessionid from ( select t , userid , nvl(last_value(nullif(sessionid, 0) ignore nulls) over ( partition by userid order by userid, t),0) sessionid from ( select t , userid , case when trunc( mod( (t-lag(t) over ( partition by userid order by userid, t))*24*60, 60 )) >= 1 then row_number() over ( partition by userid order by userid, t) else 0 end sessionid from clickstream ) ) /
MHO%xe> select t 2 , userid 3 , -1 + (dense_rank() over (partition by userid order by sessionid)) sessionid 4 from ( select t 5 , userid 6 , nvl(last_value(nullif(sessionid, 0) ignore nulls) over 7 ( partition by userid order by userid, t),0) sessionid 8 from ( select t 9 , userid 10 , case when trunc( mod( (t-lag(t) over ( partition by userid order by userid, t))*24*60, 60 )) >= 1 11 then dense_rank() over ( partition by userid order by userid, t) 12 else 0 13 end sessionid 14 from clickstream 15 ) 16 ); Verstreken: 00:00:00.25 Uitvoeringspan ---------------------------------------------------------- Plan hash value: 1413192182 -------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 8 | 280 | 5 (40)| 00:00:01 | | 1 | WINDOW SORT | | 8 | 280 | 5 (40)| 00:00:01 | | 2 | VIEW | | 8 | 280 | 4 (25)| 00:00:01 | | 3 | WINDOW BUFFER | | 8 | 280 | 4 (25)| 00:00:01 | | 4 | VIEW | | 8 | 280 | 4 (25)| 00:00:01 | | 5 | WINDOW SORT | | 8 | 176 | 4 (25)| 00:00:01 | | 6 | TABLE ACCESS FULL| CLICKSTREAM | 8 | 176 | 3 (0)| 00:00:01 | -------------------------------------------------------------------------------------- Note ----- - dynamic sampling used for this statement
SQL> select userid, t, nvl (sum (s) over (partition by userid order by t), 0) sessionid from (select userid, t, case when t - lag(t) over (partition by userid order by t) > 1 / (24 * 60) then 1 end s from clickstream order by userid, t) / USERID T SESSIONID ---------- --------------------- ---------- 2 05.09.2009 10:00:00 0 2 05.09.2009 10:00:24 0 2 05.09.2009 10:01:23 0 2 05.09.2009 10:02:40 1 2 05.09.2009 10:02:50 1 2 05.09.2009 10:05:40 2 7 05.09.2009 00:58:24 0 7 05.09.2009 02:30:33 1 8 rows selected.
create table streamT(USERID,TimeVal) as select 2,to_date('2009-09-05 10:00:00','YYYY/MM/DD HH24:MI:SS') from dual union select 2,to_date('2009-09-05 10:00:24','YYYY/MM/DD HH24:MI:SS') from dual union select 2,to_date('2009-09-05 10:01:23','YYYY/MM/DD HH24:MI:SS') from dual union select 2,to_date('2009-09-05 10:02:40','YYYY/MM/DD HH24:MI:SS') from dual union select 2,to_date('2009-09-05 10:02:50','YYYY/MM/DD HH24:MI:SS') from dual union select 2,to_date('2009-09-05 10:05:40','YYYY/MM/DD HH24:MI:SS') from dual union select 7,to_date('2009-09-05 00:58:24','YYYY/MM/DD HH24:MI:SS') from dual union select 7,to_date('2009-09-05 02:30:33','YYYY/MM/DD HH24:MI:SS') from dual; select USERID,to_char(TimeVal,'yyyy-mm-dd hh24:mi:ss') as TimeVal, count(willCnt) over(partition by USERID order by TimeVal) as SESSIONID from (select USERID,TimeVal, case when TimeVal-interVal '1' minute >= Lag(TimeVal) over(partition by USERID order by TimeVal) then 1 end as willCnt from streamT) order by USERID,TimeVal; USERID TimeVal SESSIONID ------- ------------------- --------- 2 2009-09-05 10:00:00 0 2 2009-09-05 10:00:24 0 2 2009-09-05 10:01:23 0 2 2009-09-05 10:02:40 1 2 2009-09-05 10:02:50 1 2 2009-09-05 10:05:40 2 7 2009-09-05 00:58:24 0 7 2009-09-05 02:30:33 1