第六章 聚合函数与分组

使用聚合函数进行统计汇总

• 概述

–在查询分析的SQL中我们经常会对一些数据进行统计查询。比如统计某个班有多少个学生、全班总分多少、平均分多少、最高分是多少、最低分是多少。要实现这些数据的统计就需要要用到SQL提供的聚合函数。

•聚合函数的分类

–COUNT：统计行数量

–SUM：获取单个列的合计值

–AVG：计算某个列的平均值

–MAX：计算列的最大值

MIN：计算列的最小值

•执行行和列计数（COUNT）

–行计数可以统计返回的结果中有多少行数据

–标准结构

SELECT COUNT(<计数规范>) FROM <表名>

计数规范

*	计数所有选择的行，包括NULL值
ALL  列	计数指定列的所有的非空值行。如果仅仅是指定列而不带ALL或者DISTINCT，这是默认操作。
DISTINCT  列	计数指定列的所有唯一非空值行。

•计数规范（*）

–* 表示统计所有行，包括NULL值。

–举例

•统计班上一共有多少个学生

SELECT COUNT(*) FROM t_student

•也可以加入查询条件。例如：统计男生有多少人？

SELECT COUNT(*) FROM t_student WHERE 性别=‘男’

•计数规范（ALL）

–ALL表示统计指定列所有非空值的行数。

–举例

•为学生表添加班级字段，报名注册的同学都会将其分配到某个班上。如果仅仅是入学还没登记注册的同学则暂时没有班级，班级字段的值为空。

•查询已报名注册的同学又多少人

SELECT COUNT(ALL班级) FROM t_student

•这个练习就不能用*，因为*是统计返回结果中的所有行，有些同学还没报名则不能统计出来。使用ALL后可以统计指定列的所有非空的行数。有些同学没有报名，班级字段就为空，使用ALL就会把这些为空的数据排除掉。ALL可以省略，默认就是ALL。

•计数规范（DISTINCT）

–DISTINCT表示统计指定列所有非空值，且不重复数据的行数。

–举例

•DISTINCT和ALL唯一区别就是DISTINCT会排除重复。

•统计班上有多少个年龄段同学。

SELECT COUNT(DISTINCT年龄) FROM t_student

•班上年龄段学生有很多，比如有18岁、19岁、22岁等等，其中18岁有3人、18岁有2人、22岁有1人。统计年龄段就要排除重复，剩下结果为3个年龄段。

•返回列合计值（SUM）

–SUM函数用于统计某列数值的总和。

–标准结构

SELECT SUM(<计数规范>) FROM <表名>;

•SUM只有ALL和DISTINCT这两种计数规范，没有*。

–举例

•查询班上学生年龄的总和。

SELECT SUM(ALL 年龄) FROM t_student

•如果某人的年龄为空则不会被统计在内，也可以省略ALL关键字，默认为ALL。如果改为DISTICT则只会统计不重复的年龄。

•获取列平均值（AVG）

–AVG函数用于统计某列数值的平均值。

–标准结构

SELECT AVG(<平均值规范>) FROM <表名>;

•AVG只有ALL和DISTINCT这两种平均值规范，没有*。

–举例

•查询班上学生的平均年龄。

SELECT AVG(ALL 年龄) FROM t_student

•如果有个人的年龄为空，则AVG会以9个人的年龄总和除以9，而不是除以10.这样算出来的平均值会略大。如果要正确计算，可以使用SUM(年龄)/COUNT(*)的方式。

•AVG也可以省略ALL关键字，默认为ALL。如果改为DISTICT则只会统计不重复的年龄。

•返回最大值和最小值（MAX/MIN）

–MAX函数返回某列中的最大值

–MIN函数返回某列中的最小值

–标准结构

SELECT MAX(<列名>) FROM <表名>

SELECT MIN(<列名>) FROM <表名>

•MAX/MIN没有ALL、DISTINCT、*这些规范

–举例

•显示班上最大的年龄和最小的年龄

SELECT MAX(年龄) AS 最大年龄 , MIN(年龄) AS 最小年龄 FROM t_student

数据分组

• 概述

–到目前为止，我们仅仅是在一个表中的所有行或者过滤行集合中应用聚合函数。如果我们想要获得数据集合中的子集的总计值，可能需要好几个单独的查询。例如，我们要统计每个班上有多少人，那么我们需要写两个查询语句：

SELECT COUNT(*) FROMt_student WHERE 班级=‘T01’

SELECT COUNT(*) FROMt_student WHERE 班级=‘T02’

班级越多，SQL就会越多，显然这并不是一种理想的做法。SQL提供了一种技术，可以将表中的行分组，然后在每个组上应用聚合函数。这种分组技术与聚合函数一起使用时，功能尤为强大

•过滤分组数据

–标准结构

SELECT 列A, 聚合函数(聚合函数规范) FROM 表名

WHERE 过滤条件

GROUP BY 列 A …

–分组GROUP BY子句是在WHERE条件过滤后的结果中完成，如果没有WHERE条件则直接对FROM表中的数据进行分组。

–举例

•统计每个班级年龄在18岁以上的学生人数

SELECT 班级 ,COUNT(*) AS 人数 FROM t_student

WHERE 年龄>18

GROUP BY 班级

•GROUP BY会将指定列中有相同数据的分为一组。例子中用了班级进行分组，相同的班级会被分到一组。再对每组分别执行聚合。

•多列分组

–分组可以实现多列分组，比如按班级和年龄分组，那么只有当班级和年龄一样的才分一组，例如：

SELECT 班级 , 人数 ,COUNT(*) AS 人数 FROM t_student

GROUP BY 班级 , 年龄

–在有分组的SQL语句中，投影的列就会有限制。要求SELECT中的列必须满足以下两个条件之一，否则就是无效的投影。

  1.使用了聚合函数的列

  2.该列在GROUP BY 子句中

• 使用HAVING子句

–假设我们想对聚合后的数据进行过滤，例如，查询平均年龄在20岁以上的班级信息。

–思考

•能用下面的SQL查询吗？

SELECT 班级 , AVG(年龄) FROM t_student

WHERE AVG(年龄)>20

GROUP BY 班级

上面SQL执行时会出错。原因是聚合是在分组后进行的，WHERE子句是在分组前执行的，所以在WHERE子句里执行聚合函数是不行的。SQL提供了另外一个关键字HAVING来实现聚合函数的过滤。HAVING子句是在GROUP  BY后执行的

•使用HAVING子句

–标准结构

SELECT 列A, 聚合函数(聚合函数规范) FROM 表名

WHERE 过滤条件

GROUP BY 列A

HAVING过滤条件

–举例

•查询平均年龄在20岁以上的班级信息

SELECT 班级 , AVG(年龄) FROM t_student

GROUP BY 班级

HAVING AVG(年龄)>20 

•HAVING和WHERE区别

二者都是过滤条件，WHERE运行在分组前，因此不能执行任何聚合函数。HAVING是运行在分组后，只能用做聚合函数的过滤

SQL执行顺序

• 在基本的SQL学习中我们已经掌握到了SQL执行顺序，下面来看看加上分组后的SQL的执行顺序

–第一步：执行FROM

–第二步：WHERE条件过滤

–第三步：GROUP BY分组

–第四步：执行SELECT投影列

–第五步：HAVING条件过滤

–第六步：执行ORDER BY 排序