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

介绍

Structured Query Language(SQL)用于存储、管理和组织关系数据库管理系统(RDBMS)中的信息。SQL还可以通过 expressions 执行计算和操作数据。表达式结合各种 SQL 操作员、函数和值来计算一个值。 Matematical expressions 通常用于添加、抽取、分割和倍增数值。此外, aggregate functions 用于评估和组合值来生成总结,例如在特定列中平均值或值的总和。

在本教程中,您将练习使用数学表达式. 首先,您将在计算机上使用数字操作,然后在样本数据上使用这些操作员来执行使用汇总函数的查询,并在商业场景中结束查询样本数据以获取更复杂的信息和分析。

前提条件

要完成本教程,您将需要:

运行 Ubuntu 20.04 的服务器,具有sudo管理权限和启用防火墙的非根用户。按照我们的初始服务器设置与 Ubuntu 20.04 来启动。
MySQL 安装并在服务器上安全。按照我们的如何在 Ubuntu 20.04 上安装MySQL 指南来设置此设置。本指南假定您也已设置了一个非根 MySQL 用户,如本指南的 步骤 3 所述。

<$>[注] 注: 请注意,许多RDBMS使用自己的独特的SQL实现程序,虽然本教程中描述的命令将在大多数RDBMS上工作,但如果您在MySQL以外的系统上测试它们,精确的语法或输出可能会有所不同。

要练习本教程中的许多数学表达示例,您将需要一个数据库和表载有样本数据. 如果您没有一个准备好插入,您可以阅读以下 连接到MySQL和设置样本数据库部分,以了解如何创建数据库和表。

连接到MySQL并设置样本数据库

如果您的 SQL 数据库在远程服务器上运行,则从本地计算机输入 SSH 到您的服务器:

1[environment local]
2ssh sammy@your_server_ip

接下来,打开MySQL提示,以您的MySQL用户帐户信息代替sammy:

1mysql -u sammy -p

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

1CREATE DATABASE mathDB;

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

1[secondary_label Output]
2Query OK, 1 row affected (0.01 sec)

要选择mathDB数据库,运行以下USE语句:

1USE mathDB;

1[secondary_label Output]
2Database changed

选择数据库后,使用CREATE TABLE命令创建一个表,为本教程的示例,我们将创建一个名为product_information的表,以存储小茶店的库存和销售信息。

产品-id':代表int ' 数据类型的值,并将作为表格的 [_主要关键 (https://www.digitalocean.com/community/conceptual_articles/understanding-sql-constraints#primary-key)。这意味着此列中的每个值将作为各自行的独特标识符来发挥作用.
" 产品-名称 " :详细说明使用 " varchar " 数据类型的产品名称,最多30个字符。
" 产品-类型 " :存储产品类型,以 " varchar " 数据类型为证明,最多30个字符。
" 总 -- -- 库存 " :代表每个产品存有多少个单位,使用 " int " 数据类型,最多200个。
" 产品-成本 " :使用 " 十进制 " 数据类型显示按成本购买的每一种产品的价格,其中左侧最大值为3,小数点后为2。 *`产品-零售': 如 " 十进制 " 数据类型所示,在零售时出售的每件产品的价格最多为左边3个值,小数点后2个值。
" 商店-单位 " :使用 " int " 数据类型的值,显示该特定产品有多少单位可用于商店销售库存。
" 在线_单位 " :代表网上销售库存使用 " int " 数据类型值的具体产品单位数目

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

 1CREATE TABLE product_information (
 2product_id int, 
 3product_name varchar(30), 
 4product_type varchar(30), 
 5total_inventory int(200),
 6product_cost decimal(3, 2), 
 7product_retail decimal(3, 2), 
 8store_units int(100),
 9online_units int(100),
10PRIMARY KEY (product_id)
11);

1[secondary_label Output]
2Query OK, 0 rows affected, 0 warnings (0.01 sec)

现在将一些样本数据插入空表中:

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

1[secondary_label Output]
2Query OK, 10 rows affected (0.01 sec)
3Records: 10 Duplicates: 0 Warnings: 0

一旦您输入了数据,您就可以开始使用数学表达式。

用数学表达式计算

在 SQL 中,您通常使用SELECT来查询数据库并获取所需的结果集,但您也可以使用SELECT关键字来执行各种数学操作。

请记住,在真实情况下,SQL 主要用于查询并从实际数据库中的值进行计算,但对于本节,您将仅使用SELECT用于数值,以便熟悉数学表达式和运算符的语法。

在您开始之前,以下是您可以在 SQL 中执行六个算术操作的操作的概述,请注意此列表并不全面,许多 RDBMS 具有独特的数学操作组:

添加使用+符号
抽取使用-符号
倍增使用*符号
划分使用/符号
模块操作使用%符号
曝光使用POW(x,y)

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

1SELECT 893 + 579;

1[secondary_label Output]
2+-----------+
3| 893 + 579 |
4+-----------+
5|      1472 |
6+-----------+
71 row in set (0.00 sec)

请注意,由于您没有从数据库中获取任何数据,并且只计算原始数字,因此您不需要在本节中的此或其他示例查询中包含FROM条款。

现在使用减数运算器进行计算. 另外,请注意,您可以用如下几位数计算值:

1SELECT 437.82 - 66.34;

1[secondary_label Output]
2+----------------+
3| 437.82 - 66.34 |
4+----------------+
5|         371.48 |
6+----------------+
71 row in set (0.00 sec)

您可以在 SQL 中的单个计算中包含多个值和运算符. 以下示例计算使用三次运算符来找到四个数字的产物:

1SELECT 60 * 1234 * 2 * 117;

1[secondary_label Output]
2+---------------------+
3| 60 * 1234 * 2 * 117 |
4+---------------------+
5|            17325360 |
6+---------------------+
71 row in set (0.00 sec)

接下来,计算一个组合十进制值和整数值的分区问题,如下列情况:

1SELECT 2604.56 / 41;

1[secondary_label Output]
2+--------------+
3| 2604.56 / 41 |
4+--------------+
5|    63.525854 |
6+--------------+
71 row in set (0.00 sec)

另一个分割运算器是%或模块运算器,该运算器在由分割者分割股息后计算剩余值:

1SELECT 38 % 5;

1[secondary_label Output]
2+--------+
3| 38 % 5 |
4+--------+
5|      3 |
6+--------+
71 row in set (0.00 sec)

另一个可能有用的运算器是 POW(x,y),它计算了指定的基数值(x)的指数(y)的功率:

1SELECT POW(99,9);

1[secondary_label Output]
2+---------------------+
3| POW(99,9)       	|
4+---------------------+
5| 9.13517247483641e17 |
6+---------------------+
71 row in set (0.01 sec)

现在你已经练习了每个操作的计算,你可以尝试结合不同的数学运算器来练习更复杂的数学方程式。

了解 SQL 中的操作顺序

您可能熟悉的术语 PEMDAS,它代表,表数,倍增,分割,增加和减去. 这个术语作为解决更复杂方程所需的操作顺序的指导方针。

当涉及到嵌入中不同的数学操作的组合时,SQL 会读取它们从左到右,然后从内部到外部开始的值。

尝试使用和几个不同的运算器进行计算:

1SELECT (2 + 4 ) * 8;

1[secondary_label Output]
2+-----------+
3| (2+4) * 8 |
4+-----------+
5|        48 |
6+-----------+
71 row in set (0.00 sec)

请记住,关节放置很重要,如果你不小心,整个结果可能会发生变化,例如,以下使用相同的三个值和运算符,但如果关节放置不同,则会产生不同的结果:

1SELECT 2 + (4  * 8);

1[secondary_label Output]
2+-------------+
3| 2 + (4 * 8) |
4+-------------+
5|          34 |
6+-------------+
71 row in set (0.00 sec)

请记住,仍存在操作的顺序规则;因此,类似于关节放置,请验证这是基于它将评估的操作顺序所需的方程式。

1SELECT 100 / 5 - 300;

1[secondary_label Output]
2+---------------+
3| 100 / 5 - 300 |
4+---------------+
5|     -280.0000 |
6+---------------+
71 row in set (0.00 sec)

您已经成功地使用数学表达式进行数值计算和复杂计算,通过组合各种运算器,接下来,您将使用样本数据来使用汇总函数进行计算,并生成有关数据的新信息。

使用聚合函数分析数据

假设您是小茶店的所有者,并且希望对您存储在数据库中的信息进行相关的计算。SQL可以使用数学表达式来查询和操纵数据,通过从数据库表和不同的列中获取数据,这有助于生成您感兴趣的数据的新信息。

SQL 中的主要聚合函数包括SUM,MAX,MIN,AVG和COUNT。SUM函数添加了列中的所有值,例如,使用SUM在样本数据集中添加了total_inventory列的金额:

1SELECT SUM(total_inventory) FROM product_information;

1[secondary_label Output]
2+----------------------+
3| SUM(total_inventory) |
4+----------------------+
5|                 1155 |
6+----------------------+
71 row in set (0.00 sec)

此示例中,使用MAX查询product_cost列表中的产品所花费的最大金额,并使用AS语句重新标记标题,以便其更清晰地读取:

1SELECT MAX(product_cost) AS cost_max 
2FROM product_information;

1[secondary_label Output]
2+----------+
3| cost_max |
4+----------+
5|     7.40 |
6+----------+
71 row in set (0.00 sec)

MIN函数与MAX函数相反,因为它计算了最低值。

1SELECT MIN(product_retail) AS retail_min 
2FROM product_information;

1[secondary_label Output]
2+------------+
3| retail_min |
4+------------+
5|       5.00 |
6+------------+
71 row in set (0.00 sec)

AVG 函数计算了表中指定的列中的所有值的平均值。另外,请注意,您可以在同一个查询中运行多个汇总函数。

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

1[secondary_label Output]
2+----------------+--------------+
3| retail_average | cost_average |
4+----------------+--------------+
5|       7.875000 |     5.750000 |
6+----------------+--------------+
71 row in set (0.00 sec)

COUNT函数与其他函数不同,因为它通过计算查询返回的行数来计算表本身的值。

1SELECT COUNT(product_retail) 
2FROM product_information 
3WHERE product_retail > 8.00;

1[secondary_label Output]
2+-----------------------+
3| COUNT(product_retail) |
4+-----------------------+
5|                     4 |
6+-----------------------+
71 row in set (0.00 sec)

现在查询从产品_成本中购买的产品数量,这些产品由商店以超过 8.00 美元购买:

1SELECT COUNT(product_cost) 
2FROM product_information 
3WHERE product_cost > 8.00;

1[secondary_label Output]
2+---------------------+
3| COUNT(product_cost) |
4+---------------------+
5|                   0 |
6+---------------------+
71 row in set (0.00 sec)

您已成功地使用聚合函数提供最高、最低、平均和计数等值的摘要。您从样本数据中获取了这些信息,以模拟真实情况。

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

本节提供了几个示例场景,涉及执行数据分析,以帮助茶店所有者做出决定,因为它与他们的业务有关。

作为第一个场景,计算现存库存中的总单位,以了解在商店和在线销售中剩余的产品有多少。此查询还将包含DESC声明,以便将数据从最大到最小。

1SELECT product_name, 
2total_inventory - (store_units + online_units) 
3AS remaining_inventory 
4FROM product_information 
5ORDER BY(remaining_inventory) DESC;

 1[secondary_label Output]
 2+-------------------+---------------------+
 3| product_name      | remaining_inventory |
 4+-------------------+---------------------+
 5| chamomile         |                 110 |
 6| chai              |                  56 |
 7| english_breakfast |                  54 |
 8| matcha            |                  47 |
 9| lavender          |                  38 |
10| oolong            |                  36 |
11| jasmine           |                  25 |
12| golden teaspoon   |                  15 |
13| tea sampler       |                   7 |
14| ceramic teapot    |                   7 |
15+-------------------+---------------------+
1610 rows in set (0.00 sec)

此查询是有用的,因为它计算了剩余的库存,这可以帮助茶店所有者制定计划,如果他们在产品上运行低。

对于下一个场景,您将分析和比较店内和在线销售收入的金额:

1SELECT product_name, 
2(online_units * product_retail) AS o, 
3(store_units * product_retail) AS s 
4FROM product_information;

 1[secondary_label Output]
 2
 3+-------------------+--------+--------+
 4| product_name      | o      | s      |
 5+-------------------+--------+--------+
 6| chamomile         | 390.00 | 285.00 |
 7| chai              | 243.00 | 153.00 |
 8| lavender          | 840.00 | 375.00 |
 9| english_breakfast | 555.00 | 165.00 |
10| jasmine           | 690.00 | 247.50 |
11| matcha            | 307.50 |  90.00 |
12| oolong            | 261.00 |  90.00 |
13| tea sampler       | 212.50 | 153.00 |
14| ceramic teapot    | 146.25 |  78.00 |
15| golden teaspoon   | 335.00 |  90.00 |
16+-------------------+--------+--------+
1710 rows in set (0.00 sec)

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

1SELECT SUM(online_units * product_retail) + 
2SUM(store_units * product_retail) 
3AS total_sales 
4FROM product_information;

1[secondary_label Output]
2+-------------+
3| total_sales |
4+-------------+
5|     5706.75 |
6+-------------+
71 row in set (0.00 sec)

执行这些查询很重要,原因有两个:第一,茶店所有者可以评估哪些产品是畅销的,并在未来购买更多产品时优先考虑这些产品。

接下来,您将找到每个产品的利润率。特定产品的利润率是该业务为其出售的每个产品单位获得的收入量。

若要计算您个别产品的利润率,请从每个行的产品_零售值中扣除 product_cost,然后将此值划分为产品零售,以计算利润率百分比:

1SELECT product_name, 
2(product_retail - product_cost) / product_retail 
3AS profit_margin
4FROM product_information;

 1[secondary_label Output]
 2+-------------------+-------------+
 3| product_name  	| profit_margin |
 4+-------------------+-------------+
 5| chamomile         |    0.317333 |
 6| chai              |    0.177778 |
 7| lavender          |    0.317333 |
 8| english_breakfast |    0.317333 |
 9| jasmine           |    0.177333 |
10| matcha            |    0.177333 |
11| oolong            |    0.177778 |
12| tea sampler       |    0.294118 |
13| ceramic teapot    |    0.282051 |
14| golden teaspoon   |    0.600000 |
15+-------------------+-------------+
1610 rows in set (0.00 sec)

基于此结果,您将了解到,利润率最高的产品是60%的黄金茶匙,而最低的产品是18%的Chai,Jasmine,Matcha和Oolong茶叶。

您还可以使用汇总函数AVG来计算茶店所有产品的平均利润率,该平均值作为茶店所有者的基准,然后确定哪些产品低于该数字,并制定如何改进:

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

1[secondary_label Output]
2+-------------------+
3| avg_profit_margin |
4+-------------------+
5|  	0.2838391151 |
6+-------------------+
71 row in set (0.00 sec)

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

使用这些新信息,想象一下,茶店所有者希望在下一个季度将利润率提高到 31% 对于目前利润率低于 27% 的任何产品。为了做到这一点,您将目标利润率从 1 (1 - 0.31) 扣除,然后将返回的每一个产品的成本分为此值。

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

 1[secondary_label Output]
 2+--------------+------------+
 3| product_name | new_retail |
 4+--------------+------------+
 5| chai     	|  10.724638 |
 6| jasmine  	|   8.942029 |
 7| matcha   	|   8.942029 |
 8| oolong   	|  10.724638 |
 9+--------------+------------+
104 rows in set (0.00 sec)

这些结果显示了不足产品达到31%的利润率所需的新零售价格,像这样的数据分析使茶店所有者能够做出决定性的商业决策,以提高下一季度的收入,并了解目标是什么。

结论

使用 SQL 中的数学表达式可以从像计算机一样解决数学问题到对可能影响业务决策的真实数据进行复杂分析。如果您能够识别主要数学运算符和操作顺序的规则,计算可能性是无穷无尽的。当您想要进一步分析数据时,使用这些运算符具有聚合函数可以计算对您的如果问题的答案,这可能有助于战略规划。