相差超过一个小时的数据

需求

  
  
create table   test_1 as 
select 'w' cid,'a' sid ,'2016-04-17 02:25:45' dt from dual
union all
select 'w' cid,'b','2016-04-17 02:31:46' from dual
union all
select 'w' cid,'c','2016-04-17 02:35:01' from dual
union all
select 'w' cid,'d','2016-04-17 02:47:56' from dual
union all
select 'w' cid,'e','2016-04-17 02:49:40' from dual
union all
select 'w' cid,'f','2016-04-17 02:52:25' from dual
union all
select 'w' cid,'g','2016-04-17 02:54:39' from dual
union all
select 'w' cid,'h','2016-04-17 18:22:45' from dual
union all
select 'w' cid,'i','2016-04-17 20:16:25' from dual
union all
select 'w' cid,'j','2016-04-18 01:15:18' from dual
union all
select 'w' cid,'k','2016-04-18 06:07:13' from dual
union all
select 'w' cid,'l','2016-04-18 16:02:22' from dual
union all
select 'w' cid,'m','2016-04-18 16:14:49' from dual
union all
select 'w' cid,'n','2016-04-18 16:16:41' from dual
union all
select 'w' cid,'o','2016-04-18 16:19:36' from dual
union all
select 'w' cid,'p','2016-04-18 16:52:20' from dual
union all
select 'w' cid,'q','2016-04-18 16:53:39' from dual
union all
select 'w' cid,'r','2016-04-18 17:24:17' from dual
union all
select 'w' cid,'s','2016-04-18 17:29:15' from dual
union all
select 'w' cid,'t','2016-04-18 20:11:54' from dual
union all
select 'w' cid,'u','2016-04-18 20:17:16' from dual
union all
select 'w' cid,'v','2016-04-18 20:25:39' from dual
取sid之间相差超过一个小时的数据
比如 a 和 b 相差不超过一个小时则还是取第一个（2016/4/17 02:25:45）
a 和 h 相差超过1个小时则取 h（2016/4/17 18:22:45）
后续的sid需要和 h （2016/4/17 18:22:45）这个sid的时间比较 。依次比较
最终输出结果为
w,a,2016/4/17 02:25:45
w,h,2016/4/17 18:22:45
w,i,2016/4/17 20:16:25
w,j,2016/4/18 01:15:18
w,k,2016/4/18 06:07:13
w,l,2016/4/18 16:02:22
w,r,2016/4/18 17:24:17
w,t,2016/4/18 20:11:54

教主

 
 
with v1 as
 (select CID,
         SID,
         DT,
         min(dt) over(partition by cid order by dt range between 1 / 24 following and unbounded following) as after_hour,
         min(dt) over(partition by cid) as min_dt
    from test_1 a)
select * from v1
start with dt = min_dt
connect by dt = (prior after_hour)  

ITPUB

 
 
select  cid,
         sid,
         dt
 from
 (select cid,
         sid,
         to_char(dt, 'yyyy-mm-dd hh24:mi:ss') dt,
         count(*) over(partition by cid order by dt range between current row and interval '1' hour following) cnt,
         row_number() over(partition by cid order by dt) rn
     from test_1)
  start with rn = 1
  connect by prior rn + prior cnt = rn
  ;
学习12C新功能：
SELECT cid,sid,dt
FROM test_1 
MATCH_RECOGNIZE (
     PARTITION BY cid
     ORDER BY dt
     MEASURES CLASSIFIER() as FLAG
     ALL ROWS PER MATCH
     PATTERN ((A|B)+)
     DEFINE
        A as dt>last(A.DT,1)+1/24 or prev(dt) is null
     ) 
WHERE FLAG='A'
;