Lag和Lead函数

lag(exp,N,defval) over(partition by ... order by ...)
lead(exp,N,defval) over(partition by ... order by ...) 

Lag和Lead函数可以在一次查询中取出同一字段的前N行的数据和后N行的值。这种操作可以使用对相同表的表连接来实现，不过使用LAG和LEAD有更高的效率。Lag函数为Lag(exp,N,defval)，defval是当该函数无值可用的情况下返回的值。Lead函数的用法类似。Lead和Lag函数也可以使用分组。

 

以下是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) nextdate,

lag(stepid) over (partition by caseid order by actiondate) prestepid,

lag(actiondate) over (partition by caseid order by actiondate) predate

from lead_table

 

结果如下：

 

caseid   stepid    actiondate   nextstepid  nextdate   prestepid     predate

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

        nextdate-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) nextdate,

lag(stepid) over (partition by caseid order by actiondate)  prestepid,

lag(actiondate) over (partition by caseid order by actiondate) predate

from lead_table)

 

结果如下：

 

caseid   stepid    actiondate   nextdate   datebetween

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/14613712/viewspace-617031/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/14613712/viewspace-617031/