Come utilizzare CASE WHEN in GROUP BY

Scoprite come combinare SQL CASE WHEN e GROUP BY per creare categorie personalizzate nelle vostre query SQL.

I dati grezzi, per loro stessa natura, non sono sempre leggibili. Spesso i dati da interrogare sono nella loro forma più informale. Ad esempio, i codici dei diversi reparti aziendali o gli SKU dei prodotti che rappresentano prodotti specifici. A occhio nudo, questi codici non significano nulla, quindi inserirli in un report non è utile per chi li legge.

Ci sono anche situazioni in cui i dati grezzi dovrebbero essere arrotolati a livelli superiori per renderli più facilmente digeribili. Ad esempio, un elenco di 50 prodotti può essere arrotolato a 5 categorie di prodotti, rendendo il report molto più facile da leggere e da capire.

In questo tipo di situazioni, possiamo usare le istruzioni CASE WHEN e GROUP BY per formattare i dati e aggiungere metadati al nostro set di dati originale. Nel corso di questo articolo analizzeremo il funzionamento di queste dichiarazioni. Per uno sguardo più approfondito su questi concetti, si consiglia di consultare il nostro sito web Come creare semplici report SQL che illustra tutte le sfumature di CASE e GROUP BY. È un ottimo seguito a questo articolo.

Per prima cosa, esaminiamo l'istruzione CASE WHEN con alcuni esempi. Se desiderate maggiori informazioni sull'istruzione CASE, leggete il nostro articolo Come usare CASE in SQL.

Breve rassegna di CASE WHEN

L'istruzione CASE WHEN può essere considerata come la logica if...then della query. Valuta le condizioni e, se le ritiene vere, restituisce un risultato specifico.

Ci sono tre elementi importanti per le istruzioni CASE in SQL: CASE WHEN, THEN, e END. Ognuno di questi elementi è necessario, altrimenti la query restituirà un errore.

Si inizia l'istruzione con CASE WHEN per definire la condizione logica. Poi si usa THEN per definire il valore se la condizione è vera. Dopo l'ultima istruzione THEN, si usa END per chiudere la clausola.

Un'altra clausola opzionale è la clausola ELSE. Se tutte le condizioni logiche dell'istruzione CASE WHEN falliscono, si può usare ELSE per assegnare un valore a questi dati. Si tratta in effetti di una clausola di richiamo. Se i dati non soddisfano tutte le condizioni e non si usa ELSE, i dati restituiranno un valore NULL.

Ecco come si presenta la sintassi di CASE WHEN:

CASE WHEN product = ‘Shirt’ THEN ‘Clothing’ 
     WHEN product = ‘Hat’ THEN ‘Accessories’ 
     ELSE ‘Other’
END 

Se il prodotto è una camicia, CASE WHEN la assegna alla categoria Clothing. Se il prodotto è un cappello, CASE WHEN lo assegna alla categoria Accessories. Altrimenti, CASE WHEN assegna il prodotto alla categoria Other.

Per illustrare ulteriormente la situazione, si considerino i seguenti dati trovati nella tabella cities tabella:

Se volessimo classificare la popolazione di ogni città in bassa, media o alta, eseguiremmo una query:

SELECT 
  city,
  population, 
  CASE 
   WHEN population < 500000 THEN ‘Low’ 
   WHEN population >= 500000 and population < 1500000 THEN ‘Medium’ 
   WHEN population >= 1500000 THEN ‘High’ 
  END as population_level
FROM cities

In questa query, aggiungiamo una logica condizionale basata sul campo population. Se la popolazione è inferiore a 500.000 abitanti, si assegna il valore Low. Se la popolazione è compresa tra 500.000 e 1.500.000 abitanti, si assegna il valore Medium. Infine, se la popolazione è superiore a 1.500.000, assegniamo il valore High. La colonna viene quindi aliasata come population_level. Il risultato restituito appare così:

E se volessimo trovare la popolazione media per ogni population_level? In questo scenario, possiamo ottenere questo risultato includendo una clausola di aggregazione e una clausola GROUP BY. Ne daremo una dimostrazione nella prossima sezione.

Uso di CASE WHEN con GROUP BY

Esempio 1: Aggregazioni di categorie personalizzate

Ora che abbiamo la nostra categoria personalizzata population_level, possiamo calcolare diverse metriche per essa. In questo esempio, calcoleremo la popolazione media per ogni population_level.

Utilizzeremo la stessa istruzione CASE WHEN di cui sopra, aggiungeremo un'aggregazione per la media e poi GROUP BY utilizzando la stessa sintassi.

Eseguiamo questa query:

SELECT  
  CASE 
   WHEN population < 500000 THEN ‘Low’ 
   WHEN population >= 500000 and population < 1500000 THEN ‘Medium’ 
   WHEN population >= 1500000 THEN ‘High’ 
  END as population_level, 
  AVG(population) as average_population
FROM cities
GROUP BY
  CASE 
   WHEN population < 500000 THEN ‘Low’ 
   WHEN population >= 500000 and population < 1500000 THEN ‘Medium’ 
   WHEN population >= 1500000 THEN ‘High’ 
  END

Abbiamo utilizzato la stessa categoria di popolazione della query precedente. Abbiamo aggiunto un calcolo per la popolazione media. Si noti che abbiamo ripetuto la stessa espressione CASE WHEN in SELECT e in GROUP BY.

Quando questa query viene eseguita, i dati vengono prima valutati nelle condizioni logiche dell'istruzione CASE WHEN e viene assegnato un valore a population_level. Quindi viene calcolata la media su ciascuno di questi livelli con l'espressione GROUP BY.

I risultati restituiti hanno questo aspetto:

Se avete bisogno di un ripasso sulla clausola GROUP BY, leggete questo articolo su GROUP BY in SQL.

Esempio 2: CASE WHEN con ELSE in GROUP BY

Un altro modo di scrivere questa query sarebbe quello di utilizzare la clausola ELSE. Si definiscono i primi due livelli di popolazione e poi si usa ELSE per inserire ogni altra città in high. Il risultato sarebbe la seguente query:

SELECT  
  CASE 
    WHEN population < 500000 THEN ‘Low’ 
    WHEN population >= 500000 and population < 1500000 THEN ‘Medium’ 
    ELSE ‘High’ 
  END as population_level, 
  AVG(population) as average_population
FROM cities
GROUP BY 
  CASE 
   WHEN population < 500000 THEN ‘Low’ 
   WHEN population >= 500000 and population < 1500000 THEN ‘Medium’ 
   WHEN population >= 1500000 THEN ‘High’ 
  END

Non è necessario definire la logica per le città ad alta popolazione; se la città non supera le prime due affermazioni logiche, allora la popolazione è superiore a 1.500.000 abitanti.

Esempio 3: Dichiarazioni CASE WHEN all'interno di aggregazioni

Possiamo anche inserire le dichiarazioni CASE WHEN all'interno di funzioni aggregate per contare il numero di record che soddisfano determinate condizioni. Continuando con lo stesso esempio, consideriamo come contare il numero di città in ogni population_level.

Per questo esempio avremo bisogno di tre istruzioni CASE WHEN separate, una per ogni condizione che abbiamo definito nella nostra istruzione CASE WHEN: low, medium e high.

SELECT  
  SUM(CASE 
      WHEN population < 500000 
      THEN 1 
      ELSE 0 END) as low_pop_ct,
  SUM(CASE 
      WHEN population >= 500000 and population < 1500000 
      THEN 1   
      ELSE 0 END) as medium_pop_ct, 
  SUM(CASE 
      WHEN population >= 1500000 
      THEN 1 
      ELSE 0 END) as high_pop_ct
FROM cities

Esaminiamo la situazione riga per riga. Nella prima riga, valutiamo se la città ha una popolazione inferiore a 500.000 abitanti. In caso affermativo, assegniamo il valore 1 alla città. In caso contrario, la clausola ELSE assegna il valore 0 alla città. Poi abbiamo avvolto l'intera istruzione CASE WHEN in una SUM(). In questo modo contiamo effettivamente le righe per le città con una popolazione bassa.

Ripetiamo questo schema nelle due righe successive, utilizzando le stesse condizioni logiche usate in precedenza per assegnare il valore di medium e high alle città in base alla loro popolazione.

Questa query restituirà:

Ora abbiamo un conteggio del numero di città che rientrano in ciascuna categoria.

Volete saperne di più? Consultate come utilizzare CASE WHEN con SUM() per ulteriori esempi.

Esempio 4: CASE WHEN in GROUP BY

Vediamo altri esempi di utilizzo di CASE WHEN in GROUP BY. I dati qui sotto sono contenuti nella tabella products.

Aggiungiamo a questi dati una nuova colonna che contenga product_category. Questo ci permetterà di raggruppare i singoli prodotti in una categoria di livello superiore, in modo da poter calcolare le aggregazioni.

Per aggiungere una colonna per product_category, eseguiamo:

SELECT *, 
  CASE WHEN description LIKE '%sweater%' 
         OR description LIKE '%blazer%' 
         OR description LIKE '%jacket%' THEN  'Outerwear'
       WHEN description LIKE '%dress%' 
         OR description LIKE '%jumpsuit%' THEN  'Dresses' 
       WHEN description LIKE '%shirt%' 
         OR description LIKE '%blouse%' THEN 'Tops'
  END as product_category 
FROM products

Utilizziamo l'operatore LIKE per cercare nel campo della descrizione ogni stringa tra virgolette. L'operatore % significa che può esserci qualsiasi cosa prima o dopo. In effetti, stiamo cercando in ogni descrizione una qualsiasi delle parole chiave elencate nelle dichiarazioni WHEN. I risultati sono mostrati qui sotto:

Ora che abbiamo il nostro nuovo campo product_category, possiamo contare il numero di prodotti in ogni categoria utilizzando GROUP BY:

SELECT
  CASE WHEN description LIKE '%sweater%' 
         OR description LIKE '%blazer%' 
         OR description LIKE '%jacket%' THEN  'Outerwear'
       WHEN description LIKE '%dress%' 
         OR description LIKE '%jumpsuit%' THEN  'Dresses' 
       WHEN description LIKE '%shirt%' 
         OR description LIKE '%blouse%' THEN 'Tops'
   END as product_category, 
   COUNT(DISTINCT description) as number_of_products
FROM products 
GROUP BY 
  CASE WHEN description LIKE '%sweater%' 
         OR description LIKE '%blazer%' 
         OR description LIKE '%jacket%' THEN  'Outerwear'
       WHEN description LIKE '%dress%' 
         OR description LIKE '%jumpsuit%' THEN  'Dresses' 
       WHEN description LIKE '%shirt%' 
         OR description LIKE '%blouse%' THEN 'Tops'
  END 

In questo esempio, abbiamo utilizzato CASE WHEN nella clausola SELECT, ma questo non è sempre necessario. Si può anche eseguire la query precedente senza questa clausola:

SELECT COUNT(DISTINCT description) as number_of_products
FROM products 
GROUP BY 
  CASE WHEN description LIKE '%sweater%' 
         OR description LIKE '%blazer%' 
         OR description LIKE '%jacket%' THEN  'Outerwear'
       WHEN description LIKE '%dress%' 
         OR description LIKE '%jumpsuit%' THEN  'Dresses' 
       WHEN description LIKE '%shirt%' 
         OR description LIKE '%blouse%' THEN 'Tops'
  END 

Cosa c'è di nuovo con CASE WHEN e GROUP BY?

Come si può vedere, ci sono molti scenari diversi in cui la combinazione delle istruzioni CASE WHEN e GROUP BY è estremamente utile. Esse consentono di aggiungere la logica aziendale ai dati e di calcolare le metriche in base ai nuovi campi di dati definiti. Le istruzioni CASE WHEN possono essere utilizzate sia al di fuori che all'interno delle aggregazioni; seguono la stessa sintassi. Iniziare l'istruzione con CASE WHEN per definire la logica condizionale e poi assegnare i valori con le istruzioni THEN/ELSE. Infine, chiudete il tutto con END.

Si consiglia di consultare il nostro corso su Come creare semplici report SQL. Imparerete tutte le sfumature di CASE e GROUP BY e avrete a disposizione problemi pratici da completare!