SQL - 使用分組函式

上一頁
下一頁

使用分組函式報告聚合資料

SQL 有許多預定義的聚合函式，可用於編寫查詢以生成此類資訊。GROUP BY 子句指定在聚合資訊時如何對資料表中的行進行分組，而 HAVING 子句則過濾掉不屬於指定組的行。

聚合函式執行各種操作，例如計算表中的所有行、對列的資料求平均值以及對數字資料求和。聚合還可以搜尋表以查詢列中的最高“MAX”或最低“MIN”值。與其他型別的查詢一樣，您可以使用 WHERE 子句限制或過濾掉這些函式作用的行。例如，如果經理需要知道組織中有多少員工，可以使用名為 COUNT(*) 的聚合函式來生成此資訊。下面 SELECT 語句中顯示的 COUNT(*) 函式計算表中的所有行。

SELECT COUNT(*)
FROM employees;

  COUNT(*)
----------
        24

COUNT(*) 函式的結果表是來自單行的單列，稱為標量結果或值。請注意，結果表有一個列標題，它對應於 SELECT 子句中指定的聚合函式的名稱。

一些常用的聚合函式如下所示：

SUM( [ALL | DISTINCT] expression )

AVG( [ALL | DISTINCT] expression )

COUNT( [ALL | DISTINCT] expression )

COUNT(*)

MAX(expression)

MIN(expression)

ALL 和 DISTINCT 關鍵字是可選的，它們的作用與您學習編寫的 SELECT 子句相同。ALL 關鍵字是允許選項時的預設值。語法中列出的表示式可以是常量、函式或任何列名、常量和函式的組合，這些組合透過算術運算子連線。但是，聚合函式最常與列名一起使用。除 COUNT 函式外，所有聚合函式都不考慮 NULL 值。

使用聚合時，必須理解並遵循兩條規則

  • 聚合函式既可以在 SELECT 子句中使用，也可以在 HAVING 子句中使用（HAVING 子句將在本章後面介紹）。

  • 聚合函式不能在 WHERE 子句中使用。違反此規則將產生 Oracle ORA-00934 group function is not allowed here 錯誤訊息。

示例

下面的 SELECT 查詢計算組織中員工的數量。

SELECT COUNT(*) Count
FROM employees;

COUNT
-----
   24

下面的 SELECT 查詢返回組織中員工工資的平均值。

SELECT AVG(Salary) average_sal
FROM employees;

AVERAGE_SAL
-----------
      15694

下面的 SELECT 查詢返回組織中員工工資的總和。

SELECT SUM(Salary) total_sal
FROM employees;

TOTAL_SAL
---------
    87472

下面的 SELECT 查詢返回組織中員工最早和最新的入職日期。

SELECT MIN (hire_date) oldest, MAX (hire_date) latest
FROM employees;

OLDEST		LATEST
---------	-----------
16-JAN-83	01-JUL-2012

GROUP BY

聚合函式通常與 GROUP BY 子句一起使用。GROUP BY 子句使您可以使用聚合函式來回答更復雜的問題，例如：

每個部門員工的平均工資是多少？

每個部門有多少員工？

有多少員工正在從事某個特定專案？

GROUP BY 函式根據列建立資料組，並且僅聚合組內資訊。分組標準由 GROUP BY 子句中指定的列定義。按照此層次結構，資料首先在組中組織，然後 WHERE 子句限制每個組中的行。

使用 GROUP BY 子句的指導原則

(1) GROUP BY 函式中使用的所有相關列或列都必須構成分組的基礎，因此也必須包含在 GROUP BY 子句中。

SELECT	DEPARTMENT_ID, SUM(SALARY)
FROM employees;

DEPARTMENT_ID,
*
ERROR at line 2:
ORA-00937: not a single-group group function

(2) GROUP BY 子句不支援使用列別名，而應使用實際名稱。

(3) GROUP BY 子句只能與 SUM、AVG、COUNT、MAX 和 MIN 等聚合函式一起使用。如果與單行函式一起使用，Oracle 將丟擲“ORA-00979: not a GROUP BY expression”異常。

(4) 聚合函式不能在 GROUP BY 子句中使用。Oracle 將返回錯誤訊息“ORA-00934: group function not allowed here”。

下面的查詢列出了每個部門工作的員工數量。

SELECT  DEPARTMENT_ID,  COUNT (*)
FROM employees
GROUP BY DEPARTMENT_ID;

類似地，下面的查詢用於查詢每個部門中各個職位 ID 的工資總和。請注意，組是根據部門和職位 ID 建立的。因此，它們出現在 GROUP BY 子句中。

SELECT DEPARTMENT_ID, JOB_ID, SUM (SAL)
FROM employees
GROUP BY DEPARTMENT_ID, JOB_ID;

下面的查詢也產生相同的結果。請注意，分組基於部門 ID 和職位 ID 列，但不用於顯示目的。

SELECT SUM (SALARY)
FROM employees
GROUP BY DEPARTMENT_ID, JOB_ID;

在聚合函式中使用 DISTINCT 和 ALL 關鍵字

透過在輸入引數中指定 DISTINCT 關鍵字，GROUP BY 函式僅考慮列的唯一值進行聚合。透過在輸入引數中指定 ALL 關鍵字，GROUP BY 函式考慮列的所有值進行聚合，包括 NULL 值和重複值。ALL 是預設規範。

HAVING 子句

HAVING 子句用於聚合函式，其方式與 WHERE 子句用於列名和表示式的方式相同。本質上，HAVING 和 WHERE 子句執行相同的操作，即根據條件過濾掉結果表中包含的行。雖然看起來 HAVING 子句過濾掉組，但實際上並非如此。相反，HAVING 子句過濾行。

當組的所有行都被消除時，組也會被消除。總而言之，WHERE 和 HAVING 子句之間最重要的區別是：

WHERE 子句用於在分組操作之前過濾行（即在計算聚合函式之前）。

HAVING 子句在分組操作之後過濾行（即在計算聚合函式之後）。

SELECT JOB_ID,	SUM (SALARY)
FROM employees
GROUP BY JOB_ID
HAVING SUM (SALARY) > 10000;

HAVING 子句是一個條件選項，它與 GROUP BY 子句選項直接相關，因為 HAVING 子句根據 GROUP BY 子句的結果從結果表中消除行。

SELECT department_id, AVG(Salary)
FROM employees
HAVING AVG(Salary) > 33000;
ERROR at line 1:  ORA-00937: not a single-group group function
列印頁面
上一頁
下一頁
廣告