10-313 直近との差が1分以上ならインクリメント

SQLパズル

streamT
UserID  TimeVal
------  -------------------
     2  2009-09-05 10:00:00
     2  2009-09-05 10:00:24
     2  2009-09-05 10:01:23
     2  2009-09-05 10:02:40
     2  2009-09-05 10:02:50
     2  2009-09-05 10:05:40
     7  2009-09-05 00:58:24
     7  2009-09-05 02:30:33

UserIDごとで、TimeValの昇順で直近のTimeValとの差が1分以上なら
SessionIDをインクリメントしていった形式のデータを出力する。

出力結果
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

こちらを参考にさせていただきました(英語)

データ作成スクリプト

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;

SQL

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;

解説

こういった、直近との差の大小を判定するようなケースでは、旅人算の感覚が使えません。

date型 - date型 >= 1/24/60
としてもいいのですが、InterVal型を使うのもいいかもしれません。

---