[Oracle]Lead和Lag位移量函數使用詳解

Oracle有兩個函數：LEAD和LAG，這兩個函數都是用來計算位移量的分析函數，這兩個函數的用法相同。

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。

--1、lead基本用法select e.ename,       e.deptno,       e.hiredate,       lead(e.hiredate,1,null) over(partition by e.deptno order by e.hiredate) as next_hiredatefrom emp ewhere e.deptno=30;

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

--2、不包含partition by的lead函數SELECT e.ename,       e.deptno,       e.hiredate,       lead(e.hiredate) over(order by e.hiredate) as next_hiredateFROM emp e;

3、Oracle 11g增強LEAD和LAG函數的文法，加入了{RESPECT | IGNORE} NULLS，如果LEAD的運算式結果為空白，則返回的結果也為空白，看下面這個例子。

update emp eset e.hiredate=NULLwhere e.empno='7698';commit;select e.empno,       e.ename,       e.deptno,       e.hiredate,       lead(e.hiredate,1,NULL) over(partition by e.deptno order by e.empno) as next_hiredatefrom emp ewhere e.deptno=30;

由於7698-BLAKE的HIREDATE的值為空白，所有MARTIN的NEXT_HIREDATE的值也為空白，系統預設的是RESPECT NULLS，添加IGNORE NULLS後的結果如下。

select e.empno,       e.ename,       e.deptno,       e.hiredate,       lead(e.hiredate ignore nulls) over(partition by e.deptno order by e.empno) as next_hiredatefrom emp ewhere e.deptno=30;

新增的IGNORE NULLS功能，可以忽略NULL結果，去尋找另一個滿足條件的結果。





4、位移量決定查詢時向前或後便宜N個位置，函數的預設位移量為1,我們也可以修改為自訂的位移量。

--4、位移量offset,預設值=1,手動設定為2select e.empno,       e.ename,       e.deptno,       e.hiredate,       lead(e.hiredate,2,null) over(partition by e.deptno order by e.hiredate) as next_hiredatefrom emp ewhere deptno=30;




5、LEAD的位移量如果超出了表的範圍，則DEFAULT預設返回NULL，我們可以手動設定一個固定的傳回值，該值的資料類型必須與LEAD運算式類型一致。

--5、lead函數的default參數select e.empno,       e.ename,       e.deptno,       e.hiredate,       lead(e.hiredate,1,to_date('2013-01-01','yyyy-MM-dd')) over(partition by e.deptno order by e.hiredate) as next_hiredatefrom emp ewhere deptno=30;




LAG分析函數的用法基本與lEAD函數類型，只不過作用相反，大家可以在自己的機子上親自實踐一下，更多詳細的資訊請參考：http://docs.oracle.com/cd/E11882_01/server.112/e26088/functions086.htm#SQLRF00656