精华内容
下载资源
问答
  • groupby
    千次阅读
    2020-08-04 09:38:46

    1.group by概述:
    简单的来说: 将数据库的数据用’by’后面接的规则进行分组,即将一个大数据库分成一个个相同类型数据在一起的小区域。

    2.group by的语法:

    select column_name,function(column_name) //此处的function()函数是指SQL中的一些聚合函数,例如: MAX(),MIN(),SUM(),AVG(),COUNT()等
    from table_name where column_name operator value //where条件语句 
    group by column_name;//以某一列分组
    

    语句整个的意思就是从table_name 这个表中,先筛选出满足where后条件的所有数据,然后又对这些数据按照column_name字段分组,最后将column_name展示在结果集中。
    例如:

    insert overwrite table track_info_province_stat partition(day='2013-07-21')
    select province,count(*) as cnt from track_info where day='2013-07-21' group by province;
    

    从track_info 表中选出符合day='2013-07-21’条件的数据,并按照province进行分组,
    得出以province为分组的每个分组中里面数据的数量(就是统计每个province的数量),有两个字段分别为province,cnt 。
    然后将结果写入分区表track_info_province_stat

    insert overwrite:覆盖写入
    count(*) as cnt:统计的每个privince的数量作为列起的别名cnt
    
    更多相关内容
  • 原创文章,希望多多关注支持,感谢。 目录 00 结论 01 distinct的使用 02 group by的使用 03 distinct和group by原理 * ...原因是distinct 和 group by都会进行分组操作,但group by可能会进行排序,触发fil

    原创文章,希望多多关注支持,感谢。

    目录

    00 结论
    01 distinct的使用
    02 group by的使用
    03 distinct和group by原理 *
    04 推荐group by的原因

    00结论

    先说大致的结论(完整结论在文末):

    在语义相同,有索引的情况下
    group by和distinct都能使用索引,效率相同。
    在语义相同,无索引的情况下:
    distinct效率高于group by。原因是distinct 和 group by都会进行分组操作,但group by可能会进行排序,触发filesort,导致sql执行效率低下。

    基于这个结论,你可能会问:

      
    为什么在语义相同,有索引的情况下,group by和distinct效率相同?

      
    且在什么情况下,group by会进行排序操作?

    带着这两个问题找答案。接下来,我们先来看一下distinct和group by的基础使用。

    01 distinct的使用

    distinct用法

    SELECT DISTINCT columns FROM table_name WHERE where_conditions;
    

    例如:

    mysql> select distinct age from student;
    +------+
    | age  |
    +------+
    |   10 |
    |   12 |
    |   11 |
    | NULL |
    +------+
    4 rows in set (0.01 sec)
    


      DISTINCT 关键词用于返回唯一不同的值。放在查询语句中的第一个字段前使用,且作用于主句所有列

      如果列具有NULL值,并且对该列使用DISTINCT子句,MySQL将保留一个NULL值,并删除其它的NULL值,因为DISTINCT子句将所有NULL值视为相同的值。

    distinct多列去重

      distinct多列的去重,则是根据指定的去重的列信息来进行,即只有所有指定的列信息都相同,才会被认为是重复的信息。

    SELECT DISTINCT column1,column2 FROM table_name WHERE where_conditions;
    
    mysql> select distinct sex,age from student;
    +--------+------+
    | sex    | age  |
    +--------+------+
    | male   |   10 |
    | female |   12 |
    | male   |   11 |
    | male   | NULL |
    | female |   11 |
    +--------+------+
    5 rows in set (0.02 sec)
    

    02 group by的使用

      对于基础去重来说,group by的使用和distinct类似:

    单列去重

    语法:

    SELECT columns FROM table_name WHERE where_conditions GROUP BY columns;
    

    执行:

    mysql> select age from student group by age;
    +------+
    | age  |
    +------+
    |   10 |
    |   12 |
    |   11 |
    | NULL |
    +------+
    4 rows in set (0.02 sec)
    

    多列去重

    语法:

    SELECT columns FROM table_name WHERE where_conditions GROUP BY columns;
    

    执行:

    mysql> select sex,age from student group by sex,age;
    +--------+------+
    | sex    | age  |
    +--------+------+
    | male   |   10 |
    | female |   12 |
    | male   |   11 |
    | male   | NULL |
    | female |   11 |
    +--------+------+
    5 rows in set (0.03 sec)
    

    区别示例

      两者的语法区别在于,group by可以进行单列去重,group by的原理是先对结果进行分组排序,然后返回每组中的第一条数据。且是根据group by的后接字段进行去重的。


    例如:

    mysql> select sex,age from student group by sex;
    +--------+-----+
    | sex    | age |
    +--------+-----+
    | male   |  10 |
    | female |  12 |
    +--------+-----+
    2 rows in set (0.03 sec)
    

    03 distinct和group by原理

      在大多数例子中,DISTINCT可以被看作是特殊的GROUP BY,它们的实现都基于分组操作,且都可以通过松散索引扫描、紧凑索引扫描(关于索引扫描的内容会在其他文章中详细介绍,就不在此细致介绍了)来实现。

      DISTINCT和GROUP BY都是可以使用索引进行扫描搜索的。例如以下两条sql(只单单看表格最后extra的内容),我们对这两条sql进行分析,可以看到,在extra中,这两条sql都使用了紧凑索引扫描Using index for group-by。所以,在一般情况下,对于相同语义的DISTINCT和GROUP BY语句,我们可以对其使用相同的索引优化手段来进行优化。

    mysql> explain select int1_index from test_distinct_groupby group by int1_index;
    +----+-------------+-----------------------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
    | id | select_type | table                 | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra                    |
    +----+-------------+-----------------------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
    |  1 | SIMPLE      | test_distinct_groupby | NULL       | range | index_1       | index_1 | 5       | NULL |  955 |   100.00 | Using index for group-by |
    +----+-------------+-----------------------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
    1 row in set (0.05 sec)
    
    mysql> explain select distinct int1_index from test_distinct_groupby;
    +----+-------------+-----------------------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
    | id | select_type | table                 | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra                    |
    +----+-------------+-----------------------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
    |  1 | SIMPLE      | test_distinct_groupby | NULL       | range | index_1       | index_1 | 5       | NULL |  955 |   100.00 | Using index for group-by |
    +----+-------------+-----------------------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
    1 row in set (0.05 sec)
    

      但对于GROUP BY来说,在MYSQL8.0之前,GROUP Y默认会依据字段进行隐式排序


      可以看到,下面这条sql语句在使用了临时表的同时,还进行了filesort。

    mysql> explain select int6_bigger_random from test_distinct_groupby GROUP BY int6_bigger_random;
    +----+-------------+-----------------------+------------+------+---------------+------+---------+------+-------+----------+---------------------------------+
    | id | select_type | table                 | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra                           |
    +----+-------------+-----------------------+------------+------+---------------+------+---------+------+-------+----------+---------------------------------+
    |  1 | SIMPLE      | test_distinct_groupby | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 97402 |   100.00 | Using temporary; Using filesort |
    +----+-------------+-----------------------+------------+------+---------------+------+---------+------+-------+----------+---------------------------------+
    1 row in set (0.04 sec)
    

    隐式排序

      对于隐式排序,我们可以参考Mysql官方的解释:

    MySQL :: MySQL 5.7 Reference Manual :: 8.2.1.14 ORDER BY Optimization

    GROUP BY implicitly sorts by default (that is, in the absence of ASC or DESC designators for GROUP BY columns). However, relying on implicit GROUP BY sorting (that is, sorting in the absence of ASC or DESC designators) or explicit sorting for GROUP BY (that is, by using explicit ASC or DESC designators for GROUP BY columns) is deprecated. To produce a given sort order, provide an ORDER BY clause.

    大致解释一下:

      GROUP BY 默认隐式排序(指在 GROUP BY 列没有 ASC 或 DESC 指示符的情况下也会进行排序)。然而,GROUP BY进行显式或隐式排序已经过时(deprecated)了,要生成给定的排序顺序,请提供 ORDER BY 子句。

      所以,在Mysql8.0之前,Group by会默认根据作用字段(Group by的后接字段)对结果进行排序。在能利用索引的情况下,Group by不需要额外进行排序操作;但当无法利用索引排序时,Mysql优化器就不得不选择通过使用临时表然后再排序的方式来实现GROUP BY了。且当结果集的大小超出系统设置临时表大小时,Mysql会将临时表数据copy到磁盘上面再进行操作,语句的执行效率会变得极低。这也是Mysql选择将此操作(隐式排序)弃用的原因。


      基于上述原因,Mysql在8.0时,对此进行了优化更新:

    MySQL :: MySQL 8.0 Reference Manual :: 8.2.1.16 ORDER BY Optimization

    Previously (MySQL 5.7 and lower), GROUP BY sorted implicitly under certain conditions. In MySQL 8.0, that no longer occurs, so specifying ORDER BY NULL at the end to suppress implicit sorting (as was done previously) is no longer necessary. However, query results may differ from previous MySQL versions. To produce a given sort order, provide an ORDER BY clause.

    大致解释一下:

    从前(Mysql5.7版本之前),Group by会根据确定的条件进行隐式排序。在mysql 8.0中,已经移除了这个功能,所以不再需要通过添加order by null 来禁止隐式排序了,但是,查询结果可能与以前的 MySQL 版本不同。要生成给定顺序的结果,请按通过ORDER BY指定需要进行排序的字段。

    因此,我们的结论也出来了:

    在语义相同,有索引的情况下:
    group by和distinct都能使用索引,效率相同。因为group by和distinct近乎等价,distinct可以被看做是特殊的group by。
    在语义相同,无索引的情况下:
    distinct效率高于group by。原因是distinct 和 group by都会进行分组操作,但group by在Mysql8.0之前会进行隐式排序,导致触发filesort,sql执行效率低下。但从Mysql8.0开始,Mysql就删除了隐式排序,所以,此时在语义相同,无索引的情况下,group by和distinct的执行效率也是近乎等价的。

    04 推荐group by的原因

      1.group by语义更为清晰
      2.group by可对数据进行更为复杂的一些处理

      相比于distinct来说,group by的语义明确。且由于distinct关键字会对所有字段生效,在进行复合业务处理时,group by的使用灵活性更高,group by能根据分组情况,对数据进行更为复杂的处理,例如通过having对数据进行过滤,或通过聚合函数对数据进行运算。

    展开全文
  • 看一遍就理解:group by 详解

    千次阅读 2022-01-18 14:42:27
    前言 日常开发中,我们经常会使用到group by。亲爱的小伙伴,你是否知道group by的工作原理呢?... group by + where 和 group by + having的区别 group by 优化思路 group by 使用注意点 一个生产慢SQL..

    看一遍就理解:group by 详解

    前言

    日常开发中,我们经常会使用到group by。亲爱的小伙伴,你是否知道group by的工作原理呢?group byhaving有什么区别呢?group by的优化思路是怎样的呢?使用group by有哪些需要注意的问题呢?本文将跟大家一起来学习,攻克group by~

    • 使用group by的简单例子
    • group by 工作原理
    • group by + where 和 group by + having的区别
    • group by 优化思路
    • group by 使用注意点
    • 一个生产慢SQL如何优化

    1. 使用group by的简单例子

    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 语句就可以这么写:

    select city ,count(*) as num from staff group by city;
    复制代码

    执行结果如下:

    这条SQL语句的逻辑很清楚啦,但是它的底层执行流程是怎样的呢?

    2. group by 原理分析

    2.1 explain 分析

    我们先用explain查看一下执行计划

    explain select city ,count(*) as num from staff group by city;
    复制代码

    • Extra 这个字段的Using temporary表示在执行分组的时候使用了临时表
    • Extra 这个字段的Using filesort表示使用了排序

    group by 怎么就使用到临时表和排序了呢?我们来看下这个SQL的执行流程

    2.2 group by 的简单执行流程

    explain select city ,count(*) as num from staff group by city;
    复制代码

    我们一起来看下这个SQL的执行流程哈

    1. 创建内存临时表,表里有两个字段citynum
    2. 全表扫描staff的记录,依次取出city = 'X'的记录。
    • 判断临时表中是否有为 city='X'的行,没有就插入一个记录 (X,1);
    • 如果临时表中有city='X'的行的行,就将x 这一行的num值加 1;
    1. 遍历完成后,再根据字段city排序,得到结果集返回给客户端。

    这个流程的执行图如下:

    临时表的排序是怎样的呢?

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

    • 如果是全字段排序,需要查询返回的字段,都放入sort buffer,根据排序字段排完,直接返回
    • 如果是rowid排序,只是需要排序的字段放入sort buffer,然后多一次回表操作,再返回。
    • 怎么确定走的是全字段排序还是rowid 排序排序呢?由一个数据库参数控制的,max_length_for_sort_data

    3. where 和 having的区别

    • group by + where 的执行流程
    • group by + having 的执行流程
    • 同时有where、group by 、having的执行顺序

    3.1 group by + where 的执行流程

    有些小伙伴觉得上一小节的SQL太简单啦,如果加了where条件之后,并且where条件列加了索引呢,执行流程是怎样

    好的,我们给它加个条件,并且加个idx_age的索引,如下:

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

    再来expain分析一下:

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

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

    Using index condition:表示索引下推优化,根据索引尽可能的过滤数据,然后再返回给服务器层根据where其他条件进行过滤。这里单个索引为什么会出现索引下推呢?explain出现并不代表一定是使用了索引下推,只是代表可以使用,但是不一定用了。大家如果有想法或者有疑问,可以加我微信讨论哈。

    执行流程如下:

    1. 创建内存临时表,表里有两个字段citynum
    2. 扫描索引树idx_age,找到大于年龄大于30的主键ID
    3. 通过主键ID,回表找到city = 'X'
    • 判断临时表中是否有为 city='X'的行,没有就插入一个记录 (X,1);
    • 如果临时表中有city='X'的行的行,就将x 这一行的num值加 1;
    1. 继续重复2,3步骤,找到所有满足条件的数据,
    2. 最后根据字段city排序,得到结果集返回给客户端。

    3.2 group by + having 的执行

    如果你要查询每个城市的员工数量,获取到员工数量不低于3的城市,having可以很好解决你的问题,SQL酱紫写:

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

    查询结果如下:

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

    3.3 同时有where、group by 、having的执行顺序

    如果一个SQL同时含有where、group by、having子句,执行顺序是怎样的呢。

    比如这个SQL:

    select city ,count(*) as num from staff  where age> 19 group by city having num >= 3;
    复制代码
    1. 执行where子句查找符合年龄大于19的员工数据
    2. group by子句对员工数据,根据城市分组。
    3. group by子句形成的城市组,运行聚集函数计算每一组的员工数量值;
    4. 最后用having子句选出员工数量大于等于3的城市组。

    3.4 where + having 区别总结

    • having子句用于分组后筛选,where子句用于条件筛选
    • having一般都是配合group by 和聚合函数一起出现如(count(),sum(),avg(),max(),min())
    • where条件子句中不能使用聚集函数,而having子句就可以。
    • having只能用在group by之后,where执行在group by之前

    4. 使用 group by 注意的问题

    使用group by 主要有这几点需要注意:

    • group by一定要配合聚合函数一起使用嘛?
    • group by的字段一定要出现在select中嘛
    • group by导致的慢SQL问题

    4.1 group by一定要配合聚合函数使用嘛?

    group by 就是分组统计的意思,一般情况都是配合聚合函数 如(count(),sum(),avg(),max(),min())一起使用。

    • count() 数量
    • sum() 总和
    • avg() 平均
    • max() 最大值
    • min() 最小值

    如果没有配合聚合函数使用可以吗?

    我用的是Mysql 5.7 ,是可以的。不会报错,并且返回的是,分组的第一行数据。

    比如这个SQL:

    select city,id_card,age from staff group by  city;
    复制代码

    查询结果是

    大家对比看下,返回的就是每个分组的第一条数据 

    当然,平时大家使用的时候,group by还是配合聚合函数使用的,除非一些特殊场景,比如你想去重,当然去重用distinct也是可以的。

    4.2 group by 后面跟的字段一定要出现在select中嘛。

    不一定,比如以下SQL:

    select max(age)  from staff group by city;
    复制代码

    执行结果如下:

    分组字段city不在select 后面,并不会报错。当然,这个可能跟不同的数据库,不同的版本有关吧。大家使用的时候,可以先验证一下就好。有一句话叫做,纸上得来终觉浅,绝知此事要躬行

    4.3 group by导致的慢SQL问题

    到了最重要的一个注意问题啦,group by使用不当,很容易就会产生慢SQL 问题。因为它既用到临时表,又默认用到排序。有时候还可能用到磁盘临时表

    • 如果执行过程中,会发现内存临时表大小到达了上限(控制这个上限的参数就是tmp_table_size),会把内存临时表转成磁盘临时表
    • 如果数据量很大,很可能这个查询需要的磁盘临时表,就会占用大量的磁盘空间。

    这些都是导致慢SQL的x因素,我们一起来探讨优化方案哈。

    5. group by的一些优化方案

    从哪些方向去优化呢?

    • 方向1: 既然它默认会排序,我们不给它排是不是就行啦。
    • 方向2:既然临时表是影响group by性能的X因素,我们是不是可以不用临时表?

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

    • group by 后面的字段加索引
    • order by null 不用排序
    • 尽量只使用内存临时表
    • 使用SQL_BIG_RESULT

    5.1 group by 后面的字段加索引

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

    我们回到一下这个SQL

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

    它的执行计划

    如果我们给它加个联合索引idx_age_city(age,city)

    alter table staff add index idx_age_city(age,city);
    复制代码

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

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

    5.2 order by null 不用排序

    并不是所有场景都适合加索引的,如果碰上不适合创建索引的场景,我们如何优化呢?

    如果你的需求并不需要对结果集进行排序,可以使用order by null

    select city ,count(*) as num from staff group by city order by null
    复制代码

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

    5.3 尽量只使用内存临时表

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

    5.4 使用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字段可以看到,执行没有再使用临时表,而是只有排序 

    执行流程如下:

    1. 初始化 sort_buffer,放入city字段;
    2. 扫描表staff,依次取出city的值,存入 sort_buffer 中;
    3. 扫描完成后,对 sort_buffer的city字段做排序
    4. 排序完成后,就得到了一个有序数组。
    5. 根据有序数组,统计每个值出现的次数。

    6. 一个生产慢SQL如何优化

    最近遇到个生产慢SQL,跟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 '姓名',
      `status` varchar(64) NOT NULL COMMENT 'Y-已激活 I-初始化 D-已删除 R-审核中',
      `age` int(4) NOT NULL COMMENT '年龄',
      `city` varchar(64) NOT NULL COMMENT '城市',
      `enterprise_no` varchar(64) NOT NULL COMMENT '企业号',
      `legal_cert_no` varchar(64) NOT NULL COMMENT '法人号码',
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8 COMMENT='员工表';
    复制代码

    查询的SQL是这样的:

    select * from t1 where status = #{status} group by #{legal_cert_no}
    复制代码

    我们先不去探讨这个SQL的=是否合理。如果就是这么个SQL,你会怎么优化呢?有想法的小伙伴可以留言讨论哈,也可以加我微信加群探讨。如果你觉得文章那里写得不对,也可以提出来哈,一起进步,加油呀

    展开全文
  • 文章目录MySQL——聚合函数和group by分组的使用1、聚合函数介绍2、GROUP BY 分组3、常见的聚合函数4、SQL执行顺序 MySQL——聚合函数和group by分组的使用 1、聚合函数介绍 SQL聚合函数计算一组数据的集合并返回...

    MySQL——聚合函数和group by分组的使用

    1、聚合函数介绍

    SQL聚合函数计算一组数据的集合并返回单个值。

    除 COUNT 以外,聚合函数忽略空值,如果COUNT函数的应用对象是一个确定列名,并且该列存在空值,此时COUNT仍会忽略空值。

    因为聚合函数对一组值进行操作,所以它通常与SELECT语句的GROUP BY子句一起使用,以计算为每个分组提供信息的度量。

    2、GROUP BY 分组

    分组是使用数据库时必须处理的最重要任务之一。 要将行分组,使用GROUP BY子句。

    GROUP BY子句是SELECT语句的可选子句,它根据指定列中的匹配值将行组合成组,每组返回一行。

    GROUP BY子句的语法:

    SELECT
        column1,
        column2,
        GROUP_FUNCTION (column3)
    FROM
        table1
    WHERE
        a = b
    GROUP BY
        column1,
        column2
    HAVING
        c = d
    ORDER BY
        column2 DESC;
    

    在SELECT子句中包含聚合函数不是强制性的。 但是,如果使用聚合函数,它将计算每个组的汇总值。

    需要强调的是,在对行进行分组之前应用WHERE子句,而在对行进行分组之后应用HAVING子句。 换句话说,WHERE子句应用于行,而HAVING子句应用于分组。

    要对组进行排序,请在GROUP BY子句后添加ORDER BY子句。

    GROUP BY子句中出现的列称为分组列。 如果分组列包含NULL值,则所有NULL值都汇总到一个分组中,因为GROUP BY子句认为NULL值相等。

    3、常见的聚合函数

    函数名称作用
    MAX查询指定列的最大值
    MIN查询指定列的最小值
    COUNT统计查询结果的行数
    SUM求和,返回指定列的总和
    AVG求平均值,返回指定列数据的平均值

    AVG/ SUM 只适用于数值类型的字段(或变量)

    MAX / MIN 适用于数值类型、字符串类型、日期时间类型的字段(或变量)

    使用COUNT(*)、COUNT(1)、COUNT(具体字段) 哪个效率更高:

    如果使用的是MyISAM存储引擎,则三者效率相同,都是O(1)

    如果使用的是InnoDB存储引擎,则三者效率 COUNT(*) = COUNT(1) >COUNT(字段)

    使用实例

    -- AVG 计算每个部门的平均工资
    SELECT e.department_id,department_name, ROUND(AVG(salary), 0) avg_salary
    FROM employees e
    JOIN departments d on e.department_id = d.department_id
    GROUP BY department_name
    ORDER BY department_name;
    
    -- SUM 返回每个部门中所有员工的总薪水
    SELECT department_id, SUM(salary)
    FROM employees
    GROUP BY department_id;
    
    -- MAX / MIN  返回每个部门中员工的最低和最高薪水
    SELECT department_name, MIN(salary) min_salary,MAX(salary) max_salary
    FROM employees e
    JOIN departments d on e.department_id = d.department_id
    GROUP BY department_name
    ORDER BY department_name;
    
    -- COUNT  返回每个部门的人数并根据部门名升序
    SELECT department_name, COUNT(*) headcount
    FROM employees e
    JOIN departments d on e.department_id = d.department_id
    GROUP BY department_name
    ORDER BY department_name;
    
    -- 查询最低薪资大于6000的各个部门的信息、最高薪资和平均薪资
    select e.department_id,department_name,min(salary) min_salary,max(salary) max_salary,round(avg(salary),2) average_salary
    from employees e
    join departments d on e.department_id = d.department_id
    GROUP BY e.department_id
    having min_salary > 6000
    order by department_id ;
    
    -- 查找人数大于5的部门
    SELECT e.department_id,department_name,COUNT(employee_id) headcount
    FROM employees e
    JOIN departments d ON d.department_id = e.department_id
    GROUP BY e.department_id
    HAVING headcount > 5
    ORDER BY headcount DESC;
    
    #1.where子句可否使用组函数进行过滤?
    -- 不可以
    
    #2.查询公司员工工资的最大值,最小值,平均值,总和
    select max(salary),min(salary),avg(salary),sum(salary) from employees;
    
    #3.查询各job_id的员工工资的最大值,最小值,平均值,总和
    select job_id, max(salary),min(salary),avg(salary),sum(salary)
    from employees
    group by job_id;
    
    #4.选择各个job_id具有的员工人数
    select job_id,count(*)
    from employees
    group by job_id;
    
    #5.查询员工最高工资和最低工资的差距(DIFFERENCE)
    select max(salary) - min(salary) DIFFERENCE
    from employees;
    
    #6.查询各个管理者手下员工的最低工资,其中最低工资不能低于6000,没有管理者的员工不计算在内
    select emp.employee_id,emp.manager_id, min(emp.salary) min_salary
    from employees emp
    join employees mang
    on emp.manager_id = mang.employee_id
    group by emp.manager_id
    having min_salary >= 6000;
    -- 或
    select employee_id,manager_id, min(salary) min_salary
    from employees
    where manager_id is not null
    group by manager_id
    having min_salary >= 6000;
    
    #7.查询所有部门的名字,location_id,员工数量和平均工资,并按平均工资降序
    select department_name,location_id,count(employee_id),round(avg(salary),2) avg_salary
    from  departments d
    left join employees e on e.department_id = d.department_id
    group by department_name
    order by avg_salary desc;
    
    #8.查询每个工种、每个部门的部门名、工种名和最低工资
    select department_name,job_id,min(salary)
    from employees e
    right join departments d on e.department_id = d.department_id
    group by job_id, d.department_id;
    

    4、SQL执行顺序

    SELECT 语句的完整结构(SQL99)

    select 去重   要查询的字段    from   表(注意:表和字段可以取别名)
    xxxx  join 要连接的表     on   等值判断(顺序:先onwhere)
    where (具体的值/子查询,不包含聚合函数的过滤条件)
    group by(通过那个子段来分组)
    having (过滤分组后的信息,条件和where一样,位置不同,包含聚合函数的过滤条件)
    order by(通过哪个字段排序)
    limit (分页)
    

    SQL语句的执行顺序

    在这里插入图片描述

    展开全文
  • pandas groupby 用法详解

    万次阅读 多人点赞 2020-12-21 22:55:56
    1.分组groupby 在日常数据分析过程中,经常有分组的需求。具体来说,就是根据一个或者多个字段,将数据划分为不同的组,然后进行进一步分析,比如求分组的数量,分组内的最大值最小值平均值等。在sql中,就是...
  • groupby函数详解

    万次阅读 多人点赞 2019-06-06 13:40:19
    pandas中groupby函数用法详解1 groupby()核心用法2 groupby()语法格式3 groupby()参数说明4 groupby()典型范例 1 groupby()核心用法 (1)根据DataFrame本身的某一列或多列内容进行分组聚合,(a)若按某一列聚合...
  • Python Pandas Groupby教程

    千次阅读 2020-07-15 12:15:54
    In this Pandas group by we are going to learn how to organize Pandasdataframes by groups. More specifically, we are going to learn how to group by one and multiple columns. Furthermore, we are going ....
  • 对dataframe进行groupby之后得到的是一个groupby对象,不能直接打印输出,但可以对这个对象进行各种计算 df = pd.DataFrame({'key1':['a','a','b','b','a'], 'key2':['one','two','one','two','one'], 'data1':...
  • MySQL查询优化:GROUP BY

    万次阅读 多人点赞 2019-03-05 19:46:16
    一、group by group by 优化方法 — 索引 松散索引扫描(Loose Index Scan) 为什么松散索引扫描的效率会很高? 紧凑索引扫描(Tight Index Scan) group by 优化方法 — 直接排序 二、group by 与 distinct ...
  • 详解SQL中GroupBy的用法

    千次阅读 2021-02-04 14:49:32
    GROUP BY 语句用于结合合计函数,根据一个或多个列对结果集进行分组。1、概述“Group By”从字面意义上理解就是根据“By”指定的规则对数据进行分组,所谓的分组就是将一个“数据集”划分成若干个“小区域”,然后...
  • 分组原理(GROUP BY子句)1:GROUP BY子句基本语法规则 若觉得本文写得还可以,请多多关注本人所作书籍《C++语法详解》电子工业出版社出版,作者 黄勇 本文为原创文章,转载请注明出处,或注明转载自“黄邦勇帅(原名:...
  • 深入理解group by报错注入

    千次阅读 2021-12-12 21:29:45
    文章目录深入理解floor报错注入一、理解floor,group by +count(*),rand,count函数的作用1.group by+count(*)2.rand()3.floor()4.count()返回指定列的值的数量5.floor(rand()*2)二、group by分组的原理三、group by...
  • pandas.core.groupby.GroupBy.mean

    千次阅读 2022-03-13 19:57:48
    解释:dataframe按某一列属性分组求其他属性的平均值 final GroupBy.mean(numeric_only=NoDefault.no_default, engine='cython', engine_kwargs=None)[source] Compute mean of groups, excluding missing ...
  • group by与order by的区别

    万次阅读 多人点赞 2020-07-08 14:11:33
    1. order by order by比较简单,作用就是排序 asc 从上到下慢慢升序 ...2. group by order by 我的理解是:聚合分组 聚合的意思就是聚合函数:例如 sum()、avg()、count()等等 直接上个栗子: ...
  • Python函数:groupby()

    千次阅读 2022-02-28 09:21:30
    DataFrame.groupby(by=None, axis=0, level=None, as_index=True, sort=True, group_keys=True, squeeze=NoDefault.no_default, observed=False, dropna=True) 分组操作涉及到分离对象、应用函数和组合结果的一些...
  • pandas之groupby函数

    千次阅读 2021-04-04 17:07:40
    sql中的分组语句group by很重要,pandas中也有类似的分组函数,即groupby,本文就主要介绍下它的用法。
  • 详解python中groupby函数通俗易懂

    千次阅读 2021-01-12 03:57:50
    一、groupby 能做什么?python中groupby函数主要的作用是进行数据的分组以及分组后地组内运算!对于数据的分组和分组运算主要是指groupby函数的应用,具体函数的规则如下:df[](指输出数据的结果属性名称).groupby(...
  • Python中的groupby分组

    千次阅读 2022-04-21 23:16:17
    Python中的groupby分组 一、groupby函数 groupby函数功能:对DataFrame进行分组(可单类分组,可多类分组) 需求:按“字段”列对数据data进行分组 groupby函数基本格式:data.groupby([‘分组字段’]) data:要...
  • MySQL之groupby详解

    千次阅读 2021-10-15 15:35:51
    MySQL中提供了groupby关键字,用来对数据进行分组,用来统计分组的信息。 groupby的原理 select id%10 as m, count(*) as c from t group by m; 上述的sql是对id进行分组,对10取模相同的id分到一组,然后获取组内...
  • group by 和 order by 的区别 + 理解过程

    万次阅读 多人点赞 2018-07-25 12:04:16
    order bygroup by 的区别 order by 和 group by 的区别: 1,order by 从英文里理解就是行的排序方式,默认的为升序。 order by 后面必须列出排序的字段名,可以是多个字段名。 2,group by 从...
  • MySQL 根据日期 group by

    千次阅读 2020-12-15 14:45:02
    mysql 有一张这样的表,需要根据customer_ip_date这个字段的日期进行group by 排序,得出每天的数量是多少 MySQL 根据日期 group by有两种写法: 1.字符串截取:substring,从第一个字段截取到第十个字符串,然后...
  • GROUP BY和HAVING用法介绍

    万次阅读 多人点赞 2020-09-09 16:00:26
    2、having必须和groupby一起用,且在groupby后面 3、groupby、having、order by的使用顺序:groupby 、having、order by SELECT *|字段列表 [as 别名] FROM 表名 [WHERE 子句] [GROUP BY 子句][HAVING 子句]...
  • 关于group by的用法 原理

    万次阅读 多人点赞 2018-06-14 00:26:30
    写在前面的话:用了好久group by,今天早上一觉醒来,突然感觉group by好陌生,总有个筋别不过来,为什么不能够select * from Table group by id,为什么一定不能是*,而是某一个列或者某个列的聚合函数,group by ...
  • Pandas怎样实现groupby分组统计

    千次阅读 2022-05-10 22:25:50
    groupby:先对数据分组,然后在每个分组上应用聚合函数、转换函数 通过三个实例来了解pandas是如何实现分组统计的 一、分组使用聚合函数做数据统计 二、遍历groupby的结果理解执行流程 三、实例分组探索天气数据 ...
  • pandas之分组groupby()的使用整理与总结

    万次阅读 多人点赞 2019-07-27 18:41:53
    前言 在使用pandas的时候,有些场景需要对数据内部进行分组处理,如一组全校学生成绩的数据,我们想通过班级进行分组,或者再对班级分组后...groupby的作用可以参考 超好用的 pandas 之 groupby 中作者的插图进行直...
  • groupby去重

    千次阅读 2021-03-18 12:29:58
    Groupby 分组后,如果没有对分组后的数据进行操作,如对每组求和 取平均 取最小等操作,分组后直接显示,则默认显示 该分组的第一条数据。 第一幅图是没有进行groupby操作后的数据,数据共有48条 下图是在第...
  • pandas分组统计 - groupby功能

    万次阅读 多人点赞 2019-01-05 11:11:16
    分组统计 - groupby功能 ① 根据某些条件将数据拆分成组 ② 对每个组独立应用函数 ③ 将结果合并到一个数据结构中 Dataframe在行(axis=0)或列(axis=1)上进行分组,将一个函数应用到各个分组并产生一个新值,然后...
  • python dataframe的groupby

    千次阅读 2021-07-10 11:10:51
    dataframe 的 groupby提供了分组,切片,切块的操作,根据一列或者多列进行拆分数据。 对于分组后的数据可以计数,平均值,众数等等。同样通过各种函数还可以进行透视表,交叉表,分位表等等分组的分析 groupby函数...
  • group by 与order by 一起如何使用

    千次阅读 2022-03-31 17:15:39
    yyORDER BY 语句用于对结果集进行排序。 ORDER BY 语句 ORDER BY 语句用于根据指定的列对结果集进行排序。 ORDER BY 语句默认按照...对数据进行分组排序时group by 与order by 一起使用是要遵守一定原则的 要求

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 1,022,838
精华内容 409,135
关键字:

groupby