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

最新推荐文章于 2025-01-02 00:00:00 发布

huanghui22

最新推荐文章于 2025-01-02 00:00:00 发布

阅读量1.2w

点赞数 1

CC 4.0 BY-SA版权

分类专栏： ORACLE技术文章标签： oracle sql insert table date 报表

版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。

本文链接：https://blog.csdn.net/huanghui22/article/details/1595169

ORACLE技术专栏收录该内容

52 篇文章

订阅专栏

本文介绍如何使用SQL中的LAG和LEAD窗口函数来获取数据库表中每一项操作的前后步骤及日期，并进一步计算出各步骤之间的时间间隔。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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

先创建示例表：

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

评论 4

被折叠的条评论为什么被折叠?

到【灌水乐园】发言

查看更多评论

添加红包

成就一亿技术人!

hope_wisdom

发出的红包

实付元

使用余额支付

点击重新获取

扫码支付

钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。