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

有时候报表上面需要显示该笔操作的上一步骤或者下一步骤的详细信息，这个时候可以按照下面的做法：

先创建示例表：

-- 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