【分析函式】Oracle分析函式之LAG和LEAD

				Lag和Lead函式可以在一次查詢中取出同一欄位的前N行的資料和後N行的值。這種操作可以使用對相同表的表連線來實現，不過使用LAG和LEAD有更高的效率。 
			
				lag的語法如下： 
			
				lead的語法如下： 
			
				lead 和lag 的語法類似以下以lag為例進行講解！ 
			
				lag(exp_str,offset,defval) over() 
			
				exp_str 是要做對比的欄位 
			
				offset 是exp_str欄位的偏移量 比如說 offset 為2 則 拿exp_str的第一行和第三行對比，第二行和第四行，依次類推，offset的預設值為1！ 
			
				defval是當該函式無值可用的情況下返回的值。Lead函式的用法類似。 
			
				以下是lag和lead的例子 
			
SCOTT@yangdb> set pagesize 10000

SCOTT@yangdb> 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

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.

設定了default 值之後 第一行對應的值 為500
			SCOTT@yangdb> 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@yangdb> 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@yangdb> 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！這個結果是否是應用想要的結果？

		細心很重要！！尤其是在計算和錢有關的情況下！！ 
	

lag和lead之IGNORE NULLS

Oracle 11g增強LEAD和LAG函式的語法，加入了{RESPECT | IGNORE} NULLS，當前行為NULL，想找出該列上一個不為NULL的值，怎麼辦呢，用IGNORE NULLS吧。

				
						drop table ta;

create table ta as select level id,dummy||level x from dual connect by level<=10;

UPDATE TA SET X='' WHERE ID BETWEEN 3 AND 7;

SELECT T.ID,

       T.X,

       NVL(T.X, LAG(T.X IGNORE NULLS) OVER(ORDER BY T.ID)) A,

       NVL(T.X, LAG(T.X RESPECT NULLS) OVER(ORDER BY T.ID)) B,

       NVL(T.X, LAG(T.X) OVER(ORDER BY T.ID)) C

  FROM TA T

 ORDER BY T.ID;

About Me

...............................................................................................................................

● 本文整理自網路

● 本文在itpub（http://blog.itpub.net/26736162）、部落格園（http://www.cnblogs.com/lhrbest）和個人微信公眾號（xiaomaimiaolhr）上有同步更新

● 本文itpub地址：http://blog.itpub.net/26736162/abstract/1/

● 本文部落格園地址：http://www.cnblogs.com/lhrbest

● 本文pdf版及小麥苗雲盤地址：http://blog.itpub.net/26736162/viewspace-1624453/

● QQ群：230161599 微信群：私聊

● 聯絡我請加QQ好友(646634621)，註明新增緣由

● 於 2017-04-28 09:00 ~ 2017-04-30 22:00 在魔都完成

● 文章內容來源於小麥苗的學習筆記，部分整理自網路，若有侵權或不當之處還請諒解

● 版權所有，歡迎分享本文，轉載請保留出處

...............................................................................................................................

拿起手機使用微信客戶端掃描下邊的左邊圖片來關注小麥苗的微信公眾號：xiaomaimiaolhr，掃描右邊的二維碼加入小麥苗的QQ群，學習最實用的資料庫技術。

【分析函式】Oracle分析函式之LAG和LEAD

【分析函式】Oracle分析函式之LAG和LEAD

相關文章