08丨什么是SQL的聚集函数，如何利用它们汇总表的数据？

Jun 28, 2019

08丨什么是SQL的聚集函数，如何利用它们汇总表的数据？-极客时间



下载APP





关闭

讲堂部落提薪训练营云原生训练营架构实战营企业版极客商城兑换中心 App下载浏览器插件

渠道合作

推荐作者

08丨什么是SQL的聚集函数，如何利用它们汇总表的数据？

2019-06-28 陈旸来自北京

《SQL必知必会》

课程介绍



讲述：陈旸

时长08:59大小12.34M



我们上节课讲到了 SQL 函数，包括算术函数、字符串函数、日期函数和转换函数。实际上 SQL 函数还有一种，叫做聚集函数，它是对一组数据进行汇总的函数，输入的是一组数据的集合，输出的是单个值。通常我们可以利用聚集函数汇总表的数据，如果稍微复杂一些，我们还需要先对数据做筛选，然后再进行聚集，比如先按照某个条件进行分组，对分组条件进行筛选，然后得到筛选后的分组的汇总信息。
有关今天的内容，你重点需要掌握以下几个方面：
聚集函数都有哪些，能否在一条 SELECT 语句中使用多个聚集函数；
如何对数据进行分组，并进行聚集统计；
如何使用 HAVING 过滤分组，HAVING 和 WHERE 的区别是什么。
聚集函数都有哪些SQL 中的聚集函数一共包括 5 个，可以帮我们求某列的最大值、最小值和平均值等，它们分别是：
这些函数你可能已经接触过，我们再来简单复习一遍。我们继续使用 heros 数据表，对王者荣耀的英雄数据进行聚合。
如果我们想要查询最大生命值大于 6000 的英雄数量。
SQL：SELECT COUNT(*) FROM heros WHERE hp_max > 6000
运行结果为 41。
如果想要查询最大生命值大于 6000，且有次要定位的英雄数量，需要使用 COUNT 函数。
SQL：SELECT COUNT(role_assist) FROM heros WHERE hp_max > 6000
运行结果是 23。
需要说明的是，有些英雄没有次要定位，即 role_assist 为 NULL，这时COUNT(role_assist)会忽略值为 NULL 的数据行，而 COUNT(*) 只是统计数据行数，不管某个字段是否为 NULL。
如果我们想要查询射手（主要定位或者次要定位是射手）的最大生命值的最大值是多少，需要使用 MAX 函数。
SQL：SELECT MAX(hp_max) FROM heros WHERE role_main = '射手' or role_assist = '射手'
运行结果为 6014。
你能看到，上面的例子里，都是在一条 SELECT 语句中使用了一次聚集函数，实际上我们也可以在一条 SELECT 语句中进行多项聚集函数的查询，比如我们想知道射手（主要定位或者次要定位是射手）的英雄数、平均最大生命值、法力最大值的最大值、攻击最大值的最小值，以及这些英雄总的防御最大值等汇总数据。
如果想要知道英雄的数量，我们使用的是 COUNT(*) 函数，求平均值、最大值、最小值，以及总的防御最大值，我们分别使用的是 AVG、MAX、MIN 和 SUM 函数。另外我们还需要对英雄的主要定位和次要定位进行筛选，使用的是WHERE role_main = '射手' or role_assist = '射手'。
SQL: SELECT COUNT(*), AVG(hp_max), MAX(mp_max), MIN(attack_max), SUM(defense_max) FROM heros WHERE role_main = '射手' or role_assist = '射手'
运行结果：
需要说明的是 AVG、MAX、MIN 等聚集函数会自动忽略值为 NULL 的数据行，MAX 和 MIN 函数也可以用于字符串类型数据的统计，如果是英文字母，则按照 A—Z 的顺序排列，越往后，数值越大。如果是汉字则按照全拼拼音进行排列。比如：
SQL：SELECT MIN(CONVERT(name USING gbk)), MAX(CONVERT(name USING gbk)) FROM heros
运行结果：
需要说明的是，我们需要先把 name 字段统一转化为 gbk 类型，使用CONVERT(name USING gbk)，然后再使用 MIN 和 MAX 取最小值和最大值。
我们也可以对数据行中不同的取值进行聚集，先用 DISTINCT 函数取不同的数据，然后再使用聚集函数。比如我们想要查询不同的生命最大值的英雄数量是多少。
SQL: SELECT COUNT(DISTINCT hp_max) FROM heros
运行结果为 61。
实际上在 heros 这个数据表中，一共有 69 个英雄数量，生命最大值不一样的英雄数量是 61 个。
假如我们想要统计不同生命最大值英雄的平均生命最大值，保留小数点后两位。首先需要取不同生命最大值，即DISTINCT hp_max，然后针对它们取平均值，即AVG(DISTINCT hp_max)，最后再针对这个值保留小数点两位，也就是ROUND(AVG(DISTINCT hp_max), 2)。
SQL: SELECT ROUND(AVG(DISTINCT hp_max), 2) FROM heros
运行结果为 6653.84。
你能看到，如果我们不使用 DISTINCT 函数，就是对全部数据进行聚集统计。如果使用了 DISTINCT 函数，就可以对数值不同的数据进行聚集。一般我们使用 MAX 和 MIN 函数统计数据行的时候，不需要再额外使用 DISTINCT，因为使用 DISTINCT 和全部数据行进行最大值、最小值的统计结果是相等的。
如何对数据进行分组，并进行聚集统计我们在做统计的时候，可能需要先对数据按照不同的数值进行分组，然后对这些分好的组进行聚集统计。对数据进行分组，需要使用 GROUP BY 子句。
比如我们想按照英雄的主要定位进行分组，并统计每组的英雄数量。
SQL: SELECT COUNT(*), role_main FROM heros GROUP BY role_main
运行结果（6 条记录）：
如果我们想要对英雄按照次要定位进行分组，并统计每组英雄的数量。
SELECT COUNT(*), role_assist FROM heros GROUP BY role_assist
运行结果：（6 条记录）
你能看出如果字段为 NULL，也会被列为一个分组。在这个查询统计中，次要定位为 NULL，即只有一个主要定位的英雄是 40 个。
我们也可以使用多个字段进行分组，这就相当于把这些字段可能出现的所有的取值情况都进行分组。比如，我们想要按照英雄的主要定位、次要定位进行分组，查看这些英雄的数量，并按照这些分组的英雄数量从高到低进行排序。
SELECT COUNT(*) as num, role_main, role_assist FROM heros GROUP BY role_main, role_assist ORDER BY num DESC
运行结果：（19 条记录）
如何使用 HAVING 过滤分组，它与 WHERE 的区别是什么？当我们创建出很多分组的时候，有时候就需要对分组进行过滤。你可能首先会想到 WHERE 子句，实际上过滤分组我们使用的是 HAVING。HAVING 的作用和 WHERE 一样，都是起到过滤的作用，只不过 WHERE 是用于数据行，而 HAVING 则作用于分组。
比如我们想要按照英雄的主要定位、次要定位进行分组，并且筛选分组中英雄数量大于 5 的组，最后按照分组中的英雄数量从高到低进行排序。
首先我们需要获取的是英雄的数量、主要定位和次要定位，即SELECT COUNT(*) as num, role_main, role_assist。然后按照英雄的主要定位和次要定位进行分组，即GROUP BY role_main, role_assist，同时我们要对分组中的英雄数量进行筛选，选择大于 5 的分组，即HAVING num > 5，然后按照英雄数量从高到低进行排序，即ORDER BY num DESC。
SQL: SELECT COUNT(*) as num, role_main, role_assist FROM heros GROUP BY role_main, role_assist HAVING num > 5 ORDER BY num DESC
运行结果：（4 条记录）
你能看到还是上面这个分组，只不过我们按照数量进行了过滤，筛选了数量大于 5 的分组进行输出。如果把 HAVING 替换成了 WHERE，SQL 则会报错。对于分组的筛选，我们一定要用 HAVING，而不是 WHERE。另外你需要知道的是，HAVING 支持所有 WHERE 的操作，因此所有需要 WHERE 子句实现的功能，你都可以使用 HAVING 对分组进行筛选。
我们再来看个例子，通过这个例子查看一下 WHERE 和 HAVING 进行条件过滤的区别。筛选最大生命值大于 6000 的英雄，按照主要定位、次要定位进行分组，并且显示分组中英雄数量大于 5 的分组，按照数量从高到低进行排序。
SQL: SELECT COUNT(*) as num, role_main, role_assist FROM heros WHERE hp_max > 6000 GROUP BY role_main, role_assist HAVING num > 5 ORDER BY num DESC
运行结果：（2 条记录）
你能看到，还是针对上一个例子的查询，只是我们先增加了一个过滤条件，即筛选最大生命值大于 6000 的英雄。这里我们就需要先使用 WHERE 子句对最大生命值大于 6000 的英雄进行条件过滤，然后再使用 GROUP BY 进行分组，使用 HAVING 进行分组的条件判断，然后使用 ORDER BY 进行排序。
总结今天我对 SQL 的聚集函数进行了讲解。通常我们还会对数据先进行分组，然后再使用聚集函数统计不同组的数据概况，比如数据行数、平均值、最大值、最小值以及求和等。我们也可以使用 HAVING 对分组进行过滤，然后通过 ORDER BY 按照某个字段的顺序进行排序输出。有时候你能看到在一条 SELECT 语句中，可能会包括多个子句，用 WHERE 进行数据量的过滤，用 GROUP BY 进行分组，用 HAVING 进行分组过滤，用 ORDER BY 进行排序……
你要记住，在 SELECT 查询中，关键字的顺序是不能颠倒的，它们的顺序是：
SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY ...
另外需要注意的是，使用 GROUP BY 进行分组，如果想让输出的结果有序，可以在 GROUP BY 后使用 ORDER BY。因为 GROUP BY 只起到了分组的作用，排序还是需要通过 ORDER BY 来完成。
我今天对 SQL 的聚集函数以及 SQL 查询中的关键字顺序进行了讲解，但你还是需要通过训练加深理解，基于 heros 数据表，请你写出下面 2 个 SQL 查询语句：
筛选最大生命值大于 6000 的英雄，按照主要定位进行分组，选择分组英雄数量大于 5 的分组，按照分组英雄数从高到低进行排序，并显示每个分组的英雄数量、主要定位和平均最大生命值。
筛选最大生命值与最大法力值之和大于 7000 的英雄，按照攻击范围来进行分组，显示分组的英雄数量，以及分组英雄的最大生命值与法力值之和的平均值、最大值和最小值，并按照分组英雄数从高到低进行排序，其中聚集函数的结果包括小数点后两位。
欢迎你在评论区与我分享你的答案，如果你觉得这篇文章有帮助，欢迎把它分享给你的朋友或者同事，一起切磋交流一下。

分享给需要的人，Ta购买本课程，你将得20元

生成海报并分享

赞 34

提建议

07丨什么是SQL函数？为什么使用SQL函数可能会带来问题？

09丨子查询：子查询的种类都有哪些，如何提高子查询的性能？

 写留言

精选留言(130)

grey927
2019-07-04
ORDER BY 是对分的组排序还是对分组中的记录排序呢？
作者回复: 这是个好问题，ORDER BY就是对记录进行排序。如果你在前面用到了GROUP BY，实际上是一种分组的聚合方式，已经把一组的数据聚合成为了一条记录，所以再进行排序的时候，也相当于是对分的组进行排序。
共 2 条评论
71
不负
2019-06-28
一个发现：虽然 SELECT 的执行顺序在 GROUP BY 和 HAVING 后面，但对于SELECT中列的别名都可以使用。 MySQL中 1. > SELECT COUNT(*) as num, role_main, AVG(hp_max) FROM heros -> WHERE hp_max>6000 -> GROUP BY role_main -> HAVING COUNT(*)>5 -> ORDER BY COUNT(*) DESC; +-----+-----------+-------------+ | num | role_main | AVG(hp_max) | +-----+-----------+-------------+ | 17 | 战士 | 7028 | | 10 | 坦克 | 8312.4 | | 6 | 法师 | 6417 | +-----+-----------+-------------+ 2. > SELECT COUNT(*) num, ROUND(AVG(hp_max+mp_max), 2) avg, ROUND(MAX(hp_max+mp_max), 2) max, ROUND(MIN(hp_max+mp_max), 2) min FROM heros -> WHERE (hp_max+mp_max)>7000 -> GROUP BY attack_range -> ORDER BY num DESC; +-----+---------+----------+---------+ | num | avg | max | min | +-----+---------+----------+---------+ | 36 | 8654.42 | 11036.00 | 7117.00 | | 26 | 7743.77 | 8737.00 | 7025.00 | +-----+---------+----------+---------+
展开
作者回复: 在执行顺序上，SELECT字段在GROUP BY和HAVING之后，不过在SELECT字段之前，已经计算了聚集函数，也就是COUNT(*) as num。聚集函数的计算在GROUP BY之后，HAVING之前
共 4 条评论
45
ack
2019-06-28
练习题 1.SELECT COUNT(*) AS num,role_main,AVG(hp_max) FROM heros WHERE hp_max > 6000 GROUP BY role_main HAVING num>5 ORDER BY num DESC; 2.SELECT COUNT(*) AS num,ROUND(MAX(hp_max+mp_max),2),ROUND(AVG(hp_max+mp_max),2),ROUND(MIN(hp_max+mp_max),2) FROM heros WHERE hp_max+mp_max > 7000 GROUP BY attack_range ORDER BY num DESC;
展开
作者回复: 正确
共 3 条评论
20
丁丁历险记
2019-11-07
讲个段子 having 常用来做过滤掉那些跑来冒充程序员的人。他们深深的震惊了我的认知。
作者回复: 哈哈
17
吃饭饭
2019-06-28
讲的很详细了，入门必备
作者回复: 谢谢！
10
安静的boy
2019-06-28
where先对数据进行排序，group by再进行分组。让我对数据筛选和分组恍然大悟！
作者回复: 理解了HAVING和WHERE的区别，就了解了分组过滤和条件过滤。还有SELECT语句种的关键字的顺序：SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY ...
8
峻铭
2019-09-01
前面老师在评论中回复过，在group by分组和having筛选分组之间还有一步使用聚集函数进行计算，在目前看到的having都是对cout聚集函数结果的筛选，想试试对其他聚集函数的筛选，然后对训练1做了点小改动： select count(*) as c,role_main,avg(hp_max) as v from heros where hp_max > 6000 GROUP BY role_main HAVING c > 5 and v > 7000 order by c DESC;
作者回复: Good Job
7
Cookie123456
2020-03-30
第二个问题的答案刚开始写错了，where后面条件写成了sum（hp_max + mp_max ）>7000,执行出错了，此时衍生了一个问题，where后面不可以写聚合函数，也就意味着having的诞生，其次SQL语句的执行顺序也表明，where后面是不可以后聚合函数的 SELECT COUNT( * ) AS num, ROUND( avg( hp_max + mp_max ),2 ), ROUND( max( hp_max + mp_max ), 2 ), ROUND( max( hp_max + mp_max ), 2 ) FROM heros WHERE hp_max + mp_max > 7000 GROUP BY attack_range ORDER BY num DESC
展开
6
bear
2019-08-17
Having 部分精彩，赞👍
作者回复: 感谢
5
mickey
2019-06-28
有个错误：文中“比如，我们想要按照英雄的主要定位、次要定位进行分组，查看这些英雄的数量，并按照这些分组的英雄数量从高到低进行排序。”的SQL语句：SQL: SELECT COUNT(*), role_main, role_assist FROM heros GROUP BY role_main, role_assist ORDER BY num DESC 在MySQL里会报错：[Err] 1054 - Unknown column 'num' in 'order clause' 要改为：SELECT COUNT(*) as num, role_main, role_assist FROM heros GROUP BY role_main, role_assist ORDER BY num DESC;
展开
编辑回复: 您好，文章已进行更正，谢谢您的反馈。
5
mickey
2019-06-28
/* 1.筛选最大生命值大于6000的英雄，按照主要定位进行分组，选择分组英雄数量大于5的分组，按照分组英雄数从高到低进行排序，并显示每个分组的英雄数量、主要定位和平均最大生命值。 */ SELECT count(*) as num, role_main, AVG(hp_max) FROM heros WHERE hp_max > 6000 GROUP BY role_main HAVING num > 5 ORDER BY num DESC num role_main AVG(hp_max) ------------------------------------ 17 战士 7028 10 坦克 8312.4 6 法师 6417 /* 2.筛选最大生命值与最大法力值之和大于7000的英雄，按照攻击范围来进行分组，显示分组的英雄数量，以及分组英雄的最大生命值与法力值之和的平均值、最大值和最小值，并按照分组英雄数从高到低进行排序，其中聚集函数的结果包括小数点后两位。 */ SELECT count(*) as num, ROUND(AVG(hp_max + mp_max), 2), MAX(hp_max + mp_max), MIN(hp_max + mp_max) FROM heros WHERE hp_max + mp_max > 7000 GROUP BY attack_range HAVING num > 5 ORDER BY num DESC num, ROUND(AVG(hp_max + mp_max), 2), MAX(hp_max + mp_max), MIN(hp_max + mp_max) ------------------------------------------------------------------------ 62 8272.53 11036 7025
展开
作者回复: SQL正确，最后结果贴的不太完整
共 4 条评论
3
Taozi
2019-06-28
练习2中反复出现的hp_max+mp_max可以绑定到一个变量吗？
共 2 条评论
3
圆子蛋
2019-06-28
1.SELECT COUNT(*) as num,role_main,AVG(hp_max) FROM heros WHERE hp_max > 6000 GROUP BY role_main HAVING num>5 ORDER BY num DESC; 2.SELECT COUNT(*) as num,ROUND(MAX(hp_max+mp_max),2),ROUND(AVG(hp_max+mp_max),2),ROUND(MIN(hp_max+mp_max),2) FROM heros WHERE (hp_max+mp_max) > 7000 GROUP BY attack_range ORDER BY num DESC; 老师在“如何对数据进行分组，并进行聚集统计”的第三个例子里，COUNT(*) 后面没有加 as num，但是 ORDER BY 里直接出现了 num？
展开
作者回复: COUNT(*)后面应该有 as num
3
啵啵
2021-02-19
建议将WHERE和HAVING的区别描述更加详细一些，甚至可以增加一些示例，方便理解。 1、WHERE针对的是表或视图，WHERE判断的字段可以是表或视图中任意一个字段，不能使用函数运算如MAX(`hp_max`) > 100等。 2、HAVING针对的是分组，HAVING判断的字段只能是分组的列。例如SELECT `hp_max` FROM `heros` GROUP BY `role_assist` HAVING MAX(`mp_max`) > 100; 就是错误的，因为分组列中不包含`mp_max`。HAVING是可以使用函数运算的，例如SELECT `hp_max` FROM `heros` GROUP BY `role_assist` HAVING MAX(`hp_max`) > 100; 就是对的。
展开
2
supermouse
2019-06-29
思考题 1： SELECT COUNT(*) AS num, role_main, AVG(hp_max) FROM heros WHERE hp_max > 6000 GROUP BY role_main HAVING num > 5 ORDER BY num DESC; 思考题 2： SELECT COUNT(*) AS num, attack_range, ROUND(AVG(hp_max + mp_max), 2), ROUND(MAX(hp_max + mp_max), 2), ROUND(MIN(hp_max + mp_max), 2) FROM heros WHERE hp_max + mp_max > 7000 GROUP BY attack_range ORDER BY num DESC;
展开
作者回复: 正确
2
太精
2019-06-28
SELECT COUNT(*) AS num, role_main, AVG(hp_max) AS avg_max FROM heros WHERE hp_max > 6000 GROUP BY role_main HAVING num > 5 ORDER BY num DESC; SELECT ROUND((COUNT(*)),2) AS num, ROUND((AVG(hp_max+mp_max)),2) AS heros_avg, ROUND((MAX(hp_max+mp_max)),2) AS max_avg, ROUND((MIN(hp_max+mp_max)),2) AS min_avg FROM heros WHERE (hp_max+mp_max) > 7000 GROUP BY attack_range ORDER BY num desc;
展开
作者回复: 正确不过COUNT(*)就不需要用ROUND了
2
Geek_157522
2021-02-26
筛选最大生命值大于 6000 的英雄，按照主要定位进行分组，选择分组英雄数量大于 5 的分组，按照分组英雄数从高到低进行排序，并显示每个分组的英雄数量、主要定位和平均最大生命值。 SELECT COUNT(*) as num, role_main, AVG(hp_max)FROM heros WHERE hp_max>6000 GROUP BY role_main HAVING num>5 ORDER BY num DESC 筛选最大生命值与最大法力值之和大于 7000 的英雄，按照攻击范围来进行分组，显示分组的英雄数量，以及分组英雄的最大生命值与法力值之和的平均值、最大值和最小值，并按照分组英雄数从高到低进行排序，其中聚集函数的结果包括小数点后两位。 SELECT COUNT(*) as num, ROUND(AVG(hp_max+mp_max),2)，ROUND(MAX(hp_max+mp_max),2),ROUND(MIN(hp_max+mp_max),2)FROM heros WHERE (hp_max+mp_max)>7000 GROUP BY attack_max ORDER BY num DESC
展开
作者回复: 第一个正确。第二个的 attack_max 改成 attack_range 可以运行成功。
1
nature
2022-10-11 来自北京
陈老师您好，请问有关于Sum if Count if的相关讲解吗？
lgy
2022-09-19 来自广东
SELECT COUNT(*) as num, role_main, AVG(hp_max) FROM heros WHERE hp_max > 6000 GROUP BY role_main HAVING num > 5 ORDER BY num DESC
年少挽滑稽世无双
2022-09-06 来自四川
# 筛选最大生命值大于 6000 的英雄，按照主要定位进行分组，选择分组英雄数量大于 5 的分组，按照分组英雄数从高到低进行排序，并显示每个分组的英雄数量、主要定位和平均最大生命值。 SELECT COUNT(*) AS num, role_main, AVG(hp_max) FROM heros WHERE hp_max > 6000 GROUP BY role_main HAVING num > 5 ORDER BY num DESC # 筛选最大生命值与最大法力值之和大于 7000 的英雄，按照攻击范围来进行分组，显示分组的英雄数量，以及分组英雄的最大生命值与法力值之和的平均值、最大值和最小值，并按照分组英雄数从高到低进行排序，其中聚集函数的结果包括小数点后两位。 SELECT COUNT(*) AS num, ROUND(AVG(hp_max + mp_max),2), ROUND(MAX(hp_max + mp_max),2), ROUND(MIN(hp_max + mp_max),2) FROM heros WHERE (hp_max + mp_max) > 7000 GROUP BY attack_range ORDER BY num DESC
展开

