Перевод статьи How To Use Mathematical Expressions and Aggregate Functions in SQL

Язык структурированных запросов (SQL) используется для хранения, управления и организации информации в реляционной системе управления базами данных (RDBMS). SQL также может выполнять вычисления и манипулировать данными с помощью выражений. Выражения объединяют различные операторы SQL, функции и значения для вычисления величины. Математические выражения обычно используются для сложения, вычитания, деления и умножения числовых значений. Кроме того, агрегатные функции используются для оценки и группировки значений для создания сводки, например, среднего значения или суммы значений в данном столбце. Математические и агрегатные выражения могут дать ценную информацию при анализе данных, которая может помочь в принятии будущих решений.

Подключаемся:

mysql -u root -p

И создаем базу данных с именем mathDB:

CREATE DATABASE mathDB;

и выбираем ее:

USE mathDB;

Так как от пустой базы проку нет, заполним ее таблицами. Для начала, создадим таблицу с именем product_information, в которой будут хранения сведений о запасах и продажах для небольшого чайного магазина. Эта таблица будет содержать следующие восемь столбцов:

• product_id – представляет собой значения типа данных int (целое число) и будет служить первичным ключом таблицы. Это означает, что каждое значение в этом столбце будет функционировать как уникальный идентификатор для соответствующей строки.
• product_name – подробное описание названия продукта, использует тип данных varchar с максимальным количеством символов 30;
• product_type – описывает тип продукта, о чем свидетельствует тип данных varchar с максимальным количеством 30 символов;
• total_inventory – отображает количество единиц каждого продукта оставшегося на складе, используя тип данных int с максимальным значением 200;
• product_cost – себестоимость продукта, использует десятичный тип данных с максимальным количеством значения – 3 слева и два знака справа, после десятичной точки;
• product_retail – розничная цена каждого продукта, также использует десятичный тип данных с максимальным количеством значения – 3 слева и два знака справа, после десятичной точки;
• 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)
); 

И слегка заполним ее:

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);

В SQL, оператор SELECT обычно используется для запроса к базе данных и получения определенного результатов. Однако, мы также можете использовать SELECT для выполнения различных математических операций.

Стоит отметить, что в реальном сценарии, SQL используется для запросов и вычислений на основе значений в фактической базе данных. Но в этом разделе, мы будем использовать SELECT исключительно для числовых значений, чтобы познакомиться с синтаксисом математических выражений и операторов.

Давайте рассмотрим операторы, которые можно использовать для выполнения шести арифметических операций в SQL. Также, обратим внимание, что данный список не является полным и что многие СУБД имеют уникальный набор математических операторов:

• Сложение, использует символ – +
• Вычитание, использует символ – -
• Умножение, использует символ – *
• Деление, использует символ – /
• Операции модуляции, используют символ – %
• Возведение в степень, использует символ – POW(x,y)

Продемонстрируем эти операции на следующих примерах, начнём с уравнения сложения:

SELECT 893 + 579;
+-----------+
| 893 + 579 |
+-----------+
|      1472 |
+-----------+

Обратите внимание, что поскольку мы не получаем никаких данных из базы данных и вычисляете только необработанные числа, нам не нужно включать оператор FROM в этот или другие примеры запросов в этом разделе.

Теперь выполните вычисление, при этом будем вычислять значения с десятичными знаками:

SELECT 437.82 - 66.34;
+----------------+
| 437.82 - 66.34 |
+----------------+
|         371.48 |
+----------------+

Также, есть возможность включить несколько значений и операторов в одно вычисление в SQL:

SELECT 60 * 1234 * 2 * 117;
+---------------------+
| 60 * 1234 * 2 * 117 |
+---------------------+
|            17325360 |
+---------------------+

Выполняем деление:

SELECT 2604.56 / 41;
+--------------+
| 2604.56 / 41 |
+--------------+
|    63.525854 |
+--------------+

Другим оператором деления является %, или оператор модуляции, который вычисляет оставшееся значение после деления делимого на делитель:

SELECT 38 % 5;
+--------+
| 38 % 5 |
+--------+
|      3 |
+--------+
1 ROW IN SET (0.00 sec)

Еще один полезный оператор – POW(x,y), в данном примере мы возводим (x) в степень (y):

SELECT POW(8, 3);
+-----------+
| POW(8, 3) |
+-----------+
|       512 |
+-----------+

PEMDAS – это английская аббревиатура (акроним), помогающая запомнить очерёдность выполнения действий в математических выражениях, которая обычно вводится в программу примерно на 5-ом году обучения (U.S). Она означает следующее: СКОБКИ–СТЕПЕНЬ–УМНОЖЕНИЕ/ДЕЛЕНИЕ–СЛОЖЕНИЕ/ВЫЧИТАНИЕ–СКОБКИ:

• P – Скобки (от английского Parentheses)
• E – Экспонента, т.е. возведение в степень (от английского Еxponent)
• M – Умножение (от английского Multiplication)
• D – Деление (от английского Devision)
• A – Сложение (от английского Addition)
• S – Вычитание (от английского Subtraction)

Один из популярных способов запомнить акроним PEMDAS, а значит и порядок математических действий, это английская пословица: Please Excuse My Dear Aunt Sally (Пожалуйста, извините мою дорогую тетю Салли).

Когда речь идет о комбинировании различных математических операций, вложенных в круглые скобки, SQL читает их слева направо, а затем значения, начиная от внутреннего к внешнему. Поэтому, следует убедиться, что значения в скобках точно отражают задачу, которую мы пытаемся решить.

Попробуем произвести вычисления с помощью круглых скобок и нескольких разных операторов:

SELECT (2 + 4 ) * 8;
+--------------+
| (2 + 4 ) * 8 |
+--------------+
|           48 |
+--------------+

Следует помнить, что размещение круглых скобок имеет значение, и если мы не будем внимательны, результат может измениться. Например, в следующем примере используются те же три значения и операторы, но при другом расположении круглых скобок получается совсем другой результат:

SELECT 2 + (4  * 8);
+--------------+
| 2 + (4  * 8) |
+--------------+
|           34 |
+--------------+

Если же мы предпочитаем выполнять вычисления без круглых скобок, то и такую возможность дает нам SQL. В таком случае, будет действовать правило порядка операций. В следующем примере, операция деления имеет приоритет над оператором вычитания и приводит к отрицательному значению:

SELECT 100 / 5 - 300;
+---------------+
| 100 / 5 - 300 |
+---------------+
|     -280.0000 |
+---------------+

Представим, что мы владелец небольшого чайного магазина, и нам нужно выполнить некие вычисления с данными, хранящейся в нашей базе данных. SQL может использовать математические выражения для запроса и манипулирования данными, извлекая их из таблицы базы данных и различных столбцов. Это помогает генерировать новую информацию о данных, в анализе которых мы заинтересованы.

Основные агрегатные функции в SQL включают SUM, MAX, MIN, AVG и COUNT. Функция SUM складывает все значения в столбце. Например, используем SUM, чтобы получить общую сумму для столбца total_inventory в нашей препарируемой бд:

SELECT SUM(total_inventory) FROM product_information;
 
+----------------------+
| SUM(total_inventory) |
+----------------------+
|                 1155 |
+----------------------+

Функция MAX, находит максимальное значение, содержащееся в выбранном столбце. В этом примере, используем MAX для запроса максимальной суммы, потраченной на продукты, перечисленные в столбце product_cost, а также, воспользуемся оператором AS, для переименования заголовка, чтобы он соответствовал своей сути:

SELECT MAX(product_cost) AS cost_max 
FROM product_information;
 
+----------+
| cost_max |
+----------+
|     7.40 |
+----------+

Функция MIN является противоположностью функции MAX – вычисляет минимальное значение. Здесь мы используем MIN для запроса минимальной стоимости product_retail:

SELECT MIN(product_retail) AS retail_min 
FROM product_information;
 
+------------+
| retail_min |
+------------+
|       5.00 |
+------------+

Функция AVG вычисляет среднее значение всех значений из указанного столбца таблицы. Здесь следует обратить внимание, что в одном запросе можно запустить более одной агрегатной функции. Попробуем объединить запрос, для того, чтобы найти среднюю стоимость продуктов, проданных в розницу, и продуктов, купленных по себестоимости:

SELECT AVG(product_retail) AS retail_average, 
AVG(product_cost) AS cost_average 
FROM product_information;
 
+----------------+--------------+
| retail_average | cost_average |
+----------------+--------------+
|       7.875000 |     5.750000 |
+----------------+--------------+

Функция COUNT работает иначе, она вычисляет значение из самой таблицы путем подсчета количества строк, возвращенных запросом. Используем функцию COUNT с оператором WHERE для запроса количества товаров, розничная стоимость которых превышает $8,00:

SELECT COUNT(product_retail) 
FROM product_information 
WHERE product_retail > 8.00;
 
+-----------------------+
| COUNT(product_retail) |
+-----------------------+
|                     4 |
+-----------------------+

Теперь запросим количество товаров из product_cost, которые были куплены на сумму более $8.00:

SELECT COUNT(product_cost) 
FROM product_information 
WHERE product_cost > 8.00;
 
+---------------------+
| COUNT(product_cost) |
+---------------------+
|                   0 |
+---------------------+

В этом разделе приведено несколько кейсов, которые предполагают проведение анализа данных, чтобы помочь владельцу чайного магазина в принятии решений, касающихся его бизнеса.

В первом кейсе, рассчитаем общее количество товара, имеющихся в настоящее время на складе, чтобы понять, сколько товаров осталось для продажи в магазине и в интернете. Этот запрос также будет включать оператор DESC, чтобы упорядочить данные от наибольшего количества к наименьшему. Обычно в СУБД по умолчанию используется порядок возрастания, но в данном примере используется параметр DESC, который позволяет просматривать данные в порядке убывания:

SELECT product_name, 
total_inventory - (store_units + online_units) 
AS remaining_inventory 
FROM product_information 
ORDER BY(remaining_inventory) DESC;
 
+-------------------+---------------------+
| 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 |
+-------------------+---------------------+

Этот запрос полезен, для анализа оставшихся товарных запасов, что может помочь владельцу магазина составить планы по закупке, если товар на исходе.

В следующем кейсе, мы проанализируем и сравним объем выручки от продаж в магазине и в Интернете:

SELECT product_name, 
(online_units * product_retail) AS o, 
(store_units * product_retail) AS s 
FROM product_information;
 
+-------------------+--------+--------+
| 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 |
+-------------------+--------+--------+

Далее рассчитайте общий доход от продаж в магазине и онлайн, используя функцию SUM и несколько математических операторов:

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

Данные запросы важны по двум причинам. Первая причина заключается в том, что владельцы чайного магазина могут оценить, какие товары являются наиболее ходовыми, и отдать им предпочтение при закупках в будущем. Во-вторых, они могут проанализировать, насколько хорошо в целом магазин справился с продажами товаров офлайн и онлайн.

Далее, найдем маржу прибыли для каждого продукта. Норма прибыли для данного продукта - это сумма дохода, которую получает предприятие за каждую проданную единицу этого продукта. Чтобы понять, сколько выручки мы получили, умножим объем продаж на размер прибыли.

Чтобы рассчитать размер прибыли для отдельных продуктов, вычтем product_cost из product_retail для каждой строки. Затем разделите это значение на розничную продажу продукта, для расчета процента прибыли:

SELECT product_name, 
(product_retail - product_cost) / product_retail 
AS profit_margin
FROM product_information;
 
+-------------------+---------------+
| 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 |
+-------------------+---------------+

На основе полученных данных мы узнаем, что продуктом с самой высокой прибылью является golden teaspoon – 60%, а самой низкой – чаи Chai, jasmine, matcha и oolong – 18%. Для golden teaspoon это означает, что при розничной стоимости в $5.00 и норме прибыли в 60% мы получаем доход в $3.00.

Также, мы можем использовать агрегатную функцию AVG для расчета средней нормы прибыли для всех продуктов магазина. Это среднее значение служит ориентиром для владельца, чтобы затем определить, какие продукты не соответствуют этому показателю, и разработать стратегию по улучшению ситуации:

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

Из данного расчета, можно сделать вывод, что средняя норма прибыли для продуктов в этом магазине составляет 28%.

Получив эту информацию, жадный владелец чайного магазина захотел увеличить прибыль в следующем квартале до 31% для любых продуктов, которые в настоящее время имеют норму прибыли менее 27%. Давайте вычтем целевую норму прибыли из 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;
 
+--------------+------------+
| product_name | new_retail |
+--------------+------------+
| chai         |  10.724638 |
| jasmine      |   8.942029 |
| matcha       |   8.942029 |
| oolong       |  10.724638 |
+--------------+------------+

Этот результат, отображают новые розничные цены, необходимые для неэффективных продуктов для достижения маржи прибыли в 31%.