Quando le cose si complicano con CASE

Manipolazione dei dati in SQL

Mona Khalil

Data Scientist, Greenhouse Software

Rivediamo CASE WHEN

SELECT 
    date,
    season,
    CASE WHEN home_goal > away_goal THEN 'Vittoria in casa!'
         WHEN home_goal < away_goal THEN 'Vittoria fuori casa!'
         ELSE 'Pareggio' END AS outcome
FROM match;

| date       | season    | outcome           |
|------------|-----------|-------------------|
| 2011-08-09 | 2011/2012 | Vittoria in casa! |
| 2011-09-01 | 2011/2012 | Vittoria fuori casa! |
| 2011-09-14 | 2011/2012 | Pareggio          |
| 2011-10-04 | 2011/2012 | Vittoria in casa! |

CASE WHEN ... E altro ancora

Aggiungi più condizioni logiche alla tua clausola WHEN!

SELECT date, hometeam_id, awayteam_id,
  CASE WHEN hometeam_id = 8455 AND home_goal > away_goal 
            THEN 'Vittoria in casa del Chelsea!'
       WHEN awayteam_id = 8455 AND home_goal < away_goal
            THEN 'Vittoria fuori casa del Chelsea!'
       ELSE 'Sconfitta o pareggio :(' END AS outcome
FROM match
WHERE hometeam_id = 8455 OR awayteam_id = 8455;

| date       | hometeam_id | awayteam_id | outcome                     |
|------------|-------------|-------------|-----------------------------|
| 2011-08-14 | 10194       | 8455        | Sconfitta o pareggio :(    |
| 2011-08-20 | 8455        | 8659        | Vittoria in casa del Chelsea! |
| 2011-08-27 | 8455        | 9850        | Vittoria in casa del Chelsea! |
| 2011-09-10 | 8472        | 8455        | Vittoria fuori casa del Chelsea! |

Cos'altro viene escluso?

Cosa c'è nella tua clausola ELSE?

SELECT date, hometeam_id, awayteam_id,
  CASE WHEN hometeam_id = 8455 AND home_goal > away_goal 
            THEN 'Vittoria in casa del Chelsea!'
       WHEN awayteam_id = 8455 AND home_goal < away_goal
            THEN 'Vittoria fuori casa del Chelsea!'
       ELSE 'Sconfitta o pareggio :(' END AS outcome
FROM match;

| date       | hometeam_id | awayteam_id | outcome        |
|------------|-------------|-------------|----------------|
| 2011-07-29 | 1773        | 8635        | Sconfitta o pareggio :( |
| 2011-07-30 | 9998        | 9985        | Sconfitta o pareggio :( |
| 2011-07-30 | 9987        | 9993        | Sconfitta o pareggio :( |
| 2011-07-30 | 9991        | 9984        | Sconfitta o pareggio :( |

Classifica correttamente i tuoi dati con CASE

SELECT date, hometeam_id, awayteam_id,
  CASE WHEN hometeam_id = 8455 AND home_goal > away_goal 
            THEN 'Vittoria in casa del Chelsea!'
       WHEN awayteam_id = 8455 AND home_goal < away_goal
            THEN 'Vittoria fuori casa del Chelsea!'
       ELSE 'Sconfitta o pareggio :(' END AS outcome
FROM match
WHERE hometeam_id = 8455 OR awayteam_id = 8455;

| date       | hometeam_id | awayteam_id | outcome                     |
|------------|-------------|-------------|-----------------------------|
| 2011-08-14 | 10194       | **8455**    | Sconfitta o pareggio :(    |
| 2011-08-20 | **8455**    | 8659        | Vittoria in casa del Chelsea! |
| 2011-08-27 | **8455**    | 9850        | Vittoria in casa del Chelsea! |
| 2011-09-10 | 8472        | **8455**    | Vittoria fuori casa del Chelsea! |

Cos'è NULL?

SELECT date,
CASE WHEN date > '2015-01-01' THEN 'Più recente'
     WHEN date < '2012-01-01' THEN 'Più vecchio' 
     END AS date_category
FROM match;

SELECT date,
CASE WHEN date > '2015-01-01' THEN 'Più recente'
     WHEN date < '2012-01-01' THEN 'Più vecchio' 
     ELSE NULL END AS date_category
FROM match;

| date       | date_category |
|------------|---------------|
| 2011-11-18 | Più vecchio   |
| 2012-02-11 | NULL          |
| 2014-11-07 | NULL          |
| 2015-02-14 | Più recente   |

Cosa fanno i tuoi valori NULL?

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

Dove posizionare il tuo CASE?

SELECT date, season,
  CASE WHEN hometeam_id = 8455 AND home_goal > away_goal 
            THEN 'Vittoria in casa del Chelsea!'
       WHEN awayteam_id = 8455 AND home_goal < away_goal
            THEN 'Vittoria fuori casa del Chelsea!' END AS outcome
FROM match;

Dove posizionare il tuo CASE?

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

| date       | season    | outcome                     |
|------------|-----------|-----------------------------|
| 2011-11-05 | 2011/2012 | Vittoria fuori casa del Chelsea! |
| 2011-11-26 | 2011/2012 | Vittoria in casa del Chelsea! |
| 2011-12-03 | 2011/2012 | Vittoria fuori casa del Chelsea! |

Ayo berlatih!

Manipolazione dei dati in SQL