萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

MySQL GROUP BY 聚合函數完整教學:從入門到進階實戰

GROUP BY 核心概念

GROUP BY 將結果集按指定欄位分組,配合聚合函數對每組進行統計計算。若無 GROUP BY,聚合函數作用於整個結果集(視為單一群組)。

常用聚合函數速查表

函數說明忽略 NULL範例
COUNT(expr)非 NULL 值筆數是COUNT(*) 計算行數(含 NULL)
SUM(expr)數值總和是SUM(salary)
AVG(expr)平均值是AVG(score)
MIN(expr)最小值是MIN(price)
MAX(expr)最大值是MAX(created_at)
GROUP_CONCAT(expr)字串串聯是GROUP_CONCAT(name SEPARATOR ',')
STDDEV(expr)標準差是STDDEV(score)
VARIANCE(expr)變異數是VARIANCE(score)
BIT_AND/OR/XOR位元運算是BIT_AND(flags)

基礎範例:學生成績統計

-- 建表
CREATE TABLE scores (
    id INT AUTO_INCREMENT PRIMARY KEY,
    student_name VARCHAR(50),
    subject VARCHAR(20),
    score INT
);

-- 插入測試資料
INSERT INTO scores (student_name, subject, score) VALUES
('小明', '數學', 85), ('小明', '英文', 92), ('小明', '國文', 78),
('小華', '數學', 76), ('小華', '英文', 88), ('小華', '國文', 90),
('小美', '數學', 95), ('小美', '英文', 89);

1. 基本分組統計

SELECT student_name,
       COUNT(*) AS 科目數,
       SUM(score) AS 總分,
       AVG(score) AS 平均,
       MIN(score) AS 最低,
       MAX(score) AS 最高
FROM scores
GROUP BY student_name;

結果:

student_name | 科目數 | 總分 | 平均    | 最低 | 最高
小明         | 3      | 255  | 85.00   | 78   | 92
小華         | 3      | 254  | 84.67   | 76   | 90
小美         | 2      | 184  | 92.00   | 89   | 95

2. GROUP_CONCAT 串聯應用

SELECT student_name,
       GROUP_CONCAT(subject ORDER BY score DESC SEPARATOR ' > ') AS 強項排序,
       GROUP_CONCAT(score ORDER BY score DESC SEPARATOR ', ') AS 分數分布
FROM scores
GROUP BY student_name;

結果:

小明 | 英文 > 數學 > 國文 | 92, 85, 78
小華 | 國文 > 英文 > 數學 | 90, 88, 76
小美 | 數學 > 英文        | 95, 89

進階技巧

1. 多欄位分組

SELECT department, job_title,
       COUNT(*) AS 人數,
       AVG(salary) AS 平均薪資
FROM employees
GROUP BY department, job_title
ORDER BY department, 平均薪資 DESC;

2. HAVING 過濾群組

-- 只有平均分 > 85 的學生
SELECT student_name, AVG(score) AS 平均
FROM scores
GROUP BY student_name
HAVING AVG(score) > 85;

⚠️ WHERE 過濾行,HAVING 過濾群組,執行順序:WHERE → GROUP BY → HAVING

3. WITH ROLLUP 超級聚合

SELECT student_name, subject, AVG(score)
FROM scores
GROUP BY student_name, subject WITH ROLLUP;

輸出包含:各學生各科平均 → 各學生總平均(subject=NULL) → 全班總平均(student_name=NULL, subject=NULL)

4. 條件聚合(樞紐分析)

SELECT student_name,
       SUM(CASE WHEN subject = '數學' THEN score END) AS 數學,
       SUM(CASE WHEN subject = '英文' THEN score END) AS 英文,
       SUM(CASE WHEN subject = '國文' THEN score END) AS 國文
FROM scores
GROUP BY student_name;

常見錯誤與陷阱

❌ 錯誤:SELECT 非聚合非分組欄位

-- MySQL 嚴格模式下報錯(ONLY_FULL_GROUP_BY)
SELECT student_name, subject, AVG(score)  -- subject 未分組也非聚合
FROM scores
GROUP BY student_name;

✅ 修正:加入分組、用聚合函數、或使用 ANY_VALUE()

❌ 錯誤:WHERE 使用聚合函數

-- 錯誤
SELECT student_name, AVG(score)
FROM scores
WHERE AVG(score) > 80
GROUP BY student_name;

-- 正確:用 HAVING
SELECT student_name, AVG(score)
FROM scores
GROUP BY student_name
HAVING AVG(score) > 80;

⚠️ NULL 值處理

-- COUNT(*) 計算所有行
-- COUNT(column) 忽略 NULL
-- SUM/AVG 等忽略 NULL,全 NULL 回傳 NULL

-- 若需將 NULL 視為 0 計算
SELECT AVG(COALESCE(score, 0)) FROM scores;

效能優化建議

  • 分組欄位加索引:INDEX idx_student (student_name)
  • 避免 SELECT *:只查需要欄位
  • 大資料集分頁:先聚合再 LIMIT,或用子查詢
  • 適當調整:tmp_table_size, max_heap_table_size 避免磁碟臨時表
  • 考慮物化視圖/摘要表:高頻報表預先聚合

完整 PHP 範例

<?php
$conn = new mysqli("localhost", "user", "pass", "school");

$sql = "SELECT student_name,
               COUNT(*) AS subjects,
               SUM(score) AS total,
               ROUND(AVG(score), 2) AS average,
               GROUP_CONCAT(CONCAT(subject, ':', score) ORDER BY score DESC SEPARATOR '; ') AS details
        FROM scores
        GROUP BY student_name
        HAVING COUNT(*) >= 2
        ORDER BY average DESC";

$result = $conn->query($sql);
echo "";
while ($row = $result->fetch_assoc()) {
    echo "";
}
echo "
學生科目數總分平均明細
{$row['student_name']} {$row['subjects']} {$row['total']} {$row['average']} {$row['details']}
"; $conn->close(); ?>

GROUP BY 是資料分析的瑞士刀,善用聚合函數、條件聚合與 ROLLUP,讓複雜統計變得簡單優雅!

copyright © 萬盛學電腦網 all rights reserved