Oracle的LAG和LEAD分析函數 Lag函數可以在一次查詢中取出目前行的同一字段的前面第N行的資料。 Lead 函數可以在一次查詢中取出目前行的同一字段的後 面第 N行的。 這種操作可以使用對相同表的表連接配接來實作，不過使用LAG和LEAD有更高的效率。 lag的文法如下：
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例子：
[email protected] www.linuxidc.com> set pagesize 10000
[email protected] 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：
[email protected] 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時
[email protected] 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
[email protected] 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分析函數
[email protected]> 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.
[email protected]> 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.
[email protected]> 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
[email protected]> 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 分組的例子：
[email protected]> 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.
[email protected]> 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.
[email protected]>
使用分析函數的時候注意空值 或者null 給資料帶來的影響，資料是否允許為空或者null計算的時候會導緻一定的差錯 比如 800-null 肯定為null！這個結果是否是應用想要的結果？
細心很重要！！尤其是在計算和錢有關的情況下！！
本文原創釋出php中文網，轉載請注明出處，感謝您的尊重！