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

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

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

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

樣例
逐行顯示各個部門的累計工資，每行包括部門內(nèi)前面所有人的工資總和：
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;

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

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

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