6.1常用聚集函數(shù)

聚集函數(shù)需要注意的一點(diǎn)就是：聚集函數(shù)會(huì)忽略空值，當(dāng)數(shù)據(jù)不全為空時(shí)對sum等來說沒什么影響，但對avg、count來說可能會(huì)出現(xiàn)預(yù)料之外的結(jié)果。所以要根據(jù)需求決定是否把空值轉(zhuǎn)為零。

注意，當(dāng)表中沒有數(shù)據(jù)時(shí)，不加group by會(huì)返回一行數(shù)據(jù)，但加了group by 會(huì)沒有數(shù)據(jù)返回。

建立空表：

沒有g(shù)roup by:

有g(shù)roup by:

因此，當(dāng)你在錯(cuò)誤的地點(diǎn)錯(cuò)誤地增加了group by,Oracle就會(huì)報(bào)錯(cuò)：

這個(gè)地方并不需要有g(shù)roup by:

6.2列轉(zhuǎn)行

UNION ALL可以把同表或不同表的數(shù)據(jù)通過兩個(gè)或兩個(gè)以上的語句上下拼在一起：

而當(dāng)我們把同一個(gè)表中的不同列使用UNION ALL拼在一起時(shí)，就是我們常說的列轉(zhuǎn)行：

6.3行轉(zhuǎn)列

行轉(zhuǎn)列是一個(gè)常用的功能，在處理數(shù)據(jù)或配置報(bào)表時(shí)會(huì)經(jīng)常用到，如下圖所示。

行轉(zhuǎn)列是指把不同行的數(shù)據(jù)按給定的規(guī)則轉(zhuǎn)為不同的列。

我們通過分拆的方式來學(xué)習(xí)行轉(zhuǎn)列，看下原數(shù)據(jù)：

首先需要使用條件表達(dá)式增加需要的數(shù)據(jù)列：

然后把數(shù)據(jù)匯總：

特別注意上面語句SUM的位置，我們應(yīng)對條件表達(dá)式的結(jié)果求和，所以應(yīng)把條件表達(dá)式放在SUM里面，而不僅僅是SUM(SAL):

這種簡單的條件表達(dá)式也可以直接使用DECODE函數(shù)：

6.4生成累計(jì)和

假設(shè)公司就是按EMP表中的信息來發(fā)工資的，那么HIREDATE對應(yīng)的時(shí)點(diǎn)應(yīng)該發(fā)多少工資？

第二行應(yīng)該是1600+1250，第三行是1600+1250+2850，依次類推。我們可以這樣寫語句：

或者：

兩種方式都有點(diǎn)麻煩，而且很多人也會(huì)認(rèn)為第二種方式易讀性不好。在Oracle及很多數(shù)據(jù)庫中都支持分析函數(shù)，可以在對EMP只讀取一遍的情況下通過分析計(jì)算得到需要的數(shù)據(jù)。

里面的分析函數(shù)是指“SUM(sal) over(ORDER BY hirEDAte)”這個(gè)組合。讓我們通過PLAN來看下它干了什么：

我們的原始語句：

轉(zhuǎn)換成了：

這個(gè)語句前面的SUM("SAL")容易理解，就是對sal求和。后面分為以下三部分。

１.ORDER BY "EMPNO" : 按EMPNO排序。

2.RANGE:指按數(shù)值范圍分析。

3.BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW : BETWEEN...AND...子句，表示區(qū)間從?UNBOUNDED PRECEDING（第一行）到 CURRENT ROW（當(dāng)前行）。

以分析到第三行時(shí)為例，上面三處的意思加在一起就是：

有時(shí)我們需要對數(shù)據(jù)分組處理，比如EMP的三個(gè)部門分別累計(jì)?？梢约尤?yún)?shù)PARTITION BY：

6.5累計(jì)與重復(fù)值

當(dāng)分析函數(shù)的ORDER BY后面的列有重復(fù)數(shù)據(jù)時(shí)，可能得到的結(jié)果與期望的不一致，比如我們想用5000來雇傭員工，看最多能雇傭幾個(gè)人。為了把問題一起顯示出來，我們直接用條件<=6000:

可以看到，如果我們按5000算，就會(huì)少一名工資1250的員工。原因是分析函數(shù)用<=1250計(jì)算時(shí)會(huì)查詢到兩條數(shù)據(jù)。這時(shí)我們可以增加排序列使排序數(shù)據(jù)不重復(fù)的方式解決：

也可以更改默認(rèn)的關(guān)鍵字"RANGE"為"ROWS"：

結(jié)果會(huì)同上面一樣。

6.6生成排名

配制報(bào)表時(shí)可能會(huì)遇到生成排名的需求，比如查看員工的工資排名。排名時(shí)需要注意的是重復(fù)數(shù)據(jù)，對于重復(fù)數(shù)據(jù)有三種排名方式：

因?yàn)楣べY為3000的員工有兩個(gè)，所以排序有三種方式：順序、同名跳號(hào)、同名不跳號(hào)。

查詢中分別用了分析函數(shù)ROW_NUMBER、RANK、DENSE_RANK來分組 (PARTITION BY deptno)生成排名。

ROW_NUMBER會(huì)生成序號(hào)1、2、3。

RANK相同的工資會(huì)生成同樣的序號(hào)，而且其后的序號(hào)與ROW_NUMBER相同（empno=7566,生成的序號(hào)是3）。

DENSE_RANK相同的工資會(huì)生成同樣的序號(hào)，而且其后的序號(hào)遞增（empno=7566,生成的序號(hào)是２）。

實(shí)際查詢時(shí)使用哪一個(gè)函數(shù)，要確認(rèn)好需求再定。

6.7返回最值對應(yīng)信息

有時(shí)我們需要的不是最大最小值，而是要求返回其對應(yīng)的信息，如返回各部門工資最高的員工姓名。

我們可以按上節(jié)的方法取排名第一的數(shù)據(jù)：

部門20最高工資對應(yīng)的有兩個(gè)人，我們這里通過函數(shù)RANK返回了兩條。

如果我們的需求是只顯示一條，可以使用另一個(gè)函數(shù)：

這個(gè)函數(shù)可以分兩部分來理解，KEEP()返回對應(yīng)DENSE_RANK函數(shù)取值為1的數(shù)據(jù)，而前面的聚合函數(shù)在KEEP()的基礎(chǔ)上進(jìn)一步處理。

這個(gè)函數(shù)可以把得到的數(shù)據(jù)與明細(xì)一起顯示：

6.8求總和的百分比

如下面的表格所示，要求計(jì)算各部門的工資合計(jì)，以及該合計(jì)工資占總工資的比例。

其中的工資合計(jì)很簡單，直接用group by語句就可以得到。要點(diǎn)在于總工資合計(jì)，需要用到分析函數(shù):sum()和over()。

當(dāng)over()后不加任何內(nèi)容時(shí)，就是對所有的數(shù)據(jù)進(jìn)行匯總，步驟如下。

1.分組匯總

2.通過分析函數(shù)獲取總合計(jì)

3.得到總合計(jì)后就可以計(jì)算比例

另外，我們也可以用專用的比例函數(shù)"ratio_to_report"來直接計(jì)算。

同其他分析函數(shù)一樣，可以使用PARTITION BY分組計(jì)算，如查詢各員工占本部門的工資比例：