[Mysql] GROUP BY分组

练习案例数据

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个客户"等