本文 AI 產出，尚未審核

MySQL 課程 – 聚合函式與分組

主題：GROUP BY 分組

簡介

在資料庫查詢中，分組（GROUP BY） 是將多筆資料依指定欄位彙整成「群組」的關鍵技巧。
透過分組，我們可以搭配聚合函式（如 COUNT()、SUM()、AVG()）一次算出每個類別的統計資訊，而不必寫繁瑣的迴圈或額外程式碼。

對於 初學者 來說，掌握 GROUP BY 能快速從雜亂的資料表中抽取有價值的資訊；
對 中階開發者，則是設計報表、儀表板、資料分析等功能的基礎。
本篇文章將從概念說明、實作範例、常見陷阱與最佳實踐，帶你一步步熟悉 MySQL 的分組操作。

核心概念

1. GROUP BY 的基本語法

SELECT 欄位1, 聚合函式(欄位2) AS 別名
FROM 表格名稱
WHERE 條件
GROUP BY 欄位1
ORDER BY 別名 DESC;
  • SELECT：必須列出 所有 出現在 GROUP BY 子句中的欄位，或是使用聚合函式的欄位。
  • WHERE：在分組前先篩選資料，減少不必要的計算。
  • GROUP BY：依指定欄位將資料分成多個群組。
  • ORDER BY（可選）：對分組結果排序。

⚠️ GROUP BY 之後，不能直接 取出未聚合或未列於 GROUP BY 的欄位，除非使用 ONLY_FULL_GROUP_BY 模式關閉（不建議）。

2. 常見聚合函式

函式 功能 範例
COUNT(*) 計算群組內的總筆數 COUNT(*) AS total
SUM(col) 加總數值欄位 SUM(price) AS revenue
AVG(col) 計算平均值 AVG(score) AS avg_score
MAX(col) / MIN(col) 取得最大 / 最小值 MAX(date) AS latest
GROUP_CONCAT(col) 合併字串，預設以逗號分隔 GROUP_CONCAT(name) AS members

3. 多欄位分組

可以同時依多個欄位分組，形成「二維」或「多維」的彙總結果。

SELECT department, YEAR(hire_date) AS year_hired,
       COUNT(*) AS employee_cnt,
       AVG(salary) AS avg_salary
FROM employees
WHERE status = 'active'
GROUP BY department, YEAR(hire_date)
ORDER BY department, year_hired;
  • 這裡先把 hire_date 轉成年份，再與 department 共同分組，得到每個部門每年的員工統計。

4. HAVING 子句：分組後的過濾

WHERE 在分組前過濾，HAVING 則在分組之後過濾聚合結果。

SELECT product_id, SUM(quantity) AS total_qty
FROM sales
GROUP BY product_id
HAVING total_qty > 1000;   -- 只保留銷售量超過 1000 的商品

小技巧：HAVING 常與 COUNT(*) 搭配，用來找出「出現次數」超過門檻的資料。

5. ROLLUP 與 CUBE（進階分組）

  • WITH ROLLUP：在結果最後自動加上一筆「小計」與「總計」列。
  • WITH CUBE（MySQL 8.0+）：產生所有可能的子集合小計。
SELECT region, product_category,
       SUM(sales) AS total_sales
FROM sales_data
GROUP BY region, product_category WITH ROLLUP;
  • 結果會包含每個 region 的小計、每個 product_category 的小計，以及全表的總計。

程式碼範例

以下示範 5 個實務上常用的 GROUP BY 範例，均附上說明註解。

範例 1：計算每位作者的文章數

-- 取得每位作者發表的文章總數
SELECT author_id,
       COUNT(*) AS article_count
FROM articles
WHERE publish_date >= '2024-01-01'
GROUP BY author_id
ORDER BY article_count DESC;
  • WHERE 先挑出 2024 年以後的文章，減少不必要的計算。
  • ORDER BY 依發文數從高到低排列。

範例 2：每月營收與平均訂單金額

SELECT DATE_FORMAT(order_date, '%Y-%m') AS month,
       SUM(total_amount) AS monthly_revenue,
       AVG(total_amount) AS avg_order_value,
       COUNT(*) AS order_cnt
FROM orders
WHERE order_status = 'completed'
GROUP BY month
ORDER BY month;
  • DATE_FORMAT 把日期切成「年-月」的字串，作為分組依據。
  • 同時計算總營收、平均訂單金額與訂單筆數。

範例 3：找出銷售量前 5 名的商品

SELECT product_name,
       SUM(quantity) AS total_sold
FROM order_items
GROUP BY product_name
HAVING total_sold > 0               -- 排除從未銷售的商品
ORDER BY total_sold DESC
LIMIT 5;
  • HAVING 先把 total_sold 為 0 的商品過濾掉，避免 LIMIT 把它算進前 5。
  • LIMIT 直接限制結果筆數，適合產生排行榜。

範例 4：部門與職稱的員工人數小計

SELECT department,
       job_title,
       COUNT(*) AS employee_cnt
FROM employees
WHERE status = 'active'
GROUP BY department, job_title WITH ROLLUP;
  • WITH ROLLUP 會產生兩筆額外的列：
    1. 每個 department 的小計（job_title 為 NULL）
    2. 整張表的總計（兩欄皆為 NULL）

範例 5：使用 GROUP_CONCAT 合併同班同學姓名

SELECT class_id,
       GROUP_CONCAT(student_name ORDER BY student_name SEPARATOR ', ') AS members
FROM class_enrollment
GROUP BY class_id;
  • GROUP_CONCAT 把同一班級的學生姓名串成以逗號分隔的字串，ORDER BY 確保姓名排序。
  • 常用於產生「名單」或「標籤」的顯示。

常見陷阱與最佳實踐

陷阱 說明 建議的解決方式
忘記 GROUP BY 欄位 SELECT 中出現未聚合且未列於 GROUP BY 的欄位，會觸發 ONLY_FULL_GROUP_BY 錯誤（MySQL 5.7+ 預設開啟）。 必須 把所有非聚合欄位加入 GROUP BY，或使用聚合函式（如 MAX()）取得代表值。
HAVING 與 WHERE 用錯位置 把聚合條件寫在 WHERE，會因為聚合尚未產生而失效。 把聚合條件移到 HAVING，WHERE 僅保留原始資料的過濾條件。
分組後的 NULL 值 GROUP BY 會把 NULL 視為同一組，可能導致不預期的合併。 若要排除 NULL，可在 WHERE 加上 col IS NOT NULL；或在 SELECT 使用 IFNULL(col, '未指定')。
大量資料分組性能低 沒有索引的欄位做 GROUP BY 會全表掃描，耗時長。 為分組欄位建立 索引（單欄或複合索引），或使用 物化視圖（MySQL 8.0+）預先彙總。
ROLLUP 產生的 NULL 判斷 ROLLUP 產生的合計列會把分組欄位設為 NULL，容易在程式碼中誤判。 在呈現層面使用 IFNULL(col, '小計') 或 CASE 判斷。

最佳實踐：

  1. 先過濾：盡可能在 WHERE 中先把不需要的資料排除，減少分組的計算量。
  2. 適度索引：對常用的分組欄位（或其組合）建立索引，可大幅提升查詢效能。
  3. 明確別名：聚合結果最好給予易讀的別名（AS total_sales），方便後續程式碼使用。
  4. 分頁注意：若要對分組結果分頁，必須在子查詢或 CTE 中先完成 GROUP BY，再在外層使用 LIMIT/OFFSET。
  5. 測試與分析：使用 EXPLAIN 觀察執行計畫，確保 MySQL 正在使用索引而非全表掃描。

實際應用場景

場景 需求 GROUP BY 的角色
銷售報表 每月、每商品、每通路的營收與銷量 把 order_date、product_id、channel 作為分組鍵，搭配 SUM()、COUNT() 計算。
網站分析 計算每日活躍使用者（DAU）與新註冊數 以 login_date、user_id 分組，COUNT(DISTINCT user_id) 取得唯一使用者數。
人事管理 各部門員工的人數、平均薪資與最高職等 以 department_id 分組，COUNT(*)、AVG(salary)、MAX(level) 同時呈現。
教育平台 每堂課的註冊學生名單與總人數 以 class_id 分組，GROUP_CONCAT(student_name) 產生名單，COUNT(*) 計算人數。
物流追蹤 各倉庫每日出貨筆數與總重量 以 warehouse_id、DATE(shipping_time) 分組，COUNT(*)、SUM(weight) 統計。

在上述每個案例中，GROUP BY 讓我們僅用一條 SQL 語句即可完成彙總、統計與分層的需求，省去大量程式碼與記憶體運算。

總結

  • GROUP BY 是 資料彙總 的核心工具，配合聚合函式可一次算出多種統計資訊。
  • 必須遵守 ONLY_FULL_GROUP_BY 規則：所有非聚合欄位必須列於 GROUP BY。
  • HAVING 用於分組後的過濾，WHERE 用於分組前的篩選，兩者不可混用。
  • 透過 索引、ROLLUP、CUBE 等進階功能，可提升效能與報表可讀性。
  • 在實務上，GROUP BY 幾乎遍佈所有報表、分析與業務決策的場景，熟練掌握它是成為 MySQL 專家的必經之路。

掌握分組，讓資料說話。只要了解概念、避免常見陷阱，並善用索引與聚合函式，你就能在 MySQL 中快速產出精確且高效的統計報表。祝你玩轉 GROUP BY，開發出更具洞察力的應用程式！