聚合函数的用法

       MySQL中有5种聚合函数较为常用，分别是求和函数SUM()、求平均函数AVG()、最大 值函数MAX()、最小值函数MIN()和计数函数COUNT。接下来，我就结合超市项目的真实需求，来带你掌握聚合函数的用法，帮你实现高效的分组统计。咱们的项目需求是这样的：超市经营者提出，他们需要统计某个门店，每天、每个单品的销售情况，包括销售数量和销售金额等。这里涉及3个数据表，具体信息如下：

要统计销售，就要用到数据求和，那么我们就先来学习下求和函数SUM()。

SUM ()函数

可以返回指定字段值的和。我们可以用它来获得用户某个门店，每天，每种商品的销售总计数据:

LEFT(str, n):

表示返回字符串str最左边的n个字符。我们这里的

LEFT (a.transdate,10),表示返回交易时间字符串最左边的10个字符。在MySQL中, DATETIME类型的默认格式是：YYYY-MM-DD,也就是说，年份4个字符，之后是”,然后是月份2个字符，之后又是,然后是日2个字符，所以完整的年月日是10个字符。用户要求按照日期统计，所以，我们需要从日期时间数据中，把年月日的部分截取岀来。

ORDER BY:

表示按照指定的字段排序。超市经营者指定按照日期和单品统计，那么，统计的结果按照交易日期和商品名称的顺序排序，会更加清晰。知道了 2个关键字之后，刚刚的查询就容易理解了。接下来我们就再拆解一下，看看这个査询是如何执行的。我用图表来直观地演示一下各个步骤。

第一步，完成3个表的连接(由于字段比较多，为了你理解，我省略了一些在这一步不重要的字段。

第二步，对结果集按照交易时间和商品名称进行分组，我们可以分成下面4组。

第三步，对各组的销售数量和销售金额进行统计，并且按照交易日期和商品名称排序。这样就得到了我们需要的结果。

如果用户需要知道全部商品销售的总计数量和总计金额，我们也可以把数据集的整体看作一个分组，进行计算。这样就不需要分组关键字GROUP BY,以及排序关键字ORDER BY了。你甚至不需要从关联表中获取数据，也就不需要连接了。就像下面这样:

到这里呢，求和函数SUM()的使用方法我就讲完了。需要提醒你的是，求和函数获取的是分组中的合计数据，所以你要对分组的结果有准确的把握，否则就很容易搞错。这也就是说，你要知道是按什么字段进行分组的。如果是按多个字段分组，你要知道字段之间有什么样的层次关系；如果是按照以字段作为变量的某个函数进行分组的，你要知道这个函数的返回值是什么，返回值又是如何影响分组的等。

AVG ()、MAX ()和 MIN ()

1.AVG ()

首先，我们来学习下计算平均值的函数AVG ()。它的作用是，通过计算分组内指定字段值的和，以及分组内的记录数，算出分组内指定字段的平均值。举个例子，如果用户需要计算每天、每种商品，平均一次卖出多少个、多少钱，这个时候,我们就可以用到AVG ()函数了，如下所示:

2.MAX ()和 MIN ()

MAX()表示获取指定字段在分组中的最大值，MIN表示获取指定字段在分组中最小值。它们的实现原理差不多，下面我就重点讲一下max(),知道了他的用法，min()也就很容易就理解了。举个例子，用户要计算每天里的一次销售的最大数量和最大金额，就可以用下面的代码，得到想要的结果。

SELECT

-> LEFT(a.transdate, 10), 

-> Max(b.quantity), 

-> Max(b.salesvalue) 

-> FROM  demo.transactionhead a

-> JOIN

-> demo.transact!ondetaiIs b ON (a.transactionid

-> JOIN

-> demo.goodsmaster c ON (b.itemnumber

-> GROUP BY LEFT(a.transdate,10),c.goodsname

-> ORDER BY LEFT(a.transdate,10),c.goodsname；

| LEFT(a.transdate, 10) | goodsname | AVG(b.quantity) | AVG(b.salesvalue) |

所以，MAX (字段)这个函数返回分组集中最大的那个值。如果你要查询MAX (字段1)和MAX (字段2),而它们是相互独立、分别计算的，你千万不要想当然地认为结果在同一条记录上。那样的话，你就掉坑里了。

COUNT ()

通过COUNT (),我们可以了解数据集的大小，这对系统优化十分重要。举个小例子，在项目实施的过程中，我们遇到了这么一个问题：由于用户的销售数据很多,而且每天都在增长，因此，在做销售査询的时候，经常会遇到卡顿的问题。这是因为，査询的数据量太大了，导致系统不得不花很多时间来处理数据，并给数据集分配资源，比如内存什么的。

怎么解决卡顿的问题呢？

我们想到了一个分页的策略。所谓的分页策略，其实就是，不把查询的结果一次性全部返回给客户端，而是根据用户电脑屏幕的大小，计算一屏可以显示的记录数，每次只返回用户电脑屏幕可以显示的数据集。接着，再通过翻页、t跳转等功能按钮，实现查询目标的精准锁定。这样一来，每次查询的数据量较少，也就大大提高了系统响应速度。这个策略能够实现的一个关键，就是要计算出符合条件的记录_共有多少条，之后才能计算出一共有几页、能不能翻页或跳转。要计算记录数，就要用到COUNT（）函数了。这个函数有两种情况。

• COUNT (*):统计一共有多少条记录;

• COUNT (字段):统计有多少个不为空的字段值。

1.COUNT(*)

如果count(*)与group by一起使用，就表示统计分组内有多少条数据。它也可以单独使用，这就相当于数据集全体是一个分组，统计全部数据集的记录数。

如果我们一屏可以显示30行，需要多少页才能显示完这个表的全部数据呢?

那么，如果超市经营者想知道，每天、每种商品都有几次销售，我们就需要按天、按商品名

称，进行分组查询:

2.COUNT (字段)

COUNT (字段)用来统计分组内这个字段的值岀现了多少次。如果字段值是空，就不统计。

为了说明它们的区别，我举个小例子。假设我们有这样的一个商品信息表，里面包括了商品编号、条码、名称、规格、单位和售价的信息。

       如果我们要统计字段”goodsname”岀现了多少次,就要用到函数COUNT (goodsname),结果是 3 次：如果我们统计字段"specification",用COUNT(specification),结果是1次:你可能会问，为啥计数字段"goodsname"的结果是3,计数字段”speci尸段n”却只 有1呢？其实，这里的原因就是，3条记录里面的字段”goodsnam#没有值，因此被 统计了 3次；而字段"specification"有2个空值，理解了这一点,你就可以利用计数函数对某个字段计数时,不统计空值的特点，对表中字段的非空值进行计数。