[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。

[sql]  view plain  copy --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_hiredate   from emp e   where e.deptno=30;   [sql]  view plain  copy   

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

[sql]  view plain  copy --2、不包含partition by的lead函數   SELECT e.ename,          e.deptno,          e.hiredate,          lead(e.hiredate) over(order by e.hiredate) as next_hiredate   FROM emp e;  

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

[sql]  view plain  copy update emp e   set e.hiredate=NULL   where 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_hiredate   from emp e   where e.deptno=30;  

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

[sql]  view plain  copy