Фильтрация и финальные штрихи

Отчётность в SQL

Tyler Pernes

Learning & Development Consultant

Целевой отчёт

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     |
+----------+--------------------+-------+
Отчётность в SQL

Фильтрация

Отчётность в SQL

Фильтрация с подзапросом

Верхняя часть запроса:

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;
Отчётность в SQL

Фильтрация с подзапросом

Шаг 1: Создание подзапроса

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

+------+
| id   |
|------|
| 5    |
| 12   |
| 19   |
+------+
Отчётность в SQL

Фильтрация с подзапросом

Шаг 2: Создание условия 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;
Отчётность в SQL

Фильтрация с подзапросом

Шаг 2: Создание условия 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;
Отчётность в SQL

Фильтрация с помощью 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;
Отчётность в SQL

Оставшиеся вопросы

  • 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     |
+----------+--------------------+-------+
Отчётность в SQL

Итоговый код

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;
Отчётность в SQL

Порядок операций

  • Два JOIN

Отчётность в SQL

Порядок операций

  • Два JOIN
  • Добавить LOGIC

Отчётность в SQL

Порядок операций

  • Два JOIN
  • Добавить LOGIC
  • UNION

Отчётность в SQL

Порядок операций

  • Два JOIN
  • Добавить LOGIC
  • UNION
  • ORDER BY

Отчётность в SQL

Вариант Б

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;

Отчётность в SQL

Итоговое упражнение

Отчётность в SQL

Давайте потренируемся!

Отчётность в SQL

Preparing Video For Download...