在Oracle 10G分析函式中使用 FIRST_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部門工資排二的還沒有出現。

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

相關文章