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"

---

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"