【MySQL基础教程】分组聚集

最新推荐文章于 2024-04-27 08:53:36 发布
最新推荐文章于 2024-04-27 08:53:36 发布
阅读量289 收藏
点赞数
分类专栏： MySQL 文章标签： MySQL基础
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/mingqing6364/article/details/82229284
版权

1.环境搭建

CREATE TABLE `temp` (
  `key` char(1) DEFAULT NULL,
  `value` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO temp ( `key`, `value` )
VALUES
    ( 'A', 1 ),( 'A', 2 ),( 'A', 3 ),( 'A', 9 ),( 'A', 9 ),
    ( 'B', 10 ),( 'B', 12 ),( 'B', 63 ),( 'B', 19 ),( 'B', 9 ),
    ( 'C', 60 ),( 'C', 112 ),( 'C', 6 ),( 'C', 9 ),( 'C', 99 ),
    ( 'D', NULL ),( 'D', NULL ),( 'D', NULL ),( 'D', NULL ),( 'D', NULL );

2.聚合函数（组函数）

2.1 COUNT

count用于统计次数，可以用于所有类型的字段或常量。

SELECT
    `KEY`,
    COUNT(*) count01, # 统计出现次数
    COUNT(`KEY`) count02, # 可以引用某个字段用于统计
    COUNT(`value`) count03, # 但是null不计算次数
    COUNT(DISTINCT `value`) count04 # 加上DISTINCT关键字，可以去除重复，得到不同值的数量
FROM
    temp
GROUP BY
    `KEY`;
/*
+-----+---------+---------+---------+---------+
| KEY | count01 | count02 | count03 | count04 |
+-----+---------+---------+---------+---------+
| A   |       5 |       5 |       5 |       4 |
| B   |       5 |       5 |       5 |       5 |
| C   |       5 |       5 |       5 |       5 |
| D   |       5 |       5 |       0 |       0 |
+-----+---------+---------+---------+---------+
4 rows in set (0.04 sec)
*/

2.2 SUM、AVG

sum用于求和，avg用于取平均值，尽可能传递数值类字段/常量。

SELECT
    `KEY`,
    SUM(`value`) sum01, # 对某个字段求和，null会被忽略
    SUM(1) sum02, # 也可以传递常量，sum(n)等于count(*)*n
    SUM(DISTINCT `value`) sum03, # 加上DISTINCT关键字，可以去除重复
    AVG(`value`) avg01, # 对某个字段去平均值，null会被忽略
    AVG(1) avg02, # 也可以传递常量，但是没有实际意义，avg(n)等于n
    AVG(DISTINCT `value`) avg03, # 加上DISTINCT关键字，可以去除重复
    SUM(`value`) / COUNT(`value`) sc01, # avg等效于sum/count
    SUM(DISTINCT `value`) / COUNT(DISTINCT `value`) sc02,
    SUM(`KEY`) sumstr01, AVG(`KEY`) avgstr01, # 如果传递非数值类参数，会自动转换类型
    SUM('1abc2') sumstr02, AVG(CAST('1abc2' AS SIGNED)) avgstr02 # Mysql的转换机制是"尽力而为"，无论隐式还是显式
FROM
    temp
GROUP BY
    `KEY`;
/*
+-----+-------+-------+-------+---------+--------+---------+---------+---------+----------+----------+----------+----------+
| KEY | sum01 | sum02 | sum03 | avg01   | avg02  | avg03   | sc01    | sc02    | sumstr01 | avgstr01 | sumstr02 | avgstr02 |
+-----+-------+-------+-------+---------+--------+---------+---------+---------+----------+----------+----------+----------+
| A   | 24    | 5     | 15    | 4.8000  | 1.0000 | 3.7500  | 4.8000  | 3.7500  |        0 |        0 |        5 | 1.0000   |
| B   | 113   | 5     | 113   | 22.6000 | 1.0000 | 22.6000 | 22.6000 | 22.6000 |        0 |        0 |        5 | 1.0000   |
| C   | 286   | 5     | 286   | 57.2000 | 1.0000 | 57.2000 | 57.2000 | 57.2000 |        0 |        0 |        5 | 1.0000   |
| D   | NULL  | 5     | NULL  | NULL    | 1.0000 | NULL    | NULL    | NULL    |        0 |        0 |        5 | 1.0000   |
+-----+-------+-------+-------+---------+--------+---------+---------+---------+----------+----------+----------+----------+
4 rows in set (0.06 sec)
*/

2.3 MIN、MAX

SELECT
    MAX(`KEY`) MAX01, # MAX用于获取最大值
    MAX(`value`) MAX02, # NULL依然会被忽略
    MAX(DISTINCT `KEY`) MAX03, # 支持DISTINCT关键字，但是没有实际意义
    MIN(`KEY`) MIN01, # MIN用于获取最小值，用法和注意事项与MAX完全一致
    MIN(`value`) MIN02,
    MIN(DISTINCT `KEY`) MIN03
FROM
    temp;
/*
+-------+-------+-------+-------+-------+-------+
| MAX01 | MAX02 | MAX03 | MIN01 | MIN02 | MIN03 |
+-------+-------+-------+-------+-------+-------+
| D     |   112 | D     | A     |     1 | A     |
+-------+-------+-------+-------+-------+-------+
1 row in set (0.06 sec)
*/

2.4 小结

相同点：

  1. 仅允许传递一个参数/常量
  2. 会忽略null
  3. 可以使用distinct关键字去重

不同点：

  1. 只有count允许传递*

注意：

上述5种聚合函数，不允许嵌套，例如MAX(MIN(1))。

此外mysql提供了一个拼接字符串的聚合函数group_concat，可参阅：【MySQL基础教程】字符串处理函数

3.GROUP BY

SELECT
    `KEY`,
    COUNT(*) count01
FROM
    temp
GROUP BY
    `KEY`, CONCAT('KEY IS ', `KEY`), 'KEY' # 指定分组字段，允许多个字段组合分组，支持表达式和常量，但是传递常量无意义
    ;
/*
+-----+---------+
| KEY | count01 |
+-----+---------+
| A   |       5 |
| B   |       5 |
| C   |       5 |
| D   |       5 |
+-----+---------+
4 rows in set (0.05 sec)
*/

SELECT
    `KEY` KEYWORD,
    COUNT(*) count01
FROM
    temp
GROUP BY
    KEYWORD # 也支持使用别名分组
    ;
/*
+---------+---------+
| KEYWORD | count01 |
+---------+---------+
| A       |       5 |
| B       |       5 |
| C       |       5 |
| D       |       5 |
+---------+---------+
4 rows in set (0.05 sec)
*/

SELECT
    `KEY` KEYWORD,
    COUNT(*) count01
FROM
    temp
GROUP BY
    1 # 还支持使用字段序号，从1开始
    ;
/*
+---------+---------+
| KEYWORD | count01 |
+---------+---------+
| A       |       5 |
| B       |       5 |
| C       |       5 |
| D       |       5 |
+---------+---------+
4 rows in set (0.05 sec)
*/


SELECT
    `KEY` KEYWORD,
    COUNT(*) count01
FROM
    temp
GROUP BY
    1 DESC # 还可以写入排序，简化SQL语句
    ;
/*
+---------+---------+
| KEYWORD | count01 |
+---------+---------+
| D       |       5 |
| C       |       5 |
| B       |       5 |
| A       |       5 |
+---------+---------+
4 rows in set (0.05 sec)
*/


SELECT
    `KEY`,
    `value`,
    COUNT(*) count01
FROM
    temp
GROUP BY
    `KEY`, `value`
WITH ROLLUP # 带上WITH ROLLUP，可以实现小计+总计的功能：分别以KEY+VALUE，KEY，NULL（可以看成是常量，所有数据在一个组中）分组
    ;
+------+-------+---------+
| KEY  | value | count01 |
+------+-------+---------+
| A    |     1 |       1 |
| A    |     2 |       1 |
| A    |     3 |       1 |
| A    |     9 |       2 |
| A    | NULL  |       5 |
| B    |     9 |       1 |
| B    |    10 |       1 |
| B    |    12 |       1 |
| B    |    19 |       1 |
| B    |    63 |       1 |
| B    | NULL  |       5 |
| C    |     6 |       1 |
| C    |     9 |       1 |
| C    |    60 |       1 |
| C    |    99 |       1 |
| C    |   112 |       1 |
| C    | NULL  |       5 |
| D    | NULL  |       5 |
| D    | NULL  |       5 |
| NULL | NULL  |      20 |
+------+-------+---------+
20 rows in set (0.06 sec)

4.HAVING

SELECT
    `KEY`,
    COUNT(*) count01
FROM
    temp
GROUP BY
    `KEY`
HAVING
    SUM(`VALUE`) > 100 # 过滤数据，语法与WHERE一致，但是HAVING是在分组聚合之后过滤，WHERE在GROUP BY之前
    ;
/*
+-----+---------+
| KEY | count01 |
+-----+---------+
| B   |       5 |
| C   |       5 |
+-----+---------+
2 rows in set (0.05 sec)
*/


SELECT
    `KEY`,
    COUNT(*) count01
FROM
    temp
HAVING
    SUM(`VALUE`) > 100 # 如果在没有GROUP BY字句的情况下使用HAVING，相当于GROUP BY 常量，所有的数据会放在一个组中
    ;
/*
+-----+---------+
| KEY | count01 |
+-----+---------+
| A   |      20 |
+-----+---------+
1 row in set (0.05 sec)
*/


SELECT
    `KEY`,
    COUNT(*) count01
FROM
    temp
GROUP BY
    `KEY`
HAVING
    `value` < 100 # 既没有出现在GROUP BY字句，又没有使用聚合函数包裹的列，不能使用HAVING过滤
    ;
/*
1054 - Unknown column 'value' in 'having clause'
*/

 

mingqing6364
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
MySQL之分组
汉诺塔少年的博客
09-22 1860
一.分组的目的 1.分组的目的是为了把数据进行逻辑分组，以方便对每个组进行聚集计算； 比如我们想统计学生stduents(id, class_id, name, gender, score)中男生和女生分别有多少人, 可以这么写: SELECT gender, COUNT(*) AS nums FROM students GROUP BY gender; 得出结果如图所示： 需要注意的是，一旦用到的分组，返回的数据只能是分组的 依据字段(用来做标识)和 ...
MYSQL——分组
weixin_64652936的博客
03-12 1万+
1. group by 1. group by的含义:将查询结果按照1个或多个字段进⾏分组，字段值相同的为⼀组 2. group by可⽤于单个字段分组，也可⽤于多个字段分组 根据gender字段来分组，gender字段的全部值有4个'男' , '⼥' , '中性' , '保密'，所以分为了4组 当group by单独使⽤时，只显⽰出每组的第⼀条记录, 所以group by单独使⽤时的实际意义 不⼤ 2. group by + group_concat() 1. group_concat(字段
MySQL-数据分组(GROUP BY)
最新发布
qq_57157023的博客
04-27 688
数据分组
Mysql：分组查询
热门推荐
不怕猫的耗子A
11-15 4万+
分组查询 1、分组查询是对数据按照某个或多个字段进行分组，在MYSQL中使用GROUP BY关键字对数据进行分组 2、GROUP BY关键字可以将查询结果按照某个字段或多个字段进行分组。字段中值相等的为一组 ⑴分组的核心是：在查询SQL中指定分组的列名，然后根据该列的值进行分组，值相等的为一组 3、分组查询的基本的语法格式如下： GROUP BY 字段名 [HAVING 条件表达式] 参数： 1、字段名：是指按照该字段的值进行分组(分组是所依据的列名称) 2、HAVING条件表达式：用来...
分组聚合
weixin_45037357的博客
08-31 1944
分组聚合 pandas提供了功能类似于数据库中group by语句的用于拆分数据组的方法pd.groupby()；该方法提供的是分组聚合步骤中的拆分功能，能根据索引或字段对数据进行分组（Split） 进而针对得到的多组数据执行聚合操作（Apply），最终合并为最终结果（Combine）。 分组 groupby方法的参数及其说明： DataFrame.groupby(by=None, axis=0, as_index=True, sort=True) 参数名称 说明 by .
MYSQL经典基础教程.ppt
11-15
MYSQL经典基础教程.ppt
MySQL.rar_mysql基础教程_mysql教程
09-14
本教程面向初学者，旨在帮助你系统地学习MySQL的基础知识，掌握数据存储、查询及管理的基本技能。 **1. MySQL简介** MySQL是一个开源、免费的SQL数据库，由Oracle公司维护。它以其高效性能、易用性和可靠性而受到...
MySQL 基础教程参考手册
03-21
MySQL数据库开发的三十六条军规_石展_完整.pdf mysql容灾及自动化切换.pdf MySQL 性能优化最佳实践.pdf ...MySQL存储过程基础教程.pdf MySql 手册.pdf MySQL_中文参考手册.pdf Mysql双机热备安装手册(V).doc
基本聚集和分组聚集
k的博客
03-10 460
一个代码引发的学案。。 select course_id,count(distinct id) from takes where year=2009 and semester='Fall' group by course_id;//正确 select course_id,count(distinct id) from takes where year=2009 and semester='Fal...
elasticSearch的分组聚集（group by）
清凌的博客
08-27 2万+
假如我们有一个需要选择的对象，它有两个参数来唯一确定这个对象：vport和vip（一个vip可以对应多个vport）， 除此之外，还有feature_type类型，set_info集群信息，cdate日期，feature_data特征值（数字） 我们的目标是选择出一段时间内，某个类型，某个set_info下的每个vip和vport标识对象的平均特征值。所以我们需要： 1.用query来筛选指定的某...
MySQL分组查询Group By实现原理详解
09-10
在MySQL 中，GROUP BY 的实现同样有多种（三种）方式，其中有两种方式会利用现有的索引信息来完成 GROUP BY，另外一种为完全无法使用索引的场景下使用。下面我们分别针对这三种实现方式做一个分析
使用mysql语句进行分组查询
SSS4362的博客
08-12 5892
使用mysql语句进行分组查询
MySQL之聚合函数
qq_58902919的博客
04-26 832
#聚合函数的使用 #常见的聚合函数 # AVG/SUM SELECT AVG(salary) FROM employees; SELECT SUM(salary) FROM employees; SELECT SUM(last_name),AVG(last_name),SUM(hire_date) FROM employees;#无结果或者字符串相加没有意义 # MAX/MIN:适用于字符串类型、数值类型、日期类型的字段（） SELECT MAX(salary),MIN(salary) FROM .
数据库的分组聚集
Gosick_Geass_Gate的博客
12-31 972
包含聚集、group by或having子句的查询的含义可通过以下操作顺序来定义： （1）最先根据from子句来计算出一个关系。 （2）如果出现了where子句，where子句中的谓词将应用到from子句的结果关系上。 （3）如果出现了group by子句，满足where谓词的元组通过group by子句形成分组，在每个分组上都要进行指定的聚集计算。如果没有group by子句，满足wher...
一张图让你详细理解Group By的分组聚合过程
大数据分析实战
10-29 1万+
原文链接：http://www.datastudy.cc/to/57 Group By分组聚合是我们在使用过程中使用最多的SQL之一（另外一个使用最多的估计就是JOIN了吧），为了让大家详细了解Group By的计算执行过程，我们下面使用一张图来详细讲解一下。 我们的数据就是左表，有name和score两列，我们要求每个name下，score
分组聚集的K-means算法应用实例
RainEagle专栏
05-25 1万+
概述在许多实际应用中，需要对许多数据点进行分组，划分成一个个簇（cluster），并计算出每一个簇的中心。这就是著名的k-means算法。k-means算法的输入是N个d维数据点：x_1, …, x_N，以及需要划分的簇的数目k。算法运行的结果是每个簇的中心点m_1, …, m_k，也可以输出每个簇中有哪些数据点。算法先通过随机，或启发式搜索，确定初始的中心点位置。再通过如下两个步骤的交替，进行数据
python处理excel表格数据-利用Python处理和分析Excel表中数据实战.doc
weixin_37988176的博客
11-01 544
利用Python处理和分析Excel表中数据实战【利用python进行数据分析——基础篇】利用Python处理和分析Excel表中数据实战 原创 2017年06月28日 15:09:32 标签： python /数据分析 /excel /新浪微博 作为一个学习用Python进行数据分析的新手来说，通过本文来记录分享一些我在用Python中的pandas、numpy来分析Excel表中数据的数据...
mysql分组排序
yangzsirr的博客
07-17 1万+
在平常开发中我们经常可能会遇到这样的问题，如在一个年级中，我们需要根据根据班级成绩排名，选出每个班前三名同学，那么这个地方就涉及到分组排序，那么对于mysql来说如何实现分组排序呢。 我们以学生表student为例，找出每个班成绩排名前三的同学姓名及成绩。 学生表： 在mysql低版本中不支持类似于sqlserver中row_number() over(partition by ...)等语句，那么只能采用变量的形式曲线救国。 select * from ( select x.cla.
2022年北大青鸟MYSQL基础教程2.ppt
11-02
2022年北大青鸟MYSQL基础教程2.ppt

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值