फ़िल्टरिंग और अंतिम टच

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

फ़िल्टरिंग

सबक्वेरी के साथ फ़िल्टरिंग

क्वेरी का ऊपरी आधा:

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;

सबक्वेरी के साथ फ़िल्टरिंग

चरण 1: सबक्वेरी सेटअप करें

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

+------+
| id   |
|------|
| 5    |
| 12   |
| 19   |
+------+

सबक्वेरी के साथ फ़िल्टरिंग

चरण 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;

सबक्वेरी के साथ फ़िल्टरिंग

चरण 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;

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;

शेष प्रश्न

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

अंतिम कोड

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;

ऑर्डर ऑफ ऑपरेशंस

दो JOIN

ऑर्डर ऑफ ऑपरेशंस

दो JOIN
तर्क (LOGIC) जोड़ें

ऑर्डर ऑफ ऑपरेशंस

दो JOIN
तर्क (LOGIC) जोड़ें
UNION

ऑर्डर ऑफ ऑपरेशंस

दो JOIN
तर्क (LOGIC) जोड़ें
UNION
ORDER BY

विकल्प 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;

कैपस्टोन अभ्यास

अभ्यास करते हैं!

SQL में रिपोर्टिंग