Créer des calculs complexes

Création de rapports en SQL

Tyler Pernes

Learning & Development Consultant

Approches

Fonctions de fenêtrage
Calculs en couches

Fonctions de fenêtrage

Fait référence à d'autres lignes de la table.

Fonctions de fenêtrage

Fait référence à d'autres lignes de la table.

Fonctions de fenêtrage

Fait référence à d'autres lignes de la table.

Syntaxe des fonctions de fenêtrage

SUM(value) OVER (PARTITION BY field ORDER BY field)

Syntaxe des fonctions de fenêtrage

SUM(value) OVER (PARTITION BY field ORDER BY field)

Syntaxe des fonctions de fenêtrage

SUM(value) OVER (PARTITION BY field ORDER BY field)

PARTITION BY = portée du calcul
ORDER BY = ordre des lignes lors de l'exécution du calcul

Exemples de fonctions de fenêtrage

Total des médailles de bronze

SELECT 
    country_id, 
    athlete_id, 
    SUM(bronze) OVER () AS total_bronze
FROM summer_games;

+-------------+-------------+---------------+
| country_id  | athlete_id  | total_bronze  |
|-------------|-------------|---------------|
| 11          | 77505       | 141           |
| 11          | 11673       | 141           |
| 14          | 85554       | 141           |
| 14          | 76433       | 141           |
+-------------+-------------+---------------+

Exemples de fonctions de fenêtrage

Médailles de bronze par pays

SELECT 
    country_id, 
    athlete_id, 
    SUM(bronze) OVER (PARTITION BY country_id) AS total_bronze
FROM summer_games

+-------------+-------------+---------------+
| country_id  | athlete_id  | total_bronze  |
|-------------|-------------|---------------|
| 11          | 77505       | 12            |
| 11          | 11673       | 12            |
| 14          | 85554       | 5             |
| 14          | 76433       | 5             |
+-------------+-------------+---------------+

Types de fonctions de fenêtrage

SUM()
AVG()
MIN()
MAX()

Types de fonctions de fenêtrage

LAG() et LEAD()

Types de fonctions de fenêtrage

LAG() et LEAD()

Types de fonctions de fenêtrage

ROW_NUMBER() et RANK()

Fonction de fenêtrage sur une agrégation

original_table
+----------+-----------+---------+ 
| team_id  | player_id | points  |
|----------|-----------|---------|    
| 1        | 4123      | 3       |
| 1        | 5231      | 6       |
| 2        | 8271      | 5       |
+----------+-----------+---------+

desired_report
+----------+-------------+---------------+ 
| team_id  | team_points | league_points |
|----------|-------------|---------------|    
| 1        | 9           | 43            |
| 2        | 12          | 43            |
| 3        | 22          | 43            |
+----------+-------------+---------------+

Fonction de fenêtrage sur une agrégation

Requête finale

SELECT
    team_id,
    SUM(points) AS team_points,
    SUM(SUM(points)) OVER () AS league_points
FROM original_table
GROUP BY team_id;

Fonction de fenêtrage sur une agrégation

SELECT
    team_id,
    SUM(points) AS team_points,
    SUM(points) OVER () AS league_points
FROM original_table
GROUP BY team_id;

ERROR: points must be an aggregation or appear in a GROUP BY statement.

Calculs en couches

Agréger une agrégation existante
Utiliser une sous-requête

Exemple de calculs en couches

Étape 1 : total des médailles de bronze par pays

SELECT country_id, SUM(bronze) as bronze_medals
FROM summer_games
GROUP BY country_id;

Étape 2 : convertir en sous-requête et prendre le max

SELECT MAX(bronze_medals)
FROM
  (SELECT country_id, SUM(bronze) as bronze_medals
  FROM summer_games
  GROUP BY country_id) AS subquery;

Planifier des calculs complexes

Ordonnancement pour la fonction de fenêtrage ?
Deux agrégations avec un calcul en couches ?

Passons à la pratique !

Création de rapports en SQL