4. SQL汇总分析

SQL 语句查询的最终目的其实是为了得到一些分析结果，在SQL语言中支持一些基本的统计分析函数帮我们完成数据的查询和分析。
在进行统计分析之前，首先要有数据，参考上篇文章《 3. SQL–数据库基础查询操作》，将数据生成出来。

1. 汇总分析

常用的汇总分析函数如下图所示：

下面用几个查询案例，来看一下函数的用法。

1.1 计数

1. 求教师表中有几个教师。

SELECT COUNT(*)
FROM teacher;

按照所有列统计数目。

2. 求教师表中有几个教师姓名。

SELECT COUNT(教师姓名)
FROM teacher;

由于教师表中，0003教师的名字为NULL，因此不计入总数。

1.2 求和与平均值

1. 对成绩表中所有成绩求和

SELECT SUM(成绩) AS 成绩总和
FROM score;

2. 对成绩表中所有成绩求平均值

SELECT AVG(成绩) AS 平均成绩
FROM score;

1.3 求最大最小值

SELECT MAX(成绩) AS 最高成绩,MIN(成绩) AS 最低成绩
FROM score;

1.4 空值的处理

注意：

1. 除了count(*)为特例（不忽略null值）外，其余聚集函数确实是忽略null值。
2. 当然如果count(列名)针对某一列，那么也会忽略空值null。

2. 分组聚合和统计分析

统计分析往往需要对某一类别的数据进行统计，而数据表中记录的是所有类别。所以一旦涉及到某个类别的统计分析，就要考虑使用GROUP BY 分组聚合统计分析。

注意：分组一般都要和聚合函数一起使用，如果不一起虽然不会报错，但显然没什么意义。
如下图是加入分组聚合之后的SQL语句运行顺序，篮框中按照书写顺序执行：

2.1 查询每个性别的学生数

SELECT 性别, COUNT(*) AS 数目
FROM student
GROUP BY 性别;

查询结果如下图所示：

2.2 查询90后每个性别学生数

SELECT 性别, COUNT(*) AS 数目
FROM student
WHERE 出生日期 > '1990-01-01'
GROUP BY 性别;

查询结果如下图所示：

2.3 对分组结果指定条件

我们知道WHERE子句可以加限定条件，但是对于分组条件的指定需要用HAVING 子句。
如果想要查出平均成绩大于60分的学生的学号和平均成绩，怎么实现？

SELECT 学号, AVG(成绩) AS 平均成绩
FROM score
GROUP BY 学号
HAVING AVG(成绩) > 70;

查询结果如下图所示：

3. SQL解决业务问题的步骤

对于所有的业务问题，基本可以分三步解决：

1. 翻译成大白话
   翻译成大白话，简言之就是将问题分而治之，分成几个小步骤。
2. 写出分析思路
   每个小步骤，可以通过SQL子句一一表达，需要用哪些SQL子句。
3. 写出对应的SQL子句
   将各步骤用SQL子句翻译，然后拼接到一起。

3.1 计算各科的平均成绩

1. 翻译成大白话
   计算每门课程的平均成绩。
2. 写出分析思路
   从哪张表中查找：FROM score
   提到每**： 就需要使用GROUP BY来对每门课程分组
   最终求平均值
3. 翻译成SQL语句

SELECT 查询结果[课程号，平均成绩]
FROM 从哪张表中查找数据
WHERE 限定条件（无）
GROUP BY 分组 [每门课程]
HAVING 对各分组有无条件 （无）

最终的SQL 查询语句：

SELECT 课程号,AVG(成绩) AS 平均成绩
FROM score
GROUP BY 课程号;

查询结果如下图所示：

4. 排序

对分组结果聚合完成之后，需要对结果进行排序，排序使用ORDER BY，加入ORDER BY 之后的SQL 语句执行顺序如下图所示：

4.1 指定某列排序

SELECT * FROM score ORDER BY 成绩 asc; ---按照成绩降序排列
SELECT * FROM score ORDER BY 成绩 asc,课程号 desc; ---先按成绩降序，相同成绩按照课程号升序排列

使用ORDER BY 排序查询结果如下图所示：

4.2 各科平均成绩大于80分的降序排列

1. 翻译成大白话
   每门课程计算平均成绩，取出平均成绩大于80分的课程，然后排序
2. 分析思路
   每门课程
   平均成绩
   取出大于80
   降序
3. SQL实现

SELECT 课程号, AVG(成绩) AS 平均成绩
FROM score
GROUP BY 课程号
HAVING AVG(成绩) > 80
ORDER BY 平均成绩 desc;

查询结果如下图所示：

注意：有空值的列排序，空值会被放到查询结果的头部，所以可以用此方法来查看所有空值元素。

5. 限定查询结果数量

有时候我们的查询结果一页显示不下，可以考虑使用LIMIT OFFSET 来限制。
LIMIT ： 一页最多显示多少条数据
OFFSET：从第几条开始显示
加入LIMIT OFFSET 之后SQL子句的执行顺序如下：

查询按成绩排序成绩表，每页最多显示3个从2个开始显示.

SELECT * FROM score ORDER BY 成绩 LIMIT 3 OFFSET 2;

查询结果如下图所示：

6. SQL 常见错误

1. 在group by 、having中使用了别名（MySQL数据库可以用别名，但是其他数据库不一定）
2. 在where子句中使用聚合函数

select 课程号,count(*) from score  where count(*)>2 group by 课程号;

3. 字符型类型的数字排序是按照字符串排序规则排序