SQL学习七、聚合函数

不放在第六篇中,是因为这个函数使用频率太高,所以单独作为一节。

聚合函数的使用场景

  • 确定表中行数(或者满足某个条件或包含某个特定值的行数);
  • 获得表中某些行的和;
  • 找出表列(或所有行或某些特定的行)的最大值、最小值、平均值

也就是需要汇总表中的数据,而不是直接获取表中的数据。


1、常用的聚合函数

函 数说 明
AVG()返回某列的平均值
COUNT()返回某列的行数
MAX()返回某列的最大值
MIN()返回某列的最小值
SUM()返回某列值之和
GROUP_CONCAT()返回一个字符串

聚集函数用来汇总数据。SQL支持 5个聚集函数,可以用多种方法使用 它们,返回所需的结果。这些函数很高效,它们返回结果一般比你在自己的客户端应用程序中计算要快得多。

我们用下面这张表来演示上述函数

  • 建表语句
CREATE TABLE "oderlist" 
( "id" INTEGER NOT NULL, "goodsName" TEXT, "quantity" integer, "item_price" real, 
"orderNo" text, "userId" INTEGER, "userName" TEXT, "orderTime" TEXT, PRIMARY KEY ("id") )

数据表


AVG()函数 - 求均值

  • 比如,我们需要返回表中所有产品的平均价格,我们可以使用这样的SQL
SELECT AVG(item_price) avgPrice FROM "oderlist"
//...or
SELECT AVG(item_price) AS avgPrice FROM "oderlist"

查询结果

  • 比如,我们需要返回表中单价低于10块的所有产品的平均价格,我们可以使用这样的SQL
SELECT AVG(item_price) AS avgPrice FROM "oderlist" WHERE item_price <10

查询结果


COUNT()函数 - 求行数

可利用 COUNT()来确定表中行的数目或符合特定条件的行的数目。

  • 使用 COUNT(*)对表中行的数目进行计数时,不管表列中包含的是空值(NULL)还是非空值都会被计数,因为*代表了所有。
SELECT COUNT(*) FROM "oderlist"

查所有行

  • 使用 COUNT(column)对特定列中具有值的行进行计数,忽略 NULL 值。
SELECT COUNT(item_price) FROM "oderlist"

忽略NULL


MAX()函数 - 返回指定列中的最大值

1、需要指定列名
2、 MAX()一般用来找出最大的数值或日期值,但许多(并非所有) DBMS 允许将它用来返回任意列中的最大值,包括返回文本列中的最 大值
3、在用于文本数据时,MAX()返回按该列排序后的最后一行,比如某(文本)列中的数据是“5-5”、“5-6”、“5-10”,那么该列的排序会是“5-10”、“5-5”、“5-6”,返回最后一行会返回“5-6”,像这种情况我们需要注意,解决方法有很多比如:先利用字符长度进行一次排序
4、MAX()函数忽略列值为 NULL 的行。

  • 比如,我们需要返回表中的最大单价,我们可以使用这样的SQL
SELECT MAX(item_price) AS maxPrice FROM "oderlist"

最大单价


MIN()函数 - 返回指定列中的最小值

1、需要指定列名
2、 MIN()一般用来找出最小的数值或日期值,但许多(并非所有) DBMS 允许将它用来返回任意列中的最小值,包括返回文本列中的最小值
3、在用于文本数据时,MIN()返回按该列排序后的最前面一行,比如某(文本)列中的数据是“5-5”、“5-6”、“5-10”,那么该列的排序会是“5-10”、“5-5”、“5-6”,返回最前面一行会返回“5-10”,像这种情况我们需要注意,解决方法有很多比如:先利用字符长度进行一次排序
4、MIN()函数忽略列值为 NULL 的行。

  • 比如,我们需要返回表中的最小单价,我们可以使用这样的SQL
SELECT MIN(item_price) AS maxPrice FROM "oderlist"

最小单价


SUM()函数 - 返回指定列值的和(总计)

SUM()函数忽略列值为 NULL 的行

  • 比如,我们需要返回某一种水果(“火龙果”)的销售数量,我们可以使用这样的SQL
SELECT SUM(quantity) AS totalQuantity FROM "oderlist" WHERE goodsName = '火龙果'

查询结果

  • 比如,我们需要返回各种类别水果的销售额,我们可以使用这样的SQL
SELECT goodsName, SUM(quantity*item_price) AS totalPrice FROM "oderlist" GROUP BY goodsName 

查询结果


GROUP_CONCAT() 函数

GROUP_CONCAT(x,y)函数返回一个字符串,该字符串将会连接所有非NULL的x值。该函数的y参数将作为每个x值之间的分隔符,如果在调用时忽略该参数,在连接时将使用缺省分隔符”,”。再有就是各个字符串之间的连接顺序是不确定的。

  • 使用默认分隔符
select group_concat(id) as ids from supplier_new

查询结果

  • 使用下划线分隔
select group_concat(id ,'_') as ids from supplier_new

查询结果


2、聚合函数的限制参数

聚合函数的限制参数有两个, ALL 参数(聚合所有行)DISTINCT 参数(聚合值不同的行),默认(不指定时)是ALL 参数

1、不要在 Access 中使用,Access在聚集函数中不支持 DISTINCT
2、DISTINCT 不能用于 COUNT(*)
3、DISTINCT 必须使用列名,不能用于计算或表 达式
4、有的DBMS还支持其他参数, 请参阅相应的文档。

使用,第一条 SQL 和 第二条 SQL 是一样的结果,第三条就会排除相同值的行。

SELECT goodsName, SUM(quantity*item_price) AS totalPrice FROM "oderlist" GROUP BY goodsName 

SELECT goodsName, SUM(ALL quantity*item_price) AS totalPrice FROM "oderlist" GROUP BY goodsName 

查询结果1、2

SELECT goodsName, SUM(DISTINCT quantity*item_price) AS totalPrice FROM "oderlist" GROUP BY goodsName 

查询结果3

说明:
1、DISTINCT 限制的是(quantity*item_price)的值,而不是quantity的值;
2、火龙果排除了一个数量为10的,一个数量为9的;
3、猕猴桃数量没有相同的,所以总价没变;
4、菠萝排除一个数量为6的;
5、上面只用数量说明,是因为它们同类型的水果单价都是一样的。
数据源


3、聚合函数的组合使用

我们可以在一条SQL中使用多个聚合函数
比如,我们需要查询订单列表中订单量(totalOrders)、最低单价(minItemPrice)、最高单价(maxItemPrice)、均价(avgItemPrice)

SELECT 
COUNT(*) AS totalOrders,
MIN(item_price) AS minItemPrice,
MAX(item_price) AS maxItemPrice,
AVG(item_price) AS avgItemPrice
FROM "oderlist" 

查询结果

  • 2
    点赞
  • 6
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值