[Oracle]Lead和Lag偏移量函数使用详解

旧城等待， 2022-05-28 11:42 317阅读 0赞

数据库学习网站 [https://www.itkc8.com][https_www.itkc8.com]

Oracle有两个函数：LEAD和LAG，这两个函数都是用来计算偏移量的分析函数，这两个函数的用法相同。

![1352253552_5287.jpg][]

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][view plain]

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;

**\[sql\]** [view plain][] [copy][view plain]

1.

![1352253836_2455.jpg][]

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

**\[sql\]** [view plain][] [copy][view plain]

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;

![1352254410_6225.jpg][]

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

**\[sql\]** [view plain][] [copy][view plain]

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;

![1352266759_4598.jpg][]

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

**\[sql\]** [view plain][] [copy][view plain]

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;

![1352266948_5686.jpg][]

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

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

**\[sql\]** [view plain][] [copy][view plain]

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;

![1352267196_6946.jpg][]

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

**\[sql\]** [view plain][] [copy][view plain]

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;

![1352267470_1763.jpg][]

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

数据库学习网站 [https://www.itkc8.com][https_www.itkc8.com]

[https_www.itkc8.com]: https://www.itkc8.com/
[1352253552_5287.jpg]: https://img-my.csdn.net/uploads/201211/07/1352253552_5287.jpg
[view plain]: https://blog.csdn.net/zanbiyajj/article/details/8156200#
[1352253836_2455.jpg]: https://img-my.csdn.net/uploads/201211/07/1352253836_2455.jpg
[1352254410_6225.jpg]: https://img-my.csdn.net/uploads/201211/07/1352254410_6225.jpg
[1352266759_4598.jpg]: https://img-my.csdn.net/uploads/201211/07/1352266759_4598.jpg
[1352266948_5686.jpg]: https://img-my.csdn.net/uploads/201211/07/1352266948_5686.jpg
[1352267196_6946.jpg]: https://img-my.csdn.net/uploads/201211/07/1352267196_6946.jpg
[1352267470_1763.jpg]: https://img-my.csdn.net/uploads/201211/07/1352267470_1763.jpg
[http_docs.oracle.com_cd_E11882_01_server.112_e26088_functions086.htm_SQLRF00656]: http://docs.oracle.com/cd/E11882_01/server.112/e26088/functions086.htm#SQLRF00656

发表评论取消回复

表情：

评论列表（有 0 条评论，317人围观）

还没有评论，来说两句吧...

相关阅读

相关 flink lag和lead函数bug

1、[https://issues.apache.org/jira/browse/FLINK-19449][https_issues.apache.org_jira_brows

怼烎@/ 2023年01月16日 02:48/ 0 赞/ 231 阅读

相关 Oracle函数lag和lead详解

1.函数功能 Lag和Lead函数可以在一次查询中取出同一字段的前N行的数据和后N行的值。这种操作可以使用对相同表的表连接来实现，不过使用LAG和LEAD有更高的效率。 2

迈不过友情╰/ 2022年10月27日 12:10/ 0 赞/ 1301 阅读

相关结构体偏移量

\include <stdio.h> \include <stdlib.h> \pragma pack(2) typedef struct \{ int a

秒速五厘米/ 2022年07月12日 10:13/ 0 赞/ 207 阅读

相关 [Oracle]Lead和Lag偏移量函数使用详解

数据库学习网站 [https://www.itkc8.com][https_www.itkc8.com] Oracle有两个函数：LEAD和LAG，这两个函数都是用来计算偏移

旧城等待，/ 2022年05月28日 11:42/ 0 赞/ 318 阅读

相关 Kafka - 偏移量提交

Kafka - 偏移量提交 -------------------- 一、偏移量提交消费者提交偏移量的主要是消费者往一个名为`_consumer_offset`

Myth丶恋晨/ 2022年05月26日 02:23/ 0 赞/ 223 阅读

相关 kafka 有序性和偏移量offset

Kafka分布式的单位是partition，同一个partition用一个write ahead log组织，所以可以保证FIFO的顺序。不同partition之间不能

「爱情、让人受尽委屈。」/ 2022年05月21日 03:34/ 0 赞/ 218 阅读

相关数组元素偏移量

在网上看了一些博客和资料总结一下，如有错误，请不要点赞。数组偏移量的定义：当前数组元素的地址与数组首地址的差值。举例：一维数组：一维数组：a\[n\]

小鱼儿/ 2022年05月15日 18:06/ 0 赞/ 324 阅读

相关 Oracle函数之LAG函数

语法 ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmN

电玩女神/ 2022年03月14日 14:40/ 0 赞/ 347 阅读

相关 ORACLE 偏移分析函数 lag()与lead() 用法

一、简介　　lag与lead函数是跟偏移量相关的两个分析函数，通过这两个函数可以在一次查询中取出同一字段的前N行的数据(lag)和后N行的数据(lead)作为独立的列,从而

快来打我*/ 2022年01月12日 13:53/ 0 赞/ 268 阅读

相关 ORALCE函数：LAG()和LEAD() 分析函数详解

`Lag`和`Lead`分析函数可以在同一次查询中取出同一字段的前N行的数据(`Lag`)和后N行的数据(`Lead`)作为独立的列。在实际应用当中，若要用到取今天和昨天的

╰+哭是因爲堅強的太久メ/ 2021年06月26日 16:06/ 0 赞/ 403 阅读