【MySQL必知必会】：五个聚集函数

言羽
152 阅读4分钟

开启掘金成长之旅！这是我参与「掘金日新计划 · 2 月更文挑战」的第 22 天，点击查看活动详情

聚集函数

聚集函数（aggregate function）：运行在组上，计算和返回单个值的函数

有时，我们不用检索出实际数据，只需汇总数据。此时我们可以使用 MySQL 提供的聚集函数，便于分析和报表生成。

比如：

  • 确定表中总行数（或者满足某个条件的特定行数总数）
  • 获得表中行组的和
  • 找出表列（或者行）的最大值，最小值和平均值

上述例子都是用于对表中的数据进行汇总，而不是检索实际数据本身。

下面是五种聚集函数的相关记录，如下所示：

  • avg()：返回某列的平均值
  • count()：返回某列的行数
  • max()：返回某列的最大值
  • min()：返回某列的最小值
  • sum()：返回某列值之和

注意：利用标准的算术运算符，所有的聚集函数都可以用来执行多个列上的计算。

avg

avg() 函数通过对表中行数计数并计算特定列值之和，求得该列的平均值。

备注：avg()函数既可以用来返回所有列的平均值，也可以用于返回特定列或行的平均值。

比如，现在使用 avg() 函数来返回 products 表中所有产品的平均价格。

select
    avg(price) as avg_price
from
    products;

上述例子中，avg_price 是一个别名。

下面，是利用 avg() 函数来返回特定品牌所提供的产品的平均价格：

select
    avg(price) as avg_price
from
    products
where
    brand_id = 1001;

上述例子中，where 子句仅用于过滤出品牌 brand_id 为 1001 的产品。因此该 SQL 语句可以返回指定品牌产品的平均值。

最后需要注意的是，avg() 函数会忽略列值为 NULL 的行，并且只能用于确定特定数值列的平均值，而列名必须作为函数参数给出。（如果想要获得多个列的平均值，则必须使用多个 avg() 函数）

count

count() 函数用于计数，即确定表中行的数目，或者符合特定条件的行的数目。

一般来说，count() 函数有两种使用方式：

  • count(*)：对表中所有行数进行汇总，得出总行数的数目（包含空值 NULL）。
  • count(列)：对特定列中具有值的行进行计数（此时不包含空值 NULL）

比如，下面的例子用于返回 customers 表中客户的总数：

select
    count(*) as num_cust
from
    customers;

下面这个例子则是返回 customers 表中具有电子邮件地址的客户总数：

select
    count(cust_email) as num_cust
from
    customers;

max 和 min

max() 函数用于返回列中的最大值，只需将指定的列名当做参数传给 max() 即可。

比如，下面的例子用于返回 customers 表中价格最大的物品。

select
    max(price) as max_price
from
    products;

与 max() 功能相反的函数是 min() 函数，它返回指定列的最小值，同样，我们需要将指定的列名传给该函数。

比如：

select
    min(price) as min_price
from
    products;

注意：

  • max() 和 min() 函数都会忽略列值为 NULL 的行
  • 如果对非数值数据使用 max() 和 min() 函数也是允许的。

sum

sum() 函数用于返回指定列值的总和。

比如，现在有一个订单表 orderitems，表中包含了订单中实际的物品，每个物品都有相应的数量 quantity 。下面的语句用于返回物品的总数（所有 quantity 的总和）。

select
    sum(quantity) as items_ordered
from
    orderitems
where
    order_id = 10001;

下面的例子则是用于返回订单总额：

select
    sum(item_price * quantity) as total_price
from
    orderitems
where
    order_id = 10001;

注意：sum 函数会忽略列值为 NULL 的行。

DISTINCT

在 MySQL5 及后期版本，使用聚集函数可以添加 DISTINCT 参数，记录如下：

  • 不指定参数：则默认是 ALL ，因为 ALL 是默认行为
  • 指定 ALL参数：对所有的行执行计算
  • 指定 DISTINCT参数：只包含不同的值

来看一个例子：

select
    avg(DISTINCT price) as avg_price
from
    products
where
    brand_id = 1001;

同样是返回特定品牌的产品平均价格，但使用了 DISTINCT 参数后，平均值只考虑各个不同的价格。

注意：

  • DISTINCT 参数不能用于 count(*)，不允许使用 count(DISTINCT)，会产生错误
  • DISTINCT 必须使用列名，不能用于计算或表达式

组合聚集函数

一条 select 语句可以包含多个聚集函数，比如：

select
    count(*) as num_items,
    min(price) as price_min,
    max(price) as price_max,
    avg(price) as price_avg
from
    products;

上述语句执行了 4 个聚集计算，返回了产品表中物品的总数目，价格的最高值和最低值以及平均值。

注意：

  • 一个良好的习惯就是使用唯一的标识符来命名某个聚集函数的返回结果，即指定别名，而不是使用表中实际的别名。
avatar
文章
阅读
粉丝