mysql菜鸟教程
7.1 聚合函数：COUNT、SUM、AVG、MAX、MIN
当我们需要对数据进行统计分析时——比如计算学生总数、求平均成绩、找出最高分或统计销售总额——就需要使用聚合函数。聚合函数是SQL中用于对一组值执行计算并返回单个汇总值的强大工具。
一、聚合函数的核心价值
为什么需要聚合函数？
-
数据汇总：从大量数据中提取有意义的统计信息
-
决策支持：为业务决策提供数据依据（如平均销售额、最高业绩）
-
性能监控：统计系统指标（如用户总数、活跃度）
-
报表生成：生成各种统计报表和仪表板
二、五大核心聚合函数概览
函数
功能描述
典型应用场景
COUNT()
统计行数或非NULL值的数量
统计学生总数、订单数量
SUM()
计算数值列的总和
计算销售总额、工资总和
AVG()
计算数值列的平均值
计算平均成绩、平均价格
MAX()
找出最大值
找出最高分、最新订单
MIN()
找出最小值
找出最低分、最早日期
数据准备
为了方便演示，我们创建一个scores成绩表：
-- 创建学生成绩表
CREATE TABLE scores (
id INT PRIMARY KEY AUTO_INCREMENT,
student_id INT NOT NULL COMMENT '学生ID',
student_name VARCHAR(50) NOT NULL COMMENT '学生姓名',
subject VARCHAR(50) NOT NULL COMMENT '科目',
score DECIMAL(5,2) COMMENT '成绩（百分制）',
exam_date DATE NOT NULL COMMENT '考试日期'
);
-- 插入示例数据
INSERT INTO scores (student_id, student_name, subject, score, exam_date) VALUES
(1, '张三', '数学', 85.5, '2024-06-15'),
(1, '张三', '英语', 92.0, '2024-06-16'),
(1, '张三', '物理', 78.5, '2024-06-17'),
(2, '李四', '数学', 90.0, '2024-06-15'),
(2, '李四', '英语', 88.5, '2024-06-16'),
(2, '李四', '物理', 95.0, '2024-06-17'),
(3, '王五', '数学', 76.0, '2024-06-15'),
(3, '王五', '英语', 81.5, '2024-06-16'),
(3, '王五', '物理', 69.0, '2024-06-17'),
(4, '赵六', '数学', 94.5, '2024-06-15'),
(4, '赵六', '英语', 96.0, '2024-06-16'),
(4, '赵六', '物理', 92.5, '2024-06-17'),
(5, '钱七', '数学', 88.0, '2024-06-15'),
(5, '钱七', '英语', NULL, '2024-06-16'), -- 缺考，成绩为NULL
(5, '钱七', '物理', 84.5, '2024-06-17');
-- 查看数据
SELECT * FROM scores ORDER BY student_id, subject;
1. COUNT() - 计数函数
统计行数或非NULL值的数量。
基本语法：
COUNT(*) -- 统计所有行数，包括NULL值
COUNT(列名) -- 统计指定列的非NULL值数量
COUNT(DISTINCT 列名) -- 统计指定列的不重复非NULL值数量
使用示例：
-- 1. 统计总记录数（包括所有行）
SELECT COUNT(*) AS total_records FROM scores;
-- 返回：15
-- 2. 统计有成绩的学生数量（排除NULL）
SELECT COUNT(score) AS scored_students FROM scores;
-- 返回：14（钱七的英语成绩为NULL，不计数）
-- 3. 统计参加考试的不同学生数量
SELECT COUNT(DISTINCT student_id) AS distinct_students FROM scores;
-- 返回：5
-- 4. 统计有多少个不同的科目
SELECT COUNT(DISTINCT subject) AS distinct_subjects FROM scores;
-- 返回：3
2. SUM() - 求和函数
计算数值列的总和。
基本语法：
SUM(列名) -- 计算总和
SUM(DISTINCT 列名) -- 计算不重复值的总和
使用示例：
-- 1. 计算所有成绩的总分
SELECT SUM(score) AS total_score FROM scores;
-- 返回：所有有效成绩的总和
-- 2. 计算数学科目的总分
SELECT SUM(score) AS math_total_score
FROM scores
WHERE subject = '数学';
-- 返回：433.5 (85.5 + 90 + 76 + 94.5 + 88)
-- 3. 计算不重复成绩的总和（意义不大，仅演示语法）
SELECT SUM(DISTINCT score) AS distinct_scores_sum
FROM scores
WHERE subject = '数学';
-- 返回：可能有重复分数时会有差异
3. AVG() - 平均值函数
计算数值列的平均值。
基本语法：
AVG(列名) -- 计算平均值
AVG(DISTINCT 列名) -- 计算不重复值的平均值
使用示例：
-- 1. 计算所有成绩的平均分
SELECT AVG(score) AS average_score FROM scores;
-- 注意：NULL值不参与计算，分母是14不是15
-- 2. 计算数学科目的平均分
SELECT
AVG(score) AS math_avg_score,
COUNT(score) AS math_student_count
FROM scores
WHERE subject = '数学';
-- 返回：平均分86.7，学生数5
-- 3. 手动验证平均分计算
SELECT
SUM(score) AS total,
COUNT(score) AS count,
SUM(score) / COUNT(score) AS manual_avg
FROM scores
WHERE subject = '数学';
4. MAX() - 最大值函数
找出指定列的最大值。
基本语法：
MAX(列名)
使用示例：
-- 1. 找出最高成绩
SELECT MAX(score) AS highest_score FROM scores;
-- 返回：96.0
-- 2. 找出数学科目的最高分
SELECT MAX(score) AS math_highest_score
FROM scores
WHERE subject = '数学';
-- 返回：94.5
-- 3. 找出最晚的考试日期
SELECT MAX(exam_date) AS latest_exam_date FROM scores;
-- 返回：2024-06-17
-- 4. 找出最长的学生姓名
SELECT MAX(LENGTH(student_name)) AS max_name_length FROM scores;
5. MIN() - 最小值函数
找出指定列的最小值。
基本语法：
MIN(列名)
使用示例：
-- 1. 找出最低成绩
SELECT MIN(score) AS lowest_score FROM scores;
-- 返回：69.0
-- 2. 找出英语科目的最低分
SELECT MIN(score) AS english_lowest_score
FROM scores
WHERE subject = '英语' AND score IS NOT NULL;
-- 返回：81.5
-- 3. 找出最早的考试日期
SELECT MIN(exam_date) AS earliest_exam_date FROM scores;
-- 返回：2024-06-15
组合使用多个聚合函数
在实际应用中，我们经常需要同时查看多个统计指标：
-- 查看所有成绩的总体统计
SELECT
COUNT(*) AS total_records,
COUNT(score) AS valid_scores,
SUM(score) AS total_score,
AVG(score) AS average_score,
MAX(score) AS highest_score,
MIN(score) AS lowest_score
FROM scores;
-- 查看数学科目的详细统计
SELECT
subject,
COUNT(*) AS total_tests,
COUNT(score) AS completed_tests,
SUM(score) AS total_score,
AVG(score) AS average_score,
MAX(score) AS highest_score,
MIN(score) AS lowest_score,
MAX(score) - MIN(score) AS score_range -- 计算极差
FROM scores
WHERE subject = '数学';
三、重要注意事项与特殊行为
1. NULL值的处理
聚合函数在计算时自动忽略NULL值（COUNT(*)除外）：
-- 注意COUNT(*)和COUNT(score)的区别
SELECT
COUNT(*) AS total_rows, -- 返回15（所有行）
COUNT(score) AS non_null_scores, -- 返回14（忽略NULL）
AVG(score) AS average_score -- 基于14个有效成绩计算
FROM scores;
2. 空表查询的结果
当对空表使用聚合函数时：
-- 假设查询一个不存在的条件
SELECT
COUNT(*) AS count_all,
SUM(score) AS sum_score,
AVG(score) AS avg_score,
MAX(score) AS max_score,
MIN(score) AS min_score
FROM scores
WHERE 1 = 0; -- 条件永远不成立
-- 结果：
-- count_all: 0
-- sum_score: NULL
-- avg_score: NULL
-- max_score: NULL
-- min_score: NULL
3. 数据类型限制
-
SUM()和AVG()只适用于数值类型（INT、DECIMAL等）
-
MAX()和MIN()适用于数值、日期、字符串等可比较的类型
-
COUNT()适用于任何类型
四、聚合函数与GROUP BY结合
聚合函数最强大的功能是与GROUP BY子句结合，进行分组统计（这将在下一节详细介绍，此处先做简要介绍）：
-- 按科目分组统计
SELECT
subject,
COUNT(*) AS student_count,
AVG(score) AS average_score,
MAX(score) AS highest_score,
MIN(score) AS lowest_score
FROM scores
WHERE score IS NOT NULL
GROUP BY subject
ORDER BY average_score DESC;
-- 按学生分组统计
SELECT
student_id,
student_name,
COUNT(*) AS exam_count,
AVG(score) AS average_score,
SUM(score) AS total_score
FROM scores
WHERE score IS NOT NULL
GROUP BY student_id, student_name
ORDER BY average_score DESC;
五、实用技巧与常见问题
1. 保留小数位数
-- 使用ROUND函数控制小数位数
SELECT
subject,
ROUND(AVG(score), 1) AS avg_score_1decimal,
ROUND(AVG(score), 2) AS avg_score_2decimal
FROM scores
WHERE score IS NOT NULL
GROUP BY subject;
2. 处理除零错误
-- 安全计算百分比（避免COUNT(*)为0时出错）
SELECT
subject,
COUNT(score) AS completed,
COUNT(*) AS total,
CASE
WHEN COUNT(*) > 0 THEN
ROUND(COUNT(score) * 100.0 / COUNT(*), 2)
ELSE 0
END AS completion_rate_percent
FROM scores
GROUP BY subject;
3. 性能优化建议
-
对经常用于聚合的列建立索引
-
在大数据表上，考虑使用采样统计或预计算
-
避免在WHERE条件中对聚合结果进行筛选（应使用HAVING）
六、练习与解答
练习题目
基于scores表，请编写SQL语句实现以下需求：
-
基础统计：统计总共有多少个有效的成绩记录
-
科目分析：计算每个科目的平均分、最高分和最低分
-
学生分析：计算每个学生的总成绩和平均成绩
-
数据质量：统计每个学生的缺考科目数量（成绩为NULL）
-
综合应用：找出平均分最高的科目和学生
-
挑战题：计算每个科目的成绩标准差（使用STDDEV或手动计算）
参考答案
-- 1. 统计总共有多少个有效的成绩记录
SELECT COUNT(score) AS valid_score_count FROM scores;
-- 2. 计算每个科目的平均分、最高分和最低分
SELECT
subject,
COUNT(score) AS student_count,
ROUND(AVG(score), 2) AS average_score,
MAX(score) AS highest_score,
MIN(score) AS lowest_score
FROM scores
GROUP BY subject
ORDER BY average_score DESC;
-- 3. 计算每个学生的总成绩和平均成绩
SELECT
student_id,
student_name,
COUNT(score) AS exam_count,
SUM(score) AS total_score,
ROUND(AVG(score), 2) AS average_score
FROM scores
GROUP BY student_id, student_name
ORDER BY average_score DESC;
-- 4. 统计每个学生的缺考科目数量
SELECT
student_id,
student_name,
COUNT(*) - COUNT(score) AS missing_exams
FROM scores
GROUP BY student_id, student_name
HAVING missing_exams > 0;
-- 5. 找出平均分最高的科目和学生
-- 最高平均分的科目
SELECT subject, ROUND(AVG(score), 2) AS avg_score
FROM scores
WHERE score IS NOT NULL
GROUP BY subject
ORDER BY avg_score DESC
LIMIT 1;
-- 最高平均分的学生
SELECT student_name, ROUND(AVG(score), 2) AS avg_score
FROM scores
WHERE score IS NOT NULL
GROUP BY student_name
ORDER BY avg_score DESC
LIMIT 1;
-- 6. 计算每个科目的成绩标准差
SELECT
subject,
COUNT(score) AS count,
ROUND(AVG(score), 2) AS average,
ROUND(STDDEV(score), 2) AS std_deviation -- MySQL中使用STD或STDDEV
FROM scores
WHERE score IS NOT NULL
GROUP BY subject;
七、聚合函数要点总结
函数特性对比表
函数
是否忽略NULL
适用数据类型
空表返回值
典型用途
COUNT()
COUNT(*)否，COUNT(列)是
任意
0
统计数量
SUM()
是
数值型
NULL
计算总和
AVG()
是
数值型
NULL
计算平均值
MAX()
是
可比较类型
NULL
找出最大值
MIN()
是
可比较类型
NULL
找出最小值
使用注意事项总结
-
NULL处理：几乎所有聚合函数都自动忽略NULL值（COUNT(*)除外）
-
数据类型：确保函数与数据类型匹配（如对字符串使用SUM()会报错）
-
性能考虑：大数据表上使用聚合函数可能较慢，考虑添加索引
-
分组使用：聚合函数通常与GROUP BY结合进行分组统计
-
结果精度：使用ROUND()控制小数位数，提高结果可读性
最佳实践建议
-
先验证数据质量：使用COUNT()检查NULL值和数据完整性
-
组合使用函数：同时查看多个统计指标以获得全面了解
-
添加条件过滤：使用WHERE在聚合前过滤不需要的数据
-
使用别名：为聚合结果列起有意义的别名
-
测试边界情况：测试空数据集、全NULL数据集等边界情况
聚合函数是SQL数据分析的基石，掌握它们能让你从"查看数据"升级到"理解数据"。记住核心原则：聚合函数将多行数据汇总为有意义的单个统计值。
发表评论
所有评论