[Mysql] GROUP BY分组

news/2024/10/11 16:20:01/

练习案例数据

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


http://www.ppmy.cn/news/688583.html

相关文章

微信小程序类ExpandableListView分组实现

效果图 小程序分组实现。 实现 代码结构如下图图: 文件目录说明如下: │ app.js │ app.json │ app.wxss │ project.config.json │ ├─constants │ restApi.js //存放api │ ├─images //图片 │ arrow-down.png …

微信公众平台如何给粉丝分组打标签?

公号粉丝自动打标签的5种类型 1、关键词自动打标签(小程序或公号) 2、根据关注条件自动打标签(公号) 3、输入openid自动打标签(公号) 4、根据点击菜单自动打标签(公号) 5、渠道二维…

group by 如何自定义规则分组,合并分组

sql语句 group by时如何自定义规则进行分组 1. 问题描述 最近开发项目时遇到了一个需求:统计不同类型的物品的数量,但是某几种类型要合并到一起进行统计,例如:有A,B,C,D,E,F,G其中类型,但是统计时ACD合计为一种&…

使用List进行分组操作

普通对象列表进行分组 // 创建一个实体类 Data public class People {String name; //姓名Integer age; //年龄Integer sex; //性别 0 - 女; 1 - 男Double height; //身高Double weight; //体重 }public class ListGroupTest {public static void ma…

排序和分组

Python序列操作之进阶篇 转载 更新时间:2016年12月08日 09:31:59 投稿:daisy 我要评论 序列sequence是python中最基本的数据结构,本文是Python序列操作的进阶篇,本文先对序列做一个简单的概括&#…

微信公众号【用户分组管理-web页面】

人生的意志和劳动将创造奇迹般的奇迹。 Model层实体类: public class UserList{public string total { get; set; }public string count { get; set; }public userlistopenid data { get; set; }public string next_openid { get; set; }}public class userlistopenid{public …

分组、分类(分组汇总)

分类一: GROUP BY (PRODUCT,FLOOR); COL1COL2countsumAA115AA214BB113 分类二: GROUP BY ROLLUP (Col1,Col2); COL1COL2countsum备注AA115AA214A(null)29 (每一组COL2的综合) BB113B(null)13 (每一组COL2的综合) (null)(null…

2.分组列表

分组列表 sqlite3 /var/lib/murmur/murmur.sqliteserver_id,channel_id,parent_id,name,inheritaclinsert into channels values(1,2,0,2-分组,1); 来源张永光的博客