使用聚合函数进行统计汇总
•
概述
–在查询分析的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 排序