MySQL必知必会 08丨聚合函数：怎么高效地进行分组统计？

MySQL 中有 5 种聚合函数较为常用，分别是

求和函数 SUM()
求平均函数 AVG()
最大值函数` MAX()
最小值函数MIN()
计数函数 COUNT()

接下来，结合超市项目的真实需求，来掌握聚合函数的用法，实现高效的分组统计。

项目需求：超市经营者提出，需要统计某个门店，每天、每个单品的销售情况，包括销售数量和销售金额等。这里涉及 3 个数据表，具体信息如下所示：

销售明细表（demo.transactiondetails)

transactionid	itemnumber	quantity	price	salesvalue
1	1	2	89	178
1	2	5	5	25
2	1	3	89	267
2	2	6	5	30
3	1	1	89	89
3	2	10	5	50

销售单头表（demo.transactionhead)

transactionid	transactionno	cashierid	memberid	operatorid	transdate
1	0120201201000001	1	1	1	2020-12-01 14:25:56
2	0120201202000001	1	NULL	2	2020-12-02 10:50:50
3	0120201202000002	1	NULL	1	2020-12-02 12:10:05

商品信息表（demo.goodsmaster）

itemnumber	barcode	goodsname	specification	unit	salesprice
1	1	书	16开	本	89
2	2	笔	10支装	包	5

Mysql 数据准备：

demo.transactiondetails

create table demo.transactiondetails
(
transactionid int,
itemnumber int,
quantity decimal(10,3),
price decimal(5,2),
salesvalue decimal(5,2)
);
describe transactiondetails;

insert into demo.transactiondetails (transactionid, itemnumber, quantity, price, salesvalue) values (1, 1, 2, 89, 178);
insert into demo.transactiondetails (transactionid, itemnumber, quantity, price, salesvalue) values (1, 2, 5, 5, 25);
insert into demo.transactiondetails (transactionid, itemnumber, quantity, price, salesvalue) values (2, 1, 3, 89, 267);
insert into demo.transactiondetails (transactionid, itemnumber, quantity, price, salesvalue) values (2, 2, 6, 5, 30);
insert into demo.transactiondetails (transactionid, itemnumber, quantity, price, salesvalue) values (3, 1, 1, 89, 89);
insert into demo.transactiondetails (transactionid, itemnumber, quantity, price, salesvalue) values (3, 2, 10, 5, 50);

mysql> select * from demo.transactiondetails;
+---------------+------------+----------+-------+------------+
| transactionid | itemnumber | quantity | price | salesvalue |
+---------------+------------+----------+-------+------------+
|             1 |          1 |    2.000 | 89.00 |     178.00 |
|             1 |          2 |    5.000 |  5.00 |      25.00 |
|             2 |          1 |    3.000 | 89.00 |     267.00 |
|             2 |          2 |    6.000 |  5.00 |      30.00 |
|             3 |          1 |    1.000 | 89.00 |      89.00 |
|             3 |          2 |   10.000 |  5.00 |      50.00 |
+---------------+------------+----------+-------+------------+

demo.transactionhead

create table demo.transactionhead
(
transactionid int,
transactionno text,
cashierid int,
memberid int,
operatorid int,
transdate datetime
);
describe transactionhead;

insert into demo.transactionhead (transactionid, transactionno, cashierid, memberid, operatorid, transdate) values (1, '0120201201000001', 1, 1, 1, '2020-12-01 14:25:56');
insert into demo.transactionhead (transactionid, transactionno, cashierid, memberid, operatorid, transdate) values (2, '0120201202000001', 1, null, 2, '2020-12-02 10:50:50');
insert into demo.transactionhead (transactionid, transactionno, cashierid, memberid, operatorid, transdate) values (3, '0120201202000002', 1, null, 1, '2020-12-02 12:10:05');
select * from demo.transactionhead;

mysql> select * from demo.transactionhead;
+---------------+------------------+-----------+----------+------------+---------------------+
| transactionid | transactionno    | cashierid | memberid | operatorid | transdate           |
+---------------+------------------+-----------+----------+------------+---------------------+
|             1 | 0120201201000001 |         1 |        1 |          1 | 2020-12-01 14:25:56 |
|             2 | 0120201202000001 |         1 |     NULL |          2 | 2020-12-02 10:50:50 |
|             3 | 0120201202000002 |         1 |     NULL |          1 | 2020-12-02 12:10:05 |
+---------------+------------------+-----------+----------+------------+---------------------+

demo.goodsmaster

create table demo.goodsmaster
(
itemnumber int,
barcode int,
goodsname text,
specification text,
unit text,
salesprice decimal(10,3)
);

insert into demo.goodsmaster (itemnumber, barcode, goodsname, specification, unit, saleprice) values (1, 1, 书, 16开, 本, 89.00);
insert into demo.goodsmaster (itemnumber, barcode, goodsname, specification, unit, saleprice) values (2, 2, 笔, 10支装, 支, 5.00);

mysql> select * from demo.goodsmaster;
+------------+---------+-----------+---------------+------+-----------+
| itemnumber | barcode | goodsname | specification | unit | saleprice |
+------------+---------+-----------+---------------+------+-----------+
|          1 | 0001    | 书        | 16开          | 本   |     89.00 |
|          2 | 0002    | 笔        | 10支装        | 支   |      5.00 |
+------------+---------+-----------+---------------+------+-----------+

SUM

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

mysql> select left(b.transdate, 10), c.goodsname, sum(a.quantity), sum(a.salesvalue)
    -> from demo.transactiondetails as a
    -> join demo.transactionhead as b on (b.transactionid = a.transactionid)
    -> join demo.goodsmaster as c on (c.itemnumber = a.itemnumber)
    -> group by left(b.transdate, 10), c.goodsname
    -> order by left(b.transdate, 10), c.goodsname;
+-----------------------+-----------+-----------------+-------------------+
| left(b.transdate, 10) | goodsname | sum(a.quantity) | sum(a.salesvalue) |
+-----------------------+-----------+-----------------+-------------------+
| 2020-12-01            | 书        |           2.000 |            178.00 |
| 2020-12-01            | 笔        |           5.000 |             25.00 |
| 2020-12-02            | 书        |           4.000 |            356.00 |
| 2020-12-02            | 笔        |          16.000 |             80.00 |
+-----------------------+-----------+-----------------+-------------------+

left(str，n)：表示返回字符串 str 最左边的 n 个字符。这里left（a.transdate,10）表示返回交易时间字符串最左边的 10 个字符。在 MySQL 中，DATETIME 类型的默认格式是：YYYY-MM-DD，也就是说，年份 4 个字符，之后是“-”，然后是月份 2 个字符，之后又是“-”，然后是日 2 个字符，所以完整的年月日是 10 个字符。用户要求按照日期统计，所以，我们需要从日期时间数据中，把年月日的部分截取出来。
order by：表示按照指定的字段排序。超市经营者指定按照日期和单品统计，那么，统计的结果按照交易日期和商品名称的顺序排序，会更加清晰。

这个查询是如何执行的，用图表来直观地演示一下各个步骤：

完成 3 个表的连接（省略了一些在这一步不重要的字段）：

对结果集按照交易时间和商品名称进行分组，可以分成下面 4 组：

12.1号-商品1

12.1号-商品2

12.2号-商品1

12.2号-商品2

对各组的销售数量和销售金额进行统计，并且按照交易日期和商品名称排序：

+-----------------------+-----------+-----------------+-------------------+
| left(b.transdate, 10) | goodsname | sum(a.quantity) | sum(a.salesvalue) |
+-----------------------+-----------+-----------------+-------------------+
| 2020-12-01            | 书        |           2.000 |            178.00 |
| 2020-12-01            | 笔        |           5.000 |             25.00 |
| 2020-12-02            | 书        |           4.000 |            356.00 |
| 2020-12-02            | 笔        |          16.000 |             80.00 |
+-----------------------+-----------+-----------------+-------------------+

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

mysql> select sum(quantity), sum(salesvalue)
    -> from demo.transactiondetails;
+---------------+-----------------+
| sum(quantity) | sum(salesvalue) |
+---------------+-----------------+
|        27.000 |          639.00 |
+---------------+-----------------+

求和函数获取的是分组中的合计数据，所以要对分组的结果有准确的把握，否则就很容易搞错。

这也就是说，要知道是按什么字段进行分组的。如果是按多个字段分组，要知道字段之间有什么样的层次关系；如果是按照以字段作为变量的某个函数进行分组的，要知道这个函数的返回值是什么，返回值又是如何影响分组的等。

AVG()

通过计算分组内指定字段值的和，以及分组内的记录数，算出分组内指定字段的平均值。

举例，用户需要计算每天、每种商品，平均一次卖出多少个、多少钱

mysql> select left(b.transdate, 10), c.goodsname, avg(a.quantity), avg(a.salesvalue)
    -> from demo.transactiondetails as a
    -> join demo.transactionhead as b on (b.transactionid = a.transactionid)
    -> join demo.goodsmaster as c on (c.itemnumber = a.itemnumber)
    -> group by left(b.transdate, 10), c.goodsname
    -> order by left(b.transdate, 10), c.goodsname;
+-----------------------+-----------+-----------------+-----------------+
| left(b.transdate, 10) | goodsname | avg(a.quantity) | avg(salesvalue) |
+-----------------------+-----------+-----------------+-----------------+
| 2020-12-01            | 书        |       2.0000000 |      178.000000 |
| 2020-12-01            | 笔        |       5.0000000 |       25.000000 |
| 2020-12-02            | 书        |       2.0000000 |      178.000000 |
| 2020-12-02            | 笔        |       8.0000000 |       40.000000 |
+-----------------------+-----------+-----------------+-----------------+

MAX() 和MIN()

MAX() 表示获取指定字段在分组中的最大值，MIN() 表示获取指定字段在分组中的最小值。

假如用户要求计算每天里的一次销售的最大数量和最大金额：

mysql> select left(b.transdate, 10), max(a.quantity), max(a.salesvalue)
    -> from demo.transactiondetails as a
    -> join demo.transactionhead as b on (b.transactionid = a.transactionid)
    -> group by left(b.transdate, 10)
    -> order by left(b.transdate, 10);
+-----------------------+-----------------+-------------------+
| left(b.transdate, 10) | max(a.quantity) | max(a.salesvalue) |
+-----------------------+-----------------+-------------------+
| 2020-12-01            |           5.000 |            178.00 |
| 2020-12-02            |          10.000 |            267.00 |
+-----------------------+-----------------+-------------------+

千万不要以为 max(a.quantity), max(a.salesvalue) 算出的结果一定是同一条记录的数据。实际上，MySQL 是分别计算的。

max(字段) 这个函数返回分组集中最大的那个值。如果要查询max(字段1)和max(字段2) ，且它们是相互独立、分别计算的，那么就不要想当然地认为结果在同一条记录上。

COUNT()

我们通常使用分页策略来解决查询数据卡顿的问题，所谓分页策略就是每次查询只返回用户电脑屏幕可以显示的数据集。这一策略的关键就是要计算出符合条件的记录一共有多少条，之后才能计算一共有几页，能不能翻页或跳转。

要计算记录数，就要用到 COUNT() 函数了。这个函数有两种情况：

COUNT（*）：统计一共有多少条记录；
COUNT（字段）：统计有多少个不为空的字段值

count(*)

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

mysql> select count(*) from demo.transactiondetails;
+----------+
| count(*) |
+----------+
|        6 |
+----------+

超市经营者想知道，每天、每种商品都有几次销售，我们就需要按天、按商品名称，进行分组查询：

mysql> select left(b.transdate, 10), c.goodsname, count(*)
    -> from demo.transactiondetails as a
    -> join demo.transactionhead as b on (b.transactionid = a.transactionid)
    -> join demo.goodsmaster as c on (c.itemnumber = a.itemnumber)
    -> group by left(b.transdate, 10), c.goodsname
    -> order by left(b.transdate, 10), c.goodsname;
+-----------------------+-----------+----------+
| left(b.transdate, 10) | goodsname | count(*) |
+-----------------------+-----------+----------+
| 2020-12-01            | 书        |        1 |
| 2020-12-01            | 笔        |        1 |
| 2020-12-02            | 书        |        2 |
| 2020-12-02            | 笔        |        2 |
+-----------------------+-----------+----------+

count(字段)

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

mysql> select * from demo.goodsmaster;
+------------+---------+-----------+---------------+------+-----------+
| itemnumber | barcode | goodsname | specification | unit | saleprice |
+------------+---------+-----------+---------------+------+-----------+
|          1 | 0001    | 书        | 16开          | 本   |     89.00 |
|          2 | 0002    | 笔        | 10支装        | 支   |      5.00 |
|          3 | 003     | 纸        | NULL          | 张   |      0.10 |
+------------+---------+-----------+---------------+------+-----------+
3 rows in set (0.01 sec)

mysql> select count(goodsname) from demo.goodsmaster;
+------------------+
| count(goodsname) |
+------------------+
|                3 |
+------------------+
1 row in set (0.00 sec)

mysql> select count(specification) from demo.goodsmaster;
+----------------------+
| count(specification) |
+----------------------+
|                    2 |
+----------------------+
1 row in set (0.00 sec)

Channing's Blog