Le 7 principali query SQL avanzate per l'analisi dei dati

Esplora le query SQL avanzate essenziali per l'analisi dei dati.

Il linguaggio di interrogazione strutturato, o SQL, è uno strumento indispensabile per gli analisti di dati. La maggior parte delle persone impara il linguaggio in tempi relativamente brevi e può iniziare ad analizzare i dati in modo approfondito dopo poche lezioni. Per questo motivo, molti analisti di dati tendono a rimanere nel livello principiante/intermedio di utilizzo di SQL. Questo livello di comprensione consente di "portare a termine il lavoro", ma potrebbe non essere il modo più efficiente di scrivere una query.

La padronanza di tecniche SQL avanzate può migliorare notevolmente le vostre capacità di analisi dei dati, consentendovi di approfondire i vostri set di dati e di ricavarne preziose informazioni. In questo articolo esploreremo sette query SQL avanzate che possono essere utili per l'analisi dei dati. Per ogni esempio, presenteremo la query e i suoi risultati, discuteremo la sintassi utilizzata e forniremo una breve spiegazione di come il database calcola il risultato.

Se desiderate migliorare le vostre competenze in SQL, non mancate di dare un'occhiata alla nostra traccia SQL Reporting. È un ottimo modo per imparare e praticare funzioni SQL più avanzate.

7 Advanced SQL Query che gli analisti di dati dovrebbero conoscere

1. Raggruppare i dati per periodo di tempo

In questo primo esempio, esaminiamo i dati della tabella sales. Questo set di dati include un ID per ogni prodotto, la data della transazione e l'importo totale della transazione.

Uno scenario di analisi dei dati molto comune è quello di prendere dati grezzi come questi e aggregarli per periodi di tempo specifici, come il mese o l'anno. Eseguiamo la seguente query:

SELECT
  EXTRACT(YEAR FROM sale_date) AS year, 
  EXTRACT(MONTH FROM sale_date) AS month,
 SUM(amount) AS total_sales
FROM sales
GROUP BY year, month
ORDER BY year, month;

Questa query restituirà questi risultati:

In questa query, utilizziamo la funzione EXTRACT() per estrarre l'anno e il mese dal campo sale_date. Raggruppiamo quindi i dati in base ai nuovi campi anno e mese e calcoliamo le vendite totali per ciascun gruppo utilizzando la funzione SUM().

Per ottenere risultati precisi, è necessario raggruppare sia per anno che per mese. Se inserissimo solo il mese, i risultati combinerebbero i valori di mesi specifici per tutti gli anni (quindi tutti i mesi di gennaio di tutti gli anni verrebbero combinati in un'unica riga, tutti i mesi di febbraio verrebbero combinati insieme e così via). Quando si raggruppano i risultati per anno e mese, i mesi di anni diversi vengono inseriti in righe separate.

Quindi ordiniamo le righe per anno e mese per avere risultati ordinati.

I diversi motori di database hanno spesso funzioni diverse e talvolta migliori per ottenere lo stesso risultato, come DATE_TRUNC() in PostgreSQL o TRUNC() in Oracle.

2. Creare più livelli di raggruppamento con ROLLUP

A volte si può desiderare di raggruppare i dati per più livelli in una singola query. Supponiamo di voler aggiungere all'esempio precedente il totale delle vendite (per tutti gli anni) e il totale delle vendite di ogni anno. È possibile aggiungere righe aggiuntive per il totale generale e i totali di ogni anno utilizzando la parola chiave ROLLUP.

Utilizzando lo stesso set di dati, eseguiremo questa query:

SELECT
 EXTRACT(YEAR FROM sale_date) AS year,
 EXTRACT(MONTH FROM sale_date) AS month,
 SUM(amount) AS total_sales
FROM sales
GROUP BY ROLLUP(year,month)
ORDER BY year, month;

Risultato:

Nel nostro esempio, ROLLUP aggiunge tre righe supplementari: una per il totale generale di tutte le vendite e due per il totale delle vendite di ogni anno. Nel nostro set di risultati, la terza riga è il totale annuale per il 2021: il valore nella colonna year è 2021 e il valore nella colonna month è NULL. La sesta riga è il totale per il 2022: il valore nella colonna anno è 2022 e il valore nella colonna month è NULL. L'ultima riga è il totale per entrambi gli anni: ha NULL sia nella colonna anno che nella colonna month. Queste righe sono state aggiunte da ROLLUP. I valori NULL nelle colonne anno e month indicano le righe aggregate.

3. Classificazione dei dati Utilizzo Window Functions

La classificazione dei dati è un requisito comune nell'analisi avanzata dei dati. Alcuni casi aziendali possono essere la classificazione dei prodotti in base alle vendite più elevate per capire quali sono i prodotti che generano maggiori entrate o la classificazione dei negozi in base alle vendite più basse per capire quali sono i negozi con le prestazioni più basse.

È possibile utilizzare funzioni della finestra come RANK() o DENSE_RANK() per assegnare i ranghi alle righe in base al valore di una colonna specifica. Per prima cosa, diamo un'occhiata alla funzione RANK():

SELECT
  product_id,
  SUM(amount) AS total_sales,
  RANK() OVER(ORDER BY SUM(amount) DESC) AS rank
FROM sales
GROUP BY id;

Risultato:

Questa query calcola prima il totale delle vendite per ogni product_id e poi le classifica in ordine decrescente. La funzione RANK() è una funzione finestra utilizzata per calcolare la classifica. La clausola OVER() è la sintassi utilizzata per le funzioni finestra. La funzione RANK() assegna un rango univoco a ciascuna riga all'interno di un insieme di risultati, in base a un ordinamento specifico. La clausola ORDER BY nella clausola OVER() specifica l'ordine di assegnazione del rango, in base a una o più colonne. Nel nostro esempio, classifichiamo le righe in base all'importo totale delle vendite.

La stessa sintassi può essere utilizzata per la funzione DENSE_RANK(). La differenza tra le due è nello scenario in cui due valori sono uguali. Se due valori si equivalgono per il primo posto, RANK() salterà a 3 per la terza riga, mentre DENSE_RANK() assegnerà la terza riga a 2.

Per ulteriori informazioni sulle funzioni di classificazione, leggete il nostro articolo di approfondimento Panoramica delle funzioni di classificazione in SQL.

È anche possibile filtrare le prime X righe utilizzando un'espressione di tabella comune (CTE) e una clausola WHERE. Ad esempio, è possibile eseguire la seguente query per estrarre solo le righe più basse:

WITH sales_cte AS (
  SELECT
    product_id,
    SUM(amount) AS total_sales,
    RANK() OVER (ORDER BY SUM(amount) DESC) AS rank
  FROM sales
  GROUP BY id 
) 
SELECT * 
FROM sales_cte
WHERE rank = 1;

In questa query, definiamo una CTE denominata sales_cteche fornisce il grado di ogni product_id in base alle vendite totali. Si esegue quindi una query su questa CTE, estraendo solo le righe con rango = 1. In questo modo è possibile restituire solo le righe più alte. Questo ci consente di restituire solo la riga superiore.

4. Calcolo della differenza (Delta) tra le righe

A volte è necessario confrontare i valori di righe consecutive. Ad esempio, è possibile estrarre le vendite del mese precedente per confrontarle con quelle del mese in corso. Se si dispone delle vendite del mese corrente e di quelle del mese precedente, è possibile calcolare la differenza tra i due valori.

A tale scopo si possono utilizzare funzioni della finestra come LEAD() e LAG(). Questa volta utilizzeremo una tabella diversa: sales_agg. Questa tabella contiene le vendite aggregate per ogni anno e mese.

Calcoliamo la differenza di vendite tra i mesi utilizzando LAG():

SELECT
  year, 
  month,
  total_sales,
  LAG(total_sales) OVER(ORDER BY year, month) AS previous_month,
  total_sales – LAG(total_sales) OVER(ORDER BY year, month) AS            sales_difference
FROM sales_agg
ORDER BY year, month;

Questa query utilizza la funzione LAG() per calcolare la differenza tra le vendite del mese corrente e quelle del mese precedente:

La funzione LAG() è una funzione finestra. Estrae il valore total_sales dalla riga precedente, ordinata per anno e mese. La colonna viene aliasata come previous_month.

La colonna sales_difference viene calcolata prendendo il valore total_sales per ogni riga e sottraendo il valore previous_month. La prima riga contiene i valori NULL, poiché non esiste una riga precedente.

Se si volesse estrarre le vendite del mese successivo, si utilizzerebbe la funzione LEAD() al posto di LAG(). LAG() confronta la riga corrente con i valori precedenti; LEAD() confronta la riga corrente con i valori successivi.

Come calcolare la differenza tra due righe in SQL fornisce diversi esempi reali di utilizzo di questa sintassi per calcolare la differenza tra due righe.

5. Calcolo di un totale progressivo

Un totale progressivo calcola la somma di una sequenza di numeri. È noto anche come totale cumulativo o somma cumulativa; aggiunge ogni nuovo valore al totale precedente.

I totali progressivi sono utili per calcolare l'effetto cumulativo dei punti di dati nel tempo. Ad esempio, si potrebbe voler calcolare il numero cumulativo di utenti che hanno visitato il sito web fino a un certo momento per capire la crescita degli utenti del sito.

Rivediamo la funzione SUM() e vediamo come utilizzarla per calcolare una somma progressiva delle vendite. Come nell'esempio precedente, dovremo utilizzare SUM() come funzione finestra per ottenere i risultati desiderati.

SELECT
  year,
  month,
  total_sales,
  SUM(total_sales) OVER(ORDER BY year, month) AS running_total
FROM sales_agg
ORDER BY year, month;

Il totale progressivo viene calcolato usando la funzione SUM() con la clausola OVER(). In questo modo si aggiunge il valore total_sales della riga corrente a tutte le righe precedenti nell'ordine specificato.

Nella seconda riga, il totale progressivo viene calcolato aggregando il valore total_sales della prima e della seconda riga. Nel nostro esempio, la seconda riga si riferisce a febbraio 2021. Il valore running_total è la somma della prima riga (per gennaio 2021) e della seconda riga (per febbraio 2021).

Nella terza riga, il totale progressivo viene calcolato aggregando i valori dalla prima alla terza riga. Lo stesso schema continua per ogni riga.

Per ulteriori informazioni sul calcolo dei totali progressivi in SQL, consultate il nostro articolo Cos'è un totale progressivo SQL e come si calcola?

6. Calcolo di una media mobile

Quando si analizzano le tendenze di vendita nell'analisi dei dati, è spesso utile utilizzare una media mobile piuttosto che ogni singolo punto di dati. Una media mobile (nota anche come media mobile) calcola la media del valore corrente e di un numero specifico di valori immediatamente precedenti.

Questa tecnica aiuta a smussare i dati e a identificare le tendenze, soprattutto quando i dati presentano un'elevata volatilità. L'idea principale è quella di esaminare il comportamento di queste medie nel tempo, invece di esaminare il comportamento dei punti dati originali.

Ad esempio, è possibile analizzare le vendite giornaliere di un ristorante che registra vendite elevate nel fine settimana ma basse dal lunedì al mercoledì. Se si tracciassero i singoli punti di dati, si vedrebbero valori molto alti e valori molto bassi uno accanto all'altro, rendendo più difficile individuare le tendenze a lungo termine. Utilizzando una media mobile a 3 giorni, si prende la media degli ultimi 3 giorni, che uniforma i massimi e i minimi.

Per questo esempio, modificheremo la tabella sales_agg.

Ora calcoliamo una media mobile a 3 mesi delle vendite:

SELECT
  year, 
  month, 
  total_sales, 
  AVG(total_sales) OVER(ORDER BY year, month ROWS BETWEEN 2 PRECEDING and CURRENT ROW) AS moving_average
FROM aales_agg
ORDER BY year, month;

Questa query calcola la media mobile utilizzando la funzione AVG() con la clausola OVER(). La clausola ROWS BETWEEN specifica l'intervallo di righe da includere nel calcolo della media mobile, in questo caso la riga corrente e le due righe precedenti.

Il risultato:

Poiché non ci sono righe precedenti, la media mobile della prima riga è solo il valore totale delle vendite. La media mobile della seconda riga è la media di 300 e 700. Per la terza riga, ora abbiamo le due righe precedenti, come definito nella nostra query; la media mobile è calcolata facendo la media di 300, 700 e 500. Questo schema continua per le altre righe.

Cos'è una media mobile e come calcolarla in SQL è un'ottima risorsa per ulteriori informazioni su questo argomento.

7. Conteggio degli elementi nelle categorie personalizzate con SUM() e CASE WHEN

È possibile contare gli elementi in categorie personalizzate combinando SUM() con CASE WHEN. Questo si usa quando si deve creare una logica di business che non esiste nei dati. Ad esempio, si potrebbe voler raggruppare località specifiche per regioni personalizzate e poi calcolare le metriche in base a tali regioni.

CASE WHEN Le dichiarazioni consentono di eseguire la logica condizionale nelle query. La sintassi è strutturata in questo modo:

CASE 
  WHEN condition_1 THEN result_1
  WHEN condition_2 THEN result_2
END

Le condizioni vengono valutate dall'alto verso il basso. La prima condizione valutata come vera determina il risultato restituito.

Nel nostro esempio, creiamo una suddivisione delle categorie di vendita in base agli importi delle vendite:

SELECT
  SUM(CASE WHEN total_sales < 400 THEN 1 ELSE 0 END) AS low_sales,  
  SUM(CASE WHEN total_sales >= 400 AND total_sales < 700 THEN 1 ELSE 0 END) AS medium_sales,
  SUM(CASE WHEN total_sales >= 700 THEN 1 ELSE 0 END) AS high_sales
FROM sales_agg

Nella query precedente, utilizziamo l'espressione CASE WHEN per classificare ogni mese nelle categorie low_sales, medium_sales o high_sales. Quando la condizione dell'espressione CASE WHEN è soddisfatta, restituisce il valore 1, altrimenti restituisce il valore 0. La funzione viene quindi utilizzata per creare una suddivisione delle categorie di vendita in base agli importi delle vendite.

La funzione SUM() viene quindi utilizzata per sommare gli 1 e gli 0 di ogni categoria, contando di fatto il numero di mesi che rientrano in ciascuna categoria. Il risultato è un'unica riga con i conteggi dei mesi di low_sales, medium_sales e high_sales.

Questo approccio consente di creare categorie personalizzate e di contare gli elementi di ciascuna categoria con un'unica query. La combinazione di SUM() e CASE WHEN è versatile e può essere adattata a diversi casi d'uso.

Se si desidera utilizzare questa struttura nelle proprie query, si consiglia di consultare la sezione Come utilizzare CASE WHEN con SUM() in SQL per maggiori dettagli.

Potenziare l'analisi dei dati con le query di Advanced SQL

La padronanza delle query SQL avanzate è essenziale per un'analisi efficiente e accurata dei dati. In questo articolo abbiamo illustrato sette potenti tecniche SQL che possono aiutarvi a ottenere approfondimenti sui vostri dati.

Imparando a raggruppare i dati per periodi di tempo, a utilizzare ROLLUP per più livelli di raggruppamento, a classificare i dati con le funzioni finestra, a calcolare le differenze tra le righe, a calcolare i totali progressivi e le medie mobili e a contare gli elementi in categorie personalizzate, sarete ben attrezzati per affrontare compiti complessi di analisi dei dati.

Continuando ad affinare le vostre competenze in SQL, troverete ancora più modi per sfruttare questo potente strumento per sbloccare il pieno potenziale dei vostri set di dati e migliorare il processo decisionale nella vostra organizzazione.

Per fare più pratica e acquisire un livello di comprensione ancora più approfondito di questi argomenti, iscrivetevi al nostro percorso SQL Reporting. È un ottimo modo per padroneggiare queste funzioni SQL avanzate.