lag()和lead()</p>
<p>【语法】</p>
<p>lag(EXPR,<OFFSET>,<DEFAULT>)</p>
<p>LEAD(EXPR,<OFFSET>,<DEFAULT>)</p>
<p>【功能】表示根据COL1分组，在分组内部根据 COL2排序，而这个值就表示每组内部排序后的顺序编号（组内连续的唯一的） </p>
<p>lead （） 下一个值 lag（） 上一个值</p>
<p>【参数】</p>
<p>EXPR是从其他行返回的表达式 </p>
<p>OFFSET是缺省为1 的正数，表示相对行数。希望检索的当前行分区的偏移量</p>
<p>DEFAULT是在OFFSET表示的数目超出了分组的范围时返回的值。</p>
<p>【说明】Oracle分析函数</p>
<p>【示例】</p>
<p>-- Create table</p>
<p>create table LEAD_TABLE</p>
<p>(</p>
<p>CASEID VARCHAR2(10),</p>
<p>STEPID VARCHAR2(10),</p>
<p>ACTIONDATE DATE</p>
<p>)</p>
<p>tablespace COLM_DATA</p>
<p>pctfree 10</p>
<p>initrans 1</p>
<p>maxtrans 255</p>
<p>storage</p>
<p>(</p>
<p>initial 64K</p>
<p>minextents 1</p>
<p>maxextents unlimited</p>
<p>);</p>
<p>insert into LEAD_TABLE values('Case1','Step1',to_date('20070101','yyyy-mm-dd'));</p>
<p>insert into LEAD_TABLE values('Case1','Step2',to_date('20070102','yyyy-mm-dd'));</p>
<p>insert into LEAD_TABLE values('Case1','Step3',to_date('20070103','yyyy-mm-dd'));</p>
<p>insert into LEAD_TABLE values('Case1','Step4',to_date('20070104','yyyy-mm-dd'));</p>
<p>insert into LEAD_TABLE values('Case1','Step5',to_date('20070105','yyyy-mm-dd'));</p>
<p>insert into LEAD_TABLE values('Case1','Step4',to_date('20070106','yyyy-mm-dd'));</p>
<p>insert into LEAD_TABLE values('Case1','Step6',to_date('20070101','yyyy-mm-dd'));</p>
<p>insert into LEAD_TABLE values('Case1','Step1',to_date('20070201','yyyy-mm-dd'));</p>
<p>insert into LEAD_TABLE values('Case2','Step2',to_date('20070202','yyyy-mm-dd'));</p>
<p>insert into LEAD_TABLE values('Case2','Step3',to_date('20070203','yyyy-mm-dd'));</p>
<p>commit;</p>
<p>结果如下：</p>
<p>Case1 Step1 2007-1-1 Step2 2007-1-2 </p>
<p>Case1 Step2 2007-1-2 Step3 2007-1-3 Step1 2007-1-1</p>
<p>Case1 Step3 2007-1-3 Step4 2007-1-4 Step2 2007-1-2</p>
<p>Case1 Step4 2007-1-4 Step5 2007-1-5 Step3 2007-1-3</p>
<p>Case1 Step5 2007-1-5 Step4 2007-1-6 Step4 2007-1-4</p>
<p>Case1 Step4 2007-1-6 Step6 2007-1-7 Step5 2007-1-5</p>
<p>Case1 Step6 2007-1-7 Step4 2007-1-6</p>
<p>Case2 Step1 2007-2-1 Step2 2007-2-2 </p>
<p>Case2 Step2 2007-2-2 Step3 2007-2-3 Step1 2007-2-1</p>
<p>Case2 Step3 2007-2-3 Step2 2007-2-2</p>
<p>还可以进一步统计一下两者的相差天数</p>
<p>select caseid,stepid,actiondate,nextactiondate,nextactiondate-actiondate datebetween from (</p>
<p>select caseid,stepid,actiondate,lead(stepid) over (partition by caseid order by actiondate) nextstepid,</p>
<p>lead(actiondate) over (partition by caseid order by actiondate) nextactiondate,</p>
<p>lag(stepid) over (partition by caseid order by actiondate) prestepid,</p>
<p>lag(actiondate) over (partition by caseid order by actiondate) preactiondate</p>
<p>from lead_table) </p>
<p>结果如下：</p>
<p>Case1 Step1 2007-1-1 2007-1-2 1</p>
<p>Case1 Step2 2007-1-2 2007-1-3 1</p>
<p>Case1 Step3 2007-1-3 2007-1-4 1</p>
<p>Case1 Step4 2007-1-4 2007-1-5 1</p>
<p>Case1 Step5 2007-1-5 2007-1-6 1</p>
<p>Case1 Step4 2007-1-6 2007-1-7 1</p>
<p>Case1 Step6 2007-1-7 </p>
<p>Case2 Step1 2007-2-1 2007-2-2 1</p>
<p>Case2 Step2 2007-2-2 2007-2-3 1</p>
<p>Case2 Step3 2007-2-3 </p>
<p>每一条记录都能连接到上/下一行的内容</p>
<p>lead （） 下一个值 lag（） 上一个值</p>
<p>select caseid,stepid,actiondate,lead(stepid) over (partition by caseid order by actiondate) nextstepid,</p>
<p>lead(actiondate) over (partition by caseid order by actiondate) nextactiondate,</p>
<p>lag(stepid) over (partition by caseid order by actiondate) prestepid,</p>
<p>lag(actiondate) over (partition by caseid order by actiondate) preactiondate</p>
<p>from lead_table