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

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

先创建示例表：

--Createtable
createtableLEAD_TABLE
(
CASEIDVARCHAR2(10),
STEPIDVARCHAR2(10),
ACTIONDATEDATE
)
tablespaceCOLM_DATA
pctfree10
initrans1
maxtrans255
storage
(
initial64K
minextents1
maxextentsunlimited
);

insertintoLEAD_TABLEvalues('Case1','Step1',to_date('20070101','yyyy-mm-dd'));
insertintoLEAD_TABLEvalues('Case1','Step2',to_date('20070102','yyyy-mm-dd'));
insertintoLEAD_TABLEvalues('Case1','Step3',to_date('20070103','yyyy-mm-dd'));
insertintoLEAD_TABLEvalues('Case1','Step4',to_date('20070104','yyyy-mm-dd'));
insertintoLEAD_TABLEvalues('Case1','Step5',to_date('20070105','yyyy-mm-dd'));
insertintoLEAD_TABLEvalues('Case1','Step4',to_date('20070106','yyyy-mm-dd'));
insertintoLEAD_TABLEvalues('Case1','Step6',to_date('20070101','yyyy-mm-dd'));
insertintoLEAD_TABLEvalues('Case1','Step1',to_date('20070201','yyyy-mm-dd'));
insertintoLEAD_TABLEvalues('Case2','Step2',to_date('20070202','yyyy-mm-dd'));
insertintoLEAD_TABLEvalues('Case2','Step3',to_date('20070203','yyyy-mm-dd'));
commit;

每一条记录都能连接到上/下一行的内容

selectcaseid,stepid,actiondate,lead(stepid)over(partitionbycaseidorderbyactiondate)nextstepid,
lead(actiondate)over(partitionbycaseidorderbyactiondate)nextactiondate,
lag(stepid)over(partitionbycaseidorderbyactiondate)prestepid,
lag(actiondate)over(partitionbycaseidorderbyactiondate)preactiondate
fromlead_table

结果如下：

Case1Step12007-1-1Step22007-1-2
Case1Step22007-1-2Step32007-1-3Step12007-1-1
Case1Step32007-1-3Step42007-1-4Step22007-1-2
Case1Step42007-1-4Step52007-1-5Step32007-1-3
Case1Step52007-1-5Step42007-1-6Step42007-1-4
Case1Step42007-1-6Step62007-1-7Step52007-1-5
Case1Step62007-1-7 Step42007-1-6
Case2Step12007-2-1Step22007-2-2
Case2Step22007-2-2Step32007-2-3Step12007-2-1
Case2Step32007-2-3Step22007-2-2

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

selectcaseid,stepid,actiondate,nextactiondate,nextactiondate-actiondatedatebetweenfrom(
selectcaseid,stepid,actiondate,lead(stepid)over(partitionbycaseidorderbyactiondate)nextstepid,
lead(actiondate)over(partitionbycaseidorderbyactiondate)nextactiondate,
lag(stepid)over(partitionbycaseidorderbyactiondate)prestepid,
lag(actiondate)over(partitionbycaseidorderbyactiondate)preactiondate
fromlead_table)

结果如下：

Case1Step12007-1-12007-1-21
Case1Step22007-1-22007-1-31
Case1Step32007-1-32007-1-41
Case1Step42007-1-42007-1-51
Case1Step52007-1-52007-1-61
Case1Step42007-1-62007-1-71
Case1Step62007-1-7
Case2Step12007-2-12007-2-21
Case2Step22007-2-22007-2-31
Case2Step32007-2-3