Filtri e ritocchi finali

Reportistica in SQL

Tyler Pernes

Learning & Development Consultant

Report obiettivo

Gold Medals by Demographic Group
(Western European Countries Only)
+----------+--------------------+-------+
| 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     |
+----------+--------------------+-------+
Reportistica in SQL

Filtrare

Reportistica in SQL

Filtrare con una sottoquery

Metà superiore della query:

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;
Reportistica in SQL

Filtrare con una sottoquery

Passo 1: Imposta la sottoquery

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

+------+
| id   |
|------|
| 5    |
| 12   |
| 19   |
+------+
Reportistica in SQL

Filtrare con una sottoquery

Passo 2: Imposta l'istruzione 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;
Reportistica in SQL

Filtrare con una sottoquery

Passo 2: Imposta l'istruzione 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;
Reportistica in SQL

Filtrare con 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;
Reportistica in SQL

Domande rimaste

  • ORDER BY?
  • LIMIT?
Gold Medals by Demographic Group
(Western European Countries Only)
+----------+--------------------+-------+
| 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     |
+----------+--------------------+-------+
Reportistica in SQL

Codice finale

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;
Reportistica in SQL

Ordine delle operazioni

  • Due JOIN

Reportistica in SQL

Ordine delle operazioni

  • Due JOIN
  • Aggiungi LOGIC

Reportistica in SQL

Ordine delle operazioni

  • Due JOIN
  • Aggiungi LOGIC
  • UNION

Reportistica in SQL

Ordine delle operazioni

  • Due JOIN
  • Aggiungi LOGIC
  • UNION
  • ORDER BY

Reportistica in SQL

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

Reportistica in SQL

Esercizio capstone

Reportistica in SQL

Esercitiamoci!

Reportistica in SQL

Preparing Video For Download...