[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