การสร้างฟิลด์แบบกำหนดเอง

การรายงานผลด้วย SQL

Tyler Pernes

Learning & Development Consultant

รายงานเป้าหมาย

+----------+--------------------+-------+
| season   |  demographic_group | golds |
|----------+--------------------+-------|
| Winter   | Male Age 26+       | 13    |
| Winter   | Female Age 26+     | 8     |
| Summer   | Male Age 13-25     | 7     |
+----------+--------------------+-------+

SELECT athlete_id, gender, age, gold  
FROM summer_games AS sg
JOIN athletes AS a 
ON sg.athlete_id = a.id
UNION ALL
SELECT athlete_id, gender, age, gold  
FROM winter_games AS wg
JOIN athletes AS a 
ON wg.athlete_id = a.id;

การเตรียมการ

ขั้นตอนที่ 1: คอมเมนต์ส่วนล่างออก

SELECT athlete_id, gender, age, gold  
FROM summer_games AS sg
JOIN athletes AS a 
ON sg.athlete_id = a.id;

/*UNION ALL
SELECT athlete_id, gender, age, gold  
FROM winter_games AS wg
JOIN athletes AS a 
ON wg.athlete_id = a.id;*/

การเตรียมการ

ขั้นตอนที่ 2: เพิ่ม placeholder สำหรับฟิลด์ใหม่

SELECT 
    --___ AS season, 
    --___ AS demographic_group,
    --___ AS golds
FROM summer_games AS sg
JOIN athletes AS a 
ON sg.athlete_id = a.id;

/*UNION ALL
SELECT athlete_id, gender, age, gold  
FROM winter_games AS wg
JOIN athletes AS a 
ON wg.athlete_id = a.id;*/

ฟิลด์ที่ 1: seasons

SELECT 
    'Summer' AS season,
    --___ AS demographic_group,
    --___ AS golds
FROM summer_games AS sg
JOIN athletes AS a 
ON sg.athlete_id = a.id;

ฟิลด์ที่ 2: golds

SELECT 
    'Summer' AS season,
    --___ AS demographic_group,
    SUM(gold) AS golds
FROM summer_games AS sg
JOIN athletes AS a 
ON sg.athlete_id = a.id;

+---------+-------+
| season  | golds |
|---------+-------|
| Summer  | 159   |
+---------+-------+

ฟิลด์ที่ 3: demographic_group

+--------+-------+--------------------+
| gender | age   | demographic_group  |
|--------+-------+--------------------|
| M      | 18    | Male Age 13-25     |
| M      | 31    | Male Age 26+       |
| F      | 22    | Female Age 13-25   |
| F      | 26    | Female Age 26+     |
+--------+-------+--------------------+

คำสั่ง CASE

CASE WHEN {condition_1} THEN {output_1}
WHEN {condition_2} THEN {output_2}
ELSE {output_3} 
END

ฟิลด์ที่ 3: demographic_group

SELECT 
    'Summer' AS season,
    CASE WHEN ___ THEN 'Male Age 13-25'
    WHEN ___ THEN 'Male Age 26+'
    WHEN ___ THEN 'Female Age 13-25'
    WHEN ___ 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;

ฟิลด์ที่ 3: demographic_group

SELECT 
    'Summer' AS season,
    CASE WHEN age >= 13 AND age <= 25 AND gender = 'M' THEN 'Male Age 13-25'
    WHEN ___ THEN 'Male Age 26+'
    WHEN ___ THEN 'Female Age 13-25'
    WHEN ___ 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;

ฟิลด์ที่ 3: demographic_group

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;

ERROR: Column must be in a GROUP BY clause.

ฟิลด์ที่ 3: demographic_group

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;

Query Ran Successfully!

ฟิลด์ที่ 3: demographic_group

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;

ไม่มีคำสั่ง ELSE = ตรวจสอบข้อมูลได้ง่ายขึ้น

สถานะปัจจุบันของคิวรี

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
UNION ALL
SELECT 
    ...
FROM winter_games AS wg
JOIN athletes AS a 
ON wg.athlete_id = a.id
GROUP BY demographic_group;

มาฝึกกันเถอะ!

การรายงานผลด้วย SQL