轻松上手MYSQL：掌握MYSQL聚合函数，数据分析不再难

avatar
作者
猴君
阅读量:26

​🌈 个人主页：danci_
🔥 系列专栏：《设计模式》《MYSQL》
💪🏻 制定明确可量化的目标，坚持默默的做事。

✨欢迎加入探索MYSQL聚合函数之旅✨
    👋 大家好！文本学习和探索MYSQL聚合函数。👋 效率与精准是制胜的关键。MySQL聚合函数，作为数据库操作中的强大工具，能帮你快速进行数据统计、分析和汇总。本篇文章将带你轻松上手MySQL聚合函数，让复杂的数据处理变得简单高效。准备好提升你的数据库技能了吗？让我们一同揭开MySQL聚合函数的神秘面纱，让你的数据飞起来！

目录

一、AVG 求平均值

二、COUNT 统计数量(非null值)

三、GROUP_CONCAT 分组列连接成字符串

四、MAX 取最大值

五、MIN 取最小值

六、SUM 求和(非null值)

七、BIT_AND 按位与运算(非null值)

八、BIT_OR 按位或运算(非null值)

九、BIT_XOR 按位异或运算(非null值)

十、JSON_ARRAYAGG 聚合为一个 JSON 数组

十一、JSON_OBJECTAGG 聚合为一个 JSON 对象

十二、STD 求总体标准差(非null)

十三、STDDEV 求总体标准差(非null)

十四、STDDEV_POP  求总体标准差(非null)

十五、STDDEV_SAMP 计算样本标准差(非null)

十六、VAR_POP 计算总体方差(非null)

十七、VARIANCE 计算总体方差(非null)

十八、VAR_SAMP 计算样本方差(非null)

一、AVG 求平均值

MySQL AVG() 函数计算并返回表达式的平均值。

忽略 NULL 值。

语法：

AVG(expr) AVG(DISTINCT expr)  SELECT AVG(expr), ... FROM table_name [WHERE ...];  SELECT AVG(expr), group_expr1, group_expr2, ... FROM table_name [WHERE ...] GROUP BY group_expr1, group_expr2, ...;

参数说明

  • expr：expr 一个用于聚合运算的表达式。它可以是一个列名，也可以是一个表达式

  • group_expr1, group_expr2, ...：用于分组的表达式或者列名。

返回值

  • 表达式的平均值。
  • 返回 NULL情况：没有匹配的行。

示例：

CREATE TABLE `student_score` (     `score` INT NOT NULL ); INSERT INTO `student_score` (`score`) VALUES (80),     (90),     (84),     (96),     (80),     ( 98),     ( 75);  SELECT AVG(score) from student_score; // 86.1429

 

二、COUNT 统计数量(非null值)

MySQL COUNT() 函数用于统计表达式代表的所有值的中的非 NULL 值的数量。

语法：

COUNT(expr)  SELECT COUNT(expr), ... FROM table_name [WHERE ...];  SELECT COUNT(expr), group_expr1, group_expr2, ... FROM table_name [WHERE ...] GROUP BY group_expr1, group_expr2, ...;

参数说明

  • expr：expr 一个需要统计数量的表达式。它可以是一个列名，也可以是一个表达式。

  • group_expr1, group_expr2, ...：用于分组的表达式或者列名。

返回值

  • 所有行中的表达式代表的所有值的中的非 NULL 值的数量。
  • 注意： 如果使用 COUNT(*) 或者类似于 COUNT(1) 之类的语句，则会统计 NULL 值。

示例：

CREATE TABLE `student_score` (     `score` INT ); INSERT INTO `student_score` (`score`) VALUES (80),     (90),     (84),     (96),     (80),     ( 98),     ( 75),     ( NULL);  SELECT  	COUNT(score),        // 7 	COUNT(*),            // 8 	COUNT(1)             // 8 	from student_score;

 

三、GROUP_CONCAT 分组列连接成字符串

MySQL GROUP_CONCAT() 函数将一个分组中指定的列或表达式的值连接成一个字符串并返回。

语法：

GROUP_CONCAT(     [DISTINCT] expr [, expr2 ...]     [ORDER BY ...]     [SEPARATOR separator] )  SELECT GROUP_CONCAT(expr), ... FROM table_name [WHERE ...] [GROUP BY group_expr1, group_expr2, ...];

参数说明

  • expr [, expr2 ...]：必须的。它指定了要连接的一个或者多个列或表达式。

  • ORDER BY：可选的。它用于对要连接的内容排序。

  • SEPARATOR separator：可选的。separator 连接符。默认是 ,。

返回值

  • 将通过列或者表达式指定的内容连接起来。
  • 返回 NULL情况：结果集没有任何行。

示例：

DROP TABLE IF EXISTS `student_score`; CREATE TABLE `student_score` (     `id` INT PRIMARY KEY AUTO_INCREMENT,     `name` VARCHAR(255) NOT NULL,     `score` INT NOT NULL );  SELECT     name AS `Name`,     GROUP_CONCAT(subject) AS `Subjects` FROM student_score GROUP BY name;  +------+--------------+ | Name | Subjects     | +------+--------------+ | Tim  | English      | | Tom  | Math,English | +------+--------------+  ----------------------------------------------------------  SELECT     name AS `Name`,     GROUP_CONCAT(subject SEPARATOR '/') AS `Subjects` FROM student_score GROUP BY name;  +------+--------------+ | Name | Subjects     | +------+--------------+ | Tim  | English      | | Tom  | Math/English | +------+--------------+  ----------------------------------------------------------  SELECT     name AS `Name`,     GROUP_CONCAT(         CONCAT(subject, '-'), score         ORDER BY subject         SEPARATOR '/'     ) AS `Scores` FROM student_score GROUP BY name;  或 SELECT     name AS `Name`,     GROUP_CONCAT(         CONCAT_WS('-', subject, score)         ORDER BY subject         SEPARATOR '/'     ) AS `Scores` FROM student_score GROUP BY name;  +------+--------------------+ | Name | Scores             | +------+--------------------+ | Tim  | English-98         | | Tom  | English-90/Math-80 | +------+--------------------+

 

四、MAX 取最大值

MySQL MAX() 函数返回表达式代表的所有值中的最大值。如果您需要获取一个表达式中的最小值，请使用 MIN() 函数。

语法：

MAX(expr)  SELECT MAX(expr), ... FROM table_name [WHERE ...];  SELECT MAX(expr), group_expr1, group_expr2, ... FROM table_name [WHERE ...] GROUP BY group_expr1, group_expr2, ...;

参数说明

  • expr：expr 一个用于聚合运算的表达式。它可以是一个列名，也可以是一个表达式

  • group_expr1, group_expr2, ...：用于分组的表达式或者列名。

返回值

  • 表达式代表的所有值中的最大值。
  • 返回 NULL情况：没有匹配的行。

示例：

SELECT MAX(score) from student_score;    // 98   SELECT name, MAX(score) FROM student_score GROUP BY name; +------+------------+ | name | MAX(score) | +------+------------+ | Tom  |         90 | | Tim  |         98 | +------+------------+

 

五、MIN 取最小值

MySQL MIN() 函数返回表达式代表的所有值中的最小值。如果您需要获取一个表达式中的最大值，请使用 MAX() 函数。

语法：

MIN(expr)  SELECT MIN(expr), ... FROM table_name [WHERE ...];  SELECT MIN(expr), group_expr1, group_expr2, ... FROM table_name [WHERE ...] GROUP BY group_expr1, group_expr2, ...;

参数说明

  • expr：expr 一个用于聚合运算的表达式。它可以是一个列名，也可以是一个表达式。

  • group_expr1, group_expr2, ...：用于分组的表达式或者列名。

返回值

  • 表达式代表的所有值中的最小值。
  • 返回 NULL情况：没有匹配的行。

示例：

mysql> SELECT MIN(score) from student_score;  +------------+ | MIN(score) | +------------+ |         80 | +------------+  mysql> SELECT subject, MIN(score) FROM student_score GROUP BY subject; +---------+------------+ | subject | MIN(score) | +---------+------------+ | Math    |         80 | | English |         90 | +---------+------------+

 

六、SUM 求和(非null值)

MySQL SUM() 函数计算所有指定的非 NULL 值的总和并返回。

语法：

SUM(expr)  SELECT SUM(expr), ... FROM table_name [WHERE ...];  SELECT SUM(expr), group_expr1, group_expr2, ... FROM table_name [WHERE ...] GROUP BY group_expr1, group_expr2, ...;

参数说明

  • expr：expr 一个需要统计数量的表达式。它可以是一个列名，也可以是一个表达式。

  • group_expr1, group_expr2, ...：用于分组的表达式或者列名。

返回值

  • 所有指定的非 NULL 值的总和。
  • 返回 NULL情况：没有匹配的行。 

示例：

mysql> SELECT SUM(score) from student_score; +------------+ | SUM(score) | +------------+ | 268        | +------------+  mysql> SELECT name, SUM(score)FROM student_score GROUP BY name; +------+------------+ | name | SUM(score) | +------+------------+ | Tom  | 170        | | Tim  | 98         | +------+------------+

 

七、BIT_AND 按位与运算(非null值)

MySQL BIT_AND() 函数是一个聚合函数，它对所有的非 null 输入值执行"按位与"运算。（只处理那些非 null 的值）
按位与处理两个长度相同的二进制数，两个相应的二进位都为 1，该位的结果值才为 1，否则为 0。

语法：

BIT_AND(expr)  SELECT BIT_AND(expr), ... FROM table_name [WHERE ...];  SELECT BIT_AND(expr), group_expr1, group_expr2, ... FROM table_name [WHERE ...] GROUP BY group_expr1, group_expr2, ...;

参数说明

  • expr：必需的。一个列名或者表达式。它接受一个数值或者二进制值。

返回值

  • 返回值的类型与输入参数的类型相同，它返回 对所有的非 null 输入值执行"按位与"运算的结果。
  • 返回 NULL情况：所有的输入的值为 null。

示例：

mysql> SELECT BIT_AND(x) FROM (SELECT 4 x UNION SELECT 5 x UNION SELECT 6 x ) t; +------------+ | BIT_AND(x) | +------------+ |          4 | +------------+

 “按位与” 运算，运算步骤如下:

4 -> 100 5 -> 101 6 -> 110 BIT_AND() = 100 = 4

 

八、BIT_OR 按位或运算(非null值)

MySQL BIT_OR() 函数是一个聚合函数，它对所有的非 null 输入值执行"按位或"运算。（只处理那些非 null 的值）
按位或处理两个长度相同的二进制数，两个相应的二进位都为 0，该位的结果值为 0，否则为 1。

语法：

BIT_OR(expr)  SELECT BIT_OR(expr), ... FROM table_name [WHERE ...];  SELECT BIT_OR(expr), group_expr1, group_expr2, ... FROM table_name [WHERE ...] GROUP BY group_expr1, group_expr2, ...;

参数说明

  • expr：必需的。一个列名或者表达式。它接受一个整数或者 bit 类型的值。

返回值

  • 返回值的类型与输入参数的类型相同，它返回 对所有的非 null 输入值执行"按位或"运算的结果。
  • 返回 NULL情况：所有的输入的值为 null。

示例：

mysql> SELECT BIT_OR(x) FROM (SELECT 4 x UNION SELECT 5 x UNION SELECT 6 x ) t; +-----------+ | BIT_OR(x) | +-----------+ |         7 | +-----------+

“按位与” 运算，运算步骤如下:

4 -> 100 5 -> 101 6 -> 110 BIT_OR() = 111 = 7

 

九、BIT_XOR 按位异或运算(非null值)

MySQL BIT_XOR() 函数是一个聚合函数，它对所有的非 null 输入值执行"按位异或"运算。（只处理那些非 null 的值）
按位异或处理两个长度相同的二进制数，两个相应的二进位只要不同，该位的结果值为 1，否则为 0。

语法：

BIT_XOR(expr)  SELECT BIT_XOR(expr), ... FROM table_name [WHERE ...];  SELECT BIT_XOR(expr), group_expr1, group_expr2, ... FROM table_name [WHERE ...] GROUP BY group_expr1, group_expr2, ...;

参数说明

  • expr：必需的。一个列名或者表达式。它接受一个整数或者 bit 类型的值。

返回值

  • 返回值的类型与输入参数的类型相同，它返回 对所有的非 null 输入值执行"按位异或"运算的结果。
  • 返回 NULL情况：所有的输入的值为 null。

示例：

mysql> SELECT BIT_XOR(x) FROM ( SELECT 4 x UNION SELECT 5 x UNION SELECT 6 x ) t; +------------+ | BIT_XOR(x) | +------------+ |          7 | +------------+

“按位异或” 运算，运算步骤如下:

4 -> 100 5 -> 101 6 -> 110 BIT_XOR() = 111 = 7

 

十、JSON_ARRAYAGG 聚合为一个 JSON 数组

MySQL JSON_ARRAYAGG() 函数将指定的列或者表达式的值聚合为一个 JSON 数组。

语法：

JSON_ARRAYAGG(expr)  SELECT JSON_ARRAYAGG(expr), ... FROM table_name [WHERE ...] [GROUP BY group_expr1, group_expr2, ...];

参数说明

  • expr：必须的。它可以是一个列名，也可以是一个表达式。

返回值

  • 聚合了所有符合条件的值。
  • 返回 NULL情况：结果集没有任何行。

示例：

mysql> select * from student_score; +----+------+---------+-------+ | id | name | subject | score | +----+------+---------+-------+ |  1 | Tom  | Math    |    80 | |  2 | Tom  | English |    90 | |  3 | Tim  | English |    98 | +----+------+---------+-------+ 3 rows in set (0.00 sec)  mysql> SELECT name AS `Name`, JSON_ARRAYAGG(subject) AS `Subjects` FROM student_score GROUP BY name; +------+---------------------+ | Name | Subjects            | +------+---------------------+ | Tim  | ["English"]         | | Tom  | ["Math", "English"] | +------+---------------------+

 

十一、JSON_OBJECTAGG 聚合为一个 JSON 对象

MySQL JSON_OBJECTAGG() 函数将由第一个参数作为键和第二个参数作为值的键值对聚合为一个 JSON 对象。

语法：

JSON_OBJECTAGG(key_expr, value_expr)  SELECT JSON_OBJECTAGG(key_expr, value_expr), ... FROM table_name [WHERE ...] [GROUP BY group_expr1, group_expr2, ...];

参数说明

  • key_expr：必须的。它的值作为结果对象中的键值对中的键。它可以是一个列名，也可以是一个表达式。

  • value_expr：可选的。它的值作为结果对象中的键值对中的值。它可以是一个列名，也可以是一个表达式。

返回值

  • 一个 JSON 对象，其中的键值对中的键是 key_expr 的值，值是 value_expr 的值。
  • 返回 NULL情况：结果集没有任何行。
  • 注意：如果存在重复的键，则只保留最后一个键作为键值对，其他重复的键值对都被丢弃。

示例：

mysql> select * from student_score; +----+------+---------+-------+ | id | name | subject | score | +----+------+---------+-------+ |  1 | Tom  | Math    |    80 | |  2 | Tom  | English |    90 | |  3 | Tim  | English |    98 | +----+------+---------+-------+ 3 rows in set (0.00 sec)   mysql> SELECT name AS `Name`, JSON_OBJECTAGG(subject, score) AS `Scores`FROM student_score GROUP BY name; +------+-----------------------------+ | Name | Scores                      | +------+-----------------------------+ | Tim  | {"English": 98}             | | Tom  | {"Math": 80, "English": 90} | +------+-----------------------------+

 

十二、STD 求总体标准差(非null)

MySQL STD() 函数计算所有非 null 输入值的总体标准差并返回结果。 它是 STDDEV_POP() 的别名。

只处理那些非 null 的值，忽略null值。

语法：

STD(expr)

参数说明

  • expr：必需的。一个列名或者表达式。它接受一个数值或者二进制值。

返回值

  • 总体标准差。
  • 返回 NULL情况：所有的输入的值为 null。

示例：

mysql> SELECT STD(x) FROM ( SELECT 4 x UNION SELECT 5 x UNION SELECT 6 x) t; +-------------------+ | STD(x)            | +-------------------+ | 0.816496580927726 | +-------------------+

 

十三、STDDEV 求总体标准差(非null)

MySQL STDDEV() 函数计算所有非 null 输入值的总体标准差并返回结果。 它是 STDDEV_POP() 的别名。

只处理那些非 null 的值，null 值会被忽略。

语法：

STDDEV(expr)  SELECT AVG(expr), ... FROM table_name [WHERE ...];  SELECT AVG(expr), group_expr1, group_expr2, ... FROM table_name [WHERE ...] GROUP BY group_expr1, group_expr2, ...;

参数说明

  • expr：必需的。一个列名或者表达式。它接受一个数值或者二进制值。

返回值

  • 所有非 null 输入值的总体标准差。
  • 返回 NULL情况：所有的输入的值为 null。

示例：

mysql> SELECT STDDEV(x) FROM ( SELECT 4 x UNION SELECT 5 x UNION SELECT 6 x) t; +-------------------+ | STDDEV(x)         | +-------------------+ | 0.816496580927726 | +-------------------+

 

十四、STDDEV_POP  求总体标准差(非null)

MySQL STDDEV_POP() 函数计算所有非 null 输入值的总体标准差并返回结果。

只处理那些非 null 的值，null 值会被函数忽略。

语法：

STDDEV_POP(expr)  SELECT STDDEV_POP(expr), ... FROM table_name [WHERE ...];  SELECT STDDEV_POP(expr), group_expr1, group_expr2, ... FROM table_name [WHERE ...] GROUP BY group_expr1, group_expr2, ...;

参数说明

  • expr：必需的。一个列名或者表达式。它接受一个数值或者二进制值。

返回值

  • 所有非 null 输入值的总体标准差。
  • 返回 NULL情况：所有的输入的值为 null。

示例：

mysql> SELECT STDDEV_POP(x) FROM ( SELECT 4 x UNION SELECT 5 x UNION SELECT 6 x) t; +-------------------+ | STDDEV_POP(x)     | +-------------------+ | 0.816496580927726 | +-------------------+

 

十五、STDDEV_SAMP 计算样本标准差(非null)

MySQL STDDEV_SAMP() 函数计算所有非 null 输入值的样本标准差并返回结果。

只处理那些非 null 的值，null 值会被函数忽略。

语法：

STDDEV_SAMP(expr)  SELECT STDDEV_SAMP(expr), ... FROM table_name [WHERE ...];  SELECT STDDEV_SAMP(expr), group_expr1, group_expr2, ... FROM table_name [WHERE ...] GROUP BY group_expr1, group_expr2, ...;

参数说明

  • expr：必需的。一个列名或者表达式。它接受一个数值或者二进制值。

返回值

  • 所有非 null 输入值的样本标准差。
  • 返回 NULL情况：所有的输入的值为 null。

示例：

mysql> SELECT STDDEV_SAMP(x) FROM ( SELECT 4 x UNION SELECT 5 x UNION SELECT 6 x) t; +----------------+ | STDDEV_SAMP(x) | +----------------+ |              1 | +----------------+

 

十六、VAR_POP 计算总体方差(非null)

MySQL VAR_POP() 函数计算所有非 null 输入值的总体方差（总体标准差的平方）并返回结果。

只处理那些非 null 的值，null 值会被函数忽略。

语法：

VAR_POP(expr)  SELECT VAR_POP(expr), ... FROM table_name [WHERE ...];  SELECT VAR_POP(expr), group_expr1, group_expr2, ... FROM table_name [WHERE ...] GROUP BY group_expr1, group_expr2, ...;

参数说明

  • expr：必需的。一个列名或者表达式。它接受一个数值或者二进制值。

返回值

  • 所有非 null 输入值的总体方差（总体标准差的平方）。
  • 返回 NULL情况：所有的输入的值为 null。

示例：

SELECT VAR_POP(x) FROM (     SELECT 11 x     UNION     SELECT 12 x     UNION     SELECT 13 x   ) t;   -- 输出 +--------------------+ | VAR_POP(x)         | +--------------------+ | 0.6666666666666666 | +--------------------+

 

十七、VARIANCE 计算总体方差(非null)

MySQL VAR_SAMP() 函数计算所有非 null 输入值的样本方差（样本标准差的平方）并返回结果。

只处理那些非 null 的值，null 值会被函数忽略。

语法：

VAR_SAMP(expr)  SELECT VAR_SAMP(expr), ... FROM table_name [WHERE ...];  SELECT VAR_SAMP(expr), group_expr1, group_expr2, ... FROM table_name [WHERE ...] GROUP BY group_expr1, group_expr2, ...;

参数说明

  • expr：必需的。一个列名或者表达式。它接受一个数值或者二进制值。

返回值

  • 所有非 null 输入值的样本方差（样本标准差的平方）。
  • 返回 NULL情况：所有的输入的值为 null。

示例：

mysql> SELECT VAR_SAMP(x) FROM ( SELECT 4 x UNION SELECT 5 x UNION SELECT 6 x) t; +-------------+ | VAR_SAMP(x) | +-------------+ |           1 | +-------------+

 

十八、VAR_SAMP 计算样本方差(非null)

MySQL VARIANCE() 函数计算所有非 null 输入值的总体方差（总体标准差的平方）并返回结果。 它是 VAR_POP() 的别名。

只处理那些非 null 的值，null 值会被函数忽略。

语法：

VARIANCE(expr)  SELECT VARIANCE(expr), ... FROM table_name [WHERE ...];  SELECT VARIANCE(expr), group_expr1, group_expr2, ... FROM table_name [WHERE ...] GROUP BY group_expr1, group_expr2, ...;

参数说明

  • expr：必需的。一个列名或者表达式。它接受一个数值或者二进制值。

返回值

  • 返回所有非 null 输入值的总体方差（总体标准差的平方）。

  • 返回 NULL情况：所有的输入的值为 null。

示例：

mysql> SELECT VARIANCE(x) FROM ( SELECT 4 x UNION SELECT 5 x UNION SELECT 6 x) t; +--------------------+ | VARIANCE(x)        | +--------------------+ | 0.6666666666666666 | +--------------------+

    好了，今天分享到这里。希望你喜欢这次的探索之旅！不要忘记 "点赞" 和 "关注" 哦，我们下次见！🎈

相关阅读
  1. 如何查看和设置我的Android N设备的权限？
  2. 如何在安卓手机上自定义应用图标？
  3. 如何在安卓微信中同时听语音和查看图片？
  4. 华为哪些设备将支持升级至安卓7.0？
  5. 微信安卓软件中Q10功能有哪些独特之处？
  6. 安卓用户如何挑选最佳视频播放器应用？
  7. 安卓系统中用户文件目录的确切位置是什么？
  8. 小米4用户如何安全升级到安卓8.0系统？
  9. 如何成功将魅蓝5升级至安卓原生7.0版本？
  10. 16GB存储的安卓手机是否能满足日常需求？

广告一刻

为您即时展示最新活动产品广告消息，让您随时掌握产品活动新动态！