练习案例数据
DROP TABLE IF EXISTS purchase_info;
CREATE TABLE purchase_info(
commodity_id VARCHAR(8),
category VARCHAR(16),
colour VARCHAR(16),
purchase_quantity INT,
purchase_date DATE
)
ENGINE = InnoDB
DEFAULT CHARSET = utf8;
INSERT INTO
purchase_info
(commodity_id,category,colour,purchase_quantity,purchase_date)
VALUE ('c001','clothing','black',150,'2020-04-25')
,('c002','clothing','white',50,'2020-04-05')
,('c003','shoes','white',500,'2020-03-23')
,('c004','shoes','red',200,'2020-04-07')
,('c005','clothing','blue',120,'2020-04-15')
,('c007','clothing',NULL,NULL,'2020-04-15');purchase_info表(商品进货信息表)
分组允许把数据分为多个逻辑组，以便能对每个组进行聚集计算
分组是在SELECT语句的GROUP BY子句中建立的
1.GROUP BY创建分组
GROUP BY的含义:将查询结果按照1个或多个字段进行分组，字段值相同的为一组
GROUP BY子句指示MySQL分组数据，然后对每个组而不是整个结果集进行聚集
使用GROUP BY的重要规定:
1.GROUP BY子句可以包含任意数目的列。这使得能对分组进行嵌套，为数据分组提供更细致的控制，如果在GROUP BY子句中嵌套了分组，数据将在最后规定的分组上进行汇总
2.GROUP BY子句中列出的每个列都必须是检索列或有效的表达式(但不能是聚合函数)。如果在SELECT中使用表达式，则必须在GROUP BY子句中指定相同的表达式。不能使用别名
3.除聚合函数外，SELECT语句中的每个列都必须在GROUP BY子句中给出
4.如果分组列中具有NULL值，则NULL将作为一个分组返回。如果列中有多行NULL值，它们将分为一组
5.GROUP BY子句必须出现在WHERE子句之后，ORDER BY子句之前
问题1:分组统计不同类别商品的平均进货量
SELECT category, AVG(purchase_quantity)
FROM purchase_info
GROUP BY category;结果展示:
这里的GROUP BY category表示根据category列分组，然后计算每组的AVG(purchase_quantity)
问题2:根据category列和color列分组统计平均进货量(多列分组)
SELECT category,colour,AVG(purchase_quantity)
FROM purchase_info
GROUP BY category,colour;结果展示:
由于colour列值存在Null，因此结果中会出现包含Null的记录;在上表中，由于没有category列值和colour列值都相同的记录，因此根据category列和colour列分组统计之后的数据条数还是和原始表的数据条数相同，都是6条
扩展
1.GROUP BY可用于单个字段分组，也可用于多个字段分组
2.多列分组:多列取值都相同则为一组
3.GROUP BY易错点
当存在GROUP BY时，使用SELECT查询出来的字段必须出现在GROUP BY之后，或者以聚合函数的形式出现
问题3:根据category列和purchase_date列分组统计总进货量
SELECT category,purchase_date,SUM(purchase_quantity) FROM purchase_info GROUP BY category;上述代码运行后会得不到问题3所需要的结果，需要将上述代码进行修正
SELECT category,purchase_date,SUM(purchase_quantity) FROM purchase_info GROUP BY category,purchase_date;结果展示:
2.使用HAVING过滤分组
过滤分组，规定包括哪些分组，排除哪些分组
对分组后的组进行过滤可以使用HAVING，并且需要将其写在GROUP BY部分之后
HAVING支持所有运用在WHERE上的操作符
HAVING语句与WHERE语句的区别
1.WHERE语句用于对行进行过滤，HAVING语句对分组后的组进行过滤
2.WHERE在数据分组前进行过滤，HAVING在数据分组后进行过滤
3.WHERE排除的行不包括在分组中
问题4:筛选出按照商品类别分组后，组成员个数大于3的平均进货量
SELECT category,AVG(purchase_quantity)
FROM purchase_info
GROUP BY category
HAVING COUNT(*) > 3;结果展示:
原始表中的商品类别为shoes的记录只有两条，不满足组成员个数大于3的要求，而商品类别为clothing的记录有4条，满足上述要求，则分组后会被筛选出来进行聚合计算
3.分组排序
问题5:查询平均价格最高的3种商品的颜色
SELECT colour,AVG(purchase_quantity)
FROM purchase_info
GROUP BY colour
ORDER BY AVG(purchase_quantity) DESC
LIMIT 3;结果展示:
先使用GROUP BY，再使用ORDER BY，最后使用LIMIT限制返回的结果行数
注意:上述方法有一个缺陷，如果出现平均价格相同且并列第3名的情况，则查询结果应当是所有前3名的信息(即返回超过3条数据)，解决该类问题可以使用排序类窗口函数
扩展
使用分组排序常用题型:"总订单金额最多的n个商品","总订单金额最多的n个客户"等
