本文详细介绍了Oracle数据库中的LAG和LEAD分析函数，用于在查询中获取当前行同一字段的前后行数据。通过示例展示了如何设置offset和default参数，以及在分组查询中的使用，强调了这两个函数在处理数据序列时的高效性和灵活性。
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)在這個窗口范圍執行。
參見：
設置了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！這個結果是否是應用想要的結果？
細心很重要！！尤其是在計算和錢有關的情況下！！
扫一扫
2526
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
