5 esempi pratici della clausola WITH di SQL

Questi cinque casi pratici vi faranno venire voglia di usare la clausola SQL WITH ogni giorno.

La clausola WITH può aiutare a scrivere query SQL leggibili e a suddividere calcoli complessi in passaggi logici. È stata aggiunta a SQL per semplificare le query lunghe e complicate. In questo articolo vi mostreremo 5 esempi pratici della clausola WITH e vi spiegheremo come il suo utilizzo renda le query SQL più leggibili.

La clausola WITH è anche chiamata Common Table Expression (CTE). Imparare a lavorare con questa clausola non è una conoscenza di base dell'SQL, quindi si consiglia di esercitarsi con WITH attraverso un corso interattivo strutturato, come il nostro Query ricorsive corso. In oltre 100 esercizi, imparerete la sintassi di base delle CTE e i concetti avanzati di CTE annidate e ricorsive.

Che cos'è la clausola WITH in SQL?

L'altro nome della clausola WITH, Common Table Expression (Espressione di tabella comune), fornisce un indizio su cosa fa. Una clausola WITH consente di creare un'istruzione SELECT che restituisce un risultato temporaneo; è possibile assegnare un nome a questo risultato e fare riferimento ad esso in un'altra query. In pratica, è una sottoquery con nome, ma può essere ricorsiva. Ecco come una clausola WITH si confronta con una subquery.

La CTE non può essere eseguita senza la query principale, quindi deve essere seguita da una query regolare. Anche questa query è di solito un'istruzione SELECT, ma può essere INSERT, UPDATE, o DELETE.

Si pensi a una CTE come a una tabella temporanea con una query che deve essere sempre eseguita perché la tabella possa essere utilizzata. Poiché si tratta di un tipo di tabella, è possibile fare riferimento alla CTE nella clausola FROM come a una normale tabella.

Sintassi di base della clausola WITH

Quanto spiegato sopra può essere mostrato come codice SQL:

WITH cte AS (
  SELECT …
  FROM table
)

SELECT …
FROM cte;

Vediamo le parti principali della sintassi. La CTE è sempre, senza eccezioni, iniziata dalla clausola WITH. Segue poi il nome della CTE, che nell'esempio generico sopra riportato è cte. Dopo il nome della CTE viene la parola chiave AS. Ciò che segue tra le parentesi è la definizione di CTE. In un esempio molto semplice, si tratta di una semplice istruzione SELECT. Infine, c'è la query principale (come per le normali sottoquery) che fa riferimento alla CTE.

Si tratta di una sintassi di base. La rivedremo negli esempi. Durante il percorso, si vedrà anche come questa sintassi di base possa essere estesa scrivendo CTE multiple o annidate.

Esempi 1 e 2: Informazioni sullo stipendio

Dati di esempio

Utilizzeremo la tabella employees nei primi due esempi. Ha le seguenti colonne:

• id - ID del dipendente.
• first_name - Il nome del dipendente.
• last_name - Il cognome del dipendente.
• salary - Lo stipendio del dipendente.
• department - Il reparto del dipendente.

Ecco i dati:

Ed ecco un link a una query da eseguire per creare la tabella:

Esempio 1: mostrare il confronto tra lo stipendio di ogni dipendente e la media dell'azienda.

Per risolvere questo problema, è necessario mostrare tutti i dati della tabella employees. Inoltre, è necessario mostrare il salario medio dell'azienda e poi la differenza con il salario di ciascun dipendente.

Ecco la soluzione:

WITH avg_total_salary AS (
	SELECT AVG(salary) AS average_company_salary
	FROM employees
)
	
SELECT id,
	 first_name,
	 last_name,
	 salary,
	 department,
	 average_company_salary,
	 salary - average_company_salary  AS salary_difference
FROM employees, avg_total_salary;

Per prima cosa, creare la CTE utilizzando la clausola WITH. Il nome della CTE ('avg_total_salary') viene dopo. Aprire le parentesi dopo AS e scrivere la normale istruzione SELECT. Essa calcola il salario medio dell'azienda.

Per utilizzare l'output della CTE, scrivere un'altra istruzione SELECT come query principale. Si noti che la CTE e la query principale sono separate solo da parentesi. La nuova riga è presente solo per motivi di leggibilità. La query principale seleziona tutte le colonne dalla tabella e dalla CTE. La CTE e la tabella employees sono unite come due tabelle normali. Inoltre, c'è la colonna calcolata salary_difference.

La query restituisce questo risultato:

Possiamo vedere i dati di tutti i dipendenti e quanto il loro stipendio è superiore o inferiore alla media aziendale. Ad esempio, lo stipendio di Melissa Allman è superiore di 293,48 alla media. Lo stipendio di Nina Longhetti è inferiore alla media di 993,20.

Esempio 2: mostrare i dati dei dipendenti insieme allo stipendio medio del reparto e dell'azienda

Facciamo un passo avanti rispetto all'esempio precedente. Questa volta non è necessario calcolare la differenza tra gli stipendi. Ma è necessario mostrare i dati analitici di tutti i dipendenti e poi mostrare lo stipendio medio del reparto e dell'azienda.

È possibile farlo scrivendo due CTE invece di una:

WITH avg_total_salary AS (
	SELECT AVG(salary) AS average_company_salary
	FROM employees
),
	
avg_dpt_salary AS (
	SELECT department,
		 AVG(salary) AS average_department_salary
	FROM employees
	GROUP BY department
)
	
SELECT e.id,
	   e.first_name,
	   e.last_name,
	   e.salary,
	   average_department_salary,
	   average_company_salary
FROM employees e JOIN avg_dpt_salary ads ON e.department = ads.department, avg_total_salary;

Ricordate che abbiamo detto che la sintassi di base può essere estesa. Ecco un esempio di come farlo. Sì, è possibile scrivere più CTE, una dopo l'altra, e poi fare riferimento ad esse nella query principale.

Vediamo come fare. Tutto è normale con la prima CTE: la clausola WITH, il nome della CTE, AS, e la definizione della CTE tra parentesi. Questa CTE calcola il salario medio dell'azienda.

La seconda CTE calcola la retribuzione media per reparto. Ma qui c'è qualcosa di diverso! Notate due cose fondamentali: non ci sono altre clausole WITH e le CTE sono separate da una virgola.

È così che si scrivono più CTE: la clausola WITH va scritta solo prima della prima query (non deve comparire prima di nessun'altra CTE!) e le CTE devono essere separate da una virgola.

Indipendentemente dal numero di CTE, queste devono sempre essere seguite dalla query principale. E non c'è una virgola tra l'ultima CTE e la query principale!

La query principale unisce la tabella con le due CTE e seleziona i dati rilevanti. L'unione si fa come con le tabelle normali: si specifica il tipo di unione e la colonna su cui le tabelle saranno unite.

Ecco l'output:

Ora abbiamo tutti i dati in un unico posto: stipendi individuali, media del reparto e media dell'azienda. Da qui è possibile proseguire nell'analisi dei dati.

Esempi 3 e 4: Entrate

Dati di esempio

Nei prossimi due esempi, useremo la tabella revenue. È stata creata con la query CREATE TABLE che si trova qui. Ha le seguenti colonne:

• id - L'ID del record delle entrate.
• year - L'anno delle entrate.
• quarter - Il trimestre del ricavo.
• revenue_amount - L'importo delle entrate.

Prendete confidenza con i dati mostrati di seguito:

Esempio 3: Mostrare ogni anno con le entrate annuali e le entrate totali corrispondenti

Utilizzate i dati precedenti per mostrare tutti gli anni disponibili. Insieme a ogni anno, mostrate le entrate annuali di quell'anno e le entrate totali dell'azienda in tutti gli anni.

Questo compito è simile all'Esempio 1, ma utilizzeremo una funzione di aggregazione diversa:

WITH total_revenue AS (
	SELECT SUM(revenue_amount) AS total_company_revenue
	FROM revenue
)
	
SELECT year,
	 SUM (revenue_amount) AS annual_revenue,
	 total_company_revenue
FROM revenue, total_revenue
GROUP BY year, total_company_revenue
ORDER BY year;

Il CTE utilizza la funzione aggregata SUM() per calcolare il fatturato totale dell'azienda.

La query principale unisce la CTE alla tabella revenue. Utilizziamo questa istruzione SELECT per mostrare l'anno, quindi calcoliamo le entrate annuali per ogni anno e mostriamo le entrate totali.

Questo è l'output della query:

Il risultato mostra che le entrate annuali nel 2019 sono state 10.489.632,27. Nel 2020 è stato di 20.919.087,62 e così via. Sommando tutti e quattro gli anni, il fatturato totale è di 80.812.015,89.

Esempio 4: Mostrare ogni trimestre insieme alle entrate trimestrali più alte e più basse per quell'anno e per l'intero periodo.

È necessario elencare tutti gli anni e i trimestri con i relativi ricavi. Fin qui tutto bene. Poi bisogna mostrare il ricavo trimestrale più basso per quell'anno e il ricavo trimestrale più basso per tutti gli anni. Successivamente, si deve fare lo stesso per il fatturato più alto.

Ecco la soluzione:

WITH yearly_min_max_quarter AS (
	SELECT year,
		   MIN(revenue_amount) AS minimum_quarterly_revenue_annual,
		   MAX(revenue_amount) AS maximum_quarterly_revenue_annual
FROM revenue
GROUP BY year),

min_max_overall AS (
	SELECT MIN(revenue_amount) AS overall_min_revenue,
	       MAX(revenue_amount) AS overall_max_revenue
FROM revenue)

SELECT r.year,
	   quarter,
	   revenue_amount,
	   minimum_quarterly_revenue_annual,
	   overall_min_revenue,
	   maximum_quarterly_revenue_annual, 
	   overall_max_revenue
FROM revenue r 
JOIN yearly_min_max_quarter ymmq 
ON r.year = ymmq.year, min_max_overall
ORDER BY year, quarter ASC;

Anche questa soluzione richiede due CTE. Probabilmente sapete già come scriverle, ma spieghiamo i singoli passaggi.

La prima CTE individua le entrate trimestrali più basse e più alte per ogni anno. A tale scopo, utilizzare le funzioni MIN() e MAX() e raggruppare i dati per anno.

Quindi scrivere la seconda CTE senza la clausola WITH e separarla con una virgola dalla prima. Questa CTE restituisce i ricavi trimestrali più bassi e più alti di tutti gli anni.

La query principale unisce la tabella revenue con la prima e poi con la seconda CTE. Mostrerà i dati della tabella e della CTE come un'unica tabella.

Osservare il risultato:

Oltre ai ricavi di ogni trimestre, ora si hanno altre informazioni importanti. Si sa che il fatturato più basso del 2019 è stato di 984.157,15, e si può notare che è avvenuto nel terzo trimestre. Il fatturato più basso del 2020 (2.497.441,68) è stato registrato nei primi tre mesi dell'anno. È possibile analizzare tutti gli altri anni in modo simile osservando la colonna minimum_quarterly_revenue_annual.

Il valore nella colonna overall_min_revenue rappresenta il ricavo più basso di tutti i tempi. È lo stesso in tutte le righe e corrisponde al 2019Q3. Le due colonne successive sono simili, ma mostrano le entrate più alte anziché quelle più basse. In altre parole, il ricavo più alto del 2019 è stato di 5.417.884,15, che corrisponde al quarto trimestre. Il ricavo più alto di tutti i tempi è 12.478.945,47, realizzato nel 2021Q1.

Esempio 5: Ore lavorate

Dati di esempio

La tabella dell'ultimo esempio si chiama foglio_orario_dipendente. Registra le ore di lavoro dei dipendenti. Le sue colonne sono autoesplicative, quindi ci limiteremo a osservare i dati:

Esempio 5: mostrare le ore di lavoro medie più basse e più alte

Questo esempio vuole prima trovare la media delle ore di lavoro per dipendente e poi mostrare solo la media più bassa e quella più alta.

Ecco il codice per risolvere questo problema:

WITH login_times AS (
SELECT 
id,
employee_id,
start_time,
end_time,
end_time - start_time AS working_hours
FROM employee_timesheet),

avg_login AS (
	SELECT 
employee_id,
AVG(working_hours) AS average_working_hours
FROM login_times
GROUP BY employee_id)

SELECT MIN(average_working_hours) AS min_average_working_hours,
	 MAX(average_working_hours) AS max_average_working_hours
FROM avg_login;

A prima vista, potrebbe sembrare una query qualsiasi con due CTE. Guardate meglio e vedrete che non è così! Sì, ci sono due CTE. Ma la differenza è che la seconda CTE fa riferimento alla prima, cosa che non accadeva negli esempi 2 e 4. Si tratta di una CTE annidata.

Si tratta di una CTE annidata. La prima CTE viene utilizzata per ottenere la differenza tra l'inizio e la fine del login; è così che si ottengono le ore di lavoro per ogni sessione.

Un dipendente ha più sessioni, quindi dobbiamo trovare la durata media della sessione, ovvero le ore medie lavorate. A questo scopo si utilizza la seconda CTE. In termini di sintassi, non c'è nulla di nuovo, a parte il fatto che la CTE fa riferimento alla prima CTE nella clausola FROM.

Poi, nella query principale, facciamo una cosa chiamata aggregazione multilivello. Prendiamo le ore di lavoro medie per dipendente (aggregazione di primo livello) e troviamo il minimo e il massimo di questi valori (aggregazione di secondo livello).

Ecco il risultato:

Il risultato ci dice che la media più bassa di ore lavorative per dipendente è di 5 ore, 57 minuti e 4 secondi. La media più alta è di 13:24:29.

Vantaggi della clausola SQL WITH

I cinque esempi che vi abbiamo mostrato sono stati accuratamente selezionati per illustrare l'uso tipico e i vantaggi della clausola WITH.

Il primo vantaggio diventa evidente quando ci sono diverse fasi di calcolo, come negli esempi precedenti. Utilizzando la clausola WITH, è possibile organizzare il codice e suddividerlo in parti logiche.

Quando i calcoli diventano più complicati, aumentano anche la lunghezza e la complessità del codice. L'uso della clausola WITH è ottimo per tenere sotto controllo questo aspetto. Sebbene i codici degli esempi precedenti possano sembrare lunghi, sarebbero molto più lunghi (e meno leggibili) se usassimo le subquery invece della clausola WITH. Inoltre, come si è visto nell'ultimo esempio, l'uso della clausola WITH consente di calcolare facilmente aggregazioni a più livelli.

Un altro vantaggio è che la clausola WITH consente di scrivere query ricorsive in SQL, il che apre un nuovo mondo di possibilità.

L'apprendimento della clausola WITH a volte può risultare travolgente, per questo abbiamo preparato una guida che vi aiuterà a strutturare il vostro approccio. Buon apprendimento e sappiamo che la clausola SQL WITH ripagherà i vostri sforzi!