Filtrage et touches finales

Création de rapports en SQL

Tyler Pernes

Learning & Development Consultant

Rapport cible

Medailles d'or par groupe démographique
(Pays d'Europe de l'Ouest seulement)
+----------+--------------------+-------+
| season   |  demographic_group | golds |
|----------+--------------------+-------|
| Winter   | Male Age 26+       | 13    |
| Winter   | Female Age 26+     | 8     |
| Summer   | Male Age 13-25     | 7     |
| Summer   | Female Age 13-25   | 6     |
| Winter   | Male Age 13-25     | 4     |
| Summer   | Male Age 26+       | 4     |
| Winter   | Female Age 13-25   | 4     |
| Summer   | Female Age 26+     | 2     |
+----------+--------------------+-------+
Création de rapports en SQL

Filtrage

Création de rapports en SQL

Filtrer avec une sous-requête

Moitié supérieure de la requête :

SELECT 
    'Summer' AS season,
    CASE WHEN age >= 13 AND age <= 25 AND gender = 'M' THEN 'Male Age 13-25'
    WHEN age > 25 AND gender = 'M' THEN 'Male Age 26+'
    WHEN age >= 13 AND age <= 25 AND gender = 'F' THEN 'Female Age 13-25'
    WHEN age > 25 AND gender = 'F' THEN 'Female Age 26+' 
    END AS demographic_group, 
    SUM(gold) AS golds
FROM summer_games AS sg
JOIN athletes AS a 
ON sg.athlete_id = a.id
GROUP BY demographic_group;
Création de rapports en SQL

Filtrer avec une sous-requête

Étape 1 : Configurer la sous-requête

SELECT id
FROM countries
WHERE region = 'WESTERN EUROPE';

+------+
| id   |
|------|
| 5    |
| 12   |
| 19   |
+------+
Création de rapports en SQL

Filtrer avec une sous-requête

Étape 2 : Configurer l'instruction WHERE

SELECT 
    'Summer' AS season,
    CASE WHEN age >= 13 AND age <= 25 AND gender = 'M' THEN 'Male Age 13-25'
    WHEN age > 25 AND gender = 'M' THEN 'Male Age 26+'
    WHEN age >= 13 AND age <= 25 AND gender = 'F' THEN 'Female Age 13-25'
    WHEN age > 25 AND gender = 'F' THEN 'Female Age 26+' 
    END AS demographic_group, 
    SUM(gold) AS golds
FROM summer_games AS sg
JOIN athletes AS a 
ON sg.athlete_id = a.id
WHERE country_id IN 
    (___)
GROUP BY demographic_group;
Création de rapports en SQL

Filtrer avec une sous-requête

Étape 2 : Configurer l'instruction WHERE

SELECT 
    'Summer' AS season,
    CASE WHEN age >= 13 AND age <= 25 AND gender = 'M' THEN 'Male Age 13-25'
    WHEN age > 25 AND gender = 'M' THEN 'Male Age 26+'
    WHEN age >= 13 AND age <= 25 AND gender = 'F' THEN 'Female Age 13-25'
    WHEN age > 25 AND gender = 'F' THEN 'Female Age 26+' 
    END AS demographic_group, 
    SUM(gold) AS golds
FROM summer_games AS sg
JOIN athletes AS a 
ON sg.athlete_id = a.id
WHERE country_id IN 
    (SELECT id
    FROM countries
    WHERE region = 'WESTERN EUROPE')
GROUP BY demographic_group;
Création de rapports en SQL

Filtrer avec un JOIN

SELECT 
    'Summer' AS season,
    CASE WHEN age >= 13 AND age <= 25 AND gender = 'M' THEN 'Male Age 13-25'
    WHEN age > 25 AND gender = 'M' THEN 'Male Age 26+'
    WHEN age >= 13 AND age <= 25 AND gender = 'F' THEN 'Female Age 13-25'
    WHEN age > 25 AND gender = 'F' THEN 'Female Age 26+' 
    END AS demographic_group, 
    SUM(gold) AS golds
FROM summer_games AS sg
JOIN athletes AS a 
ON sg.athlete_id = a.id
JOIN countries AS c
ON sg.country_id = c.id
WHERE region = 'WESTERN EUROPE'
GROUP BY demographic_group;
Création de rapports en SQL

Questions restantes

  • ORDER BY ?
  • LIMIT ?
Medailles d'or par groupe démographique
(Pays d'Europe de l'Ouest seulement)
+----------+--------------------+-------+
| season   |  demographic_group | golds |
|----------+--------------------+-------|
| Winter   | Male Age 26+       | 13    |
| Winter   | Female Age 26+     | 8     |
| Summer   | Male Age 13-25     | 7     |
| Summer   | Female Age 13-25   | 6     |
| Winter   | Male Age 13-25     | 4     |
| Summer   | Male Age 26+       | 4     |
| Winter   | Female Age 13-25   | 4     |
| Summer   | Female Age 26+     | 2     |
+----------+--------------------+-------+
Création de rapports en SQL

Code final

SELECT 
    'Summer' AS season,
    CASE WHEN age >= 13 AND age <= 25 AND gender = 'M' THEN 'Male Age 13-25'
    WHEN age > 25 AND gender = 'M' THEN 'Male Age 26+'
    WHEN age >= 13 AND age <= 25 AND gender = 'F' THEN 'Female Age 13-25'
    WHEN age > 25 AND gender = 'F' THEN 'Female Age 26+' 
    END AS demographic_group, 
    SUM(gold) AS golds
FROM summer_games AS sg
JOIN athletes AS a 
ON sg.athlete_id = a.id
WHERE country_id IN 
    (SELECT id
    FROM countries
    WHERE region = 'WESTERN EUROPE')
GROUP BY demographic_group
UNION ALL
  ...
ORDER BY golds DESC;
Création de rapports en SQL

Ordre des opérations

  • Deux JOIN

Création de rapports en SQL

Ordre des opérations

  • Deux JOIN
  • Ajouter de la LOGIQUE

Création de rapports en SQL

Ordre des opérations

  • Deux JOIN
  • Ajouter de la LOGIQUE
  • UNION

Création de rapports en SQL

Ordre des opérations

  • Deux JOIN
  • Ajouter de la LOGIQUE
  • UNION
  • ORDER BY

Création de rapports en SQL

Option B

SELECT 
    season,
    CASE WHEN age >= 13 AND age <= 25 AND gender = 'M' THEN 'Male Age 13-25'
    WHEN age > 25 AND gender = 'M' THEN 'Male Age 26+'
    WHEN age >= 13 AND age <= 25 AND gender = 'F' THEN 'Female Age 13-25'
    WHEN age > 25 AND gender = 'F' THEN 'Female Age 26+' 
    END AS demographic_group, 
    SUM(gold) as golds
FROM 
    (SELECT 'Summer' AS season, country_id, athlete_id, gold
    FROM summer_games AS sg
    UNION ALL
    SELECT 'Winter' AS season, country_id, athlete_id, gold
    FROM winter_games AS wg) AS g
JOIN athletes AS a
ON g.athlete_id = a.id
WHERE country_id IN 
    (SELECT id 
    FROM countries
    WHERE region = 'WESTERN EUROPE')
GROUP BY season, demographic_group
ORDER BY golds DESC;

Création de rapports en SQL

Exercice d'intégration

Création de rapports en SQL

Passons à la pratique !

Création de rapports en SQL

Preparing Video For Download...