Coût et CTE (Common Table Expressions)

Analyser des données métier en SQL

Michel Semaan

Data Scientist

Coût

L’argent dépensé par une entreprise
Exemples
- Salaires des employés
- Acquisition et maintenance de la flotte de livraison
- Coûts des repas

Tables requises

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
...            ...       ...

Calcul du coût

Requête

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;

Résultat

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

Comment combiner chiffre d’affaires et coût ?

Profit = Chiffre d’affaires - Coût
Les requêtes pour le chiffre d’affaires et le coût sont déjà écrites

Common Table Expressions (CTE)

Stocker les résultats d’une requête dans une table temporaire
Référencer cette table temporaire dans une requête suivante

Requête

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

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

CTE en pratique

Requête

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;

Résultat

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

Coût et CTE (Common Table Expressions)

Analyser des données métier en SQL