不放在第六篇中,是因为这个函数使用频率太高,所以单独作为一节。
聚合函数的使用场景
- 确定表中行数(或者满足某个条件或包含某个特定值的行数);
- 获得表中某些行的和;
- 找出表列(或所有行或某些特定的行)的最大值、最小值、平均值
也就是需要汇总表中的数据,而不是直接获取表中的数据。
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"
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
SELECT goodsName, SUM(DISTINCT quantity*item_price) AS totalPrice FROM "oderlist" GROUP BY goodsName
说明:
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"