【MySQL】使用分組查詢並搭配聚合函數來統計資料

日期：

https://www.flickr.com/photos/8058098@N07/2718571627/

上一篇介紹的內建函數，是對查詢結果中的欄位值做運算，產生更有意義的值。而本文將介紹「聚合函數」，它的用途在於統計，例如加總、平均與計數。可聯想成將所有查詢結果的特定欄位值聚集起來做計算。

另外也會介紹分組查詢，根據欄位值進行分組，再搭配聚合函數做統計。最後提供一些例題。

此篇內容轉載自本人在 iThome 的文章。



一、測試資料

本文使用的測試資料，參考自此影片。

部門資料表。

員工資料表，其中包含部門編號。

客戶資料表。

銷售資料表，其中包含員工編號與客戶編號。


二、聚合函數

（一）計數

COUNT 可用來計算查詢結果中的資料筆數。

以下範例是取得員工數量。

SELECT COUNT(*)
FROM `employee`;

若將 COUNT 中的 * 參數改為欄位名稱，則代表要計算該欄位值不為 null 的資料筆數。以下範例是計算有銷售紀錄的女員工數量。

SELECT COUNT(`sales`.`total_sales`)
FROM `employee`
LEFT JOIN `sales` ON `sales`.`emp_id` = `employee`.`emp_id`
WHERE `gender` = "F";

以下範例是計算銷售過的不重複客戶數量。

SELECT COUNT(DISTINCT `client_id`)
FROM `sales`;

（二）加總

SUM 可用來計算查詢結果中，指定數值欄位的加總。

以下範例是計算對於客戶「阿狗」的總銷售金額。

SELECT SUM(`total_sales`)
FROM `sales`
JOIN `client` ON `sales`.`client_id` = `client`.`client_id`
WHERE `client_name` = "阿狗";

（三）平均

AVG 可用來計算查詢結果中，指定數值欄位的平均。

以下範例是計算平均每次的銷售金額。

SELECT AVG(`total_sales`)
FROM `sales`;

（四）最大與最小值

MAX 與 MIN 分別可取得查詢結果中，指定數值或日期欄位的最大值或最小值。

以下範例是取得薪資最高的數字。

SELECT MAX(`salary`)
FROM `employee`;

以下範例是取得最年長的員工生日。

SELECT MIN(`birthday`)
FROM `employee`;

三、分組查詢

（一）分組

上一節的聚合函數範例，是將所有的查詢結果當成統計對象，並且只回傳一個值作為結果。而本文的分組查詢，是以欄位值為基礎，將查詢結果進行分組，搭配聚合函數針對各組分別統計。

以下範例是計算各部門的人數。

SELECT `employee`.`dept_id`, `dept_name`, COUNT(*) AS `headcount`
FROM `employee`
JOIN `dept` ON `employee`.`dept_id` = `dept`.`dept_id`
GROUP BY `employee`.`dept_id`;

查詢結果如下。

dept_iddept_nameheadcount
1研發2
2行政1
3資訊2

使用 GROUP BY 語法指定用來分組的欄位。至於要用 SELECT 語法顯示出來的欄位，通常是採用以下幾種：

  • 聚合函數結果，此處為「COUNT」。
  • 分組欄位，例如部門編號（dept_id）。
  • 依賴於分組欄位的其他欄位，例如部門名稱（dept_name）是跟著部門編號這個主鍵。

（二）篩選

若想對分組查詢的結果做條件篩選，需使用 HAVING 語法。以下是延續上述的範例，篩選出人數大於 1 人的部門名稱。

SELECT `dept`.`dept_name`, COUNT(*)  AS `headcount`
FROM `employee`
JOIN `dept` ON `employee`.`dept_id` = `dept`.`dept_id`
GROUP BY `employee`.`dept_id`
HAVING `headcount` > 1;

HAVING 與一般查詢的 WHERE 都是用來進行條件篩選。兩者的差別在於，HAVING 是針對分組後的資料，而WHERE 是針對分組前，因此被排除的資料將不參與分組。

以下的例子是計算各部門中，薪水達 38000 的員工人數。若部門中只有 1 人達到該薪水，則不列出該部門。

SELECT `dept`.`dept_name`, COUNT(*) AS `headcount`
FROM `employee`
JOIN `dept` ON `employee`.`dept_id` = `dept`.`dept_id`
WHERE `salary` >= 38000
GROUP BY `employee`.`dept_id`
HAVING `headcount` > 1;

查詢結果如下。

dept_nameheadcount
研發2

四、例題

最後提供其他範例，讓讀者更了解分組查詢的用法。

（一）各部門的最低薪資

dept_namemin_salary
研發50000
行政29000
資訊35000

（二）每月生日人數

此處透過一般函數 MONTH 取得月份，並取了別名「month」，根據其值來分組。

monthheadcount
92
102
121

（三）每位客戶帶來的總銷售額

client_nametotal_sales
阿狗79800
阿貓24000
露西24000
艾瑞克87940

（四）總銷售額未達 5 萬的部門

dept_nametotal_sales
行政24000
資訊33800

（五）各部門對每位客戶的總銷售額

本文的測試資料中，有 3 個部門、5 位客戶。此處會用這兩個欄位來分組，若測試資料夠豐富，最多可達 3 * 5 = 15 種組合。

dept_nameclient_nametotal_sales
研發阿狗70000
研發艾瑞克87940
行政阿貓24000
資訊阿狗9800
資訊露西24000

上一篇：【MySQL】數值、字串、日期與判斷函數

下一篇：【MySQL】子查詢（subquery）與視圖（view）

留言

張貼留言