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

本系列文章导航

[Oracle]高效的SQL语句之分析函数(一)--sum()

[Oracle]高效的SQL语句之分析函数(二)--max()

[Oracle]高效的SQL语句之分析函数(三)--row_number() /rank()/dense_rank()

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

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

先创建示例表：

-- Createtable
create table LEAD_TABLE
(
CASEID VARCHAR2 ( 10 ),
STEPID VARCHAR2 ( 10 ),
ACTIONDATEDATE
)
tablespaceCOLM_DATA
pctfree 10
initrans 1
maxtrans 255
storage
(
initial64K
minextents 1
maxextentsunlimited
);

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

结果如下：

Case1Step1 2007 - 1 - 1 Step2 2007 - 1 - 2
Case1Step2 2007 - 1 - 2 Step3 2007 - 1 - 3 Step1 2007 - 1 - 1
Case1Step3 2007 - 1 - 3 Step4 2007 - 1 - 4 Step2 2007 - 1 - 2
Case1Step4 2007 - 1 - 4 Step5 2007 - 1 - 5 Step3 2007 - 1 - 3
Case1Step5 2007 - 1 - 5 Step4 2007 - 1 - 6 Step4 2007 - 1 - 4
Case1Step4 2007 - 1 - 6 Step6 2007 - 1 - 7 Step5 2007 - 1 - 5
Case1Step6 2007 - 1 - 7 Step4 2007 - 1 - 6
Case2Step1 2007 - 2 - 1 Step2 2007 - 2 - 2
Case2Step2 2007 - 2 - 2 Step3 2007 - 2 - 3 Step1 2007 - 2 - 1
Case2Step3 2007 - 2 - 3 Step2 2007 - 2 - 2

还可以进一步统计一下两者的相差天数

select caseid,stepid,actiondate,nextactiondate,nextactiondate - actiondatedatebetween 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)

结果如下：

Case1Step1 2007 - 1 - 1 2007 - 1 - 2 1
Case1Step2 2007 - 1 - 2 2007 - 1 - 3 1
Case1Step3 2007 - 1 - 3 2007 - 1 - 4 1
Case1Step4 2007 - 1 - 4 2007 - 1 - 5 1
Case1Step5 2007 - 1 - 5 2007 - 1 - 6 1
Case1Step4 2007 - 1 - 6 2007 - 1 - 7 1
Case1Step6 2007 - 1 - 7
Case2Step1 2007 - 2 - 1 2007 - 2 - 2 1
Case2Step2 2007 - 2 - 2 2007 - 2 - 3 1
Case2Step3 2007 - 2 - 3

博文来源：http://blog.youkuaiyun.com/huanghui22/archive/2007/05/03/1595169.aspx