Затраты и обобщённые табличные выражения (CTE)

Анализ бизнес-данных с помощью SQL

Michel Semaan

Data Scientist

Затраты

Деньги, которые компания тратит
Примеры
- Зарплаты сотрудников
- Приобретение и обслуживание автопарка доставки
- Стоимость блюд

Таблицы, которые понадобятся

meals

meal_id   eatery                    meal_price  meal_cost
--------  ------------------------  ----------  ---------
0         'Leaning Tower of Pizza'  4           2         
1         'Leaning Tower of Pizza'  3.5         1.25      
2         'Leaning Tower of Pizza'  4.5         1.75
...       ...                         ...         ...

stock

stocking_date  meal_id   stocked_quantity
-------------  --------  ----------------
2018-06-01     0         76               
2018-06-01     1         42               
2018-06-01     2         56
...            ...       ...

Вычисление затрат

Запрос

SELECT
  meals.meal_id, 
  SUM(meal_cost * stocked_quantity) AS cost 
FROM meals
JOIN stock ON meals.meal_id = stock.meal_id
GROUP BY meals.meal_id
ORDER BY meals.cost DESC
LIMIT 3;

Результат

meal_id  cost      
  --------
9        3820.0
7        3592.5    
8        2332.5

Как объединить выручку и затраты?

Прибыль = Выручка − Затраты
Отдельные запросы для выручки и затрат уже написаны

Обобщённые табличные выражения (CTE)

Сохранить результаты запроса во временной таблице
Обратиться к временной таблице в следующем запросе

Запрос

WITH table_1 AS
  (SELECT ...
   FROM ...),
  table_2 AS
  (SELECT ...
   FROM   ...)

SELECT ...
FROM table_1
JOIN table_2 ON ...
...

CTE в действии

Запрос

WITH costs_and_quantities AS (
 SELECT
   meals.meal_id,
   SUM(stocked_quantity) AS quantity,
   SUM(meal_cost * stocked_quantity) AS cost
 FROM meals
 JOIN stock ON meals.meal_id = stock.meal_id
 GROUP BY meals.meal_id)


SELECT 
  meal_id,
  quantity,
  cost
FROM costs_and_quantities
ORDER BY cost DESC
LIMIT 3;

Результат

meal_id  quantity  cost      
-------  --------  -------
5        3062      12248     
4        3142      10211.5   
6        2989      8219.75

Затраты и обобщённые табличные выражения (CTE)

Анализ бизнес-данных с помощью SQL