17th Mar 2023 Tempo di lettura: 24 minuti

25 esempi di query SQL avanzate

Uno dei modi migliori per imparare l'SQL avanzato è studiare query di esempio. In questo articolo mostreremo 25 esempi di query SQL avanzate di media e alta complessità. Potete usarli per rinfrescare le vostre conoscenze di SQL avanzato o per ripassare prima di un colloquio SQL.

Molti degli esempi contenuti in questo articolo si basano sulla seguente tabella employee tabella. Solo alcuni esempi si baseranno su altre tabelle; in questi casi, le tabelle saranno spiegate insieme all'esempio.

employee_id	first_name	last_name	dept_id	manager_id	salary	expertise
100	John	White	IT	103	120000	Senior
101	Mary	Danner	Account	109	80000	junior
102	Ann	Lynn	Sales	107	140000	Semisenior
103	Peter	O'connor	IT	110	130000	Senior
106	Sue	Sanchez	Sales	107	110000	Junior
107	Marta	Doe	Sales	110	180000	Senior
109	Ann	Danner	Account	110	90000	Senior
110	Simon	Yang	CEO	null	250000	Senior
111	Juan	Graue	Sales	102	37000	Junior

Anche per le persone con esperienza di SQL, un buon corso interattivo online di SQL può essere di grande aiuto. La serie più completa di corsi interattivi di SQL si trova nel nostro percorso SQL dalla A alla Z. Contiene 7 corsi interattivi di SQL con oltre 850 (!) esercizi disposti in modo logico per condurvi da un principiante assoluto a un utente avanzato di SQL. I corsi per principianti coprono le basi di SQL e sono un modo perfetto per rivedere e rinfrescare le conoscenze di base di SQL. I corsi SQL avanzati vi insegneranno concetti come le funzioni finestra, le query ricorsive e i report SQL complessi. Create un account gratuito su LearnSQL.it e provate i nostri corsi interattivi senza dover spendere nulla. Poi, se vi piace quello che state imparando, potete acquistare l'accesso completo.

Ok, iniziamo con le query SQL avanzate!

25 esempi di query Advanced SQL con spiegazioni

Esempio n. 1 - Classificare le righe in base a uno specifico criterio di ordinamento

A volte è necessario creare una query SQL per mostrare una classifica di righe in base a un criterio d'ordine specifico. In questo esempio di query, mostreremo un elenco di tutti i dipendenti ordinati per stipendio (prima lo stipendio più alto). Il report includerà la posizione di ciascun dipendente nella classifica.

Ecco il codice:

SELECT 	
  employee_id, 
  last_name, 
  first_name, 
  salary, 
  RANK() OVER (ORDER BY salary DESC) as ranking
FROM employee
ORDER BY ranking

Nella query precedente, utilizziamo la funzione RANK(). Si tratta di una funzione a finestra che restituisce la posizione di ciascuna riga nell'insieme dei risultati, in base all'ordine definito nella clausola OVER (1 per lo stipendio più alto, 2 per il secondo più alto e così via). È necessario utilizzare una clausola di ranking ORDER BY alla fine della query per indicare l'ordine in cui verrà mostrato l'insieme dei risultati.

Se volete saperne di più sulle funzioni di ranking in SQL, vi consiglio il nostro articolo Cos'è la funzione RANK() in SQL e come si usa?

Esempio n. 2 - Elencare le prime 5 righe di un insieme di risultati

La prossima query SQL crea un report con i dati dei dipendenti per i primi 5 stipendi dell'azienda. Questo tipo di report deve essere ordinato in base a un determinato criterio; nel nostro esempio, il criterio d'ordine sarà ancora una volta salary DESC:

WITH employee_ranking AS (
  SELECT 
    employee_id, 
    last_name, 
    first_name, 
    salary,
    RANK() OVER (ORDER BY salary DESC) as ranking
  FROM employee
)
SELECT
  employee_id, 
  last_name, 
  first_name, 
  salary
FROM employee_ranking
WHERE ranking <= 5
ORDER BY ranking

La clausola WITH della query precedente crea una CTE chiamata employee_ranking, che è una sorta di tabella virtuale che viene consumata nella query principale. La sottoquery nella CTE utilizza la funzione RANK() per ottenere la posizione di ogni riga nella classifica. La clausola OVER (ORDER BY salary DESC) indica come deve essere calcolato il valore RANK(). La funzione RANK() per la riga con il salario più alto restituirà 1, e così via.

Infine, nella WHERE della query principale chiediamo le righe con un valore di ranking minore o uguale a 5. In questo modo otteniamo solo le prime 5 posizioni della classifica. In questo modo si ottengono solo le prime 5 righe per valore di ranking. Anche in questo caso, utilizziamo una clausola ORDER BY per mostrare l'insieme dei risultati, ordinati per rango crescente.

Esempio n. 3 - Elencare le ultime 5 righe di un insieme di risultati

Questa query è simile alla query top 5, ma vogliamo le ultime 5 righe. È sufficiente cambiare il tipo di ordine, cioè usare ASC invece di DESC. Nella CTE, creeremo una colonna di classificazione basata su un ordine ascendente di stipendio (prima lo stipendio più basso):

WITH employee_ranking AS (
  SELECT 
    employee_id, 
    last_name, 
    first_name, 
    salary,
    RANK() OVER (ORDER BY salary ASC) as ranking
  FROM employee
)
SELECT
  employee_id, 
  last_name, 
  first_name, 
  salary
FROM employee_ranking
WHERE ranking <= 5
ORDER BY ranking

Nella query principale, utilizziamo WHERE ranking <= 5 per filtrare le righe con i 5 stipendi più bassi. Successivamente, si utilizza ORDER BY ranking per ordinare le righe del report in base al valore della classifica.

Esempio 4 - Elencare la seconda riga più alta di un set di risultati

Supponiamo di voler ottenere i dati del dipendente con il secondo stipendio più alto dell'azienda. Possiamo applicare un approccio simile alla nostra query precedente:

WITH employee_ranking AS (
  SELECT 
    employee_id, 
    last_name, 
    first_name, 
    salary,
    RANK() OVER (ORDER BY salary DESC) as ranking
  FROM employee
)
SELECT 
  employee_id, 
  last_name, 
  first_name, 
  salary
FROM employee_ranking
WHERE ranking = 2

La condizione WHERE ranking = 2 è utilizzata per filtrare le righe con il salario in posizione 2. Si noti che è possibile avere più di un dipendente in posizione 2 se hanno lo stesso stipendio.

A questo punto, è importante capire il comportamento della funzione RANK() e delle altre funzioni disponibili, come ROW_NUMBER() e DENSE_RANK(). Questo argomento è trattato in dettaglio nella nostra panoramica sulle funzioni di classificazione in SQL. Vi consiglio vivamente di leggere questo articolo se avete bisogno di lavorare con diversi tipi di classifiche.

Esempio #5 - Elencare il secondo stipendio più alto per reparto

Aggiungiamo una variante alla query SQL precedente. Poiché ogni dipendente appartiene a un reparto, ora vogliamo un report che mostri l'ID del reparto e il nome del dipendente con il secondo stipendio più alto in questo reparto. Vogliamo un record per ogni reparto dell'azienda. Ecco la query:

WITH employee_ranking AS (
  SELECT 
    employee_id, 
    last_name, 
    first_name, 
    salary, 
    dept_id
    RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) as ranking
  FROM employee
)
SELECT
  dept_id, 
  employee_id, 
  last_name, 
  first_name, 
  salary
FROM employee_ranking
WHERE ranking = 2
ORDER BY dept_id, last_name

La principale modifica introdotta in questa query è la clausola PARTITION BY dept_id in OVER. Questa clausola raggruppa le righe che hanno lo stesso dept_id nome, ordinando le righe di ciascun gruppo in base allo stipendio DESC. Poi viene calcolata la funzione RANK() per ogni reparto.

Nella query principale, vengono restituiti i dati dept_id e i dati dei dipendenti che si trovano nella posizione 2 della loro classifica di reparto.

Per i lettori che vogliono saperne di più su come trovare l'ennesimariga più alta di un gruppo, consiglio di leggere l'articolo Come trovare l'ennesimo stipendio più alto per reparto con SQL.

Esempio #6 - Elencare il primo 50% di righe in un insieme di risultati

In alcuni casi, potremmo essere interessati a ottenere il primo 50% dell'insieme di risultati (o qualsiasi altra percentuale). Per questo tipo di report, esiste una funzione SQL chiamata NTILE() che riceve un parametro intero che indica il numero di sottoinsiemi in cui si desidera dividere l'intero set di risultati. Ad esempio NTILE(2) divide l'insieme dei risultati in 2 sottoinsiemi con la stessa quantità di elementi; per ogni riga restituisce un 1 o un 2 a seconda del sottoinsieme in cui si trova la riga.

Ecco la query:

WITH employee_ranking AS (
  SELECT 
    employee_id, 
    last_name, 
    first_name, 
    salary,
    NTILE(2) OVER (ORDER BY salary ) as ntile
  FROM employee
)
SELECT 
  employee_id, 
  last_name, 
  first_name, 
  salary
FROM employee_ranking
WHERE ntile = 1
ORDER BY salary

La query precedente restituisce solo le righe della prima metà di un report di dipendenti ordinati per stipendio in ordine crescente. Utilizziamo la condizione ntile = 1 per filtrare solo le righe nella prima metà del report. Se siete interessati alla funzione finestra NTILE(), consultate l'articolo Common SQL Window Functions: Using Partitions With Ranking Functions.

Esempio #7 - Elencare l'ultimo 25% di righe in un insieme di risultati

Come per la query precedente, in questo esempio utilizzeremo NTILE(4) per dividere l'insieme dei risultati in 4 sottoinsiemi; ogni sottoinsieme avrà il 25% dell'insieme totale dei risultati. Utilizzando la funzione NTILE(), genereremo una colonna chiamata ntile con i valori 1, 2, 3 e 4:

WITH employee_ranking AS (
  SELECT 
    employee_id, 
    last_name, 
    first_name, 
    salary,
    NTILE(4) OVER (ORDER BY salary) as ntile
  FROM employee
)
SELECT 
  employee_id, 
  last_name, 
  first_name, 
  salary
FROM employee_ranking
WHERE ntile = 4
ORDER BY salary

La condizione WHERE ntile = 4 filtra solo le righe dell'ultimo trimestre del report. L'ultima clausola ORDER BY salary ordina l'insieme dei risultati restituiti dalla query, mentre OVER (ORDER BY salary) ordina le righe prima di dividerle in 4 sottoinsiemi utilizzando NTILE(4).

Esempio #8 - Numerare le righe in un insieme di risultati

A volte si vuole creare una classifica che assegni a ogni riga un numero che ne indichi la posizione nella classifica: 1 alla prima riga, 2 alla seconda e così via. SQL offre alcuni modi per farlo. Se vogliamo una semplice sequenza di numeri da 1 a N, possiamo usare la funzione ROW_NUMBER(). Se invece vogliamo una classifica che permetta di avere due righe nella stessa posizione (cioè perché condividono lo stesso valore), possiamo usare la funzione RANK() o DENSE_RANK(). La seguente query crea un report in cui ogni riga ha un valore di posizione:

SELECT
  employee_id, 
  last_name, 
  first_name, 
  salary,
  ROW_NUMBER() OVER (ORDER BY employee_id) as ranking_position
FROM employee

Se volete conoscere le diverse funzioni di classificazione avanzate, vi consiglio di leggere l'articolo Panoramica delle funzioni di classificazione in SQL.

Esempio n. 9 - Elenco di tutte le combinazioni di righe di due tabelle

In alcuni casi, potrebbe essere necessario un join che includa tutte le possibili combinazioni di righe di due tabelle. Supponiamo di avere un'azienda alimentare che vende 3 tipi di cereali: corn flakes, corn flakes zuccherati e fiocchi di riso. Tutti questi cereali sono venduti in 3 diverse confezioni: 1 libbra, 3 libbre e 5 libbre. Poiché offriamo 3 prodotti in 3 diverse dimensioni di confezione, offriamo nove diverse combinazioni.

Abbiamo una product tabella con 3 record (corn flakes, corn flakes zuccherati e fiocchi di riso) e un'altra tabella chiamata box_size con 3 record, uno per 1 libbra e due record per 3 e 5 libbre, rispettivamente. Se vogliamo creare un report con il listino prezzi delle nostre nove combinazioni, possiamo utilizzare la seguente query:

SELECT
  grain.product_name,
  box_size.description, 
  grain.price_per_pound * box_size.box_weight
FROM product
CROSS JOIN	box_sizes

Il risultato della query sarà:

product	package_size	price
Corn flake	1 pound box	2.43
Corn flake	3 pound box	7.29
Corn flake	5 pound box	12.15
Sugared corn flake	1 pound box	2.85
Sugared corn flake	3 pound box	8.55
Sugared corn flake	5 pound box	14.25
Rice flake	1 pound box	1.98
Rice flake	3 pound box	5.94
Rice flake	5 pound box	9.90

La clausola CROSS JOIN senza alcuna condizione produce una tabella con tutte le combinazioni di righe di entrambe le tabelle. Si noti che il prezzo viene calcolato in base al prezzo per libbra memorizzato nella tabella product e il peso da box_sizes con l'espressione:

    grain.price_per_pound * box_size.box_weight

Un approfondimento su CROSS JOIN è disponibile in Guida illustrata alla funzione CROSS JOIN di SQL.

Esempio n. 10 - Unire una tabella a se stessa

In alcuni casi è necessario unire una tabella a se stessa. Pensate alla employee tabella. Ogni riga ha una colonna chiamata manager_id con l'ID del manager che supervisiona il dipendente. Utilizzando un self-join possiamo ottenere un report con le colonne employee_name e manager_name; questo ci mostrerà chi gestisce ogni dipendente. Ecco la query:

SELECT 	
  e1.first_name ||’ ‘|| e1.last_name AS manager_name,
  e2.first_name ||’ ‘|| e2.last_name AS employee_name
FROM employee e1
JOIN employee e2 
ON e1.employee_id = e2.manager_id

Nella query precedente, si può notare che la tabella employee viene citata due volte come e1 e e2, e la condizione di join è e1.employee_id = e2.manager_id. Questa condizione collega ogni riga di dipendente con la riga del manager. L'articolo Che cos'è una Self Join in SQL? Una spiegazione con sette esempi vi darà altre idee su quando è possibile applicare le auto-unioni nelle query SQL.

Esempio #11 - Mostrare tutte le righe con un valore superiore alla media

Abbiamo bisogno di un report che mostri tutti i dipendenti con uno stipendio superiore alla media aziendale. Possiamo prima creare una sottoquery per ottenere il salario medio dell'azienda e poi confrontare il salario di ogni dipendente con il risultato della sottoquery. Questo è mostrato nell'esempio seguente:

SELECT 
  first_name, 
  last_name, 
  salary
FROM employee  
WHERE salary > ( SELECT AVG(salary) FROM employee )

È possibile vedere la sottoquery che ottiene il salario medio nella clausola WHERE. Nella query principale, vengono selezionati il nome del dipendente e lo stipendio. Per ulteriori informazioni sulle subquery, consultare l'articolo Come esercitarsi con le subquery in SQL.

Esempio n. 12 - Dipendenti con stipendio superiore alla media del loro dipartimento

Supponiamo di voler ottenere i record dei dipendenti con stipendio superiore alla media dei loro reparti. Questa query è diversa dalla precedente, perché ora abbiamo bisogno di una sottoquery per ottenere lo stipendio medio del reparto del dipendente corrente, anziché dell'intera azienda. Si chiama sottoquery correlata perché all'interno della sottoquery c'è un riferimento a una colonna della riga corrente della tabella principale della query.

Ecco il codice:

SELECT
  first_name, 
  last_name, 
  salary
FROM employee e1 
WHERE salary > 
    (SELECT AVG(salary) 
     FROM employee e2 
     WHERE e1.departmet_id = e2.department_id)

Nella subquery, possiamo vedere un riferimento alla colonna e1.department_id, che è una colonna a cui si fa riferimento nella query principale. La condizione e1.departmet_id = e2.department_id è la chiave della subquery perché ci consente di ottenere la media di tutti i dipendenti del reparto della riga corrente. Una volta ottenuta la retribuzione media del reparto, la confrontiamo con la retribuzione del dipendente e filtriamo di conseguenza.

Esempio n. 13 - Ottenere tutte le righe in cui è presente un valore nel risultato di una sottoquery

Supponiamo che John Smith gestisca diversi reparti e che si voglia ottenere un elenco di tutti i dipendenti di tali reparti. Utilizzeremo una sottoquery per ottenere gli ID dei reparti gestiti da John Smith. Poi useremo l'operatore IN per trovare i dipendenti che lavorano in quei reparti:

SELECT 	
  first_name, 
  last_name
FROM employee e1 
WHERE department_id IN (
   SELECT department_id 
   FROM department
   WHERE manager_name=‘John Smith’)

La subquery precedente è una subquery a più righe: restituisce più di una riga. Infatti, restituirà più righe perché John Smith gestisce molti reparti. Quando si lavora con subquery a più righe, è necessario utilizzare operatori specifici (come IN) nella condizione WHERE che coinvolge la subquery.

Esempio #14 - Trovare righe duplicate in SQL

Se una tabella ha righe duplicate, è possibile trovarle con l'SQL. Utilizzare una query con una clausola GROUP BY che includa tutte le colonne della tabella e una clausola HAVING per filtrare le righe che compaiono più di una volta. Ecco un esempio:

SELECT 	
  employee_id, 
  last_name, 
  first_name, 
  dept_id, 
  manager_id, 
  salary
FROM employee
GROUP BY 	
  employee_id, 
  last_name, 
  first_name, 
  dept_id, 
  manager_id, 
  salary
HAVING COUNT(*) > 1

Le righe che non sono duplicate avranno un COUNT(*) uguale a 1, ma le righe che esistono più volte avranno un COUNT(*) che restituisce il numero di volte che la riga esiste. Suggerisco l'articolo Come trovare i valori duplicati in SQL se volete trovare maggiori dettagli su questa tecnica.

Esempio #15 - Conteggio delle righe duplicate

Se si desidera contare le righe duplicate, è possibile utilizzare la seguente query. È simile alla precedente, ma aggiungiamo un COUNT(*) nell'elenco SELECT per mostrare quante volte ogni riga duplicata appare nella tabella:

SELECT 	
  employee_id, 
  last_name, 
  first_name, 
  dept_id, 
  manager_id, 
  salary, 
  COUNT(*) AS number_of_rows
FROM employee
GROUP BY
  employee_id, 
  last_name, 
  first_name, 
  dept_id, 
  manager_id, 
  salary
HAVING COUNT(*) > 1

Anche in questo caso, potete trovare informazioni preziose su come gestire i record duplicati nell'articolo Come trovare i record duplicati in SQL.

Esempio #16 - Trovare i record comuni tra le tabelle

Se avete due tabelle con lo stesso schema o se due tabelle hanno un sottoinsieme di colonne in comune, potete ottenere le righe che compaiono in entrambe le tabelle con l'operatore set INTERSECT. Supponiamo di avere un'istantanea della tabella employee scattata nel gennaio 2020, chiamata employee_2020_jan e vogliamo ottenere l'elenco dei dipendenti presenti in entrambe le tabelle. Possiamo farlo con questa query:

SELECT 
  last_name, 
  first_name 
FROM employee
INTERSECT
SELECT 
  last_name, 
  first_name 
FROM employee_2020_jan

Come risultato, otterremo un elenco di dipendenti presenti in entrambe le tabelle. Forse avranno valori diversi nelle colonne salary o dept_id. In altre parole, otterremo i dipendenti che lavoravano per l'azienda nel gennaio 2020 e che stanno ancora lavorando per l'azienda.

Se siete interessati a saperne di più sugli operatori di set, vi suggerisco l'articolo Introduzione agli operatori di set di SQL: Union, Union All, Minus e Intersect.

Esempio #17 - Raggruppare i dati con ROLLUP

La clausola GROUP BY in SQL viene utilizzata per aggregare le righe in gruppi e applicare funzioni a tutte le righe del gruppo, restituendo un unico valore di risultato. Ad esempio, se si desidera ottenere un report con il totale degli stipendi per reparto e livello di competenza, è possibile eseguire la seguente query:

SELECT 	
  dept_id, 
  expertise, 
  SUM(salary) total_salary
FROM	employee
GROUP BY dept_id, expertise

GROUP BY ha la clausola opzionale ROLLUP, che consente di includere raggruppamenti aggiuntivi in una query. Aggiungendo la clausola ROLLUP al nostro esempio, potremmo ottenere la somma totale degli stipendi per ogni reparto (indipendentemente dal livello di competenza del dipendente) e la somma totale degli stipendi per l'intera tabella (indipendentemente dal reparto e dal livello di competenza del dipendente). La query modificata è:

SELECT 
  dept_id, 
  expertise, 
  SUM(salary) total_salary
FROM employee
GROUP BY ROLLUP (dept_id, expertise)

E il risultato sarà:

dept_id	expertise	total_salary
Account	Senior	90000
Account	Junior	80000
Account	NULL	170000
CEO	Senior	250000
CEO	NULL	250000
IT	Senior	250000
IT	NULL	250000
Sales	Junior	110000
Sales	Semisenior	140000
Sales	Senior	180000
Sales	NULL	430000
NULL	NULL	1100000

Le righe nel set di risultati con NULL sono le righe aggiuntive aggiunte dalla clausola ROLLUP. Un valore NULL nella colonna expertise indica un gruppo di righe per un valore specifico di dept_id, ma senza un valore specifico expertise. In altre parole, è l'importo totale degli stipendi per ogni dept_id. Allo stesso modo, l'ultima riga del risultato con NULL per le colonne dept_id e expertise indica il totale generale per tutti i reparti dell'azienda.

Per saperne di più sulla clausola ROLLUP e su altre clausole simili come CUBE, l'articolo Raggruppare, arrotolare e cubare i dati contiene molti esempi.

Esempio #18 - Somma condizionale

In alcuni casi, è necessario riassumere o contare i valori in base ad alcune condizioni. Ad esempio, se si vuole ottenere il totale degli stipendi dei reparti Vendite e Risorse umane combinati e dei reparti IT e Assistenza combinati, si può eseguire la seguente query:

SELECT 
  SUM (CASE
    WHEN dept_id IN (‘SALES’,’HUMAN RESOURCES’) 
    THEN salary
    ELSE 0 END) AS total_salary_sales_and_hr,
  SUM (CASE
    WHEN dept_id IN (‘IT’,’SUPPORT’) 
    THEN salary
    ELSE 0 END) AS total_salary_it_and_support
FROM employee

La query restituisce una singola riga con due colonne. La prima colonna mostra il totale degli stipendi dei reparti Vendite e Risorse umane. Questo valore viene calcolato utilizzando la funzione SUM() sulla colonna salary, ma solo quando il dipendente appartiene al reparto Vendite o Risorse umane. Quando il dipendente appartiene a qualsiasi altro reparto, alla somma viene aggiunto uno zero. La stessa idea viene applicata alla colonna total_salary_it_and_support.

Gli articoli Modelli utili di SQL: Riassunto condizionale con CASE e Come utilizzare CASE WHEN con SUM() in SQL forniscono maggiori dettagli su questa tecnica.

Esempio #19 - Raggruppare le righe in base a un intervallo

Nel prossimo esempio di query, creeremo gli intervalli salariali low, medium e high. Poi conteremo quanti dipendenti si trovano in ogni fascia salariale:

SELECT 
  CASE
    WHEN salary <= 750000 THEN ‘low’
    WHEN salary > 750000 AND salary <= 100000 THEN ‘medium’
    WHEN salary > 100000 THEN ‘high’
  END AS salary_category, 
  COUNT(*) AS number_of_employees
FROM	employee
GROUP BY 
  CASE
    WHEN salary <= 750000 THEN ‘low’
    WHEN salary > 750000 AND salary <= 100000 THEN ‘medium’
    WHEN salary > 100000 THEN ‘high’
END

In questa query, utilizziamo CASE per definire l'intervallo di stipendio di ciascun dipendente. Si può vedere la stessa istruzione CASE due volte. La prima definisce gli intervalli, come abbiamo appena detto; la seconda nel GROUP BY aggrega i record e applica la funzione COUNT(*) a ogni gruppo di record. È possibile utilizzare l'istruzione CASE nello stesso modo per calcolare conteggi o somme per altri livelli definiti in modo personalizzato.

Come usare CASE in SQL spiega altri esempi di istruzioni CASE come quella usata in questa query.

Esempio n. 20 - Calcolo di un totale progressivo in SQL

Il totale progressivo è un modello SQL molto comune, usato spesso in finanza e nell'analisi delle tendenze.

Quando si dispone di una tabella che memorizza una qualsiasi metrica giornaliera, come ad esempio una tabella sales con le colonne day e daily_amount, è possibile calcolare il totale corrente come somma cumulativa di tutti i valori daily_amount precedenti. SQL mette a disposizione una funzione chiamata SUM() per fare questo.

Nella seguente query, calcoleremo le vendite cumulative per ogni giorno:

SELECT 
  day,
  daily_amount,
  SUM (daily_amount) OVER (ORDER BY day) AS running_total
FROM sales

La funzione SUM() utilizza la clausola OVER() per definire l'ordine delle righe; tutte le righe precedenti al giorno corrente sono incluse nella SUM(). Ecco un risultato parziale:

day	daily_amount	running_total
Jan 30, 2023	1000.00	1000.00
Jan 31, 2023	800.00	1800.00
Feb 1, 2023	700.00	2500.00

Le prime due colonne day e daily_amount sono valori presi direttamente dalla tabella sales. La colonna running_total è calcolata dall'espressione:

SUM (daily_amount) OVER (order by day)

Si può vedere chiaramente come running_total sia la somma cumulativa dei precedenti daily_amounts.

Se volete approfondire questo argomento, vi suggerisco l'articolo Che cos'è un totale corrente in SQL e come si calcola?, che contiene molti esempi chiarificatori.

Esempio n. 21 - Calcolo di una media mobile in SQL

La media mobile è una tecnica di analisi delle serie temporali per analizzare le tendenze dei dati. Viene calcolata come media del valore corrente e di un numero specifico di valori immediatamente precedenti per ogni punto nel tempo. L'idea principale è quella di esaminare il comportamento di queste medie nel tempo, invece di esaminare il comportamento dei punti di dati originali o grezzi.

Calcoliamo la media mobile degli ultimi 7 giorni utilizzando la tabella dell'esempio precedente sales dell'esempio precedente:

SELECT 
  day,
  daily_amount,
  AVG (daily_amount) OVER (ORDER BY day ROWS 6 PRECEDING)
    AS moving_average
FROM sales

Nella query precedente, utilizziamo la funzione AVG() window per calcolare la media utilizzando la riga corrente (oggi) e le 6 righe precedenti. Poiché le righe sono ordinate per giorno, la riga corrente e le 6 righe precedenti definiscono un periodo di 1 settimana.

L'articolo Cos'è una media mobile e come calcolarla in SQL approfondisce questo argomento; consultatelo se volete saperne di più.

Esempio #22 - Calcolo della differenza (Delta) tra due colonne su righe diverse

Esiste più di un modo per calcolare la differenza tra due righe in SQL. Un modo per farlo è utilizzare le funzioni di finestra LEAD() e LAG(), come faremo in questo esempio.

Supponiamo di voler ottenere un report con il totale delle vendite di ogni giorno, ma di voler ottenere anche la differenza (o delta) rispetto al giorno precedente. Possiamo utilizzare una query come questa:

SELECT 
  day,
  daily_amount,
  daily_amount - LAG(daily_amount) OVER (ORDER BY day)
    AS delta_yesterday_today
FROM sales

L'espressione chiave di questa query è:

daily_amount - LAG(daily_amount) OVER (ORDER BY day)

Entrambi gli elementi della differenza aritmetica provengono da righe diverse. Il primo elemento proviene dalla riga corrente e LAG(daily_amount) proviene dalla riga del giorno precedente. LAG() restituisce il valore di qualsiasi colonna della riga precedente (in base a ORDER BY specificato nella clausola OVER ).

Se volete saperne di più su LAG() e LEAD(), vi suggerisco l'articolo Come calcolare la differenza tra due righe in SQL.

Esempio n. 23 - Calcolo della differenza tra un anno e l'altro

I confronti anno su anno (YOY) o mese su mese sono un modo popolare ed efficace per valutare le prestazioni di diversi tipi di organizzazioni. È possibile calcolare il confronto come valore o come percentuale.

In questo esempio, utilizzeremo la tabella sales che contiene dati con granularità giornaliera. Per prima cosa dobbiamo aggregare i dati all'anno o al mese, cosa che faremo creando una CTE con gli importi aggregati per anno. Ecco la query:

WITH year_metrics AS (
  SELECT 
    extract(year from day) as year,
    SUM(daily_amount) as year_amount
  FROM sales 
  GROUP BY year)
SELECT 
  year, 
  year_amount,
  LAG(year_amount) OVER (ORDER BY year) AS revenue_previous_year,
  year_amount - LAG(year_amount) OVER (ORDER BY year) as yoy_diff_value,
  ((year_amount - LAG(year_amount) OVER (ORDER BY year) ) /
     LAG(year_amount) OVER (ORDER BY year)) as yoy_diff_perc
FROM year_metrics
ORDER BY 1

La prima espressione da analizzare è quella utilizzata per calcolare yoy_diff_value:

year_amount - LAG(year_amount ) OVER (ORDER BY year)

Viene utilizzata per calcolare la differenza (come valore) tra l'importo dell'anno corrente e quello dell'anno precedente, utilizzando la funzione LAG() e ordinando i dati per anno.

Nell'espressione successiva, si calcola la stessa differenza in percentuale. Questo calcolo è un po' più complesso perché è necessario dividere per l'importo dell'anno precedente. (Nota: l'anno precedente viene utilizzato come base per il calcolo della percentuale, quindi l'anno precedente corrisponde al 100%).

((year_amount-LAG(year_amount ) OVER(ORDER BY year))/LAG(year_amount ) OVER(ORDER BY year))

Nell'articolo Come calcolare le differenze anno su anno in SQL sono riportati diversi esempi di calcolo delle differenze anno su anno e mese su mese.

Esempio n. 24 - Utilizzare Recursive Queries per gestire le gerarchie di dati

Alcune tabelle in SQL possono avere una gerarchia di dati implicita. Ad esempio, la nostra tabella employee ha una tabella manager_id per ogni dipendente. Abbiamo un manager che è responsabile di altri manager, che a loro volta hanno altri dipendenti sotto la loro responsabilità, e così via.

Quando abbiamo questo tipo di organizzazione, possiamo avere una gerarchia di vari livelli. In ogni riga, la colonna manager_id si riferisce alla riga del livello immediatamente superiore della gerarchia. In questi casi, una richiesta frequente è quella di ottenere un elenco di tutti i dipendenti che fanno capo (direttamente o indirettamente) all'amministratore delegato dell'azienda (che, in questo caso, ha la employee_id di 110). La query da utilizzare è:

WITH RECURSIVE subordinate AS (
 SELECT  
   employee_id,
   first_name,
   last_name,
   manager_id
  FROM employee
  WHERE employee_id = 110 -- id of the top hierarchy employee (CEO)
 
  UNION ALL
 
  SELECT  
    e.employee_id,
    e.first_name,
    e.last_name,
    e.manager_id
  FROM employee e 
  JOIN subordinate s 
  ON e.manager_id = s.employee_id
)
SELECT 	
  employee_id,
  first_name,
  last_name,
  manager_id
FROM subordinate ;

In questa query, abbiamo creato una CTE ricorsiva chiamata subordinate. È la parte fondamentale di questa query perché attraversa la gerarchia dei dati passando da una riga alle righe della gerarchia immediatamente inferiore.

Ci sono due sottoquery collegate da un UNION ALL; la prima sottoquery restituisce la riga superiore della gerarchia e la seconda query restituisce il livello successivo, aggiungendo tali righe al risultato intermedio della query. Quindi la seconda subquery viene eseguita di nuovo per restituire il livello successivo, che verrà nuovamente aggiunto al risultato intermedio della query. Questo processo viene ripetuto finché non vengono aggiunte nuove righe al risultato intermedio.

Infine, la query principale consuma i dati della CTE subordinate e restituisce i dati nel modo previsto. Se volete saperne di più sulle query ricorsive in SQL, vi suggerisco l'articolo Come trovare tutti i dipendenti sotto ogni manager in SQL.

Esempio #25 - Trovare la lunghezza di una serie utilizzando Window Functions

Supponiamo di avere una tabella con i dati di registrazione degli utenti. Vengono memorizzate le informazioni sul numero di utenti registrati in ogni data. Definiamo una serie di dati come la sequenza di giorni consecutivi in cui gli utenti si sono registrati. Un giorno in cui nessun utente si registra interrompe la serie di dati. Per ogni serie di dati, vogliamo trovare la sua lunghezza.

La tabella seguente mostra le serie di dati:

id	day	Registered users
1	Jan 25 2023	51
2	Jan 26 2023	46
3	Jan 27 2023	41
4	Jan 30 2023	59
5	Jan 31 2023	73
6	Feb 1 2023	34
7	Feb 2 2023	56
8	Feb 4 2023	34

Ci sono 3 diverse serie di dati mostrate con colori diversi. Stiamo cercando una query per ottenere la lunghezza di ciascuna serie di dati. La prima serie di dati inizia il 25 gennaio e ha una lunghezza di 3 elementi, la seconda inizia il 30 gennaio e la sua lunghezza è 4, e così via.

La query è la seguente:

WITH data_series AS (
  SELECT  	
    RANK() OVER (ORDER BY day) AS row_number,
    day, 
    day - RANK() OVER (ORDER BY day) AS series_id
 FROM	user_registration )
SELECT	
  MIN(day) AS series_start_day,
  MAX(day) AS series_end_day,
  MAX(day) - MIN (day) + 1 AS series_length
FROM	data_series
GROUP BY series_id
ORDER BY series_start_date

Nella query precedente, la CTE ha la colonna series_id, che è un valore destinato a essere utilizzato come ID per le righe della stessa serie di dati. Nella query principale, la clausola GROUP BY series_id viene utilizzata per aggregare le righe della stessa serie di dati. Quindi si può ottenere l'inizio della serie con MIN(day) e la sua fine con MAX(day). La lunghezza della serie viene calcolata con l'espressione:

      MAX(day) - MIN (day) + 1

Se volete approfondire questo argomento, l'articolo Come calcolare la lunghezza di una serie con SQL fornisce una spiegazione dettagliata di questa tecnica.

Esercitarsi su Advanced SQL con i corsi di LearnSQL.com

SQL è un linguaggio facile da imparare e potente. In questo articolo abbiamo mostrato 25 esempi di query SQL avanzate. Tutti possono essere spiegati in circa 5 minuti, a dimostrazione del fatto che SQL è un linguaggio accessibile anche quando si devono realizzare report o query complesse.

Se volete continuare a imparare l'SQL, vi suggerisco i nostri corsi di SQL avanzato: Window Functions (Funzioni Finestra), Query ricorsive, e Istruzione GROUP BY in SQL ed estensioni. Tutti coprono aree complesse del linguaggio SQL con parole semplici e numerosi esempi. Aumentate le vostre competenze e investite in voi stessi con SQL!

Tags: