本日主題：統計報表的核心——聚合函數與分組 + 子查詢入門 預計時間：2 小時 對應主教材：第 6.1 節

一、今日學習目標

  • [ ] 會用聚合函數（COUNT/SUM/AVG/MAX/MIN）
  • [ ] 會用 GROUP BY 分組統計
  • [ ] 分清楚 WHERE 與 HAVING
  • [ ] 認識子查詢（Subquery）
  • [ ] 會搭配 CASE WHEN 做分組統計

二、教材內容

2.1 聚合函數

對「一整欄」做計算，回傳單一結果：

SELECT COUNT(*) FROM employees;        -- 共幾筆（含 NULL）
SELECT COUNT(salary) FROM employees;   -- salary 非 NULL 的筆數
SELECT SUM(salary) FROM employees;     -- 薪水總和
SELECT AVG(salary) FROM employees;     -- 平均薪水
SELECT MAX(salary), MIN(salary) FROM employees;  -- 最高、最低

聚合函數對照表：

函數 功能 NULL 處理 範例
COUNT(*) 計算列數 包含 NULL COUNT(*) = 6
COUNT(欄位) 計算非 NULL 的值數 排除 NULL COUNT(salary)
SUM(欄位) 加總 忽略 NULL SUM(salary)
AVG(欄位) 平均 忽略 NULL AVG(salary)
MAX(欄位) 最大值 忽略 NULL MAX(salary)
MIN(欄位) 最小值 忽略 NULL MIN(salary)

2.2 GROUP BY 分組

把資料依某欄分組，再對每組做聚合（做報表必用）：

SELECT department,
       COUNT(*)    AS 人數,
       AVG(salary) AS 平均薪資,
       MAX(salary) AS 最高薪,
       MIN(salary) AS 最低薪,
       SUM(salary) AS 薪資總和
FROM employees
GROUP BY department;

執行結果： | department | 人數 | 平均薪資 | 最高薪 | 最低薪 | 薪資總和 | |---|---|---|---|---|---| | IT | 3 | 51666 | 55000 | 48000 | 155000 | | 會計 | 1 | 45000 | 45000 | 45000 | 45000 | | 維修 | 2 | 53500 | 60000 | 47000 | 107000 |

2.3 WHERE vs HAVING（高頻考點）

WHERE HAVING
作用時機 分組前篩選個別資料列 分組後篩選分組結果
能否用聚合 不能（如不能寫 WHERE COUNT(*)>3） 可以（如 HAVING COUNT(*)>3）
位置 GROUP BY 前面 GROUP BY 後面 
-- 先篩掉薪水<40000 的人，再依部門分組，最後只留人數>1 的部門
SELECT department, COUNT(*) AS 人數
FROM employees
WHERE salary >= 40000      -- 分組前：篩個別資料
GROUP BY department
HAVING COUNT(*) > 1;       -- 分組後：篩分組結果

常見錯誤示範：

-- ❌ 錯誤：在 WHERE 裡用聚合函數
SELECT department, COUNT(*) AS 人數
FROM employees
WHERE COUNT(*) > 1      -- ✗ WHERE 不能用聚合函數！
GROUP BY department;

-- ✓ 正確：用 HAVING
SELECT department, COUNT(*) AS 人數
FROM employees
GROUP BY department
HAVING COUNT(*) > 1;     -- ✓ HAVING 可以用聚合函數

2.4 GROUP BY 規則

  • SELECT 出現的非聚合欄位，必須也在 GROUP BY 裡。
  • 想對「分組後的統計值」篩選，要用 HAVING，不能用 WHERE。
-- ❌ 錯誤：SELECT 有 name 但 GROUP BY 沒有
SELECT department, name, COUNT(*)
FROM employees
GROUP BY department;    -- name 沒在 GROUP BY 裡，會報錯

-- ✓ 正確：SELECT 的非聚合欄位都在 GROUP BY 裡
SELECT department, COUNT(*)
FROM employees
GROUP BY department;

2.5 子查詢（Subquery）入門

子查詢就是「查詢裡面再放一個查詢」，又稱巢狀查詢：

-- 查出薪水高於全公司平均的員工
SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

子查詢執行順序圖解：
┌────────────────────────────────────────┐
│  Step 1: 先執行內層查詢                  │
│  SELECT AVG(salary) FROM employees      │
│  → 結果：51166                          │
│                                          │
│  Step 2: 把結果帶入外層                  │
│  SELECT name, salary FROM employees     │
│  WHERE salary > 51166                   │
│  → 結果：王小明(52000)、林志強(60000)、  │
│          張雅婷(55000)                   │
└────────────────────────────────────────┘

子查詢的三種常見用法：

-- 1. WHERE 裡的子查詢（最常見）
SELECT name, salary FROM employees
WHERE salary = (SELECT MAX(salary) FROM employees);
-- 找薪水最高的人

-- 2. WHERE IN 子查詢
SELECT name FROM employees
WHERE dept_id IN (SELECT id FROM departments WHERE dept_name = 'IT');
-- 找 IT 部門的人（透過部門表查 dept_id）

-- 3. FROM 裡的子查詢（衍生表）
SELECT dept_avg.department, dept_avg.avg_salary
FROM (
  SELECT department, AVG(salary) AS avg_salary
  FROM employees
  GROUP BY department
) AS dept_avg
WHERE dept_avg.avg_salary > 50000;
-- 先算每部門平均薪資，再篩出平均 > 50000 的

2.6 GROUP BY + CASE WHEN 進階統計

-- 統計各薪資等級的人數
SELECT
  CASE
    WHEN salary >= 55000 THEN '高薪'
    WHEN salary >= 48000 THEN '中薪'
    ELSE '一般'
  END AS 薪資等級,
  COUNT(*) AS 人數,
  AVG(salary) AS 該等級平均薪資
FROM employees
GROUP BY
  CASE
    WHEN salary >= 55000 THEN '高薪'
    WHEN salary >= 48000 THEN '中薪'
    ELSE '一般'
  END;

三、常見誤解

誤解 正確觀念
「COUNT(*) 和 COUNT(欄位) 一樣」 COUNT(*) 計算所有列（含 NULL），COUNT(欄位) 只計算該欄位非 NULL 的列
「WHERE 可以篩選分組後的結果」 不行，分組後要用 HAVING 篩選
「AVG 會把 NULL 算成 0」 AVG 會忽略 NULL，不會把它當 0 計算
「子查詢效能一定差」 簡單的子查詢效能還可以，但複雜的可用 JOIN 或 CTE 替代

四、面試加分小知識

  • CTE（Common Table Expression）：用 WITH 語法定義暫時結果集，比子查詢更易讀。
  • Window Function（窗口函數）：如 ROW_NUMBER()、RANK()，可在不分組的情況下做聚合計算。
  • ROLLUP：GROUP BY 的擴展，自動產生小計與總計。
  • CUBE：類似 ROLLUP，但會產生所有可能的分組組合。

五、今日練習

Q1. 算出全公司平均薪水。

Q2. 統計每個部門的人數與平均薪水。

Q3. 找出「人數超過 1 人」的部門及其人數。

Q4. 查出薪水高於全公司平均的員工（用子查詢）。

Q5. 找出薪水最高的員工姓名與薪水（用子查詢）。

參考解答 
-- A1：全公司平均薪水
SELECT AVG(salary) AS 平均薪水 FROM employees;

-- A2：每部門人數與平均薪水
SELECT department, COUNT(*) AS 人數, AVG(salary) AS 平均薪水
FROM employees GROUP BY department;

-- A3：人數超過 1 人的部門
SELECT department, COUNT(*) AS 人數
FROM employees GROUP BY department
HAVING COUNT(*) > 1;

-- A4：薪水高於平均的員工（子查詢）
SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

-- A5：薪水最高的員工（子查詢）
SELECT name, salary
FROM employees
WHERE salary = (SELECT MAX(salary) FROM employees);
-- 結果：林志強, 60000

六、延伸閱讀

七、今日檢核

  • [ ] 我會用 COUNT/SUM/AVG/MAX/MIN
  • [ ] 我會用 GROUP BY 做分組統計
  • [ ] 我分得清 WHERE 與 HAVING
  • [ ] 我會寫基本的子查詢
  • [ ] 我會用 CASE WHEN 搭配 GROUP BY

⬅️ 上一天：Day 2 ｜ 🏠 本週總覽 ｜ ➡️ 下一天：Day 4 — JOIN 多表關聯