Als CASE complexer wordt

Gegevens manipuleren in SQL

Mona Khalil

Data Scientist, Greenhouse Software

CASE WHEN herzien

SELECT 
    date,
    season,
    CASE WHEN home_goal > away_goal THEN 'Thuisteam wint!'
         WHEN home_goal < away_goal THEN 'Uitteam wint!'
         ELSE 'Gelijkspel' END AS outcome
FROM match;

| date       | season    | outcome           |
|------------|-----------|-------------------|
| 2011-08-09 | 2011/2012 | Thuisteam wint!   |
| 2011-09-01 | 2011/2012 | Uitteam wint!     |
| 2011-09-14 | 2011/2012 | Gelijkspel        |
| 2011-10-04 | 2011/2012 | Thuisteam wint!   |

CASE WHEN ... EN dan nog wat

Voeg meerdere logische voorwaarden toe aan je WHEN-clausule!

SELECT date, hometeam_id, awayteam_id,
  CASE WHEN hometeam_id = 8455 AND home_goal > away_goal 
            THEN 'Chelsea thuiswinst!'
       WHEN awayteam_id = 8455 AND home_goal < away_goal
            THEN 'Chelsea uitwinst!'
       ELSE 'Verlies of gelijkspel :(' END AS outcome
FROM match
WHERE hometeam_id = 8455 OR awayteam_id = 8455;

| date       | hometeam_id | awayteam_id | outcome           |
|------------|-------------|-------------|-------------------|
| 2011-08-14 | 10194       | 8455        | Verlies of gelijkspel :( |
| 2011-08-20 | 8455        | 8659        | Chelsea thuiswinst! |
| 2011-08-27 | 8455        | 9850        | Chelsea thuiswinst! |
| 2011-09-10 | 8472        | 8455        | Chelsea uitwinst! |

Wat wordt er uitgesloten in ELSE?

Wat staat er in je ELSE-clausule?

SELECT date, hometeam_id, awayteam_id,
  CASE WHEN hometeam_id = 8455 AND home_goal > away_goal 
            THEN 'Chelsea thuiswinst!'
       WHEN awayteam_id = 8455 AND home_goal < away_goal
            THEN 'Chelsea uitwinst!'
       ELSE 'Verlies of gelijkspel :(' END AS outcome
FROM match;

| date       | hometeam_id | awayteam_id | outcome        |
|------------|-------------|-------------|----------------|
| 2011-07-29 | 1773        | 8635        | Verlies of gelijkspel :( |
| 2011-07-30 | 9998        | 9985        | Verlies of gelijkspel :( |
| 2011-07-30 | 9987        | 9993        | Verlies of gelijkspel :( |
| 2011-07-30 | 9991        | 9984        | Verlies of gelijkspel :( |

Categoriseer je data correct met CASE

SELECT date, hometeam_id, awayteam_id,
  CASE WHEN hometeam_id = 8455 AND home_goal > away_goal 
            THEN 'Chelsea thuiswinst!'
       WHEN awayteam_id = 8455 AND home_goal < away_goal
            THEN 'Chelsea uitwinst!'
       ELSE 'Verlies of gelijkspel :(' END AS outcome
FROM match
WHERE hometeam_id = 8455 OR awayteam_id = 8455;

| date       | hometeam_id | awayteam_id | outcome           |
|------------|-------------|-------------|-------------------|
| 2011-08-14 | 10194       | **8455**    | Verlies of gelijkspel :( |
| 2011-08-20 | **8455**    | 8659        | Chelsea thuiswinst! |
| 2011-08-27 | **8455**    | 9850        | Chelsea thuiswinst! |
| 2011-09-10 | 8472        | **8455**    | Chelsea uitwinst! |

Wat is NULL?

SELECT date,
CASE WHEN date > '2015-01-01' THEN 'Recente'
     WHEN date < '2012-01-01' THEN 'Ouder' 
     END AS date_category
FROM match;

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

| date       | date_category |
|------------|---------------|
| 2011-11-18 | Ouder         |
| 2012-02-11 | NULL          |
| 2014-11-07 | NULL          |
| 2015-02-14 | Recente       |

Wat doen je NULL-waarden?

SELECT date, season,
  CASE WHEN hometeam_id = 8455 AND home_goal > away_goal 
            THEN 'Chelsea thuiswinst!'
       WHEN awayteam_id = 8455 AND home_goal < away_goal
            THEN 'Chelsea uitwinst!'
       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 uitwinst! |
| 2013-03-02 | 2012/2013 | Chelsea thuiswinst! |

Waar plaats je je CASE?

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

Waar plaats je je CASE?

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

| date       | season    | outcome           |
|------------|-----------|-------------------|
| 2011-11-05 | 2011/2012 | Chelsea uitwinst! |
| 2011-11-26 | 2011/2012 | Chelsea thuiswinst! |
| 2011-12-03 | 2011/2012 | Chelsea uitwinst! |

Laten we oefenen!

Gegevens manipuleren in SQL