mysql---分组与聚合函数

存储在数据库的海量数据，不仅可以根据项目需求实现数据的简单增、删、改、查操作，还可用于数据的统计分析，让每条数据变得更有价值。例如，电商网站根据用户的偏好(经常浏览/购买的商品种类)为其推荐最新最火爆的商品。在MySQL中提供分组操作的目的就是为了统计，其中为了方便统计还提供了大量的聚合函数。本节将针对MySQL中分组和聚合函数的使用进行详细讲解。

分组

在MySQL中，可以使用GROUPBY根据一个或多个字段进行分组，字段值相同的为一组。另外，对于分组的数据可以使用HAVING进行条件筛选。接下来为了便于读者理解，通过几种常用的方式对分组进行详细讲解。

1. 分组统计

在查询数据时，在WHERE条件后添加GROUPBY即可根据指定的字段进行分组，其基本语法格式如下。

SELECT [select 选项] 字段列表 FROM 数据表名
[WHERE 条件表达式] GROUP BY 字段名;

上述语法在MySQL5.7中分组后，SELECT获取的字段列表只能是GROUPBY分组的字段，或使用了聚合函数的非分组字段，若在获取非分组字段时没有使用聚合函数，MySQL会报错误提示。
为了读者更好地理解，下面通过聚合函数MAX()获取每个分类下商品的最高价格。具体SQL语句及执行结果如下。

mysql> SELECT category_id, MAX(price) FROM sh_goods GROUP BY category_id;
+-------------+------------+
| category_id | MAX(price) |
+-------------+------------+
|           3 |      15.00 |
|           6 |    1999.00 |
|           8 |      69.00 |
|           9 |     109.00 |
|          10 |    2000.00 |
|          12 |    5999.00 |
|          15 |     299.00 |
|          16 |      48.00 |
+-------------+------------+
8 rows in set (0.00 sec)

上述语句中，根据categoryid进行分组，然后获取每个category_id分组下商品的最高价格。其中，MAX()是MySQL提供的一个聚合函数，用于获取price字段的最大值。
另外，在MySQL 5.6等老版本中，分组后获取的字段列表，若非分组字段没有使用聚合函数，默认情况下只保留每组中的第一条记录，但是此操作在MySQL5.7及以上版本中已被禁止。那么，为了避免项目开发MySQL版本升级带来的问题，推荐读者在编写分组SQL语句时按照MySQL5.7版本更严格的方式进行设计。

1. 分组排序

在MySQL中，默认情况下为分组操作的字段提供了升序排序的功能，因此在分组时可以为指定的字段进行升序或降序排序，其基本语法格式如下。

SELECT [select 选项] 字段列表 FROM 数据表名
[WHERE 条件表达式] GROUP BY 字段名 [ASC | DESC];

需要注意的是，GROUPBY分组排序的实现不需要使用ORDERBY，直接在分组字段后添加ASC(升序，默认值可省略)或DESC(降序)即可。
下面根据sh_goods表中的分类id进行分组降序操作，查询并显示分组后每组的商品id以及商品的名称。具体SQL语句及执行结果如下。

mysql> SELECT category_id, GROUP_CONCAT(id), GROUP_CONCAT(name)
    -> FROM sh_goods GROUP BY category_id DESC;
+-------------+------------------+--------------------+
| category_id | GROUP_CONCAT(id) | GROUP_CONCAT(name) |
+-------------+------------------+--------------------+
|          16 | 10               |                    |
|          15 | 9                |                    |
|          12 | 4                |                    |
|          10 | 8                |                    |
|           9 | 7                |                    |
|           8 | 6                |                    |
|           6 | 5                |                    |
|           3 | 1,2,3            |                    |
+-------------+------------------+--------------------+
8 rows in set (0.00 sec)

在上述语句中，聚合函数GROUP_ _CONCAT()表示将指定字段值连接成一个字符串。例如，category_id为3的分类下有3件商品，对应的id分别为1、2和3。

1. 多分组统计

在对数据进行分组统计时，MySQL中还支持数据按照某个字段进行分组后，对已经分组的数据进行再次分组的操作，以实现多分组统计。其基本语法格式如下。

SELECT [select 选项] 字段列表 FROM 数据表名 
  [WHERE 条件表达式]
  GROUP BY 字段名1 [ASC | DESC], [, 字段名2 [ASC | DESC]]…;

在上述语法中，查询出的数据首先按照字段1进行分组排序，再将字段1相同的结果按照字段2进行分组排序，依此类推。
例如，对sh_goods表，以评分score降序分组后，再以评论数comment_count升序排序，获取的数据包括商品数量、指定分组下的商品名以及对应的评论数。具体SQL语句和执行结果如下。

mysql> SELECT score, COUNT(*), GROUP_CONCAT(name), comment_count
    -> FROM sh_goods GROUP BY score DESC, comment_count;
+-------+----------+--------------------+---------------+
| score | COUNT(*) | GROUP_CONCAT(name) | comment_count |
+-------+----------+--------------------+---------------+
|  5.00 |        2 |                    |         98000 |
|  4.90 |        2 |                    |         40000 |
|  4.80 |        1 |                    |          6000 |
|  4.80 |        1 |                    |         98000 |
|  4.50 |        1 |                    |          1000 |
|  3.90 |        2 |                    |           500 |
|  2.50 |        1 |                    |           200 |
+-------+----------+--------------------+---------------+
7 rows in set (0.00 sec)

从以上的执行结果可知，以score降序分组后，再以comment_count对查询的内容进行升序分组。例如，score为4.8分组下第1件商品的comment_count为6000，第2件商品的comment_count为98000。

统计筛选

当对查询的数据执行分组操作时，可以利用HAVING根据条件进行数据筛选，它与前面学习过的WHERE功能相同，但是在实际运用时两者有一定的区别。

WHERE操作是从数据表中获取数据，将数据从磁盘存储到内存中，而HAVING是对已存放到内存中的数据进行操作。
HAVING位于GROUP BY子句后，而WHERE位于GROUP BY 子句之前。
HAVING关键字后可以使用聚合函数，而WHERE则不可以。通常情况下，HAVING关键字与GROUP
BY一起使用，对分组后的结果进行过滤。

HAVING统计筛选的基本语法格式如下。

SELECT [select 选项] 字段列表 FROM 数据表名
[WHERE 条件表达式]
GROUP BY 字段名 [ASC | DESC], …[WITH ROLLUP]
HAVING 条件表达式;

在上述的语法中， WHERE条件之后的所有语句都是对内存中的数据进行操作。而HAVING则根据条件表达式对分组后的内容进行过滤。
例如，查询sh_goods表，获取评分score和评分数comment_count不同的情况下，含有两件商品的对应商品id。具体SQL语句及执行结果如下。

mysql> SELECT score, comment_count, GROUP_CONCAT(id)
    -> FROM sh_goods
    -> GROUP BY score, comment_count
    -> HAVING COUNT(*) = 2;
+-------+---------------+------------------+
| score | comment_count | GROUP_CONCAT(id) |
+-------+---------------+------------------+
|  3.90 |           500 | 2,7              |
|  4.90 |         40000 | 1,9              |
|  5.00 |         98000 | 3,5              |
+-------+---------------+------------------+
3 rows in set (0.00 sec)

在上述SQL语句中，首先根据评分(score)进行分组，然后再根据评分数(comment_count)进行分组，分组后利用HAVING筛选商品数量等于2的数据信息。

多学一招:在查询中使用别名

在MySQL中执行查询操作时，可以根据具体情况为获取的字段设置别名。例如，通过设置别名来缩短字段的名称长度。基本语法格式如下所示。

字段设置别名

SELECT 字段1 [AS] 别名1, 字段2 [AS] 别名2 [, …] FROM 表名

在上述语法中，AS用于为其前面的字段、表达式、函数等设置别名。也可以省略AS使用空格代替。例如，为category_id设置别名cid，可以使用“category_id AS cid”或“category_id cid”。
为了读者更好地理解，下面以获取商品分类id为3或6的商品的最高价格为例，演示别名的使用。具体SQL语句及执行结果如下。

mysql> SELECT category_id cid, MAX(price) max_price FROM sh_goods
    -> GROUP BY cid HAVING cid = 3 OR cid = 6;
+-----+-----------+
| cid | max_price |
+-----+-----------+
|   3 |     15.00 |
|   6 |   1999.00 |
+-----+-----------+
1 row in set (0.00 sec)

在上述语句中，为查询的字段category.id和MAX(price)分别设置了别名cid和max_price后，在GROUP BY分组、HAVING分组筛选和查询结果中就可以使用设置的别名，方便开发与阅读。
此外，在MySQL中还可以使用AS为数据表设置别名，基本语法格式如下。

表设置别名

SELECT 表别名.字段 [, …] FROM 表名 [AS] 表别名
同样地，在为数据表设置别名时，AS也可以省略，使用空格代替。例如，为以上示例中的数据表设置别名，修改效果如下。

SELECT g.category_id cid, MAX(g.price) max_price FROM sh_goods g
GROUP BY cid HAVING cid = 3 OR cid = 6;

需要注意的是，字段与表设置别名后，在排序和分组中可以使用原来的字段名等，也可以使用别名。表的别名主要在多表查询中使用，具体会在后面章节讲解。

聚合函数

通过前面的学习可知，在对数据进行分组统计时，经常需要结合MySQL提供的聚合函数才能够统计出具有价值的数据。例如，获取每种商品分类下的商品数量、最高价格的商品、最低价格的商品等。为此，MySQL中的聚合函数就可在查询数据时提供一些特殊的功能，具体如表5-2所示。
表5-2 常用的聚合函数

函数名	描述
COUNT()	返回参数字段的数量，不统计为NULL的记录
SUM()	返回参数字段之和
AVG()	返回参数字段的平均值
MAX()	返回参数字段的最大值
MIN()	返回参数字段的最小值
GROUP_CONCAT()	返回符合条件的参数字段值的连接字符串
JSON_ARRAYAGG()	将符合条件的参数字段值做为单个JSON数组返回，MySQL5.7.22新增
JSON_OBJECTAGG()	将符合条件的参数字段值做为单个JSON对象返回，MySQL5.7.22新增

在表5-2中，COUNT()、SUM()、AVG()、MAX()、MIN()和GROUP_CONCAT()函数中可以在参数前添加DISTINCT，表示对不重复的记录进行相关操作。其中，COUNT()的参数设置为“*”时，表示统计符合条件的所有记录(包含NULL)。
下面为了读者更好地理解，演示聚合函数单独获取指定表中符合条件的记录信息，具体如下。

mysql> SELECT MAX(price), MIN(price) FROM sh_goods;
+------------+------------+
| MIN(price) | MAX(price) |
+------------+------------+
|    5999.00 |       0.50 |
+------------+------------+
1 row in set (0.00 sec)

从上述的操作可知，利用MAX()和MIN()聚合函数可以从sh_goods表所有的记录中获取商品价格price最高和最低的值。
除此之外，聚合函数还可以与分组操作一起使用，用于分析分组后的数据信息。例如，在sh_goods中，获取不同category_id下商品数大于2的最高与最低的商品价格。具体SQL语句及执行结果如下。

mysql> SELECT category_id, MAX(price), MIN(price)
    -> FROM sh_goods GROUP BY category_id HAVING COUNT(*) > 2;
+-------------+------------+------------+
| category_id | MAX(price) | MIN(price) |
+-------------+------------+------------+
|           3 |      15.00 |       0.50 |
+-------------+------------+------------+
1 row in set (0.00 sec)

在上述语句中，首先根据category_id进行分组，获取每组中商品价格price的最大值与最小值，然后选取每组中商品数量大于2的分组，即可得到以上的结果。
除此之外，在MySQL 5.7.22中还新增了两个函数JSON_ARRAYAGG()和JSON_OBJECTAGG()，用于将符合条件的结果转为JSON格式。示例如下。

mysql> SELECT category_id, JSON_ARRAYAGG(id), JSON_OBJECTAGG(id, name) 
    -> FROM sh_goods GROUP BY category_id;
+-------------+-------------------+-----------------------------------------------+
| category_id | JSON_ARRAYAGG(id) | JSON_OBJECTAGG(id, name)                  	|
+-------------+-------------------+-----------------------------------------------+
|           3 | [1, 2, 3]         | {"1": "2B铅笔", "2": "钢笔", "3": "碳素笔"}	|
|           6 | [5]               |{"5": "智能手机"}                           	|
|           8 | [6]               |{"6": "桌面音箱"}                           	|
|           9 | [7]               |{"7": "头戴耳机"}                           	|
|          10 | [8]               |{"8": "办公电脑"}                           	|
|          12 | [4]               |{"4": "超薄笔记本"}                          	|
|          15 | [9]               |{"9": "收腰风衣"}                            	|
|          16 | [10]              |{"10": "薄毛衣"}                            	|
+-------------+-------------------+-----------------------------------------------+
8 rows in set (0.00 sec)

在上述语句中，JSON_ARRAYAGG()函数的参数可以是一个字段或表达式，返回值是一个JSON数组；JSON_OBJECTAGG()函数用于返回JSON对象，第1个参数表示“键”，第2个参数表示“键”对应的值。

超全面的测试IT技术课程，0元立即加入学习！有需要的朋友戳：

腾讯课堂测试技术学习地址

作者：kellyred

出处：https://blog.youkuaiyun.com/kellyred

欢迎转载，但未经作者同意请保留此段声明，并在文章页面明显位置给出原文链接。