5 Costruzioni SQL avanzate che ogni analista di dati dovrebbe imparare

Il vostro lavoro prevede l'analisi dei dati? Ecco cinque esempi di come l'SQL avanzato può aiutarvi nel vostro lavoro quotidiano.

Sono un analista di dati e devo dire che gli analisti di dati possono essere piuttosto strani. Le altre persone di solito hanno paura di immense quantità di dati; a noi piace. Più ci addentriamo nei dati, più siamo felici. Uno strumento importante per entrare nei dati (e quindi per essere un analista di dati più felice) è SQL.

Ricordate quando avete scoperto per la prima volta le semplici funzioni SQL? La gioia che avete provato quando avete potuto selezionare rapidamente i dati, raggrupparli e ordinarli? Se ora avete una solida padronanza di queste funzioni di base, vi starete chiedendo che cos'è l'SQL avanzato e come può aiutarvi a raggiungere nuovi livelli nella gestione dei dati e nella creazione di report interessanti. Dopotutto, tutto lavoro e niente divertimento rendono gli analisti di dati dei ragazzi noiosi.

Non mi soffermerò sulla definizione. Il modo migliore per spiegare l'SQL avanzato è mostrarvi alcune query avanzate. Poi vedrete quanto possono essere utili (e divertenti).

Dato che utilizzerò molte funzioni della finestra SQL, è meglio che sappiate già cosa sono e come funzionano. Questo articolo vi permetterà di aggiornarvi se non le conoscete.

Query 1: classificazione dei dati

Alla direzione piace vedere le classifiche di qualsiasi cosa: prodotti venduti, stipendi, dipendenti per reparto, denaro guadagnato per ogni segmento immaginabile - chiederanno sempre di vederle classificate. Per mostrarvi un esempio di come classificare le cose in SQL, utilizzerò la tabella sales tabella. Essa ha le seguenti colonne:

• product - Il nome del prodotto.
• product_price - Il prezzo del prodotto.
• items_sold - Il numero di articoli venduti.

L'idea è quella di calcolare i ricavi per ogni prodotto e classificarli utilizzando la funzione RANK(). Il codice seguente risolve questo compito:

SELECT	product,
		product_price,
		items_sold,
		product_price * items_sold AS revenue,
		RANK() OVER (ORDER BY product_price * items_sold DESC) AS revenue_rank
FROM sales;

Il codice precedente seleziona tutte le colonne della tabella. Per ottenere i ricavi per prodotto, è necessario moltiplicare il prezzo per gli articoli venduti. Questo è esattamente ciò che farà la query e il risultato sarà mostrato nella nuova colonna revenue. Infine, c'è la funzione RANK(). Questa funzione classifica tutte le righe in base alla nuova colonna ricavi (definita da product_price * items_sold). La classifica verrà mostrata nella nuova colonna revenue_rank.

Ed ecco il risultato:

RANK() è solo un modo per classificare i dati in SQL. Potete vedere gli altri modi in questo articolo. E se volete approfondire l'argomento, ecco la nostra guida completa alle funzioni di classificazione.

Query 2: Calcolo dei valori delta

Oltre alla classificazione, il calcolo dei valori delta è probabilmente uno dei compiti più comuni degli analisti di dati. Di solito è necessario per calcolare le variazioni giornaliere, mese per mese, trimestre per trimestre o anno per anno. Che si tratti di ricavi, costi, variazioni di prezzo, variazioni di volume o qualsiasi altra cosa immaginabile, è necessario calcolare la differenza tra i numeri. Per farlo, è necessaria una query SQL avanzata con la funzione LAG(). Questa funzione viene utilizzata per recuperare i dati da una riga precedente. Vi mostriamo come funziona utilizzando la tabella revenue tabella. La tabella è composta da due righe:

• month - Il mese dell'anno.
• revenue - Il fatturato di quel mese.

Il vostro compito è quello di calcolare la differenza tra le entrate di ogni mese e quelle del mese precedente (cioè il delta mensile delle entrate). Come si fa? Se si conosce la funzione LAG(), è molto semplice. Ecco il codice:

SELECT	month,
		revenue,
		revenue - LAG(revenue, 1) OVER (ORDER BY month) AS monthly_delta
FROM revenue;

Il principio del calcolo del delta consiste nel sottrarre il mese precedente dal mese corrente. Il codice qui sopra fa esattamente questo. Innanzitutto, seleziona le colonne mese e ricavi. Dopodiché, deduce l'importo delle entrate del mese corrente dal mese precedente. Questa operazione è definita dalla funzione LAG(). I valori inseriti nella parentesi della funzione (revenue, 1) indicano che il valore della colonna entrate sarà dedotto dal valore precedente di quella colonna. Ecco perché c'è il numero 1: definisce quante righe la funzione torna indietro per eseguire l'operazione.

In teoria, LAG(revenue) farà la stessa cosa, poiché la funzione LAG() si basa su una riga indietro. Tuttavia, ho voluto mostrarvelo esplicitamente. È più facile da capire e saprete cosa fare quando dovrete tornare indietro di più di una riga.

I delta devono essere calcolati in modo sequenziale, non per mesi casuali; ecco perché esiste ORDER BY month. Il delta verrà mostrato nella nuova colonna monthly_delta. Eseguire il codice e ottenere la tabella risultante:

Query 3: Calcolo dei totali progressivi

I totali progressivi (noti anche come somme cumulative) sono molto utilizzati nell'analisi dei dati. Di solito si usano con i dati delle serie temporali per vedere come si sviluppano (o si svilupperanno) alcuni indicatori di performance nel tempo. Come altri concetti avanzati di SQL, i totali progressivi hanno un uso pratico molto ampio. Vengono utilizzati per monitorare vendite, ricavi, costi, profitti e budget. Ecco un articolo che spiega bene i totali progressivi e come calcolarli in SQL.

Per ora, vi mostrerò come funziona una somma cumulativa per i bilanci. Utilizziamo una tabella chiamata in modo molto fantasioso budget. È composta da queste colonne:

• month - Il mese del flusso di cassa.
• client - Il nome del cliente.
• cash_flow - Il flusso di cassa preventivato.

Ci sono tre clienti. Il budget contiene proiezioni mensili del flusso di cassa annuale che la vostra azienda incasserà da loro. È necessario calcolare il flusso di cassa cumulativo per ciascun cliente. Potreste già sapere intuitivamente che dovete usare la funzione SUM(), ma con una sorta di modifica. Ecco come calcolare i totali progressivi:

SELECT	month,
		client,
		cash_flow,
		SUM (cash_flow) OVER (PARTITION BY client ORDER BY month) AS running_total

FROM budget;

Questa è una semplice query che fa miracoli! Seleziona le colonne mese, cliente e cash_flow. Per calcolare il totale progressivo, è necessario riepilogare i flussi di cassa. Questo è definito da SUM (cash_flow).

Tuttavia, non si è interessati ad avere totali correnti a livello di tabella. È necessario riepilogare in qualche modo i flussi di cassa mese per mese per il primo cliente, quindi resettare e ricominciare per il secondo cliente. Per fare ciò, è necessario OVER (PARTITION BY client ORDER BY month). Qui la partizione è definita dal client della colonna, il che significa che ogni set di dati è definito dai diversi client. Inoltre, l'operazione verrà eseguita solo all'interno della partizione, non sull'intera tabella. In questo modo, si ottiene un totale corrente per ogni cliente separatamente.

Naturalmente, i flussi di cassa devono essere riepilogati in sequenza; ecco perché sono ordinati in base alla colonna del mese. Il totale corrente apparirà nella nuova colonna running_total.

Ecco la tabella risultante:

Negli ultimi tre esempi ho utilizzato le funzioni finestra. Se volete approfondire questo argomento, un buon modo è il corso Window Functions , uno dei nostri corsi avanzati di SQL.

Un'altra cosa che potrebbe essere molto utile, soprattutto se siete alle prime armi con le funzioni finestra o se le usate solo occasionalmente, è questo SQL Window Functions Cheat Sheet. Lo userò sicuramente la prossima volta che scriverò di funzioni finestra!

Query 4: Creare un report basato su più condizioni

Uno dei compiti principali degli analisti di dati è quello di rendere i dati più accessibili agli altri utenti. Fornendo loro i dati in una forma facilmente utilizzabile, facilitiamo il loro lavoro. Per creare report utili, un analista di dati deve combinare gli input aziendali con la propria conoscenza dei dati. Uno degli strumenti che possono aiutare a raggiungere questo obiettivo è l'istruzione CASE, un altro concetto SQL avanzato.

Per fare un esempio, immaginiamo il seguente scenario. Lavorate in una banca e i vostri colleghi vi chiedono di creare un rapporto. C'è una tabella chiamata debt che mostra i clienti della banca e i dettagli sui loro debiti. La tabella è composta dalle seguenti colonne:

• client - Il nome del cliente.
• date_due - Il giorno di scadenza del debito.
• amount_due - L'importo del debito dovuto.

È necessario creare un report al 30.4.2020. In qualche modo è necessario calcolare il numero di giorni di scadenza alla data del report. Inoltre, è necessario assegnare il cliente a un determinato bucket temporale, in base al numero di giorni di scadenza del suo conto.

La query è riportata di seguito. Non abbiate paura: la analizzerò per voi. Non è così spaventosa come sembra!

SELECT	client,
		date_due,
		amount_due,
		DATEDIFF ('2020-04-30', date_due) AS days_due,
		CASE
	WHEN  DATEDIFF ('2020-04-30', date_due) <= 30 THEN '0-30 days'
	WHEN  DATEDIFF ('2020-04-30', date_due) > 30 AND DATEDIFF ('2020-04-30', date_due) <=90 THEN '31-90 days'
	WHEN  DATEDIFF ('2020-04-30', date_due) > 90 AND DATEDIFF ('2020-04-30', date_due) <=180 THEN '91-180 days'
	WHEN  DATEDIFF ('2020-04-30', date_due) > 180 AND DATEDIFF ('2020-04-30', date_due) <=365 THEN '181-365 days'
	ELSE '> 365 days'
END AS time_bucket

FROM debt;

Innanzitutto, è necessario specificare la parte SELECT della query. Ho selezionato le colonne esistenti cliente, data_scadenza e importo_scadenza.

Poi bisogna calcolare i giorni di scadenza. Lo si fa sottraendo la data di scadenza dalla data di rendicontazione. Questo è esattamente ciò che ho fatto con DATEDIFF ('2020-04-30', date_due) AS days_due. Ho usato la funzione DATEDIFF() per calcolare la differenza richiesta. Quando si usa questa funzione, occorre innanzitutto specificare quali date si vogliono sottrarre. Nel nostro caso, si tratta della data di riferimento e della data di scadenza. Successivamente, è necessario specificare come si desidera visualizzare il risultato, ovvero in anni, mesi o giorni. In questo caso, abbiamo bisogno di giorni, quindi inseriamo giorno come ultimo valore in DATEDIFF().

Ora arriva la parte più interessante: la creazione delle condizioni che ho utilizzato nell'istruzione CASE. L'istruzione si apre con CASE e termina con END. Nel mezzo, è necessario definire le condizioni che creeranno il report desiderato dai colleghi. A tale scopo, si utilizzeranno WHEN e THEN.

Supponiamo che il primo bucket dei giorni di scadenza sia 0-30 giorni. La prima condizione nell'istruzione CASE è WHEN DATEDIFF ('2020-04-30', date_due) <= 30 THEN '0-30 days'. Poiché è necessario assegnare i clienti a un bucket temporale in base ai giorni di scadenza, questa parte del codice fa esattamente questo. Si legge così: se la differenza tra la data di segnalazione e la data di scadenza è inferiore o uguale a 30 giorni, il cliente verrà assegnato al bucket 0-30 giorni.

Il bucket successivo è 31-90 giorni e questa è la parte del codice che lo definisce:

WHEN DATEDIFF ('2020-04-30', date_due) > 30 AND DATEDIFF ('2020-04-30', date_due) <=90 THEN '31-90 days'

Non è così complicato, vero?

Lo stesso principio vale per gli altri due scaglioni di tempo: 91-180 giorni e 181-365 giorni. Ogni debito scaduto da più di 365 giorni appartiene al bucket 365 giorni. Questo è definito da ELSE '> 365 days'. Questo definisce semplicemente i criteri di segnalazione: se il valore è questo, fai questo; se non lo è, fai questo. In sostanza, si tratta di una versione più complessa dell'istruzione IF.

Si noti che esiste un modo più elegante per scrivere questo codice: Avrei potuto dichiarare una variabile contenente il valore '2020-04-30' invece di scrivere manualmente '2020-04-30' ovunque nel codice. Tuttavia, non volevo confondervi se non avete familiarità con le variabili.

Inoltre, per quanto riguarda la funzione DATEDIFF(), si noti che ho utilizzato la funzione e la sintassi di MySQL. A seconda del motore di database utilizzato, è possibile che si debba adattare la sintassi di conseguenza.

Tutti i time bucket saranno mostrati nella nuova colonna time_bucket. Poiché si vuole che i dati abbiano un aspetto gradevole, si ordinerà la tabella per giorni_di_scadenza in ordine crescente. Eseguite il codice e otterrete una bella tabella. E probabilmente un caffè gratis dai vostri colleghi!

Eccitante, vero? Se volete altre emozioni come questa, Creating Basic SQL Reports è quello che fa per voi! Qui imparerete di più su CASE WHEN e sulle sfumature di GROUP BY.

Query 5: Aggiunta di subtotali a un report

Una richiesta molto comune è quella di mostrare subtotali e totali nello stesso report. La clausola ROLLUP rende questo compito molto più semplice. Si tratta di un'estensione della clausola GROUP BY. Consente di aggiungere subtotali e totali generali ai dati.

Ecco come usare ROLLUP. Abbiamo una tabella warehouse con le seguenti colonne:

• warehouse - Il nome del magazzino.
• brand - Il marchio del prodotto.
• product - Il nome del prodotto.
• quantity - La quantità di questo prodotto nel magazzino.

Ci sono due marchi diversi con cinque prodotti tra loro. E ci sono due magazzini. Il vostro compito è quello di calcolare la quantità totale di prodotti per entrambi i marchi in entrambi i magazzini. È necessario anche il totale complessivo di tutti i prodotti in entrambi i magazzini. Infine, è necessario fare tutto in un'unica tabella con una sola query. Come si può fare? Il codice è:

SELECT	warehouse,
		brand,
		SUM (quantity) AS sum_product

FROM warehouse
GROUP BY ROLLUP (warehouse, brand);

Per prima cosa, si selezionano le colonne magazzino e marca dalla tabella. Si vuole anche la somma della colonna quantità, che verrà mostrata nella nuova tabella sum_product. Qual è il passo successivo? A questo punto entra in gioco ROLLUP! Si usa per ottenere i totali di più livelli di raggruppamento dei dati. La parte GROUP BY ROLLUP (warehouse, brand) farà esattamente questo. Raggrupperà i dati in base alle colonne magazzino e marca. Dopodiché, sommerà i dati in base a ciascun raggruppamento. Il risultato è:

La tabella contiene i totali dei marchi Brando e Ostap nei magazzini di Amsterdam e Berlino e un totale generale. Il subtotale per entrambi i prodotti nel magazzino di Amsterdam è mostrato nella prima riga con il valore del marchio NULL. È pari a 64 039, la somma delle due righe precedenti.

Successivamente, si possono vedere i totali per entrambi i marchi nel magazzino di Berlino. Dopo di che, c'è un'altra riga con il valore del marchio NULL; questo è in realtà il subtotale di Berlino, pari a 80 807. L'ultima riga mostra il totale complessivo di tutti i prodotti in tutti i magazzini, pari a 144 846.

Perché ci sono valori NULL in alcune righe? Perché SQL non sa come nominare i marchi e i magazzini quando sono raggruppati e viene mostrato un subtotale o un totale generale. Per maggiori dettagli sulle altre estensioni di GROUP BY, consultate il nostro corso sulle estensioni GROUP BY.

Questa è l'ultima query SQL avanzata per ora.

Questi argomenti di Advanced SQL vi sono stati utili?

Advanced SQL L'analisi dei dati, così come viene utilizzata da data scientist, analisti di dati e altri, è un argomento molto vasto. Le possibilità di utilizzo di SQL per l'analisi dei dati sono molto ampie. Questa traccia di Advanced SQL vi darà un'idea di ciò che potete fare con SQL come analisti di dati.

In questo articolo ho cercato di mostrare alcuni degli usi più comuni dell'SQL avanzato. Gli esempi sono pratici e tratti dalla mia esperienza, quindi spero che siano utili. Ho cercato di rendere le query il più semplice (e comprensibile) possibile. Potete adattare queste query ai vostri report e set di dati specifici. Sentitevi liberi di usarle!

Se avete domande o commenti, fatemelo sapere nella sezione commenti!