《Oracle 入門教程》第 08 篇 分組匯總

作者: 不剪發(fā)的Tony老師
畢業(yè)于北京航空航天大學(xué)，十多年數(shù)據(jù)庫管理與開發(fā)經(jīng)驗，目前在一家全球性的金融公司從事數(shù)據(jù)庫架構(gòu)設(shè)計。CSDN學(xué)院簽約講師以及GitChat專欄作者。csdn上的博客收藏于以下地址：https://tonydong.blog.csdn.net


文章目錄

        8.1 聚合函數(shù)
        8.2 數(shù)據(jù)分組
        8.3 分組過濾

本篇我們介紹 Oracle 中的數(shù)據(jù)分組匯總和分組后的數(shù)據(jù)過濾。
8.1 聚合函數(shù)

聚合函數(shù)（aggregate function）可以針對一組數(shù)據(jù)進行匯總并返回一個結(jié)果。例如，AVG(x) 可以計算一組 x 的平均值。常見的聚合函數(shù)包括：

    AVG - 計算一組值的平均值。
    COUNT - 統(tǒng)計一組值的數(shù)量。
    MAX - 計算一組值的最大值。
    MIN - 計算一組值的最小值。
    SUM - 計算一組值的和值。
    LISTAGG - 連接一組字符串。

我們首先來看一個 AVG 函數(shù)的例子：

SELECT AVG(salary) AS "平均月薪"
FROM employee;

平均月薪|
-------|
   9832|


以上查詢返回了全體員工的平均月薪。

關(guān)于聚合函數(shù)，我們需要注意兩點：

    在參數(shù)中使用DISTINCT選項可以在計算之前排除重復(fù)數(shù)據(jù)；
    聚合函數(shù)在計算時，忽略輸入為 NULL 值的行。

例如：

SELECT COUNT(*) AS "員工總數(shù)",
       COUNT(DISTINCT sex) AS "不同性別",
       COUNT(bonus) AS "擁有獎金"
FROM employee;

員工總數(shù)|不同性別|擁有獎金|
-------|-------|--------|
     25|      2|       9|


其中，COUNT(*) 返回了所有的記錄數(shù)（員工總數(shù)），COUNT(DISTINCT sex) 返回了性別的種類，COUNT(bonus) 返回了擁有獎金的員工數(shù)量。

MIN 和 MAX 函數(shù)分別返回數(shù)據(jù)中的最小值和最大值，SUM 函數(shù)計算所有數(shù)據(jù)的和值。例如：

SELECT MIN(salary) AS "最低月薪",
       MAX(salary) AS "最高月薪",
       SUM(salary) AS "月薪總計"
FROM employee;

最低月薪|最高月薪 |月薪總計  |
-------|--------|----------|
   4000|   30000|    245800|



LISTAGG 函數(shù)可以將一組字符串通過指定分隔符進行合并，例如：

SELECT LISTAGG(emp_name, ';') AS "員工列表"
FROM employee
WHERE dept_id = 1;

員工列表      |
-------------|
劉備;關(guān)羽;張飛|


LISTAGG 函數(shù)還提供了排序、去重、合并結(jié)果超長處理等功能，具體可以參考這篇文章。

    ??除了以上常見的聚合函數(shù)之外，Oracle 還支持例如方差函數(shù)、標(biāo)準差函數(shù)等更多的聚合函數(shù)，需要時可以參考官方文檔。

8.2 數(shù)據(jù)分組

在前面的示例中，我們將所有的數(shù)據(jù)作為一個整體（分組），聚合函數(shù)只返回了一個結(jié)果。接下來，我們結(jié)合GROUP BY子句，將數(shù)據(jù)分成不同的組，然后分別計算各個組內(nèi)的數(shù)據(jù)匯總。

假如我們想要知道每個部門的平均薪水，而不是所有員工的平均值，可以使用以下語句：

SELECT dept_id AS "部門編號",
       AVG(salary) AS "平均月薪"
FROM employee
GROUP BY dept_id;

部門編號|平均月薪                                 |
-------|-----------------------------------------|
      1| 26666.6666666666666666666666666666666667|
      2| 13166.6666666666666666666666666666666667|
      3|                                     9000|
      4|7577.777777777777777777777777777777777778|
      5|                                   5012.5|


其中，GROUP BY子句指定了按照部門進行分組，然后通過 AVG 函數(shù)計算每個部門的平均薪水。

如果GROUP BY分組字段存在 NULL 值，多個 NULL 值將被分為一個組。例如，以下語句按照不同獎金值統(tǒng)計員工的數(shù)量：

SELECT bonus AS "獎金",
       COUNT(*) AS "員工數(shù)量"
FROM employee
GROUP BY bonus;

獎金 |員工數(shù)量|
-----|-------|
 8000|      1|
 1500|      1|
 5000|      2|
     |     16|
 6000|      1|
10000|      3|
 2000|      1|



從查詢結(jié)果可以看出，16 個員工沒有獎金；但他們都被分組同一個組中，而不是多個不同的組。

在使用分組匯總時，一個常見的錯誤就是SELECT列表中包含了既不是聚合函數(shù)、也不屬于GROUP BY子句的字段，例如：

-- 錯誤示例
SELECT dept_id,
       emp_name,
       AVG(salary)
FROM employee
GROUP BY dept_id;

SQL 錯誤 [979] [42000]: ORA-00979: 不是 GROUP BY 表達式



以上語句的錯誤在于 emp_name 既不是分組字段，也不是聚合函數(shù)。查詢按照部門進行分組，但是每個部門包含多個員工，數(shù)據(jù)庫無法知道需要顯示哪個員工的姓名。這是一個邏輯錯誤。

我們不僅可以基于一個字段進行分組，也可以依據(jù)多個字段將數(shù)據(jù)分成更多的組。例如，以下語句同時按照部門和性別統(tǒng)計員工的數(shù)量：

SELECT dept_id AS "部門編號",
       sex AS "性別",
       COUNT(*) AS "員工數(shù)量"
FROM employee
GROUP BY dept_id, sex;

部門編號|性別|員工數(shù)量|
-------|----|-------|
      1|男  |      3|
      2|男  |      3|
      4|女  |      1|
      4|男  |      8|
      5|男  |      8|
      3|女  |      2|



在 SQL 查詢中，如果同時存在WHERE子句和GROUP BY子句，要求WHERE子句出現(xiàn)在GROUP BY子句之前。因此，WHERE子句無法對分組后的結(jié)果進行過濾。例如，以下錯誤示例想要查找員工數(shù)量大于 5 人的部門：

-- 錯誤示例
SELECT dept_id,
       COUNT(*)
FROM employee
WHERE COUNT(*) > 5
GROUP BY dept_id;

SQL 錯誤 [934] [42000]: ORA-00934: 此處不允許使用分組函數(shù)



錯誤原因在于WHERE子句執(zhí)行時還沒有進行分組計算，它只能基于分組之前的數(shù)據(jù)行進行過濾。如果需要對分組后的結(jié)果進行過濾，需要使用HAVING子句。
8.3 分組過濾

如果要查看員工數(shù)量大于 10 的部門編號，可以使用HAVING子句實現(xiàn)：

SELECT dept_id,
       COUNT(*) AS headcount
FROM employee
GROUP BY dept_id
HAVING COUNT(*) > 5;

DEPT_ID|HEADCOUNT|
-------|---------|
      4|        9|
      5|        8|



查詢首先按照部門進行分組，計算每個部門的員工數(shù)量；然后使用HAVING子句過濾員工數(shù)量大于 5 人的部門。

我們可以同時使用WHERE子句進行數(shù)據(jù)行的過濾，使用HAVING進行分組結(jié)果的過濾。例如想要查看薪水大于 10000 的員工數(shù)量大于 2 人的部門，可以使用以下查詢：

SELECT dept_id,
       COUNT(*)
FROM employee
WHERE salary > 10000
GROUP BY dept_id
HAVING COUNT(*) > 2;

DEPT_ID|COUNT(*)|
-------|--------|
      1|       3|


首先，WHERE子句過濾薪水大于 10000 的所有員工；然后按照部門進行分組，計算每個部門的員工數(shù)量；最后，使用HAVING子句過濾這類員工數(shù)量大于 2 人的部門。

最后，我們來看一下 SQL 標(biāo)準的語法：

SELECT column1,
       column2,
       aggregate_function(column3)
FROM table_name
[WHERE conditions]
[GROUP BY column1, column2
[HAVING conditions] ]
[ORDER BY ...]
[OFFSET m {ROW | ROWS}]
[FETCH { FIRST | NEXT } [ num_rows | n PERCENT ] { ROW | ROWS } { ONLY | WITH TIES }];



對于以上各個子句，Oracle 的邏輯執(zhí)行順序為 FROM、WHERE、SELECT、GROUP BY、HAVING、ORDER BY 以及 OFFSET/FECTH。

Oracle 中的 GROUP BY 子句還支持一些高級分組選項，我們將會在第 13 篇中進行介紹。