oracle分组偏移量,[Oracle]Lead和Lag偏移量函数使⽤详解Oracle有两个函数：LEAD和LAG，这两个函数都是⽤来计算偏移量的分析函数，这两个函数的⽤法相同。
Oracle官⽅⽂档解释是：
LEADis 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,LEADprovides 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基本⽤法
ame,
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函数
ame,
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的表达式结果为空，则返回的结果也为空，看下⾯这个例⼦。
update emp e
set e.hiredate=NULL
pno='7698';
commit;
pno,
e.deptno,
e.hiredate,
lead(e.hiredate,1,NULL) over(partition by e.deptno order pno) as next_hiredate
from emp e
where e.deptno=30;
由于7698-BLAKE的HIREDATE的值为空，所有MARTIN的NEXT_HIREDATE的值也为空，系统默认的是RESPECT NULLS，添加IGNORE NULLS后的结果如下。
pno,
e.deptno,
e.hiredate,
lead(e.hiredate ignore nulls) over(partition by e.deptno order pno) as next_hiredate
from emp e
where e.deptno=30;
新增的IGNORE NULLS功能，可以忽略NULL结果，去寻另⼀个满⾜条件的结果。
4、偏移量决定查询时向前或后便宜N个位置，函数的默认偏移量为1，我们也可以修改为⾃定义的偏移量。
--4、偏移量offset，默认值=1,⼿动设置为2
pno,
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表达式类型⼀致。
--5、lead函数的default参数
pno,
e.deptno,
oracle游标的使用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

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。