Avec CASE, les choses deviennent plus complexes

Manipulation de données en SQL

Mona Khalil

Data Scientist, Greenhouse Software

Révision 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, et bien plus encore

Ajouter plusieurs conditions logiques à votre clause 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! |

Quelles autres (ELSE) informations sont exclues ?

Que contient votre clause 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 :( |

Classer correctement les données avec 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! |

Qu'est-ce que 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 |

Que font les valeurs 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! |

Où placer 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;

Où placer 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! |

Passons à la pratique !

Manipulation de données en SQL