Oracle的LAG和LEAD分析函数

迷南。 2023-10-07 11:21 21阅读 0赞

Oracle的LAG和LEAD分析函数

Lag函数可以在一次查询中取出当前行的同一字段的前面第N行的数据。

Lead函数可以在一次查询中取出当前行的同一字段的后面第N行的值。

这种操作可以使用对相同表的表连接来实现，不过使用LAG和LEAD有更高的效率。

lag的语法如下：

lead的语法如下：

lead 和lag 的语法类似以下以lag为例进行讲解！

lag(exp\_str,offset,defval) over()

exp\_str 指的是要做对比的字段。

offset 是exp\_str字段的偏移量，即 offset 为N ，指的是在表中从当前行位置向前数N行就是我们所要找的那一行了。

比如说，

在表中，假设当前我们说的当前行在表中排在第四行，则offset 为3时表示的是我们所要找的数据行就是表中的第一行（即4-3=1）。

offset的默认值为1！

lag（）函数的返回值为在表中从当前行位置向前数N行的那一行上exp\_str字段的值。

当在表中从当前行位置向前数N行已经超出了表的范围时，lag（）函数将defval这个参数值作为函数的返回值。

比如说，

在表中，假设当前我们说的当前行在表中排在第四行，则offset 为6时表示的是我们所要找的数据行就是表中的第-2行（即4-6=-2），这就表示我们所要找的数据行不在表中已经超出表的范围了，所以lag（）函数将defval这个参数值作为函数的返回值。

default 参数的默认值为空值null，即如果在lag（）函数中没有显式设置default 参数值时lag（）函数的返回值为空值null。  
Lead函数的用法类似。

以下是lag例子：  
SCOTT@ www.linuxidc.com> set pagesize 10000  
SCOTT@ www.linuxidc.com> select ename,job,sal ,lag(sal) over(order by sal) last\_sal from emp;  
ENAME JOB SAL LAST\_SAL  
\---------- --------- ---------- ----------  
SMITH CLERK 800 --此时没有设置default 值 则为空值  
JAMES CLERK 950 800 --这里的800来自第一行字段sal里的值800  
ADAMS CLERK 1100 950  
WARD SALESMAN 1250 1100  
MARTIN SALESMAN 1250 1250  
MILLER CLERK 1300 1250  
TURNER SALESMAN 1500 1300  
ALLEN SALESMAN 1600 1500  
CLARK MANAGER 2450 1600  
BLAKE MANAGER 2850 2450  
JONES MANAGER 2975 2850  
SCOTT ANALYST 3000 2975  
FORD ANALYST 3000 3000  
KING PRESIDENT 5000 3000  
14 rows selected.

注释：

lag(sal) over(order by sal) 解释

over（order by salary）表示意义如下：

首先，我们要知道由于省略分组子句，所以当前组的范围为整个表的数据行，

然后，在当前组（此时为整个表的数据行）这个范围里执行排序（即order by salary），

最后，我们知道分析函数lag(sal)在当前组（此时为整个表的数据行）这个范围里的窗口范围为当前组的第一行到当前行，即分析函数lag(sal)在这个窗口范围执行。

参见：

oracle分析函数技术详解（配上开窗函数over()）  
Oracle分析函数ROW\_NUMBER()|RANK()|LAG()使用详解

设置了default 值之后 第一行对应的值 为500：  
SCOTT@ www.linuxidc.com> select ename,job,sal ,lag(sal,1,500) over(order by sal) last\_sal from emp;  
ENAME JOB SAL LAST\_SAL  
\---------- --------- ---------- ----------  
SMITH CLERK 800 500  
JAMES CLERK 950 800  
ADAMS CLERK 1100 950  
WARD SALESMAN 1250 1100  
MARTIN SALESMAN 1250 1250  
MILLER CLERK 1300 1250  
TURNER SALESMAN 1500 1300  
ALLEN SALESMAN 1600 1500  
CLARK MANAGER 2450 1600  
BLAKE MANAGER 2850 2450  
JONES MANAGER 2975 2850  
SCOTT ANALYST 3000 2975  
FORD ANALYST 3000 3000  
KING PRESIDENT 5000 3000  
14 rows selected.

指定offset的值为2时  
SCOTT@ www.linuxidc.com> select ename,job,sal ,lag(sal,2) over(order by sal) last\_sal from emp;  
ENAME JOB SAL LAST\_SAL  
\---------- --------- ---------- ----------  
SMITH CLERK 800  
JAMES CLERK 950  
ADAMS CLERK 1100 800  
WARD SALESMAN 1250 950  
MARTIN SALESMAN 1250 1100  
MILLER CLERK 1300 1250  
TURNER SALESMAN 1500 1250  
ALLEN SALESMAN 1600 1300  
CLARK MANAGER 2450 1500  
BLAKE MANAGER 2850 1600  
JONES MANAGER 2975 2450  
SCOTT ANALYST 3000 2850  
FORD ANALYST 3000 2975  
KING PRESIDENT 5000 3000  
14 rows selected.  
offset的值为3  
SCOTT@ www.linuxidc.com> select ename,job,sal ,lag(sal,3) over(order by sal) last\_sal from emp;  
ENAME JOB SAL LAST\_SAL  
\---------- --------- ---------- ----------  
SMITH CLERK 800  
JAMES CLERK 950  
ADAMS CLERK 1100  
WARD SALESMAN 1250 800  
MARTIN SALESMAN 1250 950  
MILLER CLERK 1300 1100  
TURNER SALESMAN 1500 1250  
ALLEN SALESMAN 1600 1250  
CLARK MANAGER 2450 1300  
BLAKE MANAGER 2850 1500  
JONES MANAGER 2975 1600  
SCOTT ANALYST 3000 2450  
FORD ANALYST 3000 2850  
KING PRESIDENT 5000 2975  
14 rows selected.

使用lead分析函数  
SCOTT@yangdb> select ename,job,sal ,lead(sal) over(order by sal) last\_sal from emp;  
ENAME JOB SAL LAST\_SAL  
\---------- --------- ---------- ----------  
SMITH CLERK 800 950  
JAMES CLERK 950 1100  
ADAMS CLERK 1100 1250  
WARD SALESMAN 1250 1250  
MARTIN SALESMAN 1250 1300  
MILLER CLERK 1300 1500  
TURNER SALESMAN 1500 1600  
ALLEN SALESMAN 1600 2450  
CLARK MANAGER 2450 2850  
BLAKE MANAGER 2850 2975  
JONES MANAGER 2975 3000  
SCOTT ANALYST 3000 3000  
FORD ANALYST 3000 5000  
KING PRESIDENT 5000  
14 rows selected.  
SCOTT@yangdb> select ename,job,sal ,lead(sal,1) over(order by sal) last\_sal from emp;  
ENAME JOB SAL LAST\_SAL  
\---------- --------- ---------- ----------  
SMITH CLERK 800 950  
JAMES CLERK 950 1100  
ADAMS CLERK 1100 1250  
WARD SALESMAN 1250 1250  
MARTIN SALESMAN 1250 1300  
MILLER CLERK 1300 1500  
TURNER SALESMAN 1500 1600  
ALLEN SALESMAN 1600 2450  
CLARK MANAGER 2450 2850  
BLAKE MANAGER 2850 2975  
JONES MANAGER 2975 3000  
SCOTT ANALYST 3000 3000  
FORD ANALYST 3000 5000  
KING PRESIDENT 5000  
14 rows selected.  
SCOTT@yangdb> select ename,job,sal ,lead(sal,2) over(order by sal) last\_sal from emp;  
ENAME JOB SAL LAST\_SAL  
\---------- --------- ---------- ----------  
SMITH CLERK 800 1100  
JAMES CLERK 950 1250  
ADAMS CLERK 1100 1250  
WARD SALESMAN 1250 1300  
MARTIN SALESMAN 1250 1500  
MILLER CLERK 1300 1600  
TURNER SALESMAN 1500 2450  
ALLEN SALESMAN 1600 2850  
CLARK MANAGER 2450 2975  
BLAKE MANAGER 2850 3000  
JONES MANAGER 2975 3000  
SCOTT ANALYST 3000 5000  
FORD ANALYST 3000  
KING PRESIDENT 5000  
SCOTT@yangdb> select ename,job,sal ,lead(sal,3) over(order by sal) last\_sal from emp;  
ENAME JOB SAL LAST\_SAL  
\---------- --------- ---------- ----------  
SMITH CLERK 800 1250  
JAMES CLERK 950 1250  
ADAMS CLERK 1100 1300  
WARD SALESMAN 1250 1500  
MARTIN SALESMAN 1250 1600  
MILLER CLERK 1300 2450  
TURNER SALESMAN 1500 2850  
ALLEN SALESMAN 1600 2975  
CLARK MANAGER 2450 3000  
BLAKE MANAGER 2850 3000  
JONES MANAGER 2975 5000  
SCOTT ANALYST 3000  
FORD ANALYST 3000  
KING PRESIDENT 5000  
14 rows selected.  
lead 的offset N 是以记录的第N行和第一做对比注意末尾的 null 值！  
Lead和Lag函数也可以使用分组，以下是使用job 分组的例子：

SCOTT@yangdb> select ename,job,sal ,lead(sal,1) over(partition by job order by sal) last\_sal from emp;  
ENAME JOB SAL LAST\_SAL  
\---------- --------- ---------- ----------  
FORD ANALYST 3000 3000  
SCOTT ANALYST 3000  
SMITH CLERK 800 950  
JAMES CLERK 950 1100  
ADAMS CLERK 1100 1300  
MILLER CLERK 1300  
CLARK MANAGER 2450 2850  
BLAKE MANAGER 2850 2975  
JONES MANAGER 2975  
KING PRESIDENT 5000  
MARTIN SALESMAN 1250 1250  
WARD SALESMAN 1250 1500  
TURNER SALESMAN 1500 1600  
ALLEN SALESMAN 1600  
14 rows selected.  
SCOTT@yangdb> select ename,job,sal ,lag(sal,1) over(partition by job order by sal) last\_sal from emp;  
ENAME JOB SAL LAST\_SAL  
\---------- --------- ---------- ----------  
FORD ANALYST 3000  
SCOTT ANALYST 3000 3000  
SMITH CLERK 800  
JAMES CLERK 950 800  
ADAMS CLERK 1100 950  
MILLER CLERK 1300 1100  
CLARK MANAGER 2450  
BLAKE MANAGER 2850 2450  
JONES MANAGER 2975 2850  
KING PRESIDENT 5000  
MARTIN SALESMAN 1250  
WARD SALESMAN 1250 1250  
TURNER SALESMAN 1500 1250  
ALLEN SALESMAN 1600 1500  
14 rows selected.  
SCOTT@yangdb>  
使用分析函数的时候注意空值 或者null 给数据带来的影响，数据是否允许为空或者null计算的时候会导致一定的差错 比如 800-null 肯定为null！这个结果是否是应用想要的结果？  
细心很重要！！尤其是在计算和钱有关的情况下！！  
————————————————  
版权声明：本文为CSDN博主「haiross」的原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接及本声明。  
原文链接：https://blog.csdn.net/haiross/article/details/43733955