当前位置： 首页 > news >正文

【MySQL】聚合函数和分组查询

news 2025/5/22 20:08:16

目录

1.聚合函数

1.1.常见的聚合函数

1.2.使用示例

1.2.1. 统计班级共有多少同学

1.2.2 统计班级收集的QQ号有多少个

1.2.3 统计本次考试的数学成绩分数的个数有几个

1.2.4 统计数学成绩总分

1.2.5 统计不满100分的数学成绩总分

1.2.6 统计总分的平均分

1.2.7 求英语最高分

2.分组查询（group by)

2.1.准备工作

2.2.显示每个部门的平均工资和最高工资

2.3.显示每个部门的每种岗位的平均工资和最低工资

2.4.显示平均工资低于2000的部门和它的平均工资

 

1.聚合函数

        聚合函数是SQL中的一类特殊函数，它们用于计算一组数据的集合并返回单个值。这些函数通常用于数据的统计分析，如个数的统计、某列数据的求和、最大值、最小值以及平均值等。

1.1.常见的聚合函数

COUNT([DISTINCT] expr)

  1. 说明：返回查询结果中数据的数量。如果指定了DISTINCT关键字，则只计算不同值的数量。
  2. 示例：SELECT COUNT(*) FROM employees; 返回employees表中的记录数。

SUM([DISTINCT] expr)

  1. 说明：返回查询结果中数据的总和。如果列中的数据不是数字类型，则该函数没有意义。如果指定了DISTINCT关键字，则只计算不同值的总和。
  2. 示例：SELECT SUM(salary) FROM employees; 返回employees表中所有员工的工资总和。

AVG([DISTINCT] expr)

  1. 说明：返回查询结果中数据的平均值。如果列中的数据不是数字类型，则该函数没有意义。如果指定了DISTINCT关键字，则只计算不同值的平均值。
  2. 示例：SELECT AVG(salary) FROM employees; 返回employees表中所有员工工资的平均值。

MAX([DISTINCT] expr)

  1. 说明：返回查询结果中的最大值。如果列中的数据不是可以比较的类型（如数字或日期），则该函数没有意义。如果指定了DISTINCT关键字，则只在不同值中查找最大值。
  2. 示例：SELECT MAX(salary) FROM employees; 返回employees表中工资最高的值。

MIN([DISTINCT] expr)

  1. 说明：返回查询结果中的最小值。与MAX函数类似，它也只适用于可以比较的数据类型。如果指定了DISTINCT关键字，则只在不同值中查找最小值。
  2. 示例：SELECT MIN(salary) FROM employees; 返回employees表中工资最低的值。

大家可能有疑问，这个expr是什么玩意？ 

        在SQL的聚合函数COUNT([DISTINCT] expr), SUM([DISTINCT] expr), AVG([DISTINCT] expr), MAX([DISTINCT] expr), 和 MIN([DISTINCT] expr)中，expr代表一个表达式（expression）的占位符。这个表达式可以是列名、数学运算、函数调用，或者是这些元素的组合。
解释

  1. 列名：最常见的情况是，expr就是表中的某一列的名称。例如，在SELECT COUNT(employee_id) FROM employees;中，employee_id就是expr。
  2. 数学运算：expr也可以是数学运算的结果。例如，SELECT AVG(salary * 1.1) FROM employees;中，salary * 1.1就是expr，这里假设要给所有员工的工资增加10%后再计算平均值。
  3. 函数调用：expr还可以是函数调用的结果。例如，SELECT SUM(ABS(salary - 50000)) FROM employees;中，ABS(salary - 50000)就是expr，这里计算了每个员工工资与50000的差的绝对值之和。
  4. 组合：expr也可以是上述元素的组合。例如，SELECT MAX(salary + bonus) FROM employees;中，salary + bonus就是expr，这里假设要计算每个员工工资与奖金之和的最大值。

DISTINCT关键字

  • 当在聚合函数中指定DISTINCT关键字时，函数将只考虑表达式expr的不同（唯一）值。
  • 这意味着，对于COUNT([DISTINCT] expr)，它会计算不同值的数量；
  • 对于SUM([DISTINCT] expr)、AVG([DISTINCT] expr)，它会先去除重复的值，然后对这些不同的值进行求和或计算平均值；
  • 对于MAX([DISTINCT] expr)和MIN([DISTINCT] expr)，由于最大值和最小值本身就是唯一的（在给定的数据集中），所以DISTINCT关键字实际上对这些函数的结果没有影响，但在某些数据库系统中，即使如此，语法上仍然允许使用DISTINCT。

示例

假设有一个名为employees的表，包含以下列：employee_id, name, salary, bonus。

  1. SELECT COUNT(DISTINCT salary) FROM employees; 会返回不同工资数量的计数。
  2. SELECT SUM(DISTINCT salary) FROM employees; 会计算所有不同工资的总和。
  3. SELECT AVG(DISTINCT salary) FROM employees; 会计算所有不同工资的平均值。
  4. SELECT MAX(salary) FROM employees; 会返回最高的工资值（DISTINCT在这里是多余的，因为MAX总是返回唯一的最大值）。
  5. SELECT MIN(salary) FROM employees; 会返回最低的工资值（同样，DISTINCT在这里是多余的）。 

聚合函数的使用注意事项

  1. 空值处理：聚合函数通常忽略空值（NULL）。也就是说，如果某列中的某个值为NULL，则该值不会参与聚合函数的计算。
  2. DISTINCT关键字：通过在聚合函数中使用DISTINCT关键字，可以确保在计算中只考虑不同的值。然而，这可能会增加计算的复杂性并降低性能。
  3. SELECT语句中的使用：聚合函数通常在SELECT语句中使用，并且可以与GROUP BY子句结合使用来对数据进行分组统计。

1.2.使用示例

为了方便进行演示，下面创建一个学生表，表当中包含自增长的主键id、学号、姓名和QQ号。如下：

create table students(
id int unsigned primary key auto_increment,
sn int unsigned not null unique comment '学号',
name varchar(20) not null comment '姓名',
qq varchar(15) unique comment 'qq号码'
);desc students;INSERT INTO students (id, sn, name,qq) VALUES  
(99, 100, '唐僧',NULL),  
(102, 123, '曹操',NULL),  
(103, 187, '刘备',NULL),  
(105, 1023, '孙行者',111111);select * from students;

 

1.2.1. 统计班级共有多少同学

这里我们直接使用count(*)进行聚合统计，表示对所有的列数据进行统计：

select count(*) from students;

这里的统计原理也很简单，其实就是使用了select *将每一条记录都拿到，然后将每一条待处理记录时都传递给这个count聚合函数，然后我们就能够拿到数据的总个数了。

1.2.2 统计班级收集的QQ号有多少个

值得注意的是：我们没有对qq号码进行非空约束

我们继续使用聚会函数count进行统计。

select count(qq) from students;

结果为1，这证明聚合函数确实会忽略空值，即NULL值不会参与运算的。

1.2.3 统计本次考试的数学成绩分数的个数有几个

为了方便我们进行演示，下面创建一个成绩表，表当中包含：自增长的主键id、姓名、以及该同学的语文成绩、数学成绩和英语成绩。如下：

CREATE TABLE exam_result (
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20) NOT NULL COMMENT '同学姓名',
chinese float DEFAULT 0.0 COMMENT '语文成绩',
math float DEFAULT 0.0 COMMENT '数学成绩',
english float DEFAULT 0.0 COMMENT '英语成绩'
);
INSERT INTO exam_result (name, chinese, math, english) VALUES
('唐三藏', 67, 98, 56),
('孙悟空', 87, 78, 77),
('猪悟能', 88, 98, 90),
('曹孟德', 82, 84, 67),
('刘玄德', 55, 85, 45),
('孙权', 70, 73, 78),
('宋公明', 75, 65, 30);
select * from exam_result;

 

注意本题目要求的是数学成绩的个数，不是数学成绩的值，这意味着我们需要对相同的数学成绩的值进行先去重，然后再进行聚合统计，对于去重我们可以使用distinct进行去重，然后再使用count进行聚合统计。

select count(distinct math) from exam_result;

我们发现完全正确。

1.2.4 统计数学成绩总分

对于统计数学成绩的总分其实就是对数据进行求和，我们可以使用sum函数来进行求和：

select sum(math) from exam_result;

这个答案没错啊，不信的可以拿计算器算一算

1.2.5 统计不满100分的数学成绩总分

在刚才的示例中我们已经求得数学成绩的总分了，对于不满100分的人我们可以使用where子句进行筛选得到。

select sum(math) from exam_result where math < 80;

1.2.6 统计总分的平均分

对于平均分我们可以使用avg函数进行求得： 

select avg(chinese + math + english) from exam_result;

1.2.7 求英语最高分

求最高分其实就是求最大值，我们可以使用max函数进行求最大值： 

select max(english) from exam_result;

2.分组查询（group by)

        不知刚才你注意到没，前面我们进行聚合统计时都是在对整张表进行聚合统计，但是有时我们想要对不同的情况进行分别统计。

        例如在一个班级之中，有男生和女生，我们想要得到男生和女生中英语成绩的最高分分别是多少？我们发现我们再使用max函数是没有办法达到我们想要的目的的，但是如果我们先对班级中的男女生进行分组，然后又分别进行聚合统计，使用max函数就能够达到我们想要的目的了！

所以分组是数据库最重要任务之一，要将行分组，我们可以使用GROUP BY子句。

        GROUP BY 子句在SQL中用于将结果集按照一个或多个列进行分组，通常与聚合函数（如 COUNT(), SUM(), AVG(), MAX(), MIN() 等）一起使用，以便对每个分组进行汇总计算。下面是对 GROUP BY 子句及其相关部分的详细解释：

SELECT column1 [, column2], ...   
FROM table_name   
[WHERE ...]   
GROUP BY column [, ...]   
[ORDER BY ...]   
[LIMIT ...];

关键字解释

  • SELECT: 指定要查询的列。如果使用了聚合函数，则可以选择聚合函数的结果，或者选择用于分组的列（这些列在结果集中将显示为相同的值）。
  • FROM: 指定要查询的表。
  • WHERE: 可选，用于过滤记录，只包括满足条件的记录进行分组。
  • GROUP BY: 指定按哪些列进行分组。结果集中每个分组将包含这些列的唯一组合。
  • ORDER BY: 可选，用于对结果集进行排序。可以基于聚合函数的结果或其他列进行排序。
  • LIMIT: 可选，用于限制结果集的数量。

执行顺序

  1. WHERE: 首先执行，过滤出符合条件的记录。
  2. GROUP BY: 然后按照指定的列进行分组。
  3. SELECT: 选择要显示的列，并计算聚合函数的结果。
  4. ORDER BY: 对结果集进行排序。
  5. LIMIT: 最后限制结果集的数量。

说实话，到这里我还是没有讲清楚group by是怎么工作的

2.1.准备工作

首先我们去这个网站下载一个.sql文件：阿里云盘分享 (alipan.com)

 拿到该数据库文件以后，我们可以先打开该文件进行查看其内容：

vim scott_data.sql

我们会发现其里面都是SQL记录，对于MySQL我们备份其数据库时，其实备份的全部都是一条条有效的SQL记录，通过重新执行这些SQL，我们便能够得到和原来一摸一样数据库。

接下来我们就可以在mysql中将这个数据库给创建出来了： 

source 该文件的绝对路径;

例如我这里是：

source /home/zs_113/scott_data.sql

然后我们查询我们的数据库，发现数据库中多了一个scott的数据库：

show databases;

我们进去看看 

use scott;
show tables;

这三张表是

  • EMP员工表
  • DEPT部门表
  • SALGRADE工资等级表

我们先分别查看一下表结构和表内容：

  • EMP员工表
desc emp;
select * from emp;

  •  DEPT部门表
desc dept;
select * from dept;

  • SALGRADE工资等级表 
desc salgrade;
select * from salgrade;

2.2.显示每个部门的平均工资和最高工资

由于要显示每个部门平均工资和最高工资，所以我们一定要借助group by来将这个整表进行划分为多个组，然后我们再使用avg与max函数来对工资分别求平均和最高工资。

所以我们可以这样进行查询：

select deptno, avg(sal), max(sal) from emp group by deptno;

这个查询的目的是从emp表中检索出每个部门（deptno）的平均工资和最高工资。

这条SQL语句的工作过程如下：

1.数据读取

  • 数据库系统首先读取emp表中的所有数据行。这些行包含了员工的部门编号（deptno）、薪水（sal）以及其他可能的信息。

2. 数据分组

  • 接下来，根据GROUP BY deptno子句，数据库系统会根据deptno列的值将数据行分组。具有相同deptno值的行会被归为同一组。
  • 例如，如果表中有多条记录属于部门10，那么这些记录会被归为同一组；同样，属于部门20、30等的记录也会各自被归为不同的组。

3. 聚合计算

  • 对于每个分组，数据库系统会计算两个聚合值：平均薪水（AVG(sal)）和最高薪水（MAX(sal)）。AVG(sal)：计算该分组内所有员工薪水的平均值。
  • MAX(sal)：找出该分组内员工薪水的最大值。
  • 这些聚合计算是基于分组内的数据行进行的，每个分组都会得到一个平均薪水和最高薪水的值。

4. 结果构建

  • 数据库系统会根据上述计算的结果构建最终的结果集。结果集中的每一行都代表一个分组，并包含以下信息：deptno：分组的唯一标识符，即部门编号。
  • AVG(sal)：该分组的平均薪水。
  • MAX(sal)：该分组的最高薪水。

5. 结果返回

  • 最后，数据库系统将返回这个结果集给查询的发起者。这个结果集提供了每个部门的平均薪水和最高薪水的信息。

 很对

2.3.显示每个部门的每种岗位的平均工资和最低工资

现在对于我们来说：“求平均工资和最低工资”，是很简单的事情，但是题目的要求：显示每个部门的每种岗位，显然要求我们进行两次分组，对于group by子句来说，我们可以使用,分割，来进行多个条件分组。

select deptno, job, avg(sal), min(sal) from emp group by deptno, job;

其执行过程与之前的查询类似，但有一些关键的不同之处，因为这次我们根据两个列（deptno 和 job）来进行分组。

以下是详细的执行过程：

1. 数据读取

  • 数据库系统首先读取emp表中的所有数据行。这些行包含了员工的部门编号（deptno）、职位（job）、薪水（sal）以及其他可能的信息。

2. 数据分组

  • 接下来，根据GROUP BY deptno, job子句，数据库系统会根据deptno和job列的值组合来将数据行分组。具有相同deptno和job值组合的行会被归为同一组。（注意要两个都相同）
  • 例如，如果表中有多条记录属于部门10且职位是“MANAGER”，那么这些记录会被归为同一组；同样，属于部门20且职位是“CLERK”的记录，或者属于部门30且职位是“SALESMAN”的记录等，也会各自被归为不同的组。

3. 聚合计算

  • 对于每个分组，数据库系统会计算两个聚合值：平均薪水（AVG(sal)）和最低薪水（MIN(sal)）。AVG(sal)：计算该分组内所有员工薪水的平均值。
  • MIN(sal)：找出该分组内员工薪水的最小值。
  • 这些聚合计算是基于分组内的数据行进行的，每个分组都会得到一个平均薪水和最低薪水的值。

4. 结果构建

  • 数据库系统会根据上述计算的结果构建最终的结果集。结果集中的每一行都代表一个分组，并包含以下信息：deptno：分组的部门编号。
  • job：分组的职位。
  • AVG(sal)：该分组的平均薪水。
  • MIN(sal)：该分组的最低薪水。

5. 结果返回

  • 最后，数据库系统将返回这个结果集给查询的发起者。这个结果集提供了每个部门内不同职位的平均薪水和最低薪水的信息。

2.4.显示平均工资低于2000的部门和它的平均工资

        在这里我们会发现，我们必须先要拿到平均工资的值，然后再根据平均工资进行筛选。

        假设这里我们使用where子句，我们会发现在where子句中我们无法表示平均工资的（聚合函数不能够在where子句中使用的，因为where子句是对单个记录进行筛选，而聚合函数是对整个结果集进行计算的）

        就算假设我们能够表示平均工资，我们知道where子句的执行优先级是很高的，于是就会先按平均工资进行筛选，然后再拿到平均工资的值。显然这个逻辑是有问题的。

为了解决这个问题我们就要学习一下HAVING 条件了，having也是一个筛选条件。

含有having子句的SQL如下：

SELECT ... FROM table_name [WHERE ...] [GROUP BY ...] [HAVING ...] [order by ...] [LIMIT ...];

说明一下：

  1. SQL中大写的表示关键字，[ ]中代表的是可选项。
  2. SQL中各语句的执行顺序为：where、group by、select、having、order by、limit。
  3. having子句中可以指明一个或多个筛选条件。

having子句和where子句的区别

  • where子句放在表名后面，而having子句必须搭配group by子句使用，放在group by子句的后面。
  • where子句是对整表的数据进行筛选，having子句是对分组后的数据进行筛选。
  • where子句中不能使用聚合函数和别名，而having子句中可以使用聚合函数和别名。
  • where子句的执行优先级很高，而having的执行优先级很低。

 

于是上面的问题就被转化为了下面的问题了：

  1. 先统计每个部门的平均工资。
  2. 然后通过having子句筛选出平均工资低于2000的部门。

统计每个部门的平均工资

select deptno, avg(sal) from emp group by deptno;

通过having子句筛选出平均工资低于2000的部门

select deptno, avg(sal)  平均工资 from emp group by deptno having 平均工资<2000;

这句SQL的执行顺序如下：

  1. FROM子句：首先，从"emp"表中检索数据。
  2. GROUP BY子句：然后，根据"deptno"列将结果集分组。相同"deptno"值的行将被分为一组。
  3. SELECT子句：最后，在SELECT子句中选择"deptno"和平均工资作为结果。
  4. HAVING子句：然后，在HAVING子句中筛选出平均工资小于2000的分组。

 

以下是准确的SQL查询关键字逻辑执行顺序：

  1. WITH（公用表表达式，CTE）：首先执行，因为它定义了一个或多个临时的结果集，这些结果集可以在后续的查询中被引用。
  2. FROM：确定查询的数据来源，这包括表、视图、子查询等。
  3. JOIN：执行连接操作，将多个数据源合并成一个结果集。连接条件通常在ON子句中指定。
  4. 注意：虽然JOIN和ON在逻辑上紧密相关，但ON子句是在JOIN操作内部执行的，因此可以将其视为JOIN操作的一部分。
  5. WHERE：对结果集中的行进行过滤，只保留满足条件的行。
  6. GROUP BY：将结果集中的行分组，通常用于与聚合函数（如SUM, COUNT, AVG等）一起使用。
  7. HAVING：对分组后的结果进行过滤，与GROUP BY一起使用。
  8. SELECT：选择需要显示的列或表达式，可能包括聚合函数。
  9. DISTINCT：去除SELECT结果中的重复行。
  10. ORDER BY：对结果集进行排序。
  11. LIMIT / OFFSET：限制返回的行数，并可选地指定从哪一行开始返回。

因此，正确的逻辑执行顺序应该是：

        WITH > FROM > JOIN (with ON) > WHERE > GROUP BY > HAVING > SELECT > DISTINCT > ORDER BY > LIMIT


http://www.mrgr.cn/news/52738.html

相关文章：

  • 【ShuQiHere】 机器学习中的网格搜索（Grid Search）超参数调优
  • 手机数据恢复技巧：适用于手机的恢复应用程序
  • 面对配分函数 - 去噪得分匹配篇
  • 在FastAPI网站学python：环境变量 Python Environment Variables
  • React 列表 Keys
  • itext 转换word文档转pdf
  • 231321321
  • Spring Boot 中的 @RequestMapping 和 Spring 中的 @RequestMapping 有什么区别？
  • Python | Leetcode Python题解之第492题构造矩形
  • AI学习指南深度学习篇-预训练模型的数学原理
  • TCP/IP 寻址
  • 【ShuQiHere】Logic Programming：探索逻辑编程的奇妙世界
  • 在FastAPI网站学python：虚拟环境创建和使用
  • Python列表专题：插入元素性能分析
  • Pytest库应用详解
  • 数据库优化策略
  • 可编辑73页PPT | 企业智慧能源管控平台建设方案
  • 发布专利的基本流程：
  • 使用 Python 的 mock 库进行依赖注入
  • 设计一个支持自动化测试执行的测试框架