Tạo trường tùy chỉnh

Lập báo cáo trong SQL

Tyler Pernes

Learning & Development Consultant

Báo cáo mục tiêu

+----------+--------------------+-------+
| season   |  demographic_group | golds |
|----------+--------------------+-------|
| Winter   | Nam 26+            | 13    |
| Winter   | Nữ 26+             | 8     |
| Summer   | Nam 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;

Chuẩn bị

Bước 1: Ghi chú (comment) nửa dưới

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;*/

Chuẩn bị

Bước 2: Thêm chỗ trống cho trường mới

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;*/

Trường 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;

Trường 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   |
+---------+-------+

Trường 3: demographic_group

+--------+-------+--------------------+
| gender | age   | demographic_group  |
|--------+-------+--------------------|
| M      | 18    | Nam 13–25          |
| M      | 31    | Nam 26+            |
| F      | 22    | Nữ 13–25           |
| F      | 26    | Nữ 26+             |
+--------+-------+--------------------+

Câu lệnh CASE

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

Trường 3: demographic_group

SELECT 
    'Summer' AS season,
    CASE WHEN ___ THEN 'Nam 13-25'
    WHEN ___ THEN 'Nam 26+'
    WHEN ___ THEN 'Nữ 13-25'
    WHEN ___ THEN 'Nữ 26+' 
    END AS demographic_group, 
    SUM(gold) AS golds
FROM summer_games AS sg
JOIN athletes AS a 
ON sg.athlete_id = a.id;

Trường 3: demographic_group

SELECT 
    'Summer' AS season,
    CASE WHEN age >= 13 AND age <= 25 AND gender = 'M' THEN 'Nam 13-25'
    WHEN ___ THEN 'Nam 26+'
    WHEN ___ THEN 'Nữ 13-25'
    WHEN ___ THEN 'Nữ 26+' 
    END AS demographic_group, 
    SUM(gold) AS golds
FROM summer_games AS sg
JOIN athletes AS a 
ON sg.athlete_id = a.id;

Trường 3: demographic_group

SELECT 
    'Summer' AS season,
    CASE WHEN age >= 13 AND age <= 25 AND gender = 'M' THEN 'Nam 13-25'
    WHEN age > 25 AND gender = 'M' THEN 'Nam 26+'
    WHEN age >= 13 AND age <= 25 AND gender = 'F' THEN 'Nữ 13-25'
    WHEN age > 25 AND gender = 'F' THEN 'Nữ 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: Cột phải có trong mệnh đề GROUP BY.

Trường 3: demographic_group

SELECT 
    'Summer' AS season,
    CASE WHEN age >= 13 AND age <= 25 AND gender = 'M' THEN 'Nam 13-25'
    WHEN age > 25 AND gender = 'M' THEN 'Nam 26+'
    WHEN age >= 13 AND age <= 25 AND gender = 'F' THEN 'Nữ 13-25'
    WHEN age > 25 AND gender = 'F' THEN 'Nữ 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;

Chạy truy vấn thành công!

Trường 3: demographic_group

SELECT 
    'Summer' AS season,
    CASE WHEN age >= 13 AND age <= 25 AND gender = 'M' THEN 'Nam 13-25'
    WHEN age > 25 AND gender = 'M' THEN 'Nam 26+'
    WHEN age >= 13 AND age <= 25 AND gender = 'F' THEN 'Nữ 13-25'
    WHEN age > 25 AND gender = 'F' THEN 'Nữ 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;

Không có ELSE = dễ kiểm tra hơn

Trạng thái truy vấn mới

SELECT 
    'Summer' AS season,
    CASE WHEN age >= 13 AND age <= 25 AND gender = 'M' THEN 'Nam 13-25'
    WHEN age > 25 AND gender = 'M' THEN 'Nam 26+'
    WHEN age >= 13 AND age <= 25 AND gender = 'F' THEN 'Nữ 13-25'
    WHEN age > 25 AND gender = 'F' THEN 'Nữ 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;

Ayo berlatih!

Lập báo cáo trong SQL