如何在SQL中使用数学表达式和聚合函数

介绍

结构化查询语言 (SQL) 用于在关系数据库管理系统 (RDBMS) 中存储、管理和组织信息。 SQL还可以通过表达式进行计算和操作数据。表达式结合各种 SQL 运算符、函数和值来计算一个值。 数学表达式常用于数值的加减乘除。此外， 聚合函数 用于评估和分组值以生成摘要，例如给定列中值的平均值或总和。数学和聚合表达式可以通过数据分析提供有价值的见解，为未来的决策提供信息。

在本教程中，您将练习使用数学表达式。首先，您将在计算器上使用数值运算，然后使用这些运算符对样本数据执行聚合函数查询，最后以业务场景查询样本数据以获取更复杂的信息和分析。

先决条件

要完成本教程，您需要：

运行 Ubuntu 20.04 的服务器，非 root 用户具有 sudo 管理权限并启用了防火墙。按照我们的使用 Ubuntu 20.04 的初始服务器设置开始。
在服务器上安装并保护 MySQL。按照我们的如何在 Ubuntu 20.04 上安装 MySQL 指南进行设置。本指南假定您还设置了非 root MySQL 用户，如本指南的 Step 3 中所述。

注意： 请注意，许多 RDBMS 使用自己独特的 SQL 实现。尽管本教程中概述的命令适用于大多数 RDBMS，但如果您在 MySQL 以外的系统上测试它们，确切的语法或输出可能会有所不同。

要练习本教程中的许多数学表达式示例，您需要一个加载了示例数据的数据库和表。如果您没有准备好插入，您可以阅读以下 连接到 MySQL 并设置示例数据库 部分，以了解如何创建数据库和表。本教程将自始至终引用此示例数据库和表。

您还可以使用嵌入在此页面上的交互式终端来试验本教程中的示例查询。单击下面的Launch an Interactive Terminal!按钮开始使用。

启动交互式终端！

连接到 MySQL 并设置示例数据库

如果您的 SQL 数据库在远程服务器上运行，请从本地计算机通过 SSH 连接到您的服务器：

ssh sammy@your_server_ip

接下来，打开 MySQL 提示符，将 sammy 替换为您的 MySQL 用户帐户信息。如果您在此页面上使用嵌入式交互式终端，请注意提示时使用的密码是单词 secret：

mysql -u sammy -p

创建一个名为 mathDB 的数据库：

CREATE DATABASE mathDB;

如果数据库创建成功，您将收到以下输出：

OutputQuery OK, 1 row affected (0.01 sec)

要选择 mathDB 数据库，请运行以下 USE 语句：

USE mathDB;

OutputDatabase changed

选择数据库后，使用 CREATE TABLE 命令在其中创建一个表。对于本教程的示例，我们将创建一个名为 product_information 的表来存储一家小型茶店的库存和销售信息。该表将包含以下八列：

product_id：表示int数据类型的值，将作为表的主键。这意味着该列中的每个值都将作为其各自行的唯一标识符。
product_name：详细说明使用varchar数据类型的产品名称，最多30个字符。
product_type：存储产品的类型，如varchar数据类型，最多30个字符。
total_inventory：表示每个产品的存储单元数，使用int数据类型，最大为200。
product_cost：显示使用decimal数据类型以成本购买的每件产品的价格，左边最多3个值，小数点后2个值。
product_retail：存储零售销售的每种产品的价格，如decimal数据类型所示，左侧最多3个值，小数点后2个值。
store_units：使用 int 数据类型的值，显示特定产品有多少单位可用于店内销售库存。
online_units：表示使用int数据类型的值的特定产品有多少单位可用于在线销售库存

通过运行以下命令创建此示例表：

CREATE TABLE product_information (
product_id int, 
product_name varchar(30), 
product_type varchar(30), 
total_inventory int(200),
product_cost decimal(3, 2), 
product_retail decimal(3, 2), 
store_units int(100),
online_units int(100),
PRIMARY KEY (product_id)
);

OutputQuery OK, 0 rows affected, 0 warnings (0.01 sec)

现在将一些示例数据插入到空表中：

INSERT INTO product_information
(product_id, product_name, product_type, total_inventory, product_cost, product_retail, store_units, online_units)
VALUES
(1, 'chamomile', 'tea', 200, 5.12, 7.50, 38, 52),
(2, 'chai', 'tea', 100, 7.40, 9.00, 17, 27),
(3, 'lavender', 'tea', 200, 5.12, 7.50, 50, 112),
(4, 'english_breakfast', 'tea', 150, 5.12, 7.50, 22, 74),
(5, 'jasmine', 'tea', 150, 6.17, 7.50, 33, 92),
(6, 'matcha', 'tea', 100, 6.17, 7.50, 12, 41),
(7, 'oolong', 'tea', 75, 7.40, 9.00, 10, 29),
(8, 'tea sampler', 'tea', 50, 6.00, 8.50, 18, 25),
(9, 'ceramic teapot', 'tea item', 30, 7.00, 9.75, 8, 15),
(10, 'golden teaspoon', 'tea item', 100, 2.00, 5.00, 18, 67);

OutputQuery OK, 10 rows affected (0.01 sec)
Records: 10  Duplicates: 0  Warnings: 0

插入数据后，您就可以开始使用数学表达式了。

用数学表达式计算

在 SQL 中，您通常使用 SELECT 来查询数据库并检索所需的结果集。但是，您也可以使用 SELECT 关键字来执行各种数学运算。

请记住，在实际场景中，SQL 主要用于根据实际数据库中的值进行查询和计算。但在本节中，您将仅将 SELECT 用于数值，以熟悉数学表达式和运算符的语法。

在开始之前，这里概述了可用于在 SQL 中执行六种算术运算的运算符。请注意，此列表并不全面，并且许多 RDBMS 具有一组独特的数学运算符：

加法使用 + 符号
减法使用 - 符号
乘法使用 * 符号
除法使用 / 符号
模运算使用 % 符号
求幂使用 POW(x,y)

您可以练习使用自己的值组合运行不同类型的计算。我们将使用以下示例进行演示，从加法方程开始：

SELECT 893 + 579;

Output+-----------+
| 893 + 579 |
+-----------+
|      1472 |
+-----------+
1 row in set (0.00 sec)

请注意，由于您没有从数据库中检索任何数据，而只是在计算原始数字，因此您不需要在本节的此查询或其他示例查询中包含 FROM 子句。

现在使用减法运算符执行计算。另外，请注意，您可以计算带有小数的值，如下所示：

SELECT 437.82 - 66.34;

Output+----------------+
| 437.82 - 66.34 |
+----------------+
|         371.48 |
+----------------+
1 row in set (0.00 sec)

您可以在 SQL 的单个计算中包含多个值和运算符。以下示例计算使用三个乘法运算符来查找四个数字的乘积：

SELECT 60 * 1234 * 2 * 117;

Output+---------------------+
| 60 * 1234 * 2 * 117 |
+---------------------+
|            17325360 |
+---------------------+
1 row in set (0.00 sec)

接下来，计算一个结合了十进制值和整数值的除法问题，例如：

SELECT 2604.56 / 41;

Output+--------------+
| 2604.56 / 41 |
+--------------+
|    63.525854 |
+--------------+
1 row in set (0.00 sec)

另一个除法运算符是 %，或模运算符，它计算被除数除以除数后的剩余值：

SELECT 38 % 5;

Output+--------+
| 38 % 5 |
+--------+
|      3 |
+--------+
1 row in set (0.00 sec)

另一个可能有用的运算符是 POW(x,y)，它计算指定基值 (x) 的指数 (y) 的幂：

SELECT POW(99,9);

Output+---------------------+
| POW(99,9)         |
+---------------------+
| 9.13517247483641e17 |
+---------------------+
1 row in set (0.01 sec)

既然您已经单独练习了每个运算的计算，您可以尝试结合不同的数学运算符来练习更复杂的数学方程。

了解 SQL 中的操作顺序

您可能熟悉术语 PEMDAS，它代表括号、指数、乘法、除法、加法和减法。该术语用作求解更复杂方程所需的操作顺序的指导。 PEMDAS 是在美国使用的术语，而其他国家/地区可能使用不同的首字母缩略词来表示其操作顺序规则。

当涉及到组合嵌套在括号中的不同数学运算时，SQL 从左到右读取它们，然后从内到外读取值。出于这个原因，请确保括号内的值准确地反映了您要解决的问题。

尝试使用括号和几个不同的运算符进行计算：

SELECT (2 + 4 ) * 8;

Output+-----------+
| (2+4) * 8 |
+-----------+
|        48 |
+-----------+
1 row in set (0.00 sec)

请记住，括号的位置很重要，如果您不小心，整个结果可能会发生变化。例如，以下使用相同的三个值和运算符，但括号位置不同，这会产生不同的结果：

SELECT 2 + (4  * 8);

Output+-------------+
| 2 + (4 * 8) |
+-------------+
|          34 |
+-------------+
1 row in set (0.00 sec)

如果您更喜欢不带括号执行计算，您也可以这样做。请记住，仍然存在操作规则的顺序；因此，类似于括号放置，根据将要评估的操作顺序验证这是您想要的等式。在以下示例中，您会注意到除法运算优先于减法运算符并产生负值：

SELECT 100 / 5 - 300;

Output+---------------+
| 100 / 5 - 300 |
+---------------+
|     -280.0000 |
+---------------+
1 row in set (0.00 sec)

您已通过组合各种运算符成功地将数学表达式用于数值计算和复杂计算。接下来，您将使用示例数据通过聚合函数进行计算并生成有关数据的新信息。

使用聚合函数分析数据

想象一下，您是一家小茶店的所有者，并且您想要执行与您存储在数据库中的信息相关的计算。 SQL 可以使用数学表达式通过从数据库表和不同列中检索数据来查询和操作数据。这有助于生成有关您有兴趣分析的数据的新信息。在本节中，您将练习使用聚合函数查询和操作样本数据，以查找有关茶店业务的信息。

SQL 中的主要聚合函数包括 SUM、MAX、MIN、AVG 和 COUNT。 SUM 函数将列中的所有值相加。例如，使用 SUM 将样本数据集中 total_inventory 列的数量相加：

SELECT SUM(total_inventory) FROM product_information;

Output+----------------------+
| SUM(total_inventory) |
+----------------------+
|                 1155 |
+----------------------+
1 row in set (0.00 sec)

MAX 函数查找所选列中保存的最大值。对于本示例，使用 MAX 查询 product_cost 列中列出的产品的最大支出金额，并使用 AS 语句重新标记标题，使其读取更多清楚地：

SELECT MAX(product_cost) AS cost_max 
FROM product_information;

Output+----------+
| cost_max |
+----------+
|     7.40 |
+----------+
1 row in set (0.00 sec)

MIN 函数与 MAX 函数相反，因为它计算最小值。使用 MIN 查询 product_retail 的最低消费金额：

SELECT MIN(product_retail) AS retail_min 
FROM product_information;

Output+------------+
| retail_min |
+------------+
|       5.00 |
+------------+
1 row in set (0.00 sec)

AVG 函数计算表中指定列的所有值的平均值。另请注意，您可以在同一查询中运行多个聚合函数。尝试组合查询以查找以零售价出售的产品和以成本价购买的产品的平均成本：

SELECT AVG(product_retail) AS retail_average, 
AVG(product_cost) AS cost_average 
FROM product_information;

Output+----------------+--------------+
| retail_average | cost_average |
+----------------+--------------+
|       7.875000 |     5.750000 |
+----------------+--------------+
1 row in set (0.00 sec)

COUNT 函数的操作与其他函数不同，因为它通过计算查询返回的行数从表本身计算一个值。使用 COUNT 函数和 WHERE 语句查询零售价值超过 $8.00 的产品数量：

SELECT COUNT(product_retail) 
FROM product_information 
WHERE product_retail > 8.00;

Output+-----------------------+
| COUNT(product_retail) |
+-----------------------+
|                     4 |
+-----------------------+
1 row in set (0.00 sec)

现在查询商店购买的 product_cost 中超过 $8.00 的产品数量：

SELECT COUNT(product_cost) 
FROM product_information 
WHERE product_cost > 8.00;

Output+---------------------+
| COUNT(product_cost) |
+---------------------+
|                   0 |
+---------------------+
1 row in set (0.00 sec)

您已成功使用聚合函数来提供最大值、最小值、平均值和计数等值的摘要。您从示例数据中检索到此信息以模拟真实场景。在最后一节中，您将应用所学的有关数学表达式和聚合函数的所有知识，对小茶店的样本数据执行更详细的查询和分析。

在业务场景中应用数学表达式

本节提供了一些示例场景，这些场景涉及执行数据分析，以帮助茶店老板做出与其业务相关的决策。

作为第一种情况，计算库存中当前可用的总单位数，以了解还有多少产品可用于店内和在线销售。此查询还将包括 DESC 语句以从最大到最小数量组织数据。通常 RDBMS 将默认为升序，但此示例包含 DESC 选项，它允许您按降序查看数据：

SELECT product_name, 
total_inventory - (store_units + online_units) 
AS remaining_inventory 
FROM product_information 
ORDER BY(remaining_inventory) DESC;

Output+-------------------+---------------------+
| product_name      | remaining_inventory |
+-------------------+---------------------+
| chamomile         |                 110 |
| chai              |                  56 |
| english_breakfast |                  54 |
| matcha            |                  47 |
| lavender          |                  38 |
| oolong            |                  36 |
| jasmine           |                  25 |
| golden teaspoon   |                  15 |
| tea sampler       |                   7 |
| ceramic teapot    |                   7 |
+-------------------+---------------------+
10 rows in set (0.00 sec)

此查询很有用，因为它会计算剩余库存，这可以帮助茶店老板在产品库存不足时制定购买更多订单的计划。

在下一个场景中，您将分析和比较店内销售和在线销售的收入金额：

SELECT product_name, 
(online_units * product_retail) AS o, 
(store_units * product_retail) AS s 
FROM product_information;

Output
+-------------------+--------+--------+
| product_name      | o      | s      |
+-------------------+--------+--------+
| chamomile         | 390.00 | 285.00 |
| chai              | 243.00 | 153.00 |
| lavender          | 840.00 | 375.00 |
| english_breakfast | 555.00 | 165.00 |
| jasmine           | 690.00 | 247.50 |
| matcha            | 307.50 |  90.00 |
| oolong            | 261.00 |  90.00 |
| tea sampler       | 212.50 | 153.00 |
| ceramic teapot    | 146.25 |  78.00 |
| golden teaspoon   | 335.00 |  90.00 |
+-------------------+--------+--------+
10 rows in set (0.00 sec)

接下来，使用 SUM 函数和几个数学运算符计算店内和在线销售的总收入：

SELECT SUM(online_units * product_retail) + 
SUM(store_units * product_retail) 
AS total_sales 
FROM product_information;

Output+-------------+
| total_sales |
+-------------+
|     5706.75 |
+-------------+
1 row in set (0.00 sec)

执行这些查询很重要，原因有两个。第一个原因是，茶店老板可以评估哪些商品是畅销商品，并在未来购买更多商品时优先考虑这些商品。其次，他们可以分析茶店在店内和在线产品销售方面的整体表现。

接下来，您将找到每种产品的利润率。给定产品的 利润率 是企业从其销售的每个产品单位中获得的收入金额。要了解您获得了多少收入，您可以将销售额乘以利润率。

要计算您的单个产品的利润率，请从每行的 product_retail 中减去 product_cost。然后将该值除以产品零售额来计算利润率百分比：

SELECT product_name, 
(product_retail - product_cost) / product_retail 
AS profit_margin
FROM product_information;

Output+-------------------+-------------+
| product_name      | profit_margin |
+-------------------+-------------+
| chamomile         |    0.317333 |
| chai              |    0.177778 |
| lavender          |    0.317333 |
| english_breakfast |    0.317333 |
| jasmine           |    0.177333 |
| matcha            |    0.177333 |
| oolong            |    0.177778 |
| tea sampler       |    0.294118 |
| ceramic teapot    |    0.282051 |
| golden teaspoon   |    0.600000 |
+-------------------+-------------+
10 rows in set (0.00 sec)

根据这个输出，您将了解到利润率最高的产品是金茶匙，利润率为 60%，最低的是茶、茉莉、抹茶和乌龙茶，利润率为 18%。对于金茶匙，这意味着以 5.00 美元的零售价值和 60% 的利润率，您可以创造 3.00 美元的收入。

您还可以使用聚合函数 AVG 来计算茶店所有产品的平均利润率。该平均值可作为茶店老板的基准，然后确定哪些产品低于该数字并制定改进策略：

SELECT AVG((product_retail - product_cost) / product_retail) 
AS avg_profit_margin 
FROM product_information;

Output+-------------------+
| avg_profit_margin |
+-------------------+
|   0.2838391151 |
+-------------------+
1 row in set (0.00 sec)

从这个计算中，您可以得出结论，这家茶店产品的平均利润率为 28%。

有了这些新信息，假设茶店老板希望在下一季度将目前利润率低于 27% 的任何产品的利润率提高到 31%。为此，您将从 1 (1 - 0.31) 中减去目标利润率，然后将每个退回产品的成本除以该值。结果将是产品必须以零售价出售的新价格才能实现 31% 的利润率：

SELECT product_name, product_cost / (1 - 0.31) 
AS new_retail 
FROM product_information 
WHERE (product_retail - product_cost) / product_retail < 0.27;

Output+--------------+------------+
| product_name | new_retail |
+--------------+------------+
| chai      |  10.724638 |
| jasmine   |   8.942029 |
| matcha    |   8.942029 |
| oolong    |  10.724638 |
+--------------+------------+
4 rows in set (0.00 sec)

这些结果显示了表现不佳的产品要达到 31% 的利润率所需的新零售价格。诸如此类的数据分析使茶店老板能够就如何提高下一季度的收入做出决定性的业务决策，并了解目标。

结论

在 SQL 中使用数学表达式的范围从像在计算器上解决算术问题到对可能影响业务决策的真实数据执行复杂分析。如果您能够识别主要的数学运算符和运算顺序的规则，那么计算的可能性是无穷无尽的。当您想进一步分析数据时，将这些运算符与聚合函数结合使用可以计算出您的“假设”问题的答案，这可能有助于战略规划。通过我们关于如何使用 SQL 的系列，了解更多关于您可以在 SQL 中使用数据库的信息。