7.2 数据分组：GROUP BY

     在实际的数据分析中，仅仅计算整个表的统计值往往不够，我们需要按照某些维度对数据进行分组统计。比如：计算每个科目的平均分、统计每个学生的总成绩、按部门统计员工人数等。GROUP BY子句就是SQL中实现数据分组的核心工具，它能够将数据按照一个或多个列进行分组，然后对每个组分别应用聚合函数。

一、GROUP BY的核心价值

为什么需要分组？

1. 多维分析：按照不同维度（如科目、学生、部门）进行统计

2. 对比分析：比较各组之间的差异（如各科平均分对比）

3. 汇总报表：生成分类汇总报表（如按季度销售统计）

4. 数据洞察：发现数据中的模式和规律

典型应用场景

• 电商平台：统计每个类别的商品销售额

• 学校系统：计算每个班级的平均成绩

• 人力资源：按部门统计员工人数和平均工资

• 网站分析：按小时统计用户访问量

二、GROUP BY基础语法

GROUP BY子句通常与聚合函数一起使用，位于WHERE之后、ORDER BY之前：

SELECT 分组列1, 分组列2, ..., 聚合函数(列)
FROM 表名
[WHERE 条件]          -- 分组前筛选
GROUP BY 分组列1, 分组列2, ...
[HAVING 分组后条件]   -- 分组后筛选
[ORDER BY 排序];

执行顺序理解：

1. FROM：定位表

2. WHERE：筛选行（分组前过滤）

3. GROUP BY：将数据分组

4. 聚合函数：对每个组进行计算

5. HAVING：筛选分组（分组后过滤）

6. SELECT：选择输出列

7. ORDER BY：排序结果

继续使用上一节的scores成绩表进行演示：

-- 表结构回顾
CREATE TABLE scores (
    id INT PRIMARY KEY AUTO_INCREMENT,
    student_id INT NOT NULL,
    student_name VARCHAR(50) NOT NULL,
    subject VARCHAR(50) NOT NULL,
    score DECIMAL(5,2),
    exam_date DATE NOT NULL
);
-- 表中包含15条成绩记录，涉及5名学生和3个科目

三、基础分组示例

1. 单列分组：按科目统计

计算每个科目的考试次数、平均分、最高分和最低分：

SELECT 
    subject,
    COUNT(*) AS exam_count,
    COUNT(score) AS valid_score_count,  -- 忽略NULL
    ROUND(AVG(score), 2) AS avg_score,
    MAX(score) AS max_score,
    MIN(score) AS min_score
FROM scores
GROUP BY subject;

结果解释：

• 数据按照subject列的值（数学、英语、物理）被分成3组

• 每组分别计算COUNT、AVG等聚合函数

• 输出每个组一行结果

2. 按学生分组

计算每个学生的总成绩、平均分和考试次数：

SELECT 
    student_id,
    student_name,
    COUNT(*) AS exam_count,
    COUNT(score) AS valid_count,
    SUM(score) AS total_score,
    ROUND(AVG(score), 2) AS avg_score
FROM scores
GROUP BY student_id, student_name;  -- 按学号和姓名分组（实际按学号就够）

注意：这里按student_id和student_name分组是为了确保输出学生姓名，因为student_name依赖于student_id，在逻辑上可以只按student_id分组。

3.多列分组

当需要更细粒度的统计时，可以按多个列分组。例如，按学生和科目分组（即每个学生每门课只有一个成绩，所以每组只有一条记录，意义不大）。更有意义的例子：统计每个科目、每个考试日期的平均分。

-- 按科目和考试日期分组
SELECT 
    subject,
    exam_date,
    COUNT(*) AS exam_count,
    ROUND(AVG(score), 2) AS avg_score
FROM scores
WHERE score IS NOT NULL
GROUP BY subject, exam_date
ORDER BY subject, exam_date;

结果：每个科目在每个考试日期上的平均分（如果有多次考试）。

四、GROUP BY与聚合函数的配合

GROUP BY几乎总是和聚合函数一起使用，但也可以单独使用GROUP BY（去重效果，但不推荐）。聚合函数作用于每个组内的数据。

常见的聚合函数与GROUP BY组合

• COUNT(*)：每组行数

• COUNT(列)：每组非空值个数

• SUM(列)：每组总和

• AVG(列)：每组平均值

• MAX(列) / MIN(列)：每组的极值

示例：统计每个学生缺考科目数

SELECT 
    student_id,
    student_name,
    COUNT(*) - COUNT(score) AS missing_count
FROM scores
GROUP BY student_id, student_name
HAVING missing_count > 0;  -- 只显示有缺考的学生

五、HAVING子句：筛选分组

HAVING子句用于对分组后的结果进行筛选，类似于WHERE但作用在分组上。WHERE在分组前过滤行，HAVING在分组后过滤组。

语法示例

-- 找出平均分大于85的科目
SELECT 
    subject,
    ROUND(AVG(score), 2) AS avg_score
FROM scores
GROUP BY subject
HAVING AVG(score) > 85;

-- 找出平均分大于80且至少有2个学生的科目
SELECT 
    subject,
    ROUND(AVG(score), 2) AS avg_score,
    COUNT(*) AS student_count
FROM scores
GROUP BY subject
HAVING AVG(score) > 80 AND COUNT(*) >= 2;

WHERE vs HAVING 对比

六、重要注意事项与常见错误

1. SELECT子句中的列必须满足的条件

当使用GROUP BY时，SELECT中的非聚合列必须出现在GROUP BY子句中（或者是函数依赖的列）。否则，结果可能不确定（在MySQL中可能随机返回一个值，但其他数据库会报错）。

错误示例：

-- 错误：student_name没有在GROUP BY中出现，也没有被聚合
SELECT student_id, student_name, AVG(score)
FROM scores
GROUP BY student_id;  -- MySQL允许但结果不可靠
正确做法：
要么把student_name也加入GROUP BY，要么使用聚合函数（如MAX(student_name)）但意义不大。

2. NULL值的分组

GROUP BY会将所有NULL值视为同一组：

-- 假设scores表中某行score为NULL
SELECT score, COUNT(*)
FROM scores
GROUP BY score;  -- NULL值会聚集在一组

3. 使用别名

在GROUP BY和HAVING中通常不能使用列别名（除非数据库支持），应该使用原始列名或表达式。

4. 性能优化

• 对GROUP BY的列建立索引可提高性能

• 尽量先用WHERE过滤掉不需要的行，减少分组数据量

• 避免对大文本列进行分组

七、高级用法与技巧

1. 按表达式分组

可以根据计算结果分组，如按分数段统计学生人数：

SELECT 
    CASE 
        WHEN score >= 90 THEN '优秀'
        WHEN score >= 80 THEN '良好'
        WHEN score >= 60 THEN '及格'
        ELSE '不及格'
    END AS score_level,
    COUNT(*) AS student_count
FROM scores
WHERE score IS NOT NULL
GROUP BY score_level;

2. 使用GROUP_CONCAT()查看组内详情

GROUP_CONCAT()是一个非标准的但非常有用的函数，可以将组内某列的值连接成一个字符串：

-- 查看每个科目有哪些学生参加了考试
SELECT 
    subject,
    GROUP_CONCAT(student_name ORDER BY student_name) AS students,
    COUNT(*) AS student_count
FROM scores
GROUP BY subject;

3. ROLLUP 和 CUBE（了解）

MySQL支持WITH ROLLUP，可以在分组结果中增加一行总计或小计：

-- 按科目分组统计，并添加总计行
SELECT 
    IFNULL(subject, '总计') AS subject,
    COUNT(*) AS exam_count,
    ROUND(AVG(score), 2) AS avg_score
FROM scores
GROUP BY subject WITH ROLLUP;

八、实战练习与解答

练习题目

基于scores表，编写SQL实现以下需求：

1. 基础分组：统计每个学生参加了几门考试（有效成绩数）

2. 多列分组：统计每个科目在不同考试日期的平均分

3. HAVING筛选：找出平均分大于85分的学生

4. 条件分组：统计每个学生数学和英语的平均分（分别显示）

5. 综合应用：统计每个分数段（<60, 60-80, 80-90, >=90）的学生人数

6. 挑战题：找出每门课成绩最高的学生（显示科目、学生姓名和成绩）

参考答案

-- 1. 统计每个学生参加了几门考试（有效成绩数）
SELECT 
    student_id,
    student_name,
    COUNT(score) AS exam_count
FROM scores
GROUP BY student_id, student_name
ORDER BY exam_count DESC;

-- 2. 统计每个科目在不同考试日期的平均分
SELECT 
    subject,
    exam_date,
    ROUND(AVG(score), 2) AS avg_score
FROM scores
WHERE score IS NOT NULL
GROUP BY subject, exam_date
ORDER BY subject, exam_date;

-- 3. 找出平均分大于85分的学生
SELECT 
    student_id,
    student_name,
    ROUND(AVG(score), 2) AS avg_score
FROM scores
WHERE score IS NOT NULL
GROUP BY student_id, student_name
HAVING AVG(score) > 85;

-- 4. 统计每个学生数学和英语的平均分
SELECT 
    student_id,
    student_name,
    ROUND(AVG(CASE WHEN subject = '数学' THEN score END), 2) AS math_avg,
    ROUND(AVG(CASE WHEN subject = '英语' THEN score END), 2) AS english_avg
FROM scores
GROUP BY student_id, student_name;

-- 5. 统计每个分数段的学生人数
SELECT 
    CASE 
        WHEN score >= 90 THEN '90分以上'
        WHEN score >= 80 THEN '80-89分'
        WHEN score >= 60 THEN '60-79分'
        ELSE '60分以下'
    END AS score_range,
    COUNT(*) AS student_count
FROM scores
WHERE score IS NOT NULL
GROUP BY score_range
ORDER BY 
    CASE score_range
        WHEN '90分以上' THEN 1
        WHEN '80-89分' THEN 2
        WHEN '60-79分' THEN 3
        ELSE 4
    END;

-- 6. 每门课成绩最高的学生
SELECT 
    s1.subject,
    s1.student_name,
    s1.score
FROM scores s1
INNER JOIN (
    SELECT subject, MAX(score) AS max_score
    FROM scores
    GROUP BY subject
) s2 ON s1.subject = s2.subject AND s1.score = s2.max_score;

GROUP BY是进行数据汇总和分析的核心工具，它将数据从细节记录提升到更高层次的洞察。掌握它，你将能从数据中提取出更有价值的统计信息。