[Oracle]高效的SQL語句之分析函式(四)--lag()/lead()

fengzj發表於2008-12-27
OracleSQL函式

有時候報表上面需要顯示該筆操作的上一步驟或者下一步驟的詳細資訊，這個時候可以按照下面的做法：

先建立示例表：

-- Create table
create table LEAD_TABLE
(
  CASEID     VARCHAR2(10),
  STEPID     VARCHAR2(10),
  ACTIONDATE DATE
)
tablespace COLM_DATA
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );

insert into LEAD_TABLE values('Case1','Step1',to_date('20070101','yyyy-mm-dd'));
insert into LEAD_TABLE values('Case1','Step2',to_date('20070102','yyyy-mm-dd'));
insert into LEAD_TABLE values('Case1','Step3',to_date('20070103','yyyy-mm-dd'));
insert into LEAD_TABLE values('Case1','Step4',to_date('20070104','yyyy-mm-dd'));
insert into LEAD_TABLE values('Case1','Step5',to_date('20070105','yyyy-mm-dd'));
insert into LEAD_TABLE values('Case1','Step4',to_date('20070106','yyyy-mm-dd'));
insert into LEAD_TABLE values('Case1','Step6',to_date('20070101','yyyy-mm-dd'));
insert into LEAD_TABLE values('Case1','Step1',to_date('20070201','yyyy-mm-dd'));
insert into LEAD_TABLE values('Case2','Step2',to_date('20070202','yyyy-mm-dd'));
insert into LEAD_TABLE values('Case2','Step3',to_date('20070203','yyyy-mm-dd'));
commit;

 

每一條記錄都能連線到上/下一行的內容

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

結果如下：

Case1    Step1    2007-1-1    Step2    2007-1-2        
Case1    Step2    2007-1-2    Step3    2007-1-3    Step1    2007-1-1
Case1    Step3    2007-1-3    Step4    2007-1-4    Step2    2007-1-2
Case1    Step4    2007-1-4    Step5    2007-1-5    Step3    2007-1-3
Case1    Step5    2007-1-5    Step4    2007-1-6    Step4    2007-1-4
Case1    Step4    2007-1-6    Step6    2007-1-7    Step5    2007-1-5
Case1    Step6    2007-1-7                                       Step4    2007-1-6
Case2    Step1    2007-2-1    Step2    2007-2-2        
Case2    Step2    2007-2-2    Step3    2007-2-3    Step1    2007-2-1
Case2    Step3    2007-2-3                                       Step2    2007-2-2

 

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

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) 

結果如下：

Case1    Step1    2007-1-1    2007-1-2    1
Case1    Step2    2007-1-2    2007-1-3    1
Case1    Step3    2007-1-3    2007-1-4    1
Case1    Step4    2007-1-4    2007-1-5    1
Case1    Step5    2007-1-5    2007-1-6    1
Case1    Step4    2007-1-6    2007-1-7    1
Case1    Step6    2007-1-7        
Case2    Step1    2007-2-1    2007-2-2    1
Case2    Step2    2007-2-2    2007-2-3    1
Case2    Step3    2007-2-3        

 

 

 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/219982/viewspace-522829/，如需轉載，請註明出處，否則將追究法律責任。

相關文章