相关推荐recommended
【sql】mysql分组查询group by的案例和原理
作者:mmseoamin日期:2023-12-18

【sql】mysql分组查询group by的案例和原理

  • 【一】group by的使用场景
  • 【二】group by的基本语法
    • 【1】基本语法
    • 【2】常用的聚合函数
      • (1)max函数:取出分组中的最大值
      • (2)avg函数:取出分组中的平均值
      • (3)count函数:统计每个分组中的数据有多少条
      • (4)sum函数:取出分组结果中的总和
      • (5)min函数:取出分组中的最小值
      • (6)GROUP_CONCAT函数:把分组中的值拼接在一起(可以先排序再拼接)
      • 【3】条件where和having的区别
        • (1)案例一:where过滤
        • (2)案例二:having过滤
        • (3)案例三:where+having
        • (4)案例四:按表达式或函数进行筛选
        • (5)案例五:按照多个字段分组
        • 【三】注意点和常见的报错信息
          • 【1】注意的问题
            • (1)where + having的使用
            • (2)group by一定要配合聚合函数一起使用嘛?
            • (3)group by的字段一定要出现在select中嘛?
            • (4)group by导致的慢SQL问题
            • 【2】常见的报错信息
            • 【四】group by的原理分析
              • 【1】分析案例
              • 【2】group by的原理
                • (1)explain分析
                • (2)group by 的简单执行流程
                • 【3】where和having的原理
                  • (1)group by + where 的执行流程
                  • (2)group by + having 的执行
                  • (3)同时有where、group by 、having的执行顺序
                  • 【五】group by的优化方案
                    • 【1】优化的思路
                    • 【2】group by 后面的字段加索引
                    • 【3】order by null 不用排序
                    • 【4】尽量只使用内存临时表
                    • 【5】使用SQL_BIG_RESULT优化
                    • 【六】实际使用案例分析

                      【一】group by的使用场景

                      【二】group by的基本语法

                      【1】基本语法

                      除了出现在group by后面的字段,如果要在select后查询其他字段,必须用聚合函数进行聚合

                      select 
                      	聚合函数,
                      	列(要求出现在group by的后面)
                      from 
                      	表
                      where 
                      	筛选条件
                      group by 
                      	分组的列表
                      order by 
                      	子句
                      

                      【2】常用的聚合函数

                      (1)max函数:取出分组中的最大值

                      根据岗位来分组,并且查询每个岗位中最高工资的员工的工资是多少

                      select 
                      	max(salary),
                      	job_id
                      from 
                      	employees
                      group by 
                      	job_id;
                      

                      (2)avg函数:取出分组中的平均值

                      根据部门分组,并且查询每个部门中工资的平均值

                      select 
                      	avg(salary),
                      	department_id
                      from 
                      	employees
                      where 
                      	email like '%a%'
                      group by 
                      	department_id
                      

                      (3)count函数:统计每个分组中的数据有多少条

                      (4)sum函数:取出分组结果中的总和

                      (5)min函数:取出分组中的最小值

                      (6)GROUP_CONCAT函数:把分组中的值拼接在一起(可以先排序再拼接)

                      如果不排序就进行拼接的话,可能会因为拼接的顺序不一致,从而分到不同的类中

                      (1)mysql版本

                      在使用GROUP_CONCAT函数拼接字段时,可以通过ORDER BY子句指定排序字段的顺序,从而保证拼接字段的顺序。例如,以下是一个示例查询语句:

                      SELECT category, GROUP_CONCAT(product_name ORDER BY product_id SEPARATOR ',')
                      FROM products
                      GROUP BY category;
                      

                      在这个查询语句中,我们按照product_id字段的顺序对product_name字段进行排序,从而保证了拼接字段的顺序。

                      SELECT
                      pm.PROJ_ID,
                      pm.DOMAIN_ID,
                      GROUP_CONCAT(CONCAT(pm.PROJ_MEMBER_NM,pm.LOGIN_ACCT) ORDER BY pm.USER_ID SEPARATOR ',' ) AS  developerName,
                      GROUP_CONCAT(pm.USER_ID ORDER BY pm.USER_ID SEPARATOR ',' ) AS developerId
                      FROM
                      proj_member pm LEFT JOIN proj_member_x_role pmxr ON pm.PROJ_ID = pmxr.PROJ_ID  AND pm.ID = pmxr.PROJ_MEMBER_ID
                      WHERE
                      pmxr.PROJ_ROLE_CD='DEV_DEVELOPER'
                      GROUP BY
                      pm.PROJ_ID,
                      pm.DOMAIN_ID
                      

                      (2)oracle版本(WITHIN GROUP里进行排序再拼接)

                      在Oracle中,使用LISTAGG函数时,可以通过WITHIN GROUP子句中的ORDER BY子句来指定拼接字段的顺序。具体来说,ORDER BY子句中的列将按升序或降序排列,然后将按照指定的顺序拼接。例如,如果要按照课程名称的字母顺序拼接每个班级的课程,请使用以下查询:

                      SELECT class, LISTAGG(course, ‘,’) WITHIN GROUP (ORDER BY course) AS courses
                      FROM table1
                      GROUP BY class;
                      
                      SELECT
                      pm.PROJ_ID,
                      pm.DOMAIN_ID,
                      LISTAGG ( CONCAT( pm.PROJ_MEMBER_NM, pm.LOGIN_ACCT ), ',' ) WITHIN GROUP (ORDER  BY pm.USER_ID ASC) AS developerName,
                      LISTAGG ( pm.USER_ID, ',' ) WITHIN GROUP (ORDER BY pm.USER_ID ASC) AS  developerId
                      FROM
                      proj_member pm LEFT JOIN proj_member_x_role pmxr ON pm.PROJ_ID = pmxr.PROJ_ID  AND pm.ID = pmxr.PROJ_MEMBER_ID
                      WHERE
                      pmxr.PROJ_ROLE_CD='DEV_DEVELOPER'
                      GROUP BY
                      pm.PROJ_ID,
                      pm.DOMAIN_ID
                      

                      【3】条件where和having的区别

                      (1)分组查询中的筛选条件分为两类:

                      1-分组前筛选: 数据源是原始表,用where,放在group by前面,因为在分组前筛选

                      2-分组后筛选:数据源是分组后的结果集 ,用having,放在group by后面,因为在分组后进行筛选。

                      (1)案例一:where过滤

                      查询有奖金的每个领导手下最高工资的员工工资

                      select 
                      	max(salary),
                      	manager_id 
                      from 
                      	employees
                      where 
                      	commission_pct is not null
                      group by 
                      	manager_id
                      

                      (2)案例二:having过滤

                      查询哪个部门的员工个数大于2

                      select  
                      	count(*),
                      	department_id 
                      from 
                      	employees 
                      group by 
                      	department_id 
                      having 
                      	count(*) > 2;
                      

                      分组后筛选的思路:

                      1-查询每个部门的员工个数;

                      2-根据1的结果进行筛选,查询哪个部门员工个数大于2

                      (3)案例三:where+having

                      查询每个工种有奖金的员工的最高工资大于12000的工种编号和最高工资

                      select 
                      	max(salary),
                      	job_id
                      from 
                      	employees
                      where 
                      	commission_pct is not null
                      group by 
                      	job_id 
                      having 
                      	max(salary) > 12000;
                      

                      思路:

                      1-首先where筛选有奖金的员工

                      2-然后根据岗位id分组

                      3-然后max(salary)取出每个分组中的工资最高值

                      4-最后having筛选工资最高值大于12000的组

                      (4)案例四:按表达式或函数进行筛选

                      按照员工姓名长度分组,查询每一组员工个数,筛选员工个数大于5的有哪些

                      select 
                      	count(*)  ,
                      	length(last_name) as len_name
                      from 
                      	employees 
                      group by 
                      	length(last_name)
                      having 
                      	count(*) > 5;
                      

                      (5)案例五:按照多个字段分组

                      查询每个部门每个工种的员工平均工资,并且按照平均工资的高低排序

                      select 
                      	avg(salary),
                      	department_id,
                      	job_id 
                      from 
                      	employees
                      where 
                      	department_id is not null
                      group by 
                      	department_id,
                      	job_id
                      order by 
                      	avg(salary) desc;
                      

                      【三】注意点和常见的报错信息

                      【1】注意的问题

                      (1)where + having的使用

                      (1)having子句用于分组后筛选,where子句用于行条件筛选

                      (2)having一般都是配合group by 和聚合函数一起出现如(count(),sum(),avg(),max(),min())

                      (3)where条件子句中不能使用聚集函数,而having子句就可以。

                      (4)having只能用在group by之后,where执行在group by之前

                      (2)group by一定要配合聚合函数一起使用嘛?

                      (3)group by的字段一定要出现在select中嘛?

                      (4)group by导致的慢SQL问题

                      group by使用不当,很容易就会产生慢SQL 问题。因为它既用到临时表,又默认用到排序。有时候还可能用到磁盘临时表。

                      【2】常见的报错信息

                      (1)分组后没有使用聚合函数

                      (2)select后的字段没有出现在group by之后

                      【四】group by的原理分析

                      【1】分析案例

                      group by一般用于分组统计,它表达的逻辑就是根据一定的规则,进行分组。

                      一张员工表

                      CREATE TABLE `staff` (
                        `id` bigint(11) NOT NULL AUTO_INCREMENT COMMENT '主键id',
                        `id_card` varchar(20) NOT NULL COMMENT '身份证号码',
                        `name` varchar(64) NOT NULL COMMENT '姓名',
                        `age` int(4) NOT NULL COMMENT '年龄',
                        `city` varchar(64) NOT NULL COMMENT '城市',
                        PRIMARY KEY (`id`)
                      ) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8 COMMENT='员工表';
                      

                      表中的数据如下

                      【sql】mysql分组查询group by的案例和原理,在这里插入图片描述,第1张

                      现在有这么一个需求:统计每个城市的员工数量。对应的 SQL 语句就可以这么写:

                      select city ,count(*) as num from staff group by city;
                      

                      执行结果如下

                      【sql】mysql分组查询group by的案例和原理,在这里插入图片描述,第2张

                      【2】group by的原理

                      (1)explain分析

                      explain select city ,count(*) as num from staff group by city;
                      

                      【sql】mysql分组查询group by的案例和原理,在这里插入图片描述,第3张

                      (1)Extra 这个字段的Using temporary表示在执行分组的时候使用了临时表

                      (2)Extra 这个字段的Using filesort表示使用了排序

                      (2)group by 的简单执行流程

                      explain select city ,count(*) as num from staff group by city;
                      

                      这个sql的执行流程:

                      (1)创建内存临时表,表里有两个字段city和num;

                      (2)全表扫描staff的记录,依次取出city = 'X’的记录。

                      1-判断临时表中是否有为 city='X’的行,没有就插入一个记录 (X,1);

                      2-如果临时表中有city='X’的行的行,就将x 这一行的num值加 1;

                      (3)遍历完成后,再根据字段city做排序,得到结果集返回给客户端。

                      流程的执行图如下:

                      【sql】mysql分组查询group by的案例和原理,在这里插入图片描述,第4张

                      临时表的排序流程:

                      就是把需要排序的字段,放到sort buffer,排完就返回。在这里注意一点哈,排序分全字段排序和rowid排序

                      (1)如果是全字段排序,需要查询返回的字段,都放入sort buffer,根据排序字段排完,直接返回

                      (2)如果是rowid排序,只是需要排序的字段放入sort buffer,然后多一次回表操作,再返回。

                      (3)怎么确定走的是全字段排序还是rowid 排序排序呢?由一个数据库参数控制的,max_length_for_sort_data

                      【3】where和having的原理

                      (1)group by + where 的执行流程

                      (1)如果加了where条件之后,并且where条件列加了索引呢,执行流程是怎样?

                      select city ,count(*) as num from staff where age> 30 group by city;
                      //加索引
                      alter table staff add index idx_age (age);
                      

                      (2)explain分析

                      explain select city ,count(*) as num from staff where age> 30 group by city;
                      

                      【sql】mysql分组查询group by的案例和原理,在这里插入图片描述,第5张

                      从explain 执行计划结果,可以发现查询条件命中了idx_age的索引,并且使用了临时表和排序。

                      Using index condition:表示索引下推优化,根据索引尽可能的过滤数据,然后再返回给服务器层根据where其他条件进行过滤。这里单个索引为什么会出现索引下推呢?explain出现并不代表一定是使用了索引下推,只是代表可以使用,但是不一定用了。

                      (3)执行流程如下

                      1-创建内存临时表,表里有两个字段city和num;

                      2-扫描索引树idx_age,找到大于年龄大于30的主键ID

                      3-通过主键ID,回表找到city = ‘X’

                      判断临时表中是否有为 city='X’的行,没有就插入一个记录 (X,1);

                      如果临时表中有city='X’的行的行,就将x 这一行的num值加 1;

                      4-继续重复2,3步骤,找到所有满足条件的数据,

                      5-最后根据字段city做排序,得到结果集返回给客户端。

                      (2)group by + having 的执行

                      (1)案例分析

                      查询每个城市的员工数量,获取到员工数量不低于3的城市,having可以很好解决

                      select city ,count(*) as num from staff  group by city having num >= 3;
                      

                      查询结果

                      【sql】mysql分组查询group by的案例和原理,在这里插入图片描述,第6张

                      having称为分组过滤条件,它对返回的结果集操作。

                      (3)同时有where、group by 、having的执行顺序

                      (1)案例分析

                      select city ,count(*) as num from staff  where age> 19 group by city having num >= 3;
                      

                      (2)执行流程

                      1-执行where子句查找符合年龄大于19的员工数据

                      2-group by子句对员工数据,根据城市分组。

                      3-对group by子句形成的城市组,运行聚集函数计算每一组的员工数量值;

                      4-最后用having子句选出员工数量大于等于3的城市组。

                      【五】group by的优化方案

                      【1】优化的思路

                      方向1:既然它默认会排序,我们不给它排是不是就行了?

                      方向2:既然临时表是影响group by性能的X因素,我们是不是可以不用临时表?

                      执行group by语句为什么需要临时表呢?group by的语义逻辑,就是统计不同的值出现的个数。如果这个这些值一开始就是有序的,我们是不是直接往下扫描统计就好了,就不用临时表来记录并统计结果了?

                      (1)group by 后面的字段加索引

                      (2)order by null 不用排序

                      (3)尽量只使用内存临时表

                      (4)使用SQL_BIG_RESULT

                      【2】group by 后面的字段加索引

                      如何保证group by后面的字段数值一开始就是有序的呢?当然就是加索引。

                      select city ,count(*) as num from staff where age= 19 group by city;
                      

                      执行计划

                      【sql】mysql分组查询group by的案例和原理,在这里插入图片描述,第7张

                      给它加个联合索引idx_age_city(age,city)

                      alter table staff add index idx_age_city(age,city);
                      

                      再去看执行计划,发现既不用排序,也不需要临时表

                      【sql】mysql分组查询group by的案例和原理,在这里插入图片描述,第8张

                      加合适的索引是优化group by最简单有效的优化方式。

                      【3】order by null 不用排序

                      并不是所有场景都适合加索引的,如果碰上不适合创建索引的场景,如果你的需求并不需要对结果集进行排序,可以使用order by null。

                      select city ,count(*) as num from staff group by city order by null
                      

                      执行计划如下,已经没有filesort了

                      【sql】mysql分组查询group by的案例和原理,在这里插入图片描述,第9张

                      【4】尽量只使用内存临时表

                      如果group by需要统计的数据不多,我们可以尽量只使用内存临时表;因为如果group by 的过程因为内存临时表放不下数据,从而用到磁盘临时表的话,是比较耗时的。因此可以适当调大tmp_table_size参数,来避免用到磁盘临时表。

                      【5】使用SQL_BIG_RESULT优化

                      如果数据量实在太大怎么办呢?总不能无限调大tmp_table_size吧?但也不能眼睁睁看着数据先放到内存临时表,随着数据插入发现到达上限,再转成磁盘临时表吧?这样就有点不智能啦。

                      因此,如果预估数据量比较大,我们使用SQL_BIG_RESULT 这个提示直接用磁盘临时表。MySQl优化器发现,磁盘临时表是B+树存储,存储效率不如数组来得高。因此会直接用数组来存

                      示例SQl如下:

                      select SQL_BIG_RESULT city ,count(*) as num from staff group by city;
                      

                      执行计划的Extra字段可以看到,执行没有再使用临时表,而是只有排序

                      【sql】mysql分组查询group by的案例和原理,在这里插入图片描述,第10张

                      执行流程如下:

                      (1)初始化 sort_buffer,放入city字段;

                      (2)扫描表staff,依次取出city的值,存入 sort_buffer 中;

                      (3)扫描完成后,对 sort_buffer的city字段做排序

                      (4)排序完成后,就得到了一个有序数组。

                      (5)根据有序数组,统计每个值出现的次数。

                      【六】实际使用案例分析