當情況更複雜：CASE 登場

SQL 資料操作

Mona Khalil

Data Scientist, Greenhouse Software

複習 CASE WHEN

SELECT 
    date,
    season,
    CASE WHEN home_goal > away_goal THEN 'Home team win!'
         WHEN home_goal < away_goal THEN 'Away team win!'
         ELSE 'Tie' END AS outcome
FROM match;

| date       | season    | outcome           |
|------------|-----------|-------------------|
| 2011-08-09 | 2011/2012 | Home team win!    |
| 2011-09-01 | 2011/2012 | Away team win!    |
| 2011-09-14 | 2011/2012 | Tie               |
| 2011-10-04 | 2011/2012 | Home team win!    |

CASE WHEN…AND 再進一步

在 WHEN 子句加入多個邏輯條件！

SELECT date, hometeam_id, awayteam_id,
  CASE WHEN hometeam_id = 8455 AND home_goal > away_goal 
            THEN 'Chelsea home win!'
       WHEN awayteam_id = 8455 AND home_goal < away_goal
            THEN 'Chelsea away win!'
       ELSE 'Loss or tie :(' END AS outcome
FROM match
WHERE hometeam_id = 8455 OR awayteam_id = 8455;

| date       | hometeam_id | awayteam_id | outcome           |
|------------|-------------|-------------|-------------------|
| 2011-08-14 | 10194       | 8455        | Loss or tie :(    |
| 2011-08-20 | 8455        | 8659        | Chelsea home win! |
| 2011-08-27 | 8455        | 9850        | Chelsea home win! |
| 2011-09-10 | 8472        | 8455        | Chelsea away win! |

ELSE 排除了什麼？

你的 ELSE 子句會放什麼？

SELECT date, hometeam_id, awayteam_id,
  CASE WHEN hometeam_id = 8455 AND home_goal > away_goal 
            THEN 'Chelsea home win!'
       WHEN awayteam_id = 8455 AND home_goal < away_goal
            THEN 'Chelsea away win!'
       ELSE 'Loss or tie :(' END AS outcome
FROM match;

| date       | hometeam_id | awayteam_id | outcome        |
|------------|-------------|-------------|----------------|
| 2011-07-29 | 1773        | 8635        | Loss or tie :( |
| 2011-07-30 | 9998        | 9985        | Loss or tie :( |
| 2011-07-30 | 9987        | 9993        | Loss or tie :( |
| 2011-07-30 | 9991        | 9984        | Loss or tie :( |

用 CASE 正確分類資料

SELECT date, hometeam_id, awayteam_id,
  CASE WHEN hometeam_id = 8455 AND home_goal > away_goal 
            THEN 'Chelsea home win!'
       WHEN awayteam_id = 8455 AND home_goal < away_goal
            THEN 'Chelsea away win!'
       ELSE 'Loss or tie :(' END AS outcome
FROM match
WHERE hometeam_id = 8455 OR awayteam_id = 8455;

| date       | hometeam_id | awayteam_id | outcome           |
|------------|-------------|-------------|-------------------|
| 2011-08-14 | 10194       | **8455**    | Loss or tie :(    |
| 2011-08-20 | **8455**    | 8659        | Chelsea home win! |
| 2011-08-27 | **8455**    | 9850        | Chelsea home win! |
| 2011-09-10 | 8472        | **8455**    | Chelsea away win! |

什麼是 NULL？

SELECT date,
CASE WHEN date > '2015-01-01' THEN 'More Recently'
     WHEN date < '2012-01-01' THEN 'Older' 
     END AS date_category
FROM match;

SELECT date,
CASE WHEN date > '2015-01-01' THEN 'More Recently'
     WHEN date < '2012-01-01' THEN 'Older' 
     ELSE NULL END AS date_category
FROM match;

| date       | date_category |
|------------|---------------|
| 2011-11-18 | Older         |
| 2012-02-11 | NULL          |
| 2014-11-07 | NULL          |
| 2015-02-14 | More Recently |

你的 NULL 值在做什麼？

SELECT date, season,
  CASE WHEN hometeam_id = 8455 AND home_goal > away_goal 
            THEN 'Chelsea home win!'
       WHEN awayteam_id = 8455 AND home_goal < away_goal
            THEN 'Chelsea away win!'
       END AS outcome
FROM match
WHERE hometeam_id = 8455 OR awayteam_id = 8455;

| date       | season    | outcome           |
|------------|-----------|-------------------|
| 2011-08-14 | 2011/2012 | NULL              |
| 2011-12-22 | 2011/2012 | NULL              |
| 2012-12-08 | 2012/2013 | Chelsea away win! |
| 2013-03-02 | 2012/2013 | Chelsea home win! |

CASE 要放哪裡？

SELECT date, season,
  CASE WHEN hometeam_id = 8455 AND home_goal > away_goal 
            THEN 'Chelsea home win!'
       WHEN awayteam_id = 8455 AND home_goal < away_goal
            THEN 'Chelsea away win!' END AS outcome
FROM match;

CASE 要放哪裡？

SELECT date, season,
  CASE WHEN hometeam_id = 8455 AND home_goal > away_goal 
            THEN 'Chelsea home win!'
       WHEN awayteam_id = 8455 AND home_goal < away_goal
            THEN 'Chelsea away win!' END AS outcome
FROM match
WHERE CASE WHEN hometeam_id = 8455 AND home_goal > away_goal 
                THEN 'Chelsea home win!'
           WHEN awayteam_id = 8455 AND home_goal < away_goal
                THEN 'Chelsea away win!' END IS NOT NULL;

| date       | season    | outcome           |
|------------|-----------|-------------------|
| 2011-11-05 | 2011/2012 | Chelsea away win! |
| 2011-11-26 | 2011/2012 | Chelsea home win! |
| 2011-12-03 | 2011/2012 | Chelsea away win! |

一起來練習吧！

SQL 資料操作