ORACLE進階之三：分析函數

有時候我們需要從DB中提取一些很復雜的數據，而標準SQL卻對此無能為力，或者是執行效率非常的低；比如我們需要提取如下數據： 逐行顯示各個部門的累計工資，每行包括部門內前面所有人的工資總和； 查找各個部門工資最高的前N個人； 語法 Function名稱([參數]

有時候我們需要從DB中提取一些很復雜的數據，而標準SQL卻對此無能為力，或者是執行效率非常的低；比如我們需要提取如下數據：
逐行顯示各個部門的累計工資，每行包括部門內前面所有人的工資總和；
查找各個部門工資最高的前N個人；
……

語法
Function名稱([參數]) OVER ([partition 子句][ order 子句] [window 子句])
OVER為分析函數的關鍵字，用于區別普通的聚合函數；從語法格式上區分的話，沒加over()即時聚合函數，加了over()就是分析函數。
Partition 子句：Partition by exp1[ ,exp2]...；
主要用于分組，可以理解成select中的group by；不過它跟select語句后跟的group by 子句并不沖突；指定該子句之后，前面的函數起效范圍就是該分組內，若不指定，則Function的起效范圍是全部結果集。
Order 子句：Order by exp1[asc|desc] [ ,exp2 [asc|desc]]... [nulls first|last]；
其參數基本與select中的order by相同；Nulls first|last是用來限定nulls在分組序列中的所在位置的，我們知道oracle中對于null的定義是未知，所以默認order by的時候nulls總會被排在最前面。如果想控制值為null的行顯示位置，nulls first|last參數就能派上用場了。
Window 子句：該子句的語法比較復雜，具體可以見下圖；

該子句給出了一個定義變化或者固定的數據窗口方法，分析函數將對這些數據進行操作；默認情況下，一般用不上該子句，分析函數產生一個固定的窗口，影響的數據范圍是從第一行到當前行，其效果和RANGE BETWEEN UNDOUNDED PRECEDING AND CURRENT ROW一樣；若需要指定操作數據為當前行及其前兩行，則可以用ROWS 2 PRECEDING來實現其效果；
其中用[]標注的子句都可以為空，一個最簡單的分析函數可能是COUNT(*) OVER ()；

樣例
逐行顯示各個部門的累計工資，每行包括部門內前面所有人的工資總和：
SELECT EMP_NO,
NAME,
DEPT_NO,
SUM(SAL) OVER(PARTITION BY DEPT_NO ORDER BY EMP_NO) DEPT_SAL_SUM
FROM EMP
ORDER BY DEPT_NO, EMP_NO;

查找各個部門工資最高的前N個人：
SELECT *
FROM (SELECT DEPT_NO,
NAME,
SAL,
DENSE_RANK() OVER(PARTITION BY DEPT_NO ORDER BY SAL DESC) DR
FROM EMP)
WHERE DR <= 3
ORDER BY DEPT_NO, SAL DESC;

注意點
1、分析函數與聚合函數非常相似，不同于聚合函數的地方在于它們每個分組序列均返回多行，而聚合函數返回一行；
2、帶有分析函數的SQL列表中，除了order by子句之外，分析函數將在SQL語句中最后執行；因此，分析函數只能用于select的列或order by子句，而不能用于where、group by、having之類的語句中；
3、當分析函數中使用了distinct參數時，則只能使用partition子句，而不能指定order by子句；
4、SELECT語句中的ORDER BY子句與分析函數中的order by子句是互不影響的，但一般來說兩者一致比較好，若兩者不一致，則意味著分析函數需要對結果集進行多次排序，這將嚴重降低分析函數的執行效率；
5、dense_rank在做排序時如果遇到列有重復值，則重復值所在行的序列值相同，而其后的序列值依舊遞增，rank則是重復值所在行的序列值相同，但其后的序列值從+重復行數開始遞增，而row_number則不管是否有重復行，序列值始終遞增；

函數列表
大致有26個函數可用，其中很多都是和聚合函數同名的，比如SUM、AVG、MIN、MAX……；其他是一些提供新功能的新函數；具體的函數列表如下：

聲明：本網頁內容旨在傳播知識，若有侵權等問題請及時與本網聯系，我們將在第一時間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com