ITPub博客

首页 > Linux操作系统 > Linux操作系统 > oracle中lead和lag函数

oracle中lead和lag函数

原创 Linux操作系统 作者:shuangoracle 时间:2012-05-09 19:39:26 0 删除 编辑
这两个函数,是偏移量函数,其用途是:可以查出同一字段下一个值或上一个值。
lead(col_name,num,flag)
col_name是列名;num是取向下第几个值;flag是一个标志,也就是如果向下第几个值是空值的话就取flag;
例如lead(login_time,1,null)这个是向下取一个值,如果这个值为空则按空算,当然也可以用其他值替换。
lag(col_name,num,flag)
和lead类似,col_name是列名;num是取向上第几个值;flag是一个标志,也就是如果向上第几个值是空值的话就取flag;
例如lag(login_time,1,null)这个是向上取一个值,如果这个值为空则按空算,当然也可以用其他值替换。
举个例子:有一个表tmp_test(u_id,login_time)查一下这个表中连续7天都有登录机器的人是谁?
造下数据:
create table tmp_test(u_id number,login_time date);
insert into tmp_test
select 1 rn,sysdate + rownum as login_time
from dual
connect by level <=8
union
select 2 rn,sysdate + rownum as login_time
from dual
connect by level <=3
union
select 3 rn,sysdate + rownum as login_time
from dual
connect by level <=2
union
select 2 rn,sysdate + rownum+4 as login_time
from dual
connect by level <=5;
commit;
然后造几条重复数据:
insert into tmp_test
select 1 rn,sysdate + rownum as login_time
from dual
connect by level <=3;
查下数据:
select * from tmp_test;

 U_ID LOGIN_TIME
---------- -----------
 1 2012/3/8 6:33:24
 1 2012/3/9 6:33:24
 1 2012/3/10 6:33:24
 1 2012/3/11 6:33:24
 1 2012/3/12 6:33:24
 1 2012/3/13 6:33:24
 1 2012/3/14 6:33:24
 1 2012/3/15 6:33:24
 2 2012/3/8 6:33:24
 2 2012/3/9 6:33:24
 2 2012/3/10 6:33:24
 2 2012/3/12 6:33:24
 2 2012/3/13 6:33:24
 2 2012/3/14 6:33:24
 2 2012/3/15 6:33:24
 2 2012/3/16 6:33:24
 3 2012/3/8 6:33:24
 3 2012/3/9 6:33:24
 1 2012/3/8 6:37:35
 1 2012/3/9 6:37:35
 1 2012/3/10 6:37:35
从上面数据看出其实只有u_id=1满足条件,那么怎么用sql实现呢?
SQL> select distinct u_id
 2 from (select u_id,
 3 login_time last_login_time,
 4 lead(login_time, 6) over(partition by u_id order by u_id, login_time) next_login_time
 5 from (select distinct u_id, trunc(login_time) login_time
 6 from tmp_test))
 7 where next_login_time - last_login_time = 6;

 U_ID
----------
 1
ok,就是这个结果。其实用lag也可以实现相同结果,写法如下:
select distinct u_id
 from (select u_id,
 login_time last_login_time,
 lag(login_time, 6) over(partition by u_id order by u_id, login_time) next_login_time
 from (select distinct u_id, trunc(login_time) login_time
 from tmp_test))
 where last_login_time - next_login_time = 6;

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

下一篇: 一个递归查询
请登录后发表评论 登录
全部评论

注册时间:2010-08-25

  • 博文量
    84
  • 访问量
    206926