9.1 分組函數

本節定義SQL分組函數和它們的不同變體，介紹選擇的分組函數的語法和示例，并討論了它們的數據類型以及DISTINCT關鍵字和NULL值的影響。

9.1.1 分組函數的定義

分組函數作用于聚合數據，并且針對每個組返回單個結果。這些組通常由一個或者多個數據行組成。使用如下公式定義單行函數：F(x, y, z, …) = result，其中x, y, z…是輸入參數。函數F每次對數據集的一行執行操作，對每一行都返回一個結果?？梢允褂孟旅娴墓蕉x分組函數：

      F(g1, g2, g3, . . . , gn) = result1, result2, result2, . . . , resultn;

分組函數對每個行群集執行一次，并且針對每個組返回一個結果。這些組中的行通過公共值或特性相關聯。如果表中所有的行作為分組函數的一個組，那么返回一個結果。SELECT列表中可以出現一個或者多個分組函數，如下所示：

        SELECT group_function(column or expression), ...
        FROM table [WHERE ...] [ORDER BY...]

請考察EMPLOYEES表。表中有107行，可以依據這些行共享的公共值創建組。例如，共享相同DEPARTMENT_ID值的行可以聚集在一起。然后，對每個特定組單獨執行分組函數。

如圖9-1所示，在EMPLOYEES表中有12個不同的DEPARTMENT_ID值，包括一個空值。依據公共DEPARTMENT_ID值將這些行劃分為12個組。COUNT函數執行12次，針對每個組執行一次。注意，不同的組包含不同數量的行。

9.1.2 使用分組函數

AVG、SUM、MIN、MAX和COUNT函數用來說明分組函數的實際應用。這些分組函數都返回數字結果。另外，MIN和MAX函數還可能返回字符和日期結果。這5個函數作用于非空值，但是和其他函數不一樣，在特定條件下COUNT函數調用也可以計算包含空值的行。

1．COUNT函數

COUNT函數計算組中的行數，其語法如下所示：

        COUNT({*|[DISTINCT|ALL] expr})

該語法可以分解為下面的形式：

● COUNT(*)

● COUNT(DISTINCT expr)

● COUNT(ALL expr)

● COUNT(expr)

調用COUNT(*)時，組中的所有行(包括具有空值和重復值的行)都計算在內。執行COUNT (DISTINCT expr)時，只計算每個組中expr唯一出現的次數。ALL關鍵字是默認語法的一部分，因此COUNT(ALL expr)和COUNT(expr)是等效的表達式。如果expr基于命名的列，就忽略空值，如果expr基于各種情況的列，將為每一行進行計算，而不管行中是否有空值。expr的數據類型可以是NUMBER、DATE、CHAR或者VARCHAR2?？疾煜旅娴牟樵儯?/p>

        查詢1: select count(*) from employees;
        查詢2: select count(commission_pct), count(1) from employees;
        查詢3: select count(distinct salary) from employees;
        查詢4: select count(hire_date), count(manager_id) from employees;

查詢1計算EMPLOYEES表中的行，返回整數107。查詢2計算包含非空COMMISSION_PCT值的行，返回36。它還計算字面表達式1，該表達式不基于命名的列，因此對每一行進行計算，返回107。查詢3考察107個非空行，確定唯一SALARY值的數量，返回58。查詢4演示了如何在DATE列和NUMBER列上使用COUNT函數。該查詢返回整數107和106，因為組中有107個非空HIRE_DATE值和106個非空MANAGER_ID值。

2．SUM函數

SUM函數返回組中非空數字表達式值的總和，其語法如下所示：

        SUM([DISTINCT|ALL] expr);

該語法可分解為下面的形式：

● SUM(DISTINCT expr)

● SUM(ALL expr)

● SUM(expr)

SUM(DISTINCT expr)提供在對組中每一行計算 expr 之后返回的唯一值進行相加得到的和。SUM (expr)和SUM(ALL expr)提供組中各行的expr相加的和，并且忽略空值。expr參數的數據類型必須是NUMBER?？疾煜旅娴牟樵儯?/p>

        查詢1: select sum(2) from employees;
        查詢2: select sum(salary) from employees;
        查詢3: select sum(distinct salary) from employees;
        查詢4: select sum(commission_pct) from employees;

EMPLOYEES表有107行。查詢1把107行的數字2加起來，返回214。查詢2獲得組(整個表)中各行的SALARY列值，返回薪水總額721166。查詢3返回總和417158，因為許多員工獲得相同的薪水，DISTINCT關鍵字只是將列中的唯一值添加到總和之中。在相加非空COMMISSION_PCT值之后，查詢4返回7.9。

3．AVG函數

列或者表達式的平均值就是用和除以組中的非空行數。AVG函數的語法如下所示：

        AVG([DISTINCT|ALL] expr);

該語法可以分解為下面的形式：

● AVG(DISTINCT expr)

● AVG(ALL expr)

● AVG(expr)

調用AVG(DISTINCT expr)時，將 expr 的不同值相加，并除以 expr 唯一出現的次數。AVG(ALL expr) 和AVG(expr)將各行中expr的非空值相加，用求得的和除以組中的非空行數。expr參數的數據類型是NUMBER。考察下面的查詢：

        查詢1: select avg(2) from employees;
        查詢2: select avg(salary) from employees;
        查詢3: select avg(distinct salary) from employees;
        查詢4: select avg(commission_pct) from employees;

EMPLOYEES表有107行。查詢1把107行的數字2加起來，用總和除以行數，返回數字2。原封不動地返回提交給AVG函數的數字字面值。查詢2相加各行的SALARY值，得到薪水總數721166。用它除以包含非空SALARY值的107行，返回平均值6739.86916。對于查詢3來說，有58個唯一的薪水值，相加之后，得到總和為417158。用417158除以58，返回7192.37931作為不同薪水值的平均值。查詢4相加非空COMMISSION_PCT值，生成總和7.9。有36條員工記錄包含非空COMMISSION_PCT值。用7.9除以36，生成的平均值為0.21944444。

4．STDDEV和VARIANCE函數

標準偏差衡量值在一列或表達式中的分散程度。計算方差的平方根，就得到了標準偏差。方差是指實際值和平均值的平方差之和，再除以N-1或N；根據方差是為一個樣本建立的，還是為所有數據建立的，來選擇除以N-1還是N。VARIANCE函數為一個樣本計算方差，因此應計算實際值和平均值的平方差之和，再除以N-1。更多的信息可參閱VAR_POP和VAR_SAMP函數，它們超出了考試范圍。

計算方差時，先計算集合的平均值。接著給每個數字減去平均值，再計算結果的平方。這就是平方差。把所有的平方差加起來，再除以N-1，就得到了方差。STDDEV和VARIANCE函數有兩種形式：匯集形式和分析形式，這超出了考試范圍。匯集形式有如下語法：

        STDDEV([DISTINCT|ALL] expr)
        VARIANCE([DISTINCT|ALL] expr)

考慮圖9-2中的查詢。

有3個員工屬于部門90，其薪水分別是24000、17000和17000。查詢2返回平均薪水19333.3333，并顯示了方差和標準偏差。從統計上看，查詢3和4提供了計算方差和標準偏差的另一種方式。這些不在考試范圍內，這里提供它們，是為了通過例子解釋這些函數。查詢3使用power函數，計算(薪水-平均薪水)的2次方，得到平方差，再使用sum函數求和，最后除以N-1。它還使用sqrt函數計算標準偏差。記住N是3，因為在樣本集合中有3個薪水值。平均薪水19333.3333在查詢3中硬編碼了，而查詢4使用一個嵌套的子查詢(參見第11章)，動態計算平均薪水。

5．MAX和MIN函數

MAX和MIN函數返回組中expr的最大和最小值。MAX和MIN函數作用于NUMBER、DATE、CHAR和VARCHAR2數據類型。它們返回與輸入實參相同的數據類型的值，這個值要么是組中的最大值，要么是最小值。當應用于DATE項時，MAX返回最近日期，MIN返回最早日期。依據數據庫中的NLS設置，將字符串轉換為其組成字符的數字表示。當將MIN函數應用于一組字符串時，返回按字母順序第一個出現的單詞，而MAX返回最后出現的單詞。MAX和MIN函數的語法如下所示：

        MAX([DISTINCT|ALL] expr); MIN([DISTINCT|ALL] expr)

該語法可以分解為下面的形式：

● MAX(DISTINCT expr); MIN(DISTINCT expr)

● MAX(ALL expr); MIN(ALL expr)

● MAX(expr); MIN(expr);

MAX(expr)、MAX(ALL expr)和MAX(DISTINCT expr)檢查一組行中expr的值，返回其中的最大值，并且忽略空值。MIN(expr)、MIN(ALL expr)和MIN(DISTINCT expr)檢查一組行中expr的值，返回其中的最小值?？疾煜旅娴牟樵儯?/p>

        查詢1: select min(commission_pct), max(commission_pct) from employees;
        查詢2: select min(start_date), max(end_date) from job_history;
        查詢3: select min(job_id), max(job_id) from employees;

查詢1返回數值0.1和0.4，它們就是EMPLOYEES表中COMMISSION_PCT值的最小值和最大值。注意，忽略了COMMISSION_PCT的空值。查詢2計算DATE列，并且表明JOB_HISTORY表中最早的START_DATE是17-SEP-1995，最近的END_DATE是07-JAN-2015。查詢3返回AC_ ACCOUNT和ST_MAN作為EMPLOYEES表中按字母順序最早和最后出現的JOB_ID值。

6．LISTAGG函數

LISTAGG函數返回列值的字符串匯總。如果有ORDER BY子句，值就排序后連接起來。這個函數運行為單集合匯集函數，它作用于所有行，返回一個輸出行，或者執行為分組集合匯集函數，為GROUP BY子句中的每個組返回一個輸出行(參見下一節)。LISTAGG函數的語法如下：

        LISTAGG(expr, ['delimiter']) WITHIN GROUP (ORDER_BY_CLAUSE)

expr參數可以是任何有效的表達式?？罩禃雎?。delimiter指定分隔expr的可選字符串。ORDER BY子句確定連接好的值的返回順序?？紤]圖9-3中的查詢。

查詢1返回熟悉的輸出格式，列出按REGION_ID排序的國家，以及按COUNTRY_NAME字母倒序排序的國家。查詢2演示了LISTAGG函數的單集合匯總形式，返回一行數據，它在一個用逗號分隔的列表中連接了COUNTRY_NAME值。查詢3顯示了分組集合匯總形式，為按REGION_ID分組的每一組返回一個用逗號分隔的COUNTRY_NAME值列表，并計算出每一組的項數。

練習9-1 使用分組函數

COUNTRIES表存儲COUNTRY_NAME值列表。要求計算所有國家名稱的平均長度。所有小數部分必須舍入到最接近的整數。

(1) 啟動SQL*Plus或SQL Developer，連接到HR模式。

(2) 使用LENGTH函數計算各行中國家名稱值的長度?？梢允褂肁VG函數確定平均長度，并且使用ROUND函數將它舍入為最接近的整數?？赡艿慕鉀Q方案是：

        select round(avg(length(country_name))) average_country_name_length
        from countries;

(3) 執行該語句后，結果顯示COUNTRIES表中所有國家名稱的平均長度為8字符。