Oracle範圍處理

標籤：

1.表中欄位自關聯與分析函數的效能比較，自關聯需要掃描表兩次，分析函數掃描一次即可

----自關聯select v1.proj_id,v1.proj_start,v1.proj_endfrom v v1,v v2where v1.proj_start=v2.proj_end----分析函數select proj_id,proj_start,proj_endfrom (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

Oracle範圍處理