Oracle范围处理

浏览数：18 / 时间：2015年06月12日

1.表中字段自关联与分析函数的性能比较，自关联需要扫描表两次，分析函数扫描一次即可

----自关联
select v1.proj_id,v1.proj_start,v1.proj_end
from v v1,v v2
where v1.proj_start=v2.proj_end

----分析函数
select proj_id,proj_start,proj_end
from (select proj_id,proj_start,proj_end,
          lead(proj_start) over(order by proj_id) nproj_start
          from v)
where nproj_start = proj_end

上述处理方式稍作修改，也可以用于计算用户登录的时间间隔, lead(xxx) over(partition user order by xxx)，先按照用户进行分区

2.连续的时间段合并处理，求解合并后的时间区间。

select groupId,min(proj_start),max(proj_end) 
 from
 (select proj_id,proj_start,proj_end,sum(status)  over(order by proj_id) groupId   
  (select proj_id,proj_start,proj_end,
          case when lag(proj_end) over(order by proj_id)=proj_start 
                  then 0 else 1 end status
   from v)
) group by groupId order by 1

3.Lag和lead是一种连续数据处理函数,对于数据时区间内的判断则需要between的方式来判断

with x0 as
  (select id,sdate,edate
             max(edate) over (order by sdate rows between unbounded preceding and 1 preceding) as medate
    from b),
       x1 as
   (select sdate,edate,medate,case when medate>sdate then 0 else 1 end as status from x0),
       x2 as
   (select sdate,edate,sum(status) over(order by sdate) as groupId from x1)
   select groupId,min(sdate),max(edate) 
   from x2
   group by groupId
   order by groupId