Modelli utili di SQL: Riassunto condizionale con CASE

Quando si inizia a codificare in SQL, si utilizzano ripetutamente alcune affermazioni e tecniche. Questi sono i cosiddetti "schemi SQL". In questa serie verranno analizzati i pattern SQL più comuni e si valuterà come utilizzarli.

In precedenza, abbiamo analizzato Il modello SQL di corrispondenza dei NULL. Questo è importante quando si confrontano colonne contenenti valori NULL. Oggi prenderemo in considerazione un'altra pratica SQL: la riepilogazione condizionale con l'operatore CASE.

Che cos'è il riassunto condizionale?

Quando si utilizzano funzioni aggregate per creare query di report, ci si trova spesso a utilizzare la riepilogazione condizionale con l'operatore CASE. Ricordate che CASE restituisce un valore basato su criteri definiti. (Per ulteriori informazioni sull'espressione CASE, vedere questo post e questo.) Quando si esegue un riepilogo con CASE, si sta semplicemente sommando (riassumendo) i valori che soddisfano l'espressione CASE.

Ovviamente, si utilizzerà la parte SUM della query per aggregare questi valori. So che sembra complicato, ma non è così. Utilizziamo un semplice esempio per spiegarlo.

Inizieremo considerando una tabella di base transactions che contiene i dati transazionali di una piccola azienda.

La tabella transactions tabella ha queste colonne:

• id - Un identificatore unico per ogni transazione
• datetime - Il timestamp della transazione
• customer - L'ID del cliente
• creditcard - L'ID del tipo di carta di credito utilizzata
• amount - L'importo della transazione, in dollari
• account - Il numero di conto del cliente
• type - Il tipo di transazione

Ecco i dati che troveremmo in una tabella come questa:

Vogliamo trovare la somma degli importi delle transazioni e il numero di transazioni completate prima del 1° aprile. Inoltre, vogliamo che questi dati siano elencati per singolo cliente. Possiamo ottenere questo risultato utilizzando la seguente query:

	SELECT
 customer,
 SUM(
 CASE WHEN datetime >= TIMESTAMP '2017-04-01'
 THEN amount
 ELSE 0
 END
 )        AS sum_amount_after,
 SUM(CASE WHEN datetime >= TIMESTAMP '2017-04-01'
 THEN 1
 ELSE 0
 END) AS transaction_count_after,
 SUM(CASE WHEN datetime < TIMESTAMP '2017-04-01'
 THEN amount
 ELSE 0
 END) AS sum_amount_prior,
 SUM(CASE WHEN datetime < TIMESTAMP '2017-04-01'
 THEN 1
 ELSE 0
 END) AS transaction_count_prior
 FROM transactions
 WHERE datetime BETWEEN TIMESTAMP '2017-01-01'  AND '2017-05-01'
 GROUP BY customer

Il risultato di questa query è:

Aspetta! Come ha funzionato?

Questa lunga query può essere fonte di confusione, quindi cerchiamo di scomporla un po'. Ci concentreremo prima sulla parte che riguarda la scadenza del 1° aprile (2017-04-01).

👾 [NUOVO] Cosa succede quando si combina il #CASE con le dichiarazioni di #modifica dei dati di #SQL? Scopritelo! ➽ https://t.co/7IuBoDT85z... pic.twitter.com/wNiDiv5hSa

- Vertabelo (@Vertabelo) 6 giugno 2017

Di seguito, esaminiamo l'importo della transazione per il cliente "1". Qualsiasi importo di transazione pubblicato prima del 01.04.2017 sarà impostato su "0". Chiameremo questa colonna amount_after.

SELECT
 customer,
 datetime,
 CASE WHEN datetime >= TIMESTAMP '2017-04-01'
  THEN amount
 ELSE 0
 END
  AS amount_after,
 amount
FROM transactions
WHERE datetime BETWEEN TIMESTAMP '2017-01-01' AND '2017-05-01'
      AND customer = '1'
ORDER BY datetime;

In alternativa, possiamo sostituire gli zeri mostrati nei risultati con un NULL nell'istruzione ELSE:

SELECT
 customer,
 datetime,
 CASE WHEN datetime >= TIMESTAMP '2017-04-01'
  THEN amount
 ELSE null
 END
  AS amount_after,
 amount
FROM transactions
WHERE datetime BETWEEN TIMESTAMP '2017-01-01' AND '2017-05-01'
      AND customer = '1'
ORDER BY datetime;

Se ora riepiloghiamo queste colonne, otterremo:

1. Il totale di tutte le transazioni per sum(amount).
2. La somma di tutte le transazioni registrate dopo il 01.04. Tutte le transazioni registrate prima del 01.04 sono impostate a zero (o NULL) per sum(amount_after).

Se si desidera contare quante transazioni sono state inviate dopo il 01.04, è possibile modificare la query e creare un'istruzione COUNT che utilizza la stessa CASE con NULL nella ELSE.

SELECT
  customer,
  count(CASE WHEN datetime >= TIMESTAMP '2017-04-01'
    THEN amount
        ELSE NULL
        END)
    AS count_after
FROM transactions
WHERE datetime BETWEEN TIMESTAMP '2017-01-01' AND '2017-05-01'
GROUP BY customer
ORDER BY customer;

Nota: questa query è molto veloce, poiché l'RDBMS deve accedere a una sola tabella. La creazione di query di aggregazione che utilizzano una sola tabella è un buon modo per ottenere risultati rapidamente.

Provate a utilizzare CASE con uno zero nell'istruzione COUNT. Qual è il risultato e perché? Ditecelo nella sezione commenti qui sotto.