取上下行数据分析函数。lag()和lead() ——分析函数4

原创 2012年03月28日 17:20:47

1、测试表

create table LEAD_TABLE(
 CASEID VARCHAR2(10),
 STEPID VARCHAR2(10),
 ACTIONDATE DATE
);

2、测试数据
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;
SQL> select * from lead_table;

CASEID     STEPID     ACTIONDATE
---------- ---------- --------------
Case1      Step1      01-1月 -07
Case1      Step2      02-1月 -07
Case1      Step3      03-1月 -07
Case1      Step4      04-1月 -07
Case1      Step5      05-1月 -07
Case1      Step4      06-1月 -07
Case1      Step6      01-1月 -07
Case1      Step1      01-2月 -07
Case2      Step2      02-2月 -07
Case2      Step3      03-2月 -07

已选择10行。

每一条记录都能连接到上/下一行的内容。lead () 下一个值, lag() 上一个值。

SQL> select caseid,
  2         stepid,
  3         actiondate,
       lag(stepid) over(partition by caseid order by actiondate) prestepid, --上一个stepid
       lead(stepid) over(partition by caseid order by actiondate) nextstepid, --下一个stepid
       lag(actiondate) over(partition by caseid order by actiondate) preactiondate, --上一个日期
       lead(actiondate) over(partition by caseid order by actiondate) nextactiondate --下一个日期
  8    from lead_table;

CASEID     STEPID     ACTIONDATE     PRESTEPID  NEXTSTEPID PREACTIONDATE  NEXTACTIONDATE
---------- ---------- -------------- ---------- ---------- -------------- --------------
Case1      Step1      01-1月 -07                Step6                     01-1月 -07
Case1      Step6      01-1月 -07     Step1      Step2      01-1月 -07     02-1月 -07
Case1      Step2      02-1月 -07     Step6      Step3      01-1月 -07     03-1月 -07
Case1      Step3      03-1月 -07     Step2      Step4      02-1月 -07     04-1月 -07
Case1      Step4      04-1月 -07     Step3      Step5      03-1月 -07     05-1月 -07
Case1      Step5      05-1月 -07     Step4      Step4      04-1月 -07     06-1月 -07
Case1      Step4      06-1月 -07     Step5      Step1      05-1月 -07     01-2月 -07
Case1      Step1      01-2月 -07     Step4                 06-1月 -07
Case2      Step2      02-2月 -07                Step3                     03-2月 -07
Case2      Step3      03-2月 -07     Step2                 02-2月 -07

已选择10行。

还可以进一步统计一下两者的相差天数
select caseid, stepid, actiondate, nextactiondate - actiondate datebetween --日期相差天数
  from (select caseid,
               actiondate,
               stepid,
               actiondate,
               lag(stepid) over(partition by caseid order by actiondate) prestepid, --上一个stepid
               lead(stepid) over(partition by caseid order by actiondate) nextstepid, --下一个stepid
  7                 lag(actiondate) over(partition by caseid order by actiondate) preactiondate, --上一个日期
  8                 lead(actiondate) over(partition by caseid order by actiondate) nextactiondate --下一个日期
  9            from lead_table);

CASEID     STEPID     ACTIONDATE     DATEBETWEEN
---------- ---------- -------------- -----------
Case1      Step1      01-1月 -07               0
Case1      Step6      01-1月 -07               1
Case1      Step2      02-1月 -07               1
Case1      Step3      03-1月 -07               1
Case1      Step4      04-1月 -07               1
Case1      Step5      05-1月 -07               1
Case1      Step4      06-1月 -07              26
Case1      Step1      01-2月 -07
Case2      Step2      02-2月 -07               1
Case2      Step3      03-2月 -07

已选择10行。

缺省为1 的正数,表示相对行数。希望检索的当前行分区的偏移量。

select caseid, stepid, actiondate, nextactiondate - actiondate datebetween --日期相差天数
  from (select caseid,
  3                 stepid,
  4                 actiondate,
               lead(actiondate,1) over(partition by caseid order by actiondate) nextactiondate --下一个日期
  6            from lead_table);

CASEID     STEPID     ACTIONDATE     DATEBETWEEN
---------- ---------- -------------- -----------
Case1      Step1      01-1月 -07               0
Case1      Step6      01-1月 -07               1
Case1      Step2      02-1月 -07               1
Case1      Step3      03-1月 -07               1
Case1      Step4      04-1月 -07               1
Case1      Step5      05-1月 -07               1
Case1      Step4      06-1月 -07              26
Case1      Step1      01-2月 -07
Case2      Step2      02-2月 -07               1
Case2      Step3      03-2月 -07

已选择10行。



取上下行数据分析函数lag()和lead()

【语法】 lag(EXPR,,) LEAD(EXPR,,) 【功能】表示根据COL1分组,在分组内部根据 COL2排序,而这个值就表示每组内部排序后的顺序编号(组内连续的唯一的) lead ...
  • vic868
  • vic868
  • 2015年03月30日 10:05
  • 234

Oracle-分析函数之取上下行数据lag()和lead()

概述ORACLE函数系列:Oracle常见函数大全Oracle-分析函数之连续求和sum(…) over(…)Oracle-分析函数之排序值rank()和dense_rank()Oracle-分析函数...

Oracle SQL分析函数lag、lead解决相邻记录比较问题

Oracle SQL分析函数lag、lead解决相邻记录比较问题
  • Seandba
  • Seandba
  • 2017年05月21日 22:30
  • 733

Oracle分析函数总结(2) - 排序 - rank,dense_rank,row_number,first,first_value,last,last_value,lag,lead

分析函数的基本概念和语法 -> http://blog.csdn.net/fw0124/article/details/7842039 1) rank(),dense_rank(),row_...
  • fw0124
  • fw0124
  • 2012年08月08日 16:44
  • 5226

oracle 统计分析函数之lead和lag

无需添加内联结构便可以是实现访问当前行附近行的字段的值lead :向后 lag: 向前.  语法结构:       lag(value_expr [,offset][,default]) over...

oracle lag与lead分析函数简介

lag与lead函数是跟偏移量相关的两个分析函数,通过这两个函数我们可以取到当前行列的偏移N行列的值 lag可以看着是正的向上的偏移 lead可以认为负的向下的偏移 具体我们来看几个例子: 我们...

oracle 分析函数 LAG、LEAD

用SCOTT/TIGER登录。 LAG()和LEAD()统计函数可以在一次查询中取出同一字段的前N行的数据和后N行的值。这种操作可以使用对相同表的表连接来实现,不过使用LAG和 LEAD有更高的效...

oracle lag与lead分析函数简介

lag与lead函数是跟偏移量相关的两个分析函数,通过这两个函数我们可以取到当前行列的偏移N行列的值 lag可以看着是正的向上的偏移 lead可以认为负的向下的偏移 具体我们来看几个例子: 我们先看...
  • wdyr321
  • wdyr321
  • 2013年12月27日 12:47
  • 445

Hive 分析函数lead、lag实例应用

Hive的分析函数又叫窗口函数,在oracle中就有这样的分析函数,主要用来做数据统计分析的。 Lag和Lead分析函数可以在同一次查询中取出同一字段的前N行的数据(Lag)和后N行的数据(Lead)...

oracle下lag和lead分析函数

Lag和Lead分析函数可以在同一次查询中取出同一字段的前N行的数据(Lag)和后N行的数据(Lead)作为独立的列。 这种操作可以代替表的自联接,并且LAG和LEAD有更高的效率。 语法:/...
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:取上下行数据分析函数。lag()和lead() ——分析函数4
举报原因:
原因补充:

(最多只允许输入30个字)