Oracle-分析函數之取上下行資料lag()和lead()

概述

ORACLE函數系列：

Oracle常見函數大全

Oracle-分析函數之連續求和sum(…) over(…)

Oracle-分析函數之排序值rank()和dense_rank()

Oracle-分析函數之排序後順序号row_number()

Oracle-分析函數之取上下行資料lag()和lead()

這兩個函數是偏移量函數，可以查出一個字段的上一個值或者下一個值，配合over來使用。

lead函數，這個函數是向上偏移.

lag函數是向下偏移一位.

文法

【文法】

lag(EXPR,<OFFSET>,<DEFAULT>)
           

LEAD(EXPR,<OFFSET>,<DEFAULT>)
           

【功能】表示根據COL1分組，在分組内部根據 COL2排序，而這個值就表示每組内部排序後的順序編号（組内連續的唯一的）

lead () 下一個值 lag() 上一個值

【參數】

• EXPR是從其他行傳回的表達式
• OFFSET是預設為1 的正數，表示相對行數。希望檢索的目前行分區的偏移量
• DEFAULT是在OFFSET表示的數目超出了分組的範圍時傳回的值。

exp_str 是要做對比的字段

offset 是exp_str字段的偏移量 比如說 offset 為2 則 拿exp_str的第一行和第三行對比，第二行和第四行，依次類推，offset的預設值為1！

【說明】Oracle分析函數

栗子

create table LEAD_TABLE
(
 CASEID VARCHAR2(),
 STEPID VARCHAR2(),
 ACTIONDATE DATE
)
           

insert into LEAD_TABLE values('Case1','Step1',to_date('20161101','yyyy-mm-dd'));
insert into LEAD_TABLE values('Case1','Step2',to_date('20161102','yyyy-mm-dd'));
insert into LEAD_TABLE values('Case1','Step3',to_date('20161103','yyyy-mm-dd'));
insert into LEAD_TABLE values('Case1','Step4',to_date('20161104','yyyy-mm-dd'));
insert into LEAD_TABLE values('Case1','Step5',to_date('20161105','yyyy-mm-dd'));
insert into LEAD_TABLE values('Case1','Step4',to_date('20161106','yyyy-mm-dd'));
insert into LEAD_TABLE values('Case1','Step6',to_date('20161107','yyyy-mm-dd'));
insert into LEAD_TABLE values('Case1','Step1',to_date('20161201','yyyy-mm-dd'));
insert into LEAD_TABLE values('Case2','Step2',to_date('20161202','yyyy-mm-dd'));
insert into LEAD_TABLE values('Case2','Step3',to_date('20161203','yyyy-mm-dd'));

commit;
           

資料規格：

輸出目前 和 之前 之後的date 和 step

select 
               a.caseid  ,
               a.stepid as currentStepID,
               a.actiondate  as currentActionDate,
               lead(stepid)  over(partition by a.caseid order by a.stepid)  nextStep,
               lead(actiondate)  over(partition by a.caseid order by a.stepid)  nextActionDate,
               lag(stepid) over(partition by a.caseid order by a.stepid)  preStep,
               lag(actiondate)  over(partition by a.caseid order by a.stepid)  preActionDate
          from lead_table  a ;
           

進一步統計一下兩者的相差天數

select caseid,
       stepid,
       actiondate,
       nextactiondate,
       nextactiondate - actiondate datebetween
  from (select caseid,
               stepid,
               actiondate,
               lead(stepid) over(partition by caseid order by actiondate) nextstepid,
               lead(actiondate) over(partition by caseid order by actiondate) nextactiondate,
               lag(stepid) over(partition by caseid order by actiondate) prestepid,
               lag(actiondate) over(partition by caseid order by actiondate) preactiondate
          from lead_table) ;