以下建立一個簡單的「銷售資料表」，用來示範 SQL 如何分群與聚合資料。 ## 1. 建立資料表 ```sql CREATE TABLE sales ( id INT PRIMARY KEY, salesperson VARCHAR(50), department VARCHAR(50), product VARCHAR(50), quantity INT, amount DECIMAL(10, 2), sale_date DATE ); ``` ## 2. 新增範例資料 ```sql INSERT INTO sales (id, salesperson, department, product, quantity, amount, sale_date) VALUES (1, '王小明', '北區', '筆電', 2, 60000, '2024-01-05'), (2, '李小華', '北區', '手機', 3, 45000, '2024-01-10'), (3, '陳美玲', '南區', '筆電', 1, 30000, '2024-01-12'), (4, '王小明', '北區', '螢幕', 4, 20000, '2024-02-03'), (5, '李小華', '北區', '筆電', 1, 30000, '2024-02-08'), (6, '陳美玲', '南區', '手機', 2, 30000, '2024-02-15'), (7, '張大同', '南區', '螢幕', 3, 15000, '2024-03-01'); ``` 資料內容如下： | id | salesperson | department | product | quantity | amount | |---:|---|---|---|---:|---:| | 1 | 王小明 | 北區 | 筆電 | 2 | 60000 | | 2 | 李小華 | 北區 | 手機 | 3 | 45000 | | 3 | 陳美玲 | 南區 | 筆電 | 1 | 30000 | | 4 | 王小明 | 北區 | 螢幕 | 4 | 20000 | | 5 | 李小華 | 北區 | 筆電 | 1 | 30000 | | 6 | 陳美玲 | 南區 | 手機 | 2 | 30000 | | 7 | 張大同 | 南區 | 螢幕 | 3 | 15000 | --- # 3. 常見聚合函數 SQL 常使用以下函數計算資料： | 函數 | 用途 | |---|---| | `COUNT()` | 計算資料筆數 | | `SUM()` | 計算總和 | | `AVG()` | 計算平均值 | | `MAX()` | 找出最大值 | | `MIN()` | 找出最小值 | --- ## 4. 不分組，計算整張表的統計資料 ```sql SELECT COUNT(*) AS total_orders, SUM(quantity) AS total_quantity, SUM(amount) AS total_amount, AVG(amount) AS average_amount, MAX(amount) AS max_amount, MIN(amount) AS min_amount FROM sales; ``` 結果概念如下： | total_orders | total_quantity | total_amount | average_amount | max_amount | min_amount | |---:|---:|---:|---:|---:|---:| | 7 | 16 | 230000 | 32857.14 | 60000 | 15000 | 這裡沒有使用 `GROUP BY`，所以整張資料表會被視為一組。 --- # 5. 使用 `GROUP BY` 分組 ## 依部門分組，統計各部門銷售額 ```sql SELECT department, COUNT(*) AS order_count, SUM(quantity) AS total_quantity, SUM(amount) AS total_amount, AVG(amount) AS average_amount FROM sales GROUP BY department; ``` 結果： | department | order_count | total_quantity | total_amount | average_amount | |---|---:|---:|---:|---:| | 北區 | 4 | 10 | 155000 | 38750 | | 南區 | 3 | 6 | 75000 | 25000 | `GROUP BY department` 的意思是： > 將 `department` 欄位值相同的資料歸為同一組，再對每組進行統計。 --- ## 依產品分組 ```sql SELECT product, COUNT(*) AS order_count, SUM(quantity) AS total_quantity, SUM(amount) AS total_amount FROM sales GROUP BY product; ``` 結果： | product | order_count | total_quantity | total_amount | |---|---:|---:|---:| | 筆電 | 3 | 4 | 120000 | | 手機 | 2 | 5 | 75000 | | 螢幕 | 2 | 7 | 35000 | --- # 6. 多欄位分組 可以同時依照多個欄位分組。 ```sql SELECT department, product, SUM(quantity) AS total_quantity, SUM(amount) AS total_amount FROM sales GROUP BY department, product; ``` 這會按照「部門 + 產品」的組合分組，例如： | department | product | total_quantity | total_amount | |---|---|---:|---:| | 北區 | 筆電 | 3 | 90000 | | 北區 | 手機 | 3 | 45000 | | 北區 | 螢幕 | 4 | 20000 | | 南區 | 筆電 | 1 | 30000 | | 南區 | 手機 | 2 | 30000 | | 南區 | 螢幕 | 3 | 15000 | --- # 7. `WHERE` 與 `GROUP BY` 一起使用 `WHERE` 是在分組前先篩選資料。 例如，只統計 2024 年 2 月之後的資料： ```sql SELECT department, SUM(amount) AS total_amount FROM sales WHERE sale_date >= '2024-02-01' GROUP BY department; ``` SQL 執行概念： 1. 先使用 `WHERE` 篩選資料 2. 再使用 `GROUP BY` 分組 3. 最後使用 `SUM()` 等聚合函數計算 --- # 8. 使用 `HAVING` 篩選分組後的結果 `HAVING` 用來篩選「分組與聚合後」的結果。 例如，只顯示總銷售額超過 100,000 的部門： ```sql SELECT department, SUM(amount) AS total_amount FROM sales GROUP BY department HAVING SUM(amount) > 100000; ``` 結果： | department | total_amount | |---|---:| | 北區 | 155000 | --- ## `WHERE` 與 `HAVING` 的差異 | 語法 | 使用時機 | 範例 | |---|---|---| | `WHERE` | 分組前篩選原始資料 | `WHERE amount > 20000` | | `HAVING` | 分組後篩選統計結果 | `HAVING SUM(amount) > 100000` | 例如： ```sql SELECT department, SUM(amount) AS total_amount FROM sales WHERE amount >= 20000 GROUP BY department HAVING SUM(amount) > 100000; ``` 這段 SQL 的意思是： 1. 先找出單筆金額至少 20,000 的資料 2. 按部門分組 3. 計算每個部門的銷售總額 4. 只保留總額超過 100,000 的部門 --- # 9. 搭配 `ORDER BY` 排序 依照銷售額由高到低排列： ```sql SELECT department, SUM(amount) AS total_amount FROM sales GROUP BY department ORDER BY total_amount DESC; ``` 也可以直接使用欄位編號： ```sql ORDER BY 2 DESC; ``` 但使用欄位別名通常更容易閱讀。 --- # 10. 常見語法結構 分組與聚合的常見 SQL 結構如下： ```sql SELECT 分組欄位, 聚合函數(欄位) AS 別名 FROM 表格 WHERE 分組前篩選條件 GROUP BY 分組欄位 HAVING 分組後篩選條件 ORDER BY 排序欄位; ``` 例如： ```sql SELECT salesperson, COUNT(*) AS order_count, SUM(amount) AS total_amount FROM sales WHERE sale_date >= '2024-01-01' GROUP BY salesperson HAVING SUM(amount) >= 50000 ORDER BY total_amount DESC; ``` 這段會： - 依業務人員分組 - 計算每位業務的訂單數 - 計算每位業務的銷售總額 - 只顯示銷售總額至少 50,000 的人員 - 按銷售總額由高到低排序 ## 重點整理 - `GROUP BY`：將資料分成不同群組 - `COUNT()`：計算筆數 - `SUM()`：計算總和 - `AVG()`：計算平均值 - `MAX()` / `MIN()`：找最大值與最小值 - `WHERE`：分組前篩選資料 - `HAVING`：分組後篩選統計結果 - `ORDER BY`：排序查詢結果