Oracle有两个函数:LEAD和LAG,这两个函数都是用来计算偏移量的分析函数,这两个函数的用法相同。
Oracle官方文档解释是:
LEAD
is an analytic function. It provides access to more than one row of a table at the same time without a self join. Given a series of rows returned from a query and a position of the cursor, LEAD
provides access to a row at a given physical offset beyond that position.
LEAD函数是一个分析函数,它可以在不用自连接的情况下,同时访问一个表的多行数据。查询返回的一系列的数据行和游标,LEAD函数在当前位置上根据偏移量访问该表中的另一行数据。
我们结合查询的示例,来看看这个函数的具体用法
1、lead函数首先根据 DEPTNO 部门分组并按照 HIREDATE 进行排序,NEXT_HIREDATE表示HIREDATE的下一个偏移量(偏移量默认为1)。
比如ALLEN的HIREDATE是"1981-2-20",那么ALLEN的NEXT_HIREDATE的值就是"1981-2-22"(即WARD的HIREDATE值);
同样的,WARD的HIREDATE值是"1981-2-22",那么WARD的NEXT_HIREDATE值是"1981-5-1"(即BLAKE的HIREDATE值);
最后JAMES找不到下一个偏移量,所以NEXT_HIREDATE的默认值就是NULL。
--1、lead基本用法
select e.ename,
e.deptno,
e.hiredate,
lead(e.hiredate,1,null) over(partition by e.deptno order by e.hiredate) as next_hiredate
from emp e
where e.deptno=30;
2、上面的查询也可以不加partition by
--2、不包含partition by的lead函数SELECT e.ename, e.deptno, e.hiredate, lead(e.hiredate) over(order by e.hiredate) as next_hiredateFROM emp e;
3、Oracle 11g增强LEAD和LAG函数的语法,加入了{RESPECT
| IGNORE
} NULLS,如果LEAD的表达式结果为空,则返回的结果也为空,看下面这个例子。
update emp eset e.hiredate=NULLwhere e.empno='7698';commit;select e.empno, e.ename, e.deptno, e.hiredate, lead(e.hiredate,1,NULL) over(partition by e.deptno order by e.empno) as next_hiredatefrom emp ewhere e.deptno=30;
由于7698-BLAKE的HIREDATE的值为空,所有MARTIN的NEXT_HIREDATE的值也为空,系统默认的是RESPECT NULLS,添加IGNORE NULLS后的结果如下。
select e.empno, e.ename, e.deptno, e.hiredate, lead(e.hiredate ignore nulls) over(partition by e.deptno order by e.empno) as next_hiredatefrom emp ewhere e.deptno=30;
新增的IGNORE NULLS功能,可以忽略NULL结果,去寻找另一个满足条件的结果。
4、偏移量决定查询时向前或后便宜N个位置,函数的默认偏移量为1,我们也可以修改为自定义的偏移量。
--4、偏移量offset,默认值=1,手动设置为2select e.empno, e.ename, e.deptno, e.hiredate, lead(e.hiredate,2,null) over(partition by e.deptno order by e.hiredate) as next_hiredatefrom emp ewhere deptno=30;
5、LEAD的偏移量如果超出了表的范围,则DEFAULT默认返回NULL,我们可以手动设置一个固定的返回值,该值的数据类型必须与LEAD表达式类型一致。
--5、lead函数的default参数select e.empno, e.ename, e.deptno, e.hiredate, lead(e.hiredate,1,to_date('2013-01-01','yyyy-MM-dd')) over(partition by e.deptno order by e.hiredate) as next_hiredatefrom emp ewhere deptno=30;
LAG分析函数的用法基本与lEAD函数类型,只不过作用相反,大家可以在自己的机子上亲自实践一下,更多详细的信息请参考:http://docs.oracle.com/cd/E11882_01/server.112/e26088/functions086.htm#SQLRF00656