关于Oracle 偏移量函数lead和lag

在工作时，遇到有同事问我关于Oracle 偏移量函数lead和lag的问题，不是很了解，在网上搜索相关资料时，发现这篇博客，写得不错，以供自己学习并和大家分享：

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。

[sql] view plain copy print ?

1. --1、lead基本用法   
2. select e.ename,  
3.        e.deptno,  
4.        e.hiredate,  
5.        lead(e.hiredate,1,null) over(partition by e.deptno order by e.hiredate) as next_hiredate  
6. from emp e  
7. where e.deptno=30;  

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

[sql] view plain copy print ?

1.   

2、上面的查询也可以不加partition by 

[sql] view plaincopyprint?

1. --2、不包含partition by的lead函数   
2. SELECT e.ename,  
3.        e.deptno,  
4.        e.hiredate,  
5.        lead(e.hiredate) over(order by e.hiredate) as next_hiredate  
6. FROM emp e;  

--2、不包含partition by的lead函数
SELECT e.ename,
       e.deptno,
       e.hiredate,
       lead(e.hiredate) over(order by e.hiredate) as next_hiredate
FROM emp e;

3、Oracle 11g增强LEAD和LAG函数的语法，加入了{RESPECT | IGNORE} NULLS，如果LEAD的表达式结果为空，则返回的结果也为空，看下面这个例子。

[sql] view plain copy print ?

1. update emp e  
2. set e.hiredate=NULL  
3. where e.empno='7698';  
4. commit;  
5.   
6.   
7. select e.empno,  
8.        e.ename,  
9.        e.deptno,  
10.        e.hiredate,  
11.        lead(e.hiredate,1,NULL) over(partition by e.deptno order by e.empno) as next_hiredate  
12. from emp e  
13. where e.deptno=30;  

update emp e
set e.hiredate=NULL
where 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_hiredate
from emp e
where e.deptno=30;

由于7698-BLAKE的HIREDATE的值为空，所有MARTIN的NEXT_HIREDATE的值也为空，系统默认的是RESPECT NULLS，添加IGNORE NULLS后的结果如下。

[sql] view plain copy print ?

1. select e.empno,  
2.        e.ename,  
3.        e.deptno,  
4.        e.hiredate,  
5.        lead(e.hiredate ignore nulls) over(partition by e.deptno order by e.empno) as next_hiredate  
6. from emp e  
7. where e.deptno=30;  

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_hiredate
from emp e
where e.deptno=30;

新增的IGNORE NULLS功能，可以忽略NULL结果，去寻找另一个满足条件的结果。





4、偏移量决定查询时向前或后便宜N个位置，函数的默认偏移量为1,我们也可以修改为自定义的偏移量。

[sql] view plain copy print ?

1. --4、偏移量offset,默认值=1,手动设置为2   
2. select e.empno,  
3.        e.ename,  
4.        e.deptno,  
5.        e.hiredate,  
6.        lead(e.hiredate,2,null) over(partition by e.deptno order by e.hiredate) as next_hiredate  
7. from emp e  
8. where deptno=30;  

--4、偏移量offset,默认值=1,手动设置为2
select e.empno,
       e.ename,
       e.deptno,
       e.hiredate,
       lead(e.hiredate,2,null) over(partition by e.deptno order by e.hiredate) as next_hiredate
from emp e
where deptno=30;




5、LEAD的偏移量如果超出了表的范围，则DEFAULT默认返回NULL，我们可以手动设置一个固定的返回值，该值的数据类型必须与LEAD表达式类型一致。

[sql] view plain copy print ?

1. --5、lead函数的default参数   
2. select e.empno,  
3.        e.ename,  
4.        e.deptno,  
5.        e.hiredate,  
6.        lead(e.hiredate,1,to_date('2013-01-01','yyyy-MM-dd')) over(partition by e.deptno order by e.hiredate) as next_hiredate  
7. from emp e  
8. where deptno=30;  

--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_hiredate
from emp e
where deptno=30;




LAG分析函数的用法基本与lEAD函数类型，只不过作用相反，大家可以在自己的机子上亲自实践一下，更多详细的信息请参考：http://docs.oracle.com/cd/E11882_01/server.112/e26088/functions086.htm#SQLRF00656