mysql菜鸟教程
7.1 聚合函数：COUNT、SUM、AVG、MAX、MIN
当我们需要对数据进行统计分析时——比如计算学生总数、求平均成绩、找出最高分或统计销售总额——就需要使用聚合函数。聚合函数是SQL中用于对一组值执行计算并返回单个汇总值的强大工具。
一、聚合函数的核心价值
为什么需要聚合函数？
数据汇总：从大量数据中提取有意义的统计信息
决策支持：为业务决策提供数据依据（如平均销售额、最高业绩）
性能监控：统计系统指标（如用户总数、活跃度）
报表生成：生成各种统计报表和仪表板
二、五大核心聚合函数概览
数据准备
为了方便演示，我们创建一个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处理：几乎所有聚合函数都自动忽略NULL值（COUNT(*)除外）
数据类型：确保函数与数据类型匹配（如对字符串使用SUM()会报错）
性能考虑：大数据表上使用聚合函数可能较慢，考虑添加索引
分组使用：聚合函数通常与GROUP BY结合进行分组统计
结果精度：使用ROUND()控制小数位数，提高结果可读性
最佳实践建议
先验证数据质量：使用COUNT()检查NULL值和数据完整性
组合使用函数：同时查看多个统计指标以获得全面了解
添加条件过滤：使用WHERE在聚合前过滤不需要的数据
使用别名：为聚合结果列起有意义的别名
测试边界情况：测试空数据集、全NULL数据集等边界情况
聚合函数是SQL数据分析的基石，掌握它们能让你从"查看数据"升级到"理解数据"。记住核心原则：聚合函数将多行数据汇总为有意义的单个统计值。
发表评论
所有评论