MySQL 聚合函数：求和、计数 AVG ＆ 最大限度
马库斯·艾伦
什么是聚合函数？ MySQL?
An 聚合函数 读取同一列的多行数据，并将它们合并成一个值。聚合函数的核心在于：
- 对多行执行计算
- 表格中的单个列
- 并返回单一值。
ISO标准定义了五（5）个聚合功能，即：
- COUNT个
- SUM
- AVG
- 闵
- 最大
五条规则都适用： 聚合函数忽略 NULL 值COUNT(*) 是唯一的例外，我们下面将探讨其原因。
为何使用聚合函数
不同组织层级的信息需求各不相同。高层管理者通常更关注整体数据，而非具体细节。
聚合函数使我们能够轻松地从数据库中生成汇总数据。
例如，管理层可能需要从我们的 myflix 数据库中获取以下报告：
- 租借次数最少的电影。
- 大多数租借的电影。
- 每部电影每月平均出租次数。
以上所有报告均来自汇总函数。让我们详细了解一下每一份报告。
计数功能
COUNT 函数返回指定字段中值的总数，包括数值型和非数值型数据类型。 与所有聚合函数一样，COUNT(column) 会排除 NULL 值。
COUNT(*) 是一种特殊形式，它返回表中所有行的总数。它还可以进行计数。 空值 并且会重复，因为它统计的是行数而不是值数。
movierentals 表包含以下数据：
| 参考编号 | 交易日期 | 归期 | 会员号码 | 电影 ID | movie_ 回归 |
|---|---|---|---|---|---|
| 11 | 20-06-2012 | 无 | 1 | 1 | 0 |
| 12 | 22-06-2012 | 25-06-2012 | 1 | 2 | 0 |
| 13 | 22-06-2012 | 25-06-2012 | 3 | 2 | 0 |
| 14 | 21-06-2012 | 24-06-2012 | 2 | 2 | 0 |
| 15 | 23-06-2012 | 无 | 3 | 3 | 0 |
假设我们想要统计 ID 为 2 的电影被租借的次数。
SELECT COUNT(`movie_id`) FROM `movierentals` WHERE `movie_id` = 2;
执行此操作 MySQL 工作台 对 myflixdb 进行查询返回 3，因为有三行电影 ID 为 2。
| COUNT(`movie_id`) |
|---|
| 3 |
不同的关键字
COUNT 回答“有多少”。下一个问题通常是“有多少 不同 而这正是 DISTINCT 的作用。
DISTINCT 关键字按组从我们的结果中删除重复项ping 相同的值放在一起，正如上面的图示所示。
首先，我们执行一个简单的查询。
SELECT `movie_id` FROM `movierentals`;
| 电影 ID |
|---|
| 1 |
| 2 |
| 2 |
| 2 |
| 3 |
现在使用 DISTINCT 关键字执行相同的查询：
SELECT DISTINCT `movie_id` FROM `movierentals`;
DISTINCT 函数会排除重复记录：
| 电影 ID |
|---|
| 1 |
| 2 |
| 3 |
COUNT、COUNT(*) 和 COUNT(DISTINCT)：你应该使用哪一个？
DISTINCT 也可以放置 内 聚合函数，而这正是大多数初学者容易出错的地方。 trac实际统计了其中的 k 行。以下四个表单都针对前面展示的同一个五行电影租赁表运行，但它们返回的结果却不尽相同。差异归根结底在于两个问题：表单统计的是行数还是值数，以及它是否保留重复项？
| 产品形态 | 它的重要性 | 电影租赁结果 |
|---|---|---|
| 数数（*） | 每一行，包括重复行和完全为 NULL 的行。 | 5 |
| COUNT(`movie_id`) | 列中所有非空值，包括重复值。 | 5 |
| COUNT(`return_date`) | 仅接受非空值——两个空返回日期将被跳过。 | 3 |
| COUNT(DISTINCT `movie_id`) | 仅包含唯一非空值 | 3 |
SELECT COUNT(*) AS `all_rows`, COUNT(`return_date`) AS `returned_rows`, COUNT(DISTINCT `movie_id`) AS `unique_movies` FROM `movierentals`;
💡提示： 使用 COUNT(*) 计算行数，使用 COUNT(column) 表示 NULL 值“不适用”，使用 COUNT(DISTINCT column) 计算唯一值。DISTINCT 的反义词是 ALL——这是默认值，因此很少使用。
MIN功能
MIN 函数 返回指定表字段中的最小值.
假设我们想知道我们片库中最老的电影是哪一年上映的。 MySQL's MIN 函数可以给我们提供这个结果。
SELECT MIN(`year_released`) FROM `movies`;
结果：
| MIN(`year_released`) |
|---|
| 2005 |
MAX功能
正如名称所示，MAX 函数与 MIN 函数相反。它 返回指定表字段的最大值.
假设我们想知道数据库中最新电影的上映年份。以下示例可以返回该年份。
SELECT MAX(`year_released`) FROM `movies`;
结果：
| MAX(`year_released`) |
|---|
| 2012 |
SUM功能
MIN 和 MAX 函数从列中选取一个现有值。SUM 和 AVG 根据整列数据计算出一个新的数字。
假设我们想知道迄今为止已支付的总金额。 MySQL SUM function 返回指定列中所有值的总和. SUM 仅适用于数字字段和 结果中将排除 NULL 值。.
下表显示了付款表中的数据。
| 付款编号 | 会员号码 | 付款日期 | 描述 | 支付的金额 | 外部参考编号 |
|---|---|---|---|---|---|
| 1 | 1 | 23-07-2012 | 电影租借付款 | 2500 | 11 |
| 2 | 1 | 25-07-2012 | 电影租借付款 | 2000 | 12 |
| 3 | 3 | 30-07-2012 | 电影租借付款 | 6000 | 无 |
下面显示的查询获取所有已支付的款项，并将它们加总成一个结果：2500 + 2000 + 6000 = 10500。
SELECT SUM(`amount_paid`) FROM `payments`;
结果：
| SUM(`amount_paid`) |
|---|
| 10500 |
AVG function
此 MySQL AVG function 返回指定列中值的平均值. 就像 SUM 函数一样，它 仅适用于数字数据类型.
假设我们想要计算平均支付金额。我们可以使用以下查询，该查询将总金额 10500 除以三个非空支付记录。
SELECT AVG(`amount_paid`) FROM `payments`;
结果：
| AVG（`amount_paid`） |
|---|
| 3500 |
⚠️警告： AVG 除以非空行数，而不是除以表的总行数。空值会被跳过而不是计为零，这会悄悄地提高平均值。 AVG（IFNULL(`amount_paid`, 0)）表示缺失值为零。
实际示例：将聚合函数与 GROUP BY 结合使用
以上每个函数都为整个表格返回一个数值。添加一个 通过...分组 该子句返回一个数字 每组 相反——而这才是真正报告的撰写方式。
以下示例按姓名对成员进行分组，然后计算每个成员的付款总数、平均付款金额和付款金额总额。
SELECT m.`full_names`, COUNT(p.`payment_id`) AS `paymentscount`, AVG(p.`amount_paid`) AS `averagepaymentamount`, SUM(p.`amount_paid`) AS `totalpayments` FROM members m, payments p WHERE m.`membership_number` = p.`membership_number` GROUP BY m.`full_names`;
在 MySQL Workbench 给出了以下结果。
该查询在 WHERE 子句中连接两个表——这是较旧的逗号连接方式。现代代码则使用显式连接来编写相同的逻辑。 内连接…开启另请注意，SELECT 列表中每个非聚合列都必须出现在 GROUP BY 子句中。 MySQL 5.7 及更高版本会拒绝 ONLY_FULL_GROUP_BY 下的查询。请参阅 官方 MySQL 聚合函数参考.
