칸아카데미>>SQL>>CASE

<< 칸아카데미의 CASE를 학습한 내용입니다. 칸선생님 감사합니다. by 해인>>

Calculating results with CASE

CREATE TABLE exercise_logs

    (id INTEGER PRIMARY KEY AUTOINCREMENT,

    type TEXT,

    minutes INTEGER, 

    calories INTEGER,

    heart_rate INTEGER);

INSERT INTO exercise_logs(type, minutes, calories, heart_rate) VALUES ("biking", 30, 100, 110);

INSERT INTO exercise_logs(type, minutes, calories, heart_rate) VALUES ("biking", 10, 30, 105);

INSERT INTO exercise_logs(type, minutes, calories, heart_rate) VALUES ("dancing", 15, 200, 120);

INSERT INTO exercise_logs(type, minutes, calories, heart_rate) VALUES ("dancing", 15, 165, 120);

INSERT INTO exercise_logs(type, minutes, calories, heart_rate) VALUES ("tree climbing", 30, 70, 90);

INSERT INTO exercise_logs(type, minutes, calories, heart_rate) VALUES ("tree climbing", 25, 72, 80);

INSERT INTO exercise_logs(type, minutes, calories, heart_rate) VALUES ("rowing", 30, 70, 90);

INSERT INTO exercise_logs(type, minutes, calories, heart_rate) VALUES ("hiking", 60, 80, 85);

SELECT * FROM exercise_logs;

SELECT COUNT(*) FROM exercise_logs WHERE heart_rate > 220 - 30;

/* 50-90% of max*/

SELECT COUNT(*) FROM exercise_logs WHERE

    heart_rate >= ROUND(0.50 * (220-30)) 

    AND  heart_rate <= ROUND(0.90 * (220-30));

    

/* CASE */

SELECT type, heart_rate,

    CASE 

        WHEN heart_rate > 220-30 THEN "above max"

        WHEN heart_rate > ROUND(0.90 * (220-30)) THEN "above target"

        WHEN heart_rate > ROUND(0.50 * (220-30)) THEN "within target"

        ELSE "below target"

    END as "hr_zone"

FROM exercise_logs;

SELECT COUNT(*),

    CASE 

        WHEN heart_rate > 220-30 THEN "above max"

        WHEN heart_rate > ROUND(0.90 * (220-30)) THEN "above target"

        WHEN heart_rate > ROUND(0.50 * (220-30)) THEN "within target"

        ELSE "below target"

    END as "hr_zone"

FROM exercise_logs

GROUP BY hr_zone;

<실행>

DATABASE SCHEMA

exercise_logs8 rows
id (PK)INTEGER
typeTEXT
minutesINTEGER
caloriesINTEGER
heart_rateINTEGER

RESULTS

id	type	minutes	calories	heart_rate
1	biking	30	100	110
2	biking	10	30	105
3	dancing	15	200	120
4	dancing	15	165	120
5	tree climbing	30	70	90
6	tree climbing	25	72	80
7	rowing	30	70	90
8	hiking	60	80	85

COUNT(*)
0

COUNT(*)
4

type	heart_rate	hr_zone
biking	110	within target
biking	105	within target
dancing	120	within target
dancing	120	within target
tree climbing	90	below target
tree climbing	80	below target
rowing	90	below target
hiking	85	below target

COUNT(*)	hr_zone
4	below target
4	within target