【跟着例子学MySQL】生成统计报告 --分组聚合

文章目录

前言
生成报告
DISTINCT 关键字
GROUP BY 子句
GROUP BY 聚合函数
HAVING 子句
WITH ROLLUP 子句
未完待续

前言

在这里插入图片描述
举例子，是最简单有效的学习方法。本系列文章以一个贯穿始终的场景，结合多个实例讲解MySQL的基本用法。

❔ 为什么要写这个系列？

模仿是最好的老师，实践是检验成果的方法。
本系列以实操样例和应用场景为核心，将MySQL基本用法贯穿其中，达到学以致用的效果。

❔ 为什么要学习MySQL？

MySQL是最常用的数据库之一，具有简单易用的特点，适合初学者学习
数据库的基本用法有相通之处，学会MySQL可以为学习其他数据库打下良好基础

❔ 跟别的入门教材有什么不同？

以一个贯穿始终的应用场景为主线，渐进地讲解用法
难度适中，既有基础方法，也有值得注意的关键细节

❔ 本系列文章不包含哪些内容？

该系列文章的目标是，将最核心的知识，以最直接的方式，呈现给初学者。因此不包含：

MySQL安装方法
MySQL系统管理方法，例如备份、恢复、导入导出等
高级主题，例如数据库监控、数据库调优和SQL优化

生成报告

上一篇👉《【跟着例子学MySQL】窥探数据本质 – Select语句基础》介绍了SELECT语句基本用法，这篇文章接着介绍如何利用SELECT语句生成统计报告。要生成统计报告，我们通常需要对命中的行做聚合操作。

DISTINCT 关键字

一列可能有重复的值，我们可以使用关键字DISTINCT只选择不同的值。我们还可以对几个列使用DISTINCT来选择这些列的不同组合。例如：

-- 不用DISTINCT
mysql> SELECT price FROM products;
+-------+
| price |
+-------+
| 1.23 |
| 1.25 |
| 1.25 |
| 0.48 |
| 0.49 |
+-------+
-- price列使用DISTINCT
mysql> SELECT DISTINCT price AS `Distinct Price` FROM products;
+----------------+
| Distinct Price |
+----------------+
| 1.23 |
| 1.25 |
| 0.48 |
| 0.49 |
+----------------+
-- price和name一起使用DISTINCT 
mysql> SELECT DISTINCT price, name FROM products;
+-------+-----------+
| price | name |
+-------+-----------+
| 1.23 | Pen Red |
| 1.25 | Pen Blue |
| 1.25 | Pen Black |
| 0.48 | Pencil 2B |
| 0.49 | Pencil 2H |
+-------+-----------+

GROUP BY 子句

GROUP BY子句允许将具有相同值的多个记录组成一组。例如:

mysql> SELECT * FROM products ORDER BY productCode, productID;
+-----------+-------------+-----------+----------+-------+
| productID | productCode | name      | quantity | price |
+-----------+-------------+-----------+----------+-------+
| 1004      | PEC         | Pencil 2B | 10000    | 0.48  |
| 1005      | PEC         | Pencil 2H | 8000     | 0.49  |
| 1001      | PEN         | Pen Red   | 5000     | 1.23  |
| 1002      | PEN         | Pen Blue  | 8000     | 1.25  |
| 1003      | PEN         | Pen Black | 2000     | 1.25  |
+-----------+-------------+-----------+----------+-------+
mysql> SELECT * FROM products GROUP BY productCode;
-- 每一组中只显示第一个记录
+-----------+-------------+-----------+----------+-------+
| productID | productCode | name      | quantity | price |
+-----------+-------------+-----------+----------+-------+
| 1004      | PEC         | Pencil 2B | 10000    | 0.48  |
| 1001      | PEN         | Pen Red   | 5000     | 1.23  |
+-----------+-------------+-----------+----------+-------+

GROUP BY本身并没有任何意义。它与聚合函数（例如COUNT(), AVG(), SUM()）一起使用以生成分组统计摘要。

GROUP BY 聚合函数

我们可以将组通GROUP BY聚合函数应用于每个组，以生成组汇总报告。COUNT(*)函数返回命中的行数；COUNT(columnName)只统计给定列的非NULL值个数。例如:

-- COUNT(*)函数返回命中的行数
mysql> SELECT COUNT(*) AS `Count` FROM products;
-- 没有使用GROUP BY子句，返回所有行的数量
+-------+
| Count |
+-------+
| 5     |
+-------+
mysql> SELECT productCode, COUNT(*) FROM products GROUP BY productCode;
+-------------+----------+
| productCode | COUNT(*) |
+-------------+----------+
| PEC         | 2        |
| PEN         | 3        |
+-------------+----------+

-- 按COUNT排序 - 需要定义一个别名用于引用
mysql> SELECT productCode, COUNT(*) AS count
FROM products
GROUP BY productCode
ORDER BY count DESC;
+-------------+-------+
| productCode | count |
+-------------+-------+
| PEN         | 3     |
| PEC         | 2     |
+-------------+-------+

除COUNT()外，还有许多其他的GROUP BY聚合函数，如AVG()、MAX()、MIN()和SUM()。例如:

mysql> SELECT MAX(price), MIN(price), AVG(price), STD(price), SUM(quantity)
FROM products;
-- 没有GROUP BY - 所有行
+------------+------------+------------+------------+---------------+
| MAX(price) | MIN(price) | AVG(price) | STD(price) | SUM(quantity) |
+------------+------------+------------+------------+---------------+
| 1.25       | 0.48       | 0.940000   | 0.371591   | 33000         |
+------------+------------+------------+------------+---------------+
mysql> SELECT productCode, MAX(price) AS `Highest Price`, MIN(price) AS `Lowest Price`
       FROM products
       GROUP BY productCode;
+-------------+---------------+--------------+
| productCode | Highest Price | Lowest Price |
+-------------+---------------+--------------+
| PEC         | 0.49          | 0.48         |
| PEN         | 1.25          | 1.23         |
+-------------+---------------+--------------+
mysql> SELECT productCode, MAX(price), MIN(price),
              CAST(AVG(price) AS DECIMAL(7,2)) AS `Average`,
              CAST(STD(price) AS DECIMAL(7,2)) AS `Std Dev`,
              SUM(quantity)
       FROM products
       GROUP BY productCode;
-- 使用`CAST(... AS ...)`函数来格式化浮点数
+-------------+------------+------------+---------+---------+---------------+
| productCode | MAX(price) | MIN(price) | Average | Std Dev | SUM(quantity) |
+-------------+------------+------------+---------+---------+---------------+
| PEC         | 0.49       | 0.48       | 0.49    | 0.01    | 18000         |
| PEN         | 1.25       | 1.23       | 1.24    | 0.01    | 15000         |
+-------------+------------+------------+---------+---------+---------------+

HAVING 子句

HAVING 类似于WHERE，但它可以对 GROUP BY聚合函数进行操作；而WHERE只在列上操作。

mysql> SELECT productCode AS `Product Code`,
              COUNT(*) AS `Count`,
              CAST(AVG(price) AS DECIMAL(7,2)) AS `Average`
       FROM products
       GROUP BY productCode
       HAVING Count >=3;
-- 不能使用 WHERE count >= 3
+--------------+-------+---------+
| Product Code | Count | Average |
+--------------+-------+---------+
| PEN          | 3     | 1.24    |
+--------------+-------+---------+

WITH ROLLUP 子句

WITH ROLLUP子句显示了组统计信息的统计信息，例如，

mysql> SELECT productCode,
              MAX(price),
              MIN(price),
              CAST(AVG(price) AS DECIMAL(7,2)) AS `Average`,
              SUM(quantity)
       FROM products
       GROUP BY productCode
       WITH ROLLUP; -- 将聚合函数应用于所有组
+-------------+------------+------------+---------+---------------+
| productCode | MAX(price) | MIN(price) | Average | SUM(quantity) |
+-------------+------------+------------+---------+---------------+
| PEC         | 0.49       | 0.48       | 0.49    | 18000         |
| PEN         | 1.25       | 1.23       | 1.24    | 15000         |
| NULL        | 1.25       | 0.48       | 0.94    | 33000         |
+-------------+------------+------------+---------+---------------+