08-聚合函数:高效进行分组统计

MySQL中的五种常用函数SUM()、AVG()、MAX()、MIN()、COUNT()

超市经营者提出，他们需要统计某个门店，每天、每个单品的销售情况，包括销售数量和销售金额等。

三个数据表

销售明细表(demo.transactiondetails)

销售单头(demo.transactionhead)

商品信息表（demo.goodsmaster）

SUM()

SUM()函数返回指定字段值的和

查询用户某个门店，每天，每种商品的销售总计数据。

select 
	left(b.transdate,10),
    c.goodsname,
    sum(a.quantity),
    sum(a.salesvalue)
from
	demo.transactiondetails a
		join
	demo.transactionhead b on (a.transactionid = b.transactionid)
		join
	demo.goodsmaster c on (a.itemnumber= c.itemnumber)
group by left(b.transdate,10),c.goodsname
order by left(b.transdate,10),c.goodsname;

引入关键字left和ORDER BY

LEFT(str,n)

返回字符串str最左边的n个字符，left(a.transdate,10)，表示返回交易时间字符串最左边的10个字符。在MySQL中，DATETIME的格式为：YYYYY-MM-DD，一共是10个字符。

ORDER BY

按照指定字段排序

步骤：

第一步：完成三个表的连接

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

第一组：

第二组：

第三组：

第四组：

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

如果用户要知道全部商品销售的总计数量和总计金额，我们可以把数据集的整体看做一张表，进行计算。

这样就不需要从关联表中获取数据，也不需要连接。

select 
	sum(quantity),
	sum(salesvalue)
from
demo.transactiondetails;

AVG()、MAX()、MIN()

1.AVG()

select 
left(a.transdate,10),
c.goodsname,
avg(b.quantity),
avg(b.salesvalue)
from
demo.transactionhead a
join
demo.transactiondetails b on (a.transactionid = b.transactionid)
join
demo.goodsmaster c on(b.itemnumber = c.itemnumber)
group by left(a.transdate,10),c.goodsname
order by left(a.transdate,10),c.goodsname;

2.MAX()、MIN()

select 
left(a.transdate,10),
MAX(b.quantity),
MAX(b.salesvalue)
from
demo.transactionhead a
join 
demo.transactiondetails b on(a.transactionid = b.transactionid)
join
demo.goodsmaster c on (b.itemnumber = c.itemnumber)
group by left(a.transdate,10)
order by left(a.transdate,10);

注意：MAX(b.quantity)和MAX(b.salesvalue)算出的结果不一定是同一条记录的数据。

实际上，MySQL是分开计算的。

查询中用了3个相互关联的表：销售流水明细表、销售流水单头表、商品信息表，三张表连接完成后，MySQL进行了分组。

第一组：

第二组：

第一组，最大数量在第2条记录中，是5，最大金额在第一条记录中，是178。

第二主中，最大数量出现在第4条记录，是10，最大金额则出现在第1条记录中，是267。

所以，MAX(字段)这个函数返回分组集中最大的那个值。如果查询MAX(字段1)和MAX(字段2)，他们是相互独立、分别计算的，千万不要认为结果在同一条记录上！！！！！

COUNT()

通过COUNT()了解数据集的大小，对于系统优化十分重要。

用户数量增多，系统会出现卡顿问题。

引入分页策略。

分页策略

不将查询结果一次性全部返回客户端，而是根据屏幕大小，计算可以显示的记录数，每次返回用户电脑屏幕可以显示的数据集。

COUNT函数的两种情况：

COUNT(*):统计一共有多少条记录
COUNT(字段):统计有多少个不为空的字段值

1.COUNT(*)

1 2	`select * from demo.transactiondetails`

1 2	`select count(*) from demo.transactiondetails;`

一页可以显示30个数据，一共6个数据，所以就只有1页。

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

select 
left (a.transdate,10),c.goodsname,count(*)
from
demo.transactionhead a
join
demo.transactiondetails b on(a.transactionid = b.transactionid)
join
demo.goodsmaster c on (b.itemnumber = c.itemnumber)
group by left(a.transdate,10),c.goodsname
order by left(a.transdate,10),c.goodsname;