ITPub博客

首页 > 数据库 > Oracle > [Oracle]高效的SQL语句之分析函数--lag()/lead()

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

原创 Oracle 作者:22331218 时间:2007-11-20 09:55:05 0 删除 编辑

先创建示例表:

-- 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;

结果如下:

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

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

lead () 下一个值 lag() 上一个值

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
[@more@]

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/8111375/viewspace-983038/,如需转载,请注明出处,否则将追究法律责任。

上一篇: 我的牙
请登录后发表评论 登录
全部评论
  • 博文量
    9
  • 访问量
    301049