mysql 分组求和_MySQL数据分析:汇总分析

开始之前，先考虑一个问题：如何计算各科的平均成绩并且得出平均成绩大于等于80分的课程并降序排列？

遇到业务问题,先不要着急，捋一下思路：

要计算每一门课程的平均成绩，然后根据得出的结果，进行大于等于80分的条件查询，对查询结果进行降序排列。

这么看来，就可以解决这个业务问题了

接下来，我们需要解决的问题就是，每一步骤如何去解。

此四张表表为以下实例所用

目录

• 汇总函数
• 分组
• 对分组结果指定条件
• 排序
• 用SQL解决业务问题分析思路

一开始的问题中，要求我们计算平均成绩，平均成绩用SQL怎么来计算？这便是我们这节的内容了，这里不仅仅介绍平均值，还有计数、求和、最大值、最小值的汇总函数的介绍。

先看一下本节导图：

1）count() 求某列的行数

很容易理解，count(列名)就是求某列的行数，值得注意的一点是该列不会计算空值的行数。举例：

-- 查询共有几名教师

-- 查询教师表共有多少行
SELECT COUNT(*) FROM teacher;

如果列中有重复值，我们计数不想计算重复值，怎么办呢？我们可以使用distinct来解决：

-- 查询成绩表里共有几个学生的成绩
SELECT COUNT(DISTINCT 学号) FROM score;

2）sum() 对某列数据求和

求和，就是对某一列的数值进行求和，只能对数值进行计算。举例：

--  对所有成绩求和
SELECT SUM(成绩) FROM score;

3）avg() 对某列数值求平均值

同样，平均值的计算只能对数值进行计算：

-- 对所有成绩求平均值
SELECT AVG(成绩) FROM score;

4）max() 求某列数据的最大值、min() 求某列数据的最小值

-- 获取所有成绩中的最大成绩
SELECT MAX(成绩),MIN(成绩) FROM score;

分组

问题中，我们提到，需要知道每科成绩的平均成绩，那么我们就需要进行分组。

SQL中，group by语句可以根据一列或者多列对结果集进行分组。举例：

-- 计算每一科课程的平均成绩
SELECT 课程号,AVG(成绩) FROM score GROUP BY 课程号;

在上篇文章中，我们就有简单的介绍SQL语句执行顺序：先执行from从哪个表获取数据，再执行select语句；

一朵云：MySql数据分析:简单查询​zhuanlan.zhihu.com

这里我们从成绩表获取数据，使用group by语句将数据按某种规则进行分组；然后在分组的结果上进行计算count()；最后select组合结果，把上一步的每一组计算结果总结到一个表中。

对分组条件指定条件

在一开始的问题里，要求“计算各科的平均成绩并且得出平均成绩大于等于80分”，以上两节我们得到了计算每天一科的平均成绩，现在对平均成绩指定条件：“大于等于80分”，我们使用having子句。

having子句与where子句都是有条件的选取数据，不同的是where不能与汇总函数一起使用。

-- 计算平均成绩大于等于80分的课程;
SELECT 课程号,AVG(成绩) FROM score GROUP BY 课程号 HAVING AVG(成绩)>=80;

那是不是说，having子句的顺序也是在select结果汇总的前边呢？是的,having子句是对group by 的分组结果指定条件，比如要求大于等于80分，筛选出结果后，在进行结果汇总。

-- 对教师表根据教师姓名排序
SELECT * FROM teacher ORDER BY 教师姓名;

我们发现，空值Null排在了最前面，所以：含有空值的列在排序时候，空值null会在开头，当数据量很大想看空值的情况，就可以用这个排序。

另外，在补充一个limit语句，从查询结果中取出指定行，比如，我们如果从刚才的查询结果中只取第一行：

-- 计算平均成绩大于等于80分的课程并根据成绩降序排,并获取第一行
SELECT 课程号,AVG(成绩) FROM score GROUP BY 课程号 HAVING AVG(成绩)>=80 ORDER BY AVG(成绩) DESC LIMIT 1;

接下来我们进行一个总结。

用SQL解决业务问题分析思路

简单来说：明确问题，将问题翻译成大白话，逐步进行拆解，并写出分析思路和对应的SQL思路。

1） 明确问题，将问题翻译成大白话：

“计算各科的平均成绩并且得出平均成绩大于等于80分的课程并降序排列”翻译成大白话就是：

“计算每一门课程的平均成绩，然后根据得出的结果，进行大于等于80分的条件查询，对查询结果进行降序排列”。

2） 逐步进行拆解，并写出分析思路和对应的SQL思路：

① 对课程号进行分组，计算每一门课程的平均成绩；

② 对①的结果指定条件“>=80”;

③ 对②的结果进行降序排列；

我们可以把拆解后的思路套用到下方的公式中：

select 查询结果
from 从那张表中查找数据
where 查询条件（运算符、模糊查询）
group by 分组（每个）
having 对分组结果指定条件
order by 对查询结果排序
limit 从查询结果中取出指定行;

试一下吧：

select 查询结果[课程号，avg(成绩）]
from 从那张表中查找数据[成绩表score]
where 查询条件（运算符、模糊查询）[No]
group by 分组（每个）[课程号]
having 对分组结果指定条件[avg(成绩）>=80]
order by 对查询结果排序[avg(成绩）desc]
limit 从查询结果中取出指定行;[No]

这样子，问题是不是就迎刃而解呢？