MySQL聚合函数

假女吖☌

已于 2022-08-31 22:56:58 修改

阅读量328

点赞数

分类专栏： MySQL 文章标签： mysql 数据库

于 2022-08-30 14:06:27 首次发布

本文链接：https://blog.csdn.net/qq_43501821/article/details/126602829

版权

MySQL 专栏收录该内容

16 篇文章 0 订阅

订阅专栏

MySQL8官方文档

这些函数通常与GROUP BY子句一起使用，将值分组为子集

函数名	描述
AVG()	返回参数平均值
COUNT()	返回返回的行数
GROUP_CONCAT()	此函数返回一个字符串结果，使用分隔符把该列的所有值合成一行会忽略NULL值默认分隔符为’,’
JSON_ARRAYAGG()	将结果集作为单个 JSON 数组返回
JSON_OBJECTAGG()	将结果集作为单个 JSON 对象返回
MAX()	返回最大值
MIN()	返回最小值
SUM()	返回总和

AVG()

mysql> select avg(index_no) from ar_station;
> 1578.8678

COUNT()

mysql> select count(index_no) from ar_station;
> 3155
# 需要注意的是
mysql> select count(null);
> 0
# 所有我们有一些简单的where语句可以这样写
mysql> select COUNT(IF(index_no > 100, 1,null)) from ar_station;
> 3055
mysql> select COUNT(index_no) from ar_station  where index_no > 100;(这种效率好像比较快)
> 3055

在InnoDB中

区分下count(*)、count(1)、count(主键字段)、count(字段)

当我们看到count(*)的时候会以为会读取表中所有字段值

其实count()并不是。实际上 count() 其实等于 count(0)，也就是说，当你使用 count(*) 时，MySQL 会将 * 参数转化为参数 0 来处理。

count(1) 相比 count(主键字段) 少一个步骤，就是不需要读取记录中的字段值，所以通常会说 count(1) 执行效率会比 count(主键字段) 高一点。

 mysql> Explain select count(*) from ar_station;
 mysql> show WARNINGS;
 > Note	1003	/* select#1 */ select count(0) AS `count(*)` from `test`.`ar_station`

GROUP_CONCAT()

mysql> select GROUP_CONCAT(mc) from t_district where parentId = '6643ec9e-6ce4-48d5-bf87-363b3d010e27'
> 宜昌市,十堰市,襄阳市,孝感市,黄冈市,潜江市,天门市,武汉市,恩施土家族苗族自治州,荆门市,荆州市,仙桃市,咸宁市,神农架林区,随州市,鄂州市,黄石市

# GROUP_CONCAT 支持字段去重，还可以排序
mysql> select GROUP_CONCAT(distinct mc ORDER BY mc) from t_district where parentId = '6643ec9e-6ce4-48d5-bf87-363b3d010e27'
> 仙桃市,十堰市,咸宁市,天门市,孝感市,宜昌市,恩施土家族苗族自治州,武汉市,潜江市,神农架林区,荆州市,荆门市,襄阳市,鄂州市,随州市,黄冈市,黄石市

# 指定分隔符
mysql> select GROUP_CONCAT(distinct mc ORDER BY mc SEPARATOR '-') from t_district where parentId = '6643ec9e-6ce4-48d5-bf87-363b3d010e27'
> 仙桃市-十堰市-咸宁市-天门市-孝感市-宜昌市-恩施土家族苗族自治州-武汉市-潜江市-神农架林区-荆州市-荆门市-襄阳市-鄂州市-随州市-黄冈市-黄石市

# 如需要去除分隔符 SEPARATOR ‘’ 即可
mysql> select GROUP_CONCAT(distinct mc ORDER BY mc SEPARATOR '') from t_district where parentId = '6643ec9e-6ce4-48d5-bf87-363b3d010e27'
> 仙桃市十堰市咸宁市天门市孝感市宜昌市恩施土家族苗族自治州武汉市潜江市神农架林区荆州市荆门市襄阳市鄂州市随州市黄冈市黄石市
# 需要注意 结果被截断为系统变量给出的最大长度，group_concat_max_len 其默认值为 1024。
# 有超过部分的话会直接不显示，这个时候我们可以在运行时修改group_concat_max_len的值 
# group_concat_max_len为无符号值
set group_concat_max_len = 1024 * 1024

JSON_ARRAYAGG()

mysql> select * from t;
+------+---+
| c    | i |
+------+---+
| key  | 3 |
| key  | 4 |
| key  | 5 |
| key1 | 3 |
| key2 | 4 |
| key3 | 5 |
+------+---+
6 rows in set (0.00 sec)
 # 重复项会出现
mysql> select JSON_ARRAYAGG(c) from t;
+-----------------------------------------------+
| JSON_ARRAYAGG(c)                              |
+-----------------------------------------------+
| ["key", "key", "key", "key1", "key2", "key3"] |
+-----------------------------------------------+
1 row in set (0.00 sec)
 # 根据i分组后查看每个组的c值就那些，以json数组格式显示
mysql> select JSON_ARRAYAGG(c),i from t GROUP BY i;
+------------------+---+
| JSON_ARRAYAGG(c) | i |
+------------------+---+
| ["key", "key1"]  | 3 |
| ["key", "key2"]  | 4 |
| ["key", "key3"]  | 5 |
+------------------+---+
3 rows in set (0.01 sec)

JSON_OBJECTAGG()

mysql> select * from t;
+------+---+
| c    | i |
+------+---+
| key  | 3 |
| key  | 4 |
| key  | 5 |
| key1 | 3 |
| key2 | 4 |
| key3 | 5 |
+------+---+
6 rows in set (0.00 sec)
 
 #以c为key，i为value的JSON对象格式显示。
 #这里需要注意key没有重复出现，按照JSON规范来说就是key值只允许唯一出现。
 #哪这里重复的key是怎么取值的呢，按官方文档的说法
 # 重复密钥处理。  当此函数的结果被规范化时，具有重复键的值将被丢弃。为了与JSON不允许重复键的 MySQL 数据类型规范保持一致，只有遇到的最后一个值与返回对象中的该键一起使用（“最后一个重复键获胜”）。这意味着在 a 中的列上使用此函数的结果SELECT可能取决于返回行的顺序，这是无法保证的。
mysql> select JSON_OBJECTAGG(c, i)from t;
+---------------------------------------------+
| JSON_OBJECTAGG(c, i)                        |
+---------------------------------------------+
| {"key": 5, "key1": 3, "key2": 4, "key3": 5} |
+---------------------------------------------+
1 row in set (0.01 sec)

#根据分组后再发多条数据集合成一个JSON对象，c为key，i为value
mysql> select JSON_OBJECTAGG(c, i)from t GROUP BY i;
+-----------------------+
| JSON_OBJECTAGG(c, i)  |
+-----------------------+
| {"key": 3, "key1": 3} |
| {"key": 4, "key2": 4} |
| {"key": 5, "key3": 5} |
+-----------------------+
3 rows in set (0.00 sec)

MAX()

mysql> select * from t;
+------+---+
| c    | i |
+------+---+
| key  | 3 |
| key  | 4 |
| key  | 5 |
| key1 | 3 |
| key2 | 4 |
| key3 | 5 |
+------+---+
#返回该i列的最大值 max可以接受一个字符串参数
mysql> select max(i) from t;
+--------+
| max(i) |
+--------+
|      5 |
+--------+
1 row in set (0.00 sec)
# 也可以支持分组
mysql> select max(i),c from t group by c;
+--------+------+
| max(i) | c    |
+--------+------+
|      5 | key  |
|      3 | key1 |
|      4 | key2 |
|      5 | key3 |
+--------+------+

MIN()

mysql> select * from t;
+------+---+
| c    | i |
+------+---+
| key  | 3 |
| key  | 4 |
| key  | 5 |
| key1 | 3 |
| key2 | 4 |
| key3 | 5 |
+------+---+
#返回该i列的最大值 min可以接受一个字符串参数
mysql> select min(i) from t;
+--------+
| max(i) |
+--------+
|      3 |
+--------+
1 row in set (0.00 sec)
# 也可以支持分组
mysql> select min(i),c from t group by c;
+--------+------+
| min(i) | c    |
+--------+------+
|      3 | key  |
|      3 | key1 |
|      4 | key2 |
|      5 | key3 |
+--------+------+
4 rows in set (0.00 sec)

SUM()

# 求i列的总和
mysql> select sum(i) from t;
+--------+
| sum(i) |
+--------+
| 24     |
+--------+
1 row in set (0.00 sec)
# 支持分组
mysql> select sum(i),c from t group by c;
+--------+------+
| sum(i) | c    |
+--------+------+
| 12     | key  |
| 3      | key1 |
| 4      | key2 |
| 5      | key3 |
+--------+------+