Oracle Range Processing

1. Table field self-correlation and analysis function performance comparison, self-correlation needs to scan table two times, analysis function scan once can

----Self-correlatingSelectV1.proj_id,v1.proj_start,v1.proj_end fromv v1,v v2whereV1.proj_start=V2.proj_end----Analysis functionSelectProj_id,proj_start,proj_end from(SelectProj_id,proj_start,proj_end, Lead (Proj_start) Over(Order  byproj_id) Nproj_start fromv)whereNproj_start=Proj_end

The above processing method is slightly modified, can also be used to calculate the user logon time interval, lead (XXX) over (partition user order by XXX), first by user partition

2. Successive time periods are combined to solve the combined time interval.

SelectGroupId,min(Proj_start),Max(proj_end) from (SelectProj_id,proj_start,proj_end,sum(status) Over(Order  byproj_id) groupId (SelectProj_id,proj_start,proj_end, Case  whenLag (proj_end) Over(Order  byPROJ_ID)=Proj_start Then 0 Else 1 EndStatus fromv))Group  byGroupIdOrder  by 1

3.Lag and lead is a continuous data processing function, and the judgment in the interval of data needs to be between to judge

 withX0 as  (Selectid,sdate,edateMax(edate) Over(Order  bySdate rowsbetweenunbounded preceding and 1preceding) asmedate fromb), X1 as   (SelectSdate,edate,medate, Case  whenMedate>Sdate Then 0 Else 1 End  asStatus fromx0), X2 as   (SelectSdate,edate,sum(status) Over(Order  bySdate) asGroupId fromx1)SelectGroupId,min(Sdate),Max(edate) fromX2Group  bygroupIdOrder  byGroupId

Oracle Range Processing