Oracle NTH_VALUE分析函數及ROWS BETWEEN UNBOUNDED PRECEDING AND子句

在Oracle 10G 分析函數中使用 FIRS T_VALUE，LAST_VALUE 可返回第一行和最後一行值

在Oracle 11G推出更為強大NTH_VALUE 開窗分析函數，其作用取任意一行值，官方說明：
NTH_VALUE returns the measure_expr value of the nth row in the window defined by the analytic_clause

語法：
NTH_VALUE (measure_expr, n)

  [ FROM { FIRST | LAST } ][ { RESPECT | IGNORE } NULLS ] OVER (analytic_clause)

舉例說明：
CREATE TABLE EMP
(
EMP_NO number,
EMP_NAME VARCHAR2(10),
DEPT_NO VARCHAR2(10),
SALARY NUMBER
);

INSERT INTO EMP VALUES(1,'Oraman','DBA',20000);
INSERT INTO EMP VALUES(2,'Ross','SA',10000);
INSERT INTO EMP VALUES(3,'James','SA',12000);
INSERT INTO EMP VALUES(8,'Terry','CTO',50000);
INSERT INTO EMP VALUES(10,'GC','DBA',15000);
INSERT INTO EMP VALUES(11,'Henry','DBA',10000);
COMMIT;

  
例1: 加一列取員工所在各部門中SALARY最高的薪水 (使用傳統MAX可 實現)
SELECT EMP_NO,EMP_NAME,DEPT_NO,SALARY,
MAX(SALARY) OVER (PARTITION BY DEPT_NO) MAX_SALARY
FROM EMP;


例2:  加一列取員工所在 各部門中SALARY最高的員工 (使用 LAST_VALUE 可實現)
SELECT EMP_NO,EMP_NAME,DEPT_NO,SALARY,
LAST_VALUE(EMP_NAME) OVER (PARTITION BY DEPT_NO order by SALARY ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) MAX_EMP
FROM EMP;

例3: 加一列取員工所在 各部門中 SALARY最二高的員工(如果在11G之前會比較麻煩需使用row_number包一層再取值，11G后NTH_VALUE可完美解決，注意下面from FIRST為默認值可去掉，反之可使用from LAST)
SELECT EMP_NO,EMP_NAME,DEPT_NO,SALARY,
NTH_VALUE(EMP_NAME,2) from FIRST OVER (PARTITION BY DEPT_NO order by SALARY desc ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) MAX_EMP
FROM EMP;


附：
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING 語法含義：
取當前行 之前 多少行 到 當前行之後 多少行 的記錄，這里BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING 就是指所有行
如果 ROWS BETWEEN..AND 沒有指明，默認為：RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW，即從最開始行到當前行

官方說明：
If you omit the windowing_clause entirely, then the default is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

如果不使用ROWS  BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING，看看效果：
SELECT EMP_NO,EMP_NAME,DEPT_NO,SALARY,
NTH_VALUE(EMP_NAME,2) from FIRST OVER (PARTITION BY DEPT_NO order by SALARY desc ) MAX_EMP
FROM EMP;

為什麼 1 Oraman DBA 20000行MAX_EMP為空？因為取值是 從最開始行到當前行的值，這裡DBA部門工資排二的還沒有出現。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/25583515/viewspace-2148351/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/25583515/viewspace-2148351/