sql学习记录8-聚合函数COUNT/SUM/AVG/MAX/MMIN,GROUP BY，HAVING先过滤后聚合、先聚合后过滤、书写顺序、执行顺序

最新推荐文章于 2023-03-12 22:31:05 发布
最新推荐文章于 2023-03-12 22:31:05 发布
阅读量1.8k 收藏 3
点赞数 1
CC 4.0 BY-SA版权
分类专栏： sql学习笔记 文章标签： mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_41551159/article/details/112253319
本文详细介绍了SQL中的聚合函数，包括COUNT、SUM、AVG、MAX、MIN的用法，并通过Navicat实操展示了分组查询。讲解了GROUP BY和HAVING子句的区别，强调了SQL语句的书写和执行顺序。

摘要生成于 C知道 ，由 DeepSeek-R1 满血版支持， 前往体验 >

单表查询----聚合函数

在这里插入图片描述

  1. 普通函数输入是一列，输出也是一列，输出列中的行与输出列的行，作用于列的每一个单元格，聚合函数以列输入，但是输出结果列只有一行。

  2. 聚合函数是汇总处理数据，不是像SELECT一样的检索数据。比如求和、求平均值。

聚合函数–参数是列名

（1）计数函数 COUNT（列名）

COUNT(*) 确定表中行的数目
COUNT(1) 确定表中第一列的行的数目。如果表中没有NULL，那么任何一列的行数目是一样的

COUNT(col) 确定col这一列有多少行，只作用非NULL行，但是不可以计数多列，即参数只能是一个列名

COUNT(DISTINCT col) 对行去重计数

注：

  • COUNT是唯一可以用*的聚合函数。
  • COUNT(*)
    （col表示列，column）
  • COUNT忽略NULL，比如实际有8行，其中一行是NULL，那么COUNT的结果是7.
  • 尽量不要再一个语句中写多个COUNT，比如尽量不要这样写：
SELECT 
COUNT(m.prod_name)
,COUNT(m.saleprice) 
FROM milk_tea AS m;

说是会出现问题？？还没试出什么问题。。。

（2）求和函数 SUM

指定列的和：SUM（col）

具体用法：

  • 区别于直接四则运算；因为四则运算是不能做有空值的运算，如果有空值，那么四则运算的结果就是空值，而SUM求和是直接跳过空值，如果有空值，就忽略空值，计算其他所有非空值的和。
  • 只对数值型作用；
  • 可以同时操作多个列；
  • 可以作用于计算表达式，指的是参数可以是列名，参数也可以是列名之间的计算

（3）均值函数 AVG

指定列的平均值：AVG(col)
具体用法：

  • 分母不对NULL计数，即空值被过滤掉，即列的SUM值/列的COUNT值(分子也不对NULL行计数)
  • 可以同时操作多个列

（4）最大值函数 MAX

指定列的最大值：MAX（col）

具体用法：

  • 适用于数值型、文本型、日期型，其中文本型也是可以比较最大值最小值的，只是用的比较少

  • 要求该列可以排序，文本数据排序考虑的是编码的问题

  • 忽略NULL，因为NULL没法比较大小

  • DISTINCT无意义， DISTINCT指的是去重，但是不论去重不去重，最大值依然是最大值，所以结果是一样的。

（5）最小值函数 MIN

指定列的最小值：MIN(col)
具体用法：

  • 适用于数值型、文本型、日期型，其中文本型也是可以比较最大值最小值的，只是用的比较少

  • 要求该列可以排序，文本数据排序考虑的是编码的问题

  • 忽略NULL，因为NULL没法比较大小

  • DISTINCT无意义， DISTINCT指的是去重，但是最大值依然是最大值，所以结果是一样的。

Navicat实操

1. count

SELECT 
COUNT(*) #这个表格的行数
, COUNT(1) #第一列的行数
, COUNT(3) #第三列的行数
FROM milk_tea AS m;

在这里插入图片描述

SELECT 
COUNT(m.pro_date)  #pro_date列的行数
, COUNT(m.prod_name) #prod_name列的行数
FROM milk_tea AS m;
#m.pro_date有一个空值
#最好不要一个语句中写两个COUNT函数

在这里插入图片描述

SELECT 
COUNT(m.net_w) 
, COUNT(DISTINCT m.net_w) #先对net_w列去重，再计数该列的行数
FROM milk_tea AS m; 
#DISTINCT去重计数
#最好不要一个语句中写两个COUNT函数，可能会出错

在这里插入图片描述

SELECT DISTINCT m.sale_price FROM milk_tea AS m ;
# DISTINCT作用于所有列。去重
SELECT COUNT(DISTINCT m.sale_price) FROM milk_tea AS m; 
# sale_price中 8行，有一行是NULL，DISTINCT去重之后NULL保留，但是计数函数会忽略NULL，所以最后计数仍然是跳过NULL来计数。

在这里插入图片描述在这里插入图片描述

2.SUM

SELECT SUM(m.in_price) FROM milk_tea AS m;
SELECT SUM(m.in_price *  1.1) FROM milk_tea AS m;
#进价提升10%
SELECT SUM(m.sale_price) FROM milk_tea AS m;

在这里插入图片描述

SELECT SUM(m.sale_price-m.in_price) FROM milk_tea AS m;
#1,结果会忽略sale_price为NULL和in_price为NULL的值，原表中saleprice有一个空值。
#因为**四则运算是不对空值行做计算**，如果有空值，那么四则运算的结果就是空值
#SUM(m.sale_price-m.in_price)计算了7对值的差，然后求和
SELECT SUM(m.sale_price) - SUM(m.in_price) FROM milk_tea AS m;
#2，计算时不会忽略NULL为空的值
#SUM求和是直接跳过空值，如果有空值，就忽略空值，计算其他所有非空值的和。SUM(m.sale_price)计算了7个值的和，SUM(m.in_price)计算了8个值的和

#如果对NULL做一个处理、
SELECT SUM(IFNULL(m.sale_price,0) - m.in_price)FROM milk_tea AS m;
#3,结果与2相同，因为跳过NULL求和与将NULL变成0求和结果是一样的
#IFNULL(m.sale_price,0)是将NULL值变成0，参与四则运算后，再参与求和运算

解释：
3与2的结果相同，因为
SUM(IFNULL(m.sale_price,0) - m.in_price)
=SUM(m.sale_price) - SUM( m.in_price)
在这里插入图片描述

3.AVG

SELECT AVG(m.sale_price) FROM milk_tea AS m; #1
SELECT SUM(m.sale_price)/COUNT(m.sale_price) FROM milk_tea AS m;#2,与结果1一样
SELECT SUM(m.sale_price)/COUNT(1) FROM milk_tea AS m;#3,与结果12不一样，
#因为sale_price有空值，再SUM和COUNT中都是直接跳过NULL，而第一列是prod_id没有空值，所以两者的行数不同
#所以求均值时，分母的COUNT一定要写整列的列名，否则有可能出错

解释：
SUM和COUNT中都是直接跳过NULL
注：求均值时，如果用SUM/COUNT公式，那么**COUNT参数一定要写整列的列名，**否则一旦有NULL出现用其他的行COUNT就会出错。

在这里插入图片描述

4.先过滤后聚合

SELECT 
SUM(m.sale_price) 
FROM milk_tea AS m 
WHERE m.net_w = '100g' ; 
#是先过滤后聚合，这条规则同样适用于AVG、MIN、MAX

执行顺序是：
确定表–过滤表–选取展示列
FROM–WHERE–SELECT
在这里插入图片描述

5. MIN(),MAX()

SELECT MIN(m.sale_price) , MIN(DISTINCT m.sale_price) FROM milk_tea AS m; #说明DISTINCT再MIN、MAX中不起作用

SELECT MAX(m.sale_price) FROM milk_tea AS m;

在这里插入图片描述

6. 一行写多个聚合函数

SELECT 
COUNT(m.sale_price) 
, MIN(m.sale_price) 
, MAX(m.sale_price) 
, SUM(m.sale_price)
, AVG(m.sale_price)
FROM milk_tea AS m;

在这里插入图片描述

#如果有两个条件，分别聚合之后的得到两个结果，怎么放在一起呈现？

举例:重量是100g的一组，重量150g一组，对他们分组进行聚合，然后同时呈现结果，如何做？用到分组数据。

分组数据

在这里插入图片描述

1. 原理

数据分组，再在各组中进行聚合。分组的目的是聚合，所以分组函数与聚合函数同时出现。

2. 语句结构

SELECT … FROM … （这是必需子句）
WHERE +条件 （这是过滤子句）
GROUP BY +聚合键（这是分组子句）

注：SELECT子句中组成 包括聚合键、聚合函数、常数（作修饰）

3. 分组关键字

GROUP BY +分组子句（聚合键）

解释：聚合键就是要分组的列，即GROUP BY +指定列
比如按照重量进行分组，100g与150g两个标准分组，那么重量这组net_wet就是聚合键

4. 结果

（1）是多行数据
（2）一行数据代表一个分组
（3）SELECT展示的列必须与分组对应，聚合键决定分组标准，那么SELECT展示的列 要有聚合键所在列、聚合函数作用得到的结果列
（4）NULL值单独成为一个分组
（5）聚合键尽量不要出现别名，指的是用SELECT可以对列名检索呈现时起一个别名，但是聚合键不要起别名，因为后面要用

聚合函数：是为了达到聚合目的，比如求和、求均值

Navicat操作

（1）直接分组聚合

SELECT 
m.net_w 
, SUM(m.sale_price)
FROM milk_tea AS m 
GROUP BY m.net_w;

解释：
（1）SELECT后面第一个是聚合键，即以net_w列进行聚合（这是分类标准），第二个是聚合函数SUM，是对sale_price进行聚合（这是每个类别的聚合目标）。
（相当于看net_w中有几类，对每一类中的值进行SUM聚合）
（2）GROUP BY 后面跟的是聚合键net_w，意思是按照聚合键net_w进行分组，比如聚合键net_w中去重后有3类，那么每一类就是一组。

在这里插入图片描述

（2）先过滤，后分组聚合

SELECT 
m.net_w 
, SUM(m.sale_price) 
FROM milk_tea AS m 
WHERE m.net_w IN ('100g','150g')
GROUP BY m.net_w;

解释：
增加了过滤语句WHERE，运行原理是先对结果进行过滤，过滤完后，再根据100g和150g这两类进行聚合分组求和。

在这里插入图片描述

SELECT 
m.net_w 
, COUNT(m.sale_price) 
FROM milk_tea AS m 
WHERE m.net_w IN ('100g','150g')
GROUP BY m.net_w;

解释：运行原理是先对结果进行100g和150g的过滤，过滤完后，再聚合分组，即分别对每组进行COUNT计数。

在这里插入图片描述
其他的聚合函数都一样，注意每个部分的位置。

（3）先聚合，后过滤？

比如我想看每种重量net_w下的总价格，对这个价格满足一定的条件进行过滤怎么做？分组过滤

-------分组过滤

在这里插入图片描述

1.分组过滤

将数据分组，各组中进行聚合。对聚合结果再次筛选（即过滤），得到所需分组。

注：再次筛选的语句一定在GROUP BY 之后。

2.结构

SELECT…
FROM…
WHERE + 过滤条件
GROUP BY + 聚合键
HAVING +聚合函数（这是分组筛选子句）；

示例：

SELECT brand,
       COUNT(*)
FROM milk_tea
WHERE cost > 5
GROUP BY brand
HAVING COUNT(*) > =2 ;

解释：
（1）WHERE 指定”行“的条件，HAVING 指定”组“的条件。

即先WHERE 对整张表进行过滤，再GROUP BY对过滤的结果进行分组，再HAVING对分组结果进行筛选，结果是只剩下满足条件的组。

（2）分组之前排除的行不参与分组，意思是WHERE是分组之前进行的过滤，利用WHERE过滤掉的行便不再参与GROUP BY 的分组和HAVING的分组过滤。

Q：聚合键到底写在哪里？
A：首先，明确分组完后的结果包含聚合键和聚合函数结果两列内容，其次，对聚合键的筛选用普通WHERE行筛选就可以完成。所以聚合键写在WHERE中更合适

SELECT 
       m.net_w ,
       SUM(m.sale_price) 
FROM milk_tea AS m
GROUP BY  m.net_w;

注解：
相当于以m.net_w为类，筛选出展示m.net_w , SUM(m.sale_price) 这两列，其中m.net_w列中每类的和是对应的 SUM(m.sale_price) 中的值。
在这里插入图片描述

SELECT 
m.net_w 
, SUM(m.sale_price) 
FROM milk_tea AS m
GROUP BY  m.net_w
HAVING SUM(m.sale_price) > 20;
#这里，Having后跟的是聚合函数结果列，即对分组结果进行过滤

#HAVING相当于在分组聚合的结果下（此时两列为m.net_w和SUM(m.sale_price)），取出此时表中SUM(m.sale_price)>20的行

注：这里Having后跟的是聚合函数结果列，即对分组结果进行过滤
结果：
在这里插入图片描述
也可以对分组聚合后的net_w作为条件进行筛选，但是有一个问题，下面解释这个问题。

代码：

SELECT 
m.net_w 
, SUM(m.sale_price) 
FROM milk_tea AS m
GROUP BY  m.net_w
HAVING m.net_w IN ('100g','150g');
#不推荐这种方法

在这里插入图片描述
这个问题就是，对聚合键net_w的过滤完全可以在之前完成（分组之前）。

其实可以在分组聚合前就把where m.net_w IN (‘100g’,‘150g’)作为条件进行筛选，这样效率更高，因为这样是先过滤再进行分组聚合的求和计算，之前用Having那种方法是先进行分组聚合过滤，再进行条件过滤，所以Having的效率相对低一点，也比较能顺下来，符合逻辑。WHERE方法的代码就变成：

SELECT 
m.net_w 
, SUM(m.sale_price) 
FROM milk_tea AS m
WHERE m.net_w IN ('100g','150g')
GROUP BY  m.net_w ;

在这里插入图片描述
建议：先进行过滤，再进行分组聚合，这样对HAVING后面接的都是聚合函数，因为聚合函数只有在分组之后才能有这个结果。

举例
（1）在分组后对聚合键进行过滤

SELECT 
p.class
, COUNT(1)
, SUM(p.sale_price) 
, AVG(p.sale_price)
FROM prod_info as P
GROUP BY p.class;

结果：
在这里插入图片描述
（2）在分组后对聚合结果列进行过滤

SELECT 
p.class
, COUNT(1)
, SUM(p.sale_price) 
, AVG(p.sale_price)
FROM prod_info as P
GROUP BY p.class
HAVING COUNT(1) > 4;

在这里插入图片描述

感受一下，在分组前过滤表中的行，在分组后对组进行过滤，读起来更顺一点。

所以sql中的书写顺序和执行顺序他们的逻辑是怎样的？

在这里插入图片描述再举例感受：

（1）先过滤后分组

SELECT 
p.class
, COUNT(1)
, SUM(p.sale_price) 
, AVG(p.sale_price)
FROM prod_info as P
WHERE P.class != '零食'
GROUP BY p.class;

在这里插入图片描述
（2）先过滤后分组

SELECT 
p.class
, COUNT(1)
, SUM(p.sale_price) 
, AVG(p.sale_price)
FROM prod_info as P
WHERE p.sale_price > 10
GROUP BY p.class;

在这里插入图片描述

总结：

1.sql语句的书写顺序

SELECT FROM
WHERE （过滤的是行）
GROUP BY （分组条件）
HAVING （筛选的是组）
ORDER BY （作为排序条件的组）
;

2.sql语句的执行顺序

FROM （确定从哪个表中选数据）
WHERE （确定选什么数据）
GROUP BY （对选出来的数据进行分组）
HAVING （对分组的数据进行组的筛选）
SELECT （然后对满足条件的具体的列做一个展示）
ORDER BY （展示的时候进行排序）

注： 尽量不要对GROUP BY使用别名，但是ORDER BY可以使用别名。
（因GROUP BY是先执行的，之后才执行SELECT将列名定为别名，所以要注意尽量在GROUP BY中不要使用别名，比如当使用的DBMS不智能时就会出错。）

SQL 聚合函数中的数据过滤
weixin_44519342的博客
01-24 417
SQL 聚合函数中的数据过滤
Mysql 与聚合函数在一起时候where条件和having条件的过滤时机
男儿当自强
12-25 6618
where 在聚合之前过滤 当一个查询包含了聚合函数及where条件，像这样的情况 select max(cid) from t where t.id 这时候会先进行过滤，然后再聚合。先过滤出ID《999的记录，再查找最大的cid返回。 having 在聚合之后过滤 having在分组的时候会使用，对分组结果进行过滤，通常里面包含聚合函数。 SELECT ip,MAX(
SQL 排序，筛选，过滤，聚合函数
乱写乱画
05-19 1907
Coalesce将空值换为其他值 coalesce(col1,col2,col3..) Delete 删除语句 delete from 表名 where 条件; 注意：delete  语句如果没有加where条件，将会把表中所有数据删除 count（*）表示当前表中一共有多少记录 select count(*） from 表名； like 语句 一般配合通配符使用 通配符%：表示...
sql中group by ，order by，sum，count用法
08-24 9759
<br />sql语句Group By用法一则<br />如果我们的需求变成是要算出每一间店 (store_name) 的营业额 (sales)，那怎么办呢？在这个情况下，我们要做到两件事：第一，我们对于 store_name 及 Sales 这两个栏位都要选出。第二，我们需要确
SQL回炉重造08_数据过滤_聚合函数
qq_45703204的博客
05-19 180
汇总数据 1. 聚集函数 聚集函数（aggregate function）：运行在行组上，计算和返回单个值的函数 作用：对表中数据进行汇总，如确定表中行数(或满足某个条件/特定值的行数)，获得表中行组的和，找出表列中极值、平均值等 SQL聚集函数： 函数 说明 AVG() 返回某列的平均值 COUNT() 返回某列的行数 MAX() 返回某列的最大值 MIN() 返回某列的最小值 SUM() 返回某列值之和 1.1 AVG()函数 AVG()函数
深入理解group by 语句的执行顺序 from→where→group by→select（含聚合函数）
weixin_30278311的博客
09-26 895
　　由于之前没有对group by 语句的执行顺序（执行原理）做深入的了解，所以导致在实际应用过程中出现了一些问题。举个简单的粟子，比如一个表testA中的所有数据如下图： 　　我现在想从testA中查询username字段，并只取前7位，取了前7位后，想让相同的数据就合并为一条，我就想当然地写了如下语句：“select substr(username,0,7) username from ...
SQL先计算再聚合与先聚合再计算之间的区别
dufemt的博客
08-18 2489
做客户分析的时候遇到了一个小问题，要统计客户的性别，但是有些客户的性别为空（empty）。所以要统计下有性别标记的客户有多少。写了两个SQL。 三行数据，两行GOOD分别对应男性和女性。两个查询产生不同的原因在于CASE语句计算是发生在聚合操作之前还是之后。第一个CASE发生在聚合之前，第二个发生在聚合之后。所以要注意如果对应的GROUP BY 子句没有使用相同的表达式，聚合操作可能不会减少...
聚合函数和Group by
qq_31209383的博客
06-15 3622
聚合函数： sql语言中一种特殊的函数:聚合函数，SUM, COUNT, MAX, MIN, AVG等。这些函数和其它函数的根本区别就是它们一般作用在多条记录上。SELECT SUM(population) FROM COUNTRY 这里的SUM作用在所有返回记录的population字段上，结果就是该查询只返回一个结果，即所有国家的总人口数。通过使用GROUP BY 子句，可以让SUM 和 CO
min(),max(),avg(),sum()聚合函数和group by分组和having过滤
qq_41617901的博客
10-22 357
聚合函数和group by经常一起使用，如求出学生的每门的平均分，先用select avg(score) as dividedScore，然后根据学生id进行分组， group by studentId,最后使用having过滤出平均分大于60分的学生，having dividedScored>60; ...
MySQL——聚合函数和group by分组的使用
万里顾一程的博客
02-14 2万+
文章目录MySQL——聚合函数和group by分组的使用1、聚合函数介绍2、GROUP BY 分组3、常见的聚合函数4、SQL执行顺序 MySQL——聚合函数和group by分组的使用 1、聚合函数介绍 SQL聚合函数计算一组数据的集合并返回单个值。 除 COUNT 以外，聚合函数忽略空值，如果COUNT函数的应用对象是一个确定列名，并且该列存在空值，此时COUNT仍会忽略空值。 因为聚合函数对一组值进行操作，所以它通常与SELECT语句的GROUP BY子句一起使用，以计算为每个分组提供信息的度量。
SQL聚合操作之-count(*)记录数
SimpleLife
01-14 2380
count(*) :记录数 count(*)和count(1)其实并没有区别，这两者都会将表中所有行都算进来，也就是该表的总行数. 实例如下： 1、使用 select * from emp;查询，执行sql语句，显示结果如下: 2、 使用select count(*) from emp;执行sql语句，显示结果如下: 3、 使用select count(1) from e...
SQL中COUNT()筛选条件查询
dou_qs的博客
07-10 6076
我自己摸索出来两种，其他的统计都差不多 dept=10 的加1 不能省去or null 不然会变成 总和 select count ( dept = '10' or null) from dept 如果满足dept=10 则加1 否则为null（切记不能不能为0，为0的话会统计总和） select count(if (dept = '10', 1 ,null)) from dept ...
分组聚合统计查询HAVING和SELECT的用法
Suk__的博客
07-22 1521
有这么一张表，下面对其进行分组聚合查询： --分组聚合统计 select areaid,sum(money) from t_account group by areaid having sum(money)&gt;169000; 查询结果： 上述查询就采用了聚合函数统计分组查询，是求水费合计大于169000的区域以及水费合计，having是针对分组后的结果添加条件的，和where...
SQL SUM() 函数、SQL GROUP BY 语句、SQL HAVING 子句
热门推荐
Cactus
08-02 6万+
SQL SUM() 函数 SUM() 函数 SUM 函数返回数值列的总数（总额）。 SQL SUM() 语法 SELECT SUM(column_name) FROM table_name SQL SUM() 实例 我们拥有下面这个 "Orders" 表：
MySQL中having的用法(聚合函数，如sum，count后，又需要筛选条件时)
php菜鸟技术天地
11-18 3652
mysql中，当我们用到聚合函数，如sum，count后，又需要筛选条件时，having就派上用场了，因为WHERE是在聚合前筛选记录的，having和group by是组合着用的。然后可以用having对统计的数据进一步筛选，比如nums大于2的数。注意 having后的判断字段必须是聚合函数返回的结果。先查询分类cid下 id的统计数目。MySQL中having的用法。
Hive：聚合函数、GROUP BY、ORDER BY、LIMIT、执行顺序和JOIN、函数
qq_43601664的博客
03-12 3910
HQL
MySQL语句的执行顺序
zongx的博客
08-15 667
SQL Select语句完整的执行顺序： 1、from子句组装来自不同数据源的数据； 2、where子句基于指定的条件对记录行进行筛选； 3、group by子句将数据划分为多个分组； 4、使用聚集函数进行计算； 5、使用having子句筛选分组； 6、计算所有的表达式； 7、select 的字段； 8、使用order by对结果集进行排序。 SQL语言不同于其他编...
SQL中SUM()和COUNT()的使用
weixin_45132249的博客
05-23 3965
SQL中SUM()和COUNT()的使用 常用于： count() -- 返回某列的行数 sum() -- 返回某列值之和 -- 统计emp表中薪资大于3000的员工人数 select count(*) from emp where sal>3000; -- 统计emp表中所有员工的总薪资(不包含奖金) select sum(sal) as 员工总薪资 from emp; count()为空时返回结果为0 sum()为空时返回结果为null PS:如果想要sum()不返回null -- 利用i
tar -czf html.tar.gz -C /usr/share
最新发布
06-29
- **文件过滤条件过于严格**：通过管道传入文件列表（如结合 `find` 命令）时，若筛选结果为空，则 `tar` 无法读取到任何文件。 - **权限问题导致无法访问文件**：某些情况下，执行 `tar` 的用户可能没有读取目标...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值