Le CTE di MySQL e il loro utilizzo

Le espressioni di tabella comuni (CTE) in MySQL danno ancora più versatilità a questo popolare sistema di gestione di database. Scoprite come utilizzare le CTE per semplificare query complesse e implementare la ricorsione.

Tutti i linguaggi di programmazione hanno la possibilità di semplificare i problemi suddividendoli in parti che possono essere risolte singolarmente. Gli utenti possono unificare i risultati parziali per ottenere un unico risultato finale. Grazie alle cosiddette espressioni comuni di tabella (CTE), MySQL fa eccezione. Una CTE di MySQL consente di assegnare un nome a un insieme temporaneo di risultati e di riferirsi a tale insieme per nome (come se fosse una tabella o una vista) nelle istruzioni SELECT, INSERT, UPDATE, o DELETE.

Le CTE fanno parte dello standard SQL dal 1999. Tuttavia, la loro effettiva implementazione nei dialetti di ciascun sistema di gestione di database relazionali (RDBMS) è avvenuta gradualmente, man mano che gli utenti iniziavano a trovare utile questa nuova funzionalità. MySQL è stato uno degli ultimi RDBMS popolari a incorporare il supporto per le CTE. Solo nella versione 8, rilasciata nell'aprile 2018, il supporto per le CTE è apparso in MySQL.

Come vedremo di seguito, uno degli usi principali di MySQL CTE è quello di implementare la ricorsione. Se volete approfondire questo argomento, potete seguire il nostro corso interattivo sulle query ricorsive in MySQL. In questo corso, potrete acquisire tutte le conoscenze necessarie per padroneggiare le query ricorsive in MySQL 8. Tutto ciò di cui avete bisogno è un browser web, un computer e un computer. Tutto ciò che serve è un browser web, una connessione a Internet e la conoscenza dell'SQL di base (comprese le clausole JOIN e GROUP BY ). Ci sono 114 esercizi interattivi con suggerimenti e aiuto online per affinare le proprie capacità.

Per ora, però, concentriamoci sulle basi delle CTE di MySQL, iniziando dalla sintassi più semplice.

Sintassi di base di un CTE MySQL

La sintassi per scrivere una CTE MySQL inizia con la parola WITH, seguita dal nome della CTE e da un elenco delle colonne che la CTE restituirà. Questo elenco non è obbligatorio, poiché può essere lasciato definire dal risultato della query che definisce la CTE. Questa query deve essere scritta tra parentesi dopo la parola AS.

La sintassi di base delle espressioni di tabella comuni in MySQL può essere riassunta come segue:

WITH cte_name (column_list) AS (
    query
) 
SELECT * 
FROM cte_name;

Dopo la parentesi che racchiude la query CTE, abbiamo un'istruzione SQL convenzionale che si riferisce alla CTE con il suo nome, proprio come se fosse una vista. L'istruzione può essere un comando DML (Data Manipulation Language) come SELECT, INSERT, DELETE, o UPDATE.

Se volete una guida rapida alla sintassi o ai comandi di MySQL, mettete tra i preferiti il nostro MySQL Cheat Sheet gratuito. Vi aiuterà a rinfrescare la memoria sulle frasi comuni (e non) di MySQL.

Un CTE in MySQL che restituisce un singolo valore

Vediamo un esempio di CTE creato in un database MySQL. Utilizzeremo le informazioni sulle vendite e mostreremo la differenza tra l'importo di ogni vendita e l'importo medio della vendita.

Abbiamo una sales con informazioni dettagliate sulle vendite. Per questo esempio, siamo interessati a tre colonne: customer_id, sale_id, e amount. Ecco alcuni dati di esempio:

Creeremo una CTE chiamata sales_average che restituirà un singolo valore: la media di tutti i valori della colonna amount della tabella (cioè la media complessiva di tutti gli importi di vendita). sales (cioè la media complessiva di tutti gli importi di vendita). Nella SELECT esterna, quella che richiama la CTE, utilizzeremo il valore restituito dalla CTE per calcolare la differenza tra l'importo di ciascuna vendita e la media complessiva. Questo ci permetterà di identificare le vendite più significative:

WITH sales_average AS (
  SELECT AVG(amount) AS amount_avg
  FROM sales
)
SELECT 
  customer_id, 
  sale_id, 
  amount, 
  amount - amount_avg as difference
FROM sales, sales_average;

Quando MySQL incontra una query come quella sopra riportata, esegue innanzitutto la query nella clausola WITH e salva temporaneamente i risultati con il nome della CTE (sales_average). Quindi, esegue la clausola esterna SELECT utilizzando sales_average come una normale tabella; in questo modo si ottiene il set di risultati finale.

L'esecuzione dell'intera query con i dati di esempio sopra descritti restituisce i seguenti risultati:

CTE con funzioni di raggruppamento e aggregazione

Vediamo un esempio un po' più complesso: creeremo una query che utilizza una CTE per trovare le chiamate più vecchie in un call center. Abbiamo una tabella chiamata calls che include caller_id e call_time di ogni chiamata (oltre ad altri dati come la durata della chiamata che non includeremo qui). Queste sono alcune righe della tabella calls tabella:

La nostra CTE, chiamata first_contact, restituirà la data e l'ora della chiamata più vecchia di ogni chiamante. Nella pagina esterna SELECT, uniamo la CTE con la tabella calls per restituire tutti i dati di ogni chiamata più vecchia. In questo esempio (a differenza del precedente), i nomi delle colonne restituite dalla CTE saranno indicati nella sua dichiarazione:

WITH first_contact (caller_id, first_call_time) AS (
	SELECT 
          caller_id, 
          MIN(call_time) AS first_call_time
	FROM calls
	GROUP BY caller_id
)
SELECT c.*
FROM calls AS c
INNER JOIN first_contact AS fc 
ON fc.caller_id = c.caller_id 
  AND fc.first_call_time = c.call_time;

Per eseguire la query di cui sopra, MySQL otterrà innanzitutto i risultati della sottoquery con il nome first_contact. Questo memorizzerà temporaneamente tutti i valori distinti di caller_id insieme al più vecchio call_time di ciascuno. Poi, l'esterno SELECT unirà i risultati temporanei della precedente sottoquery con la tabella calls per ottenere tutti i dati dalla chiamata più vecchia di ciascun ID chiamante.

Questi sono i risultati della query:

Se state imparando a conoscere le CTE di MySQL per sostenere un colloquio di lavoro, leggete questa raccolta di domande di colloquio sulle CTE di SQL per capire cosa dovrete affrontare.

CTE multiple e annidate

In MySQL, una singola istruzione SQL può contenere più CTE. Indipendentemente dal numero di CTE definite in una query, la parola WITH viene usata solo una volta all'inizio dell'istruzione; le definizioni di CTE sono separate da virgole.

Utilizzo di più CTE in una singola query

Nell'esempio seguente, abbiamo una tabella chiamata customers che memorizza l'area in cui si trova ciascun cliente:

Supponiamo di essere interessati a utilizzare le informazioni contenute in questa tabella insieme alla tabella sales tabella. In particolare, vogliamo ottenere i totali delle vendite dei clienti appartenenti alle aree "OVEST" e "EST".

A tale scopo, definiremo due CTE che ci permetteranno di filtrare solo i clienti delle aree che ci interessano. Poi, nell'istruzione esterna SELECT, combineremo i dati delle due CTE con la tabella sales per ottenere le medie di vendita per ciascuna di queste aree:

WITH customers_west AS (
	SELECT *
	FROM customers
	WHERE area = 'WEST'),
customers_east AS (
	SELECT *
	FROM customers
	WHERE area = 'EAST')
SELECT 
   cw.area, 
   AVG(sw.amount) AS amount_avg
FROM customers_west AS cw
INNER JOIN sales sw 
ON sw.customer_id = cw.customer_id

UNION

SELECT 
  ce.area, 
  AVG(se.amount)
FROM customers_east AS ce
INNER JOIN sales se 
ON se.customer_id = ce.customer_id;

Ecco il risultato:

Annidamento delle CTE

MySQL consente anche di annidare le CTE, in modo che una CTE possa fare riferimento a una CTE definita in precedenza, il tutto all'interno di un'unica istruzione SQL.

La tecnica di annidamento delle CTE ci permette di suddividere query grandi e complesse in sottoquery più piccole e gestibili. Questo ci permette di avvicinarci gradualmente alla soluzione di un problema, riducendo alla fine il passaggio finale a una semplice SELECT.

Nell'esempio seguente, utilizziamo due CTE annidate per ottenere il totale delle vendite raggruppate per paese e regione. La tabella sales include una colonna Paese che indica il Paese in cui è stata effettuata la vendita.

La countries include una colonna regione che indica la regione geografica a cui appartiene ciascun paese:

Per ottenere i totali delle vendite per regione, utilizziamo due CTE che eseguono un approccio graduale alla soluzione finale:

WITH sales_by_country AS (
SELECT 
  country, 
  SUM(amount) AS total_sales_by_country
FROM sales
group BY country
),
sales_by_region AS (
SELECT 
  c.region, 
  SUM(s.total_sales_by_country) AS total_sales_by_region
FROM sales_by_country s
INNER JOIN countries c 
ON c.country = s.country
GROUP BY c.region
)
SELECT * 
FROM sales_by_region;

Nella query precedente, definiamo prima una CTE chiamata sales_by_country che raggruppa i dati per paese e restituisce il totale delle vendite di ciascun paese. Poi si definisce una seconda CTE chiamata sales_by_region che unisce la CTE sales_by_country con la tabella dei Paesi e raggruppa i totali delle vendite per regione. Infine, l'intera query viene risolta con un semplice SELECT dalla CTE sales_by_region.

Utilizzo delle CTE di MySQL con altri comandi DML

Gli esempi precedenti hanno utilizzato le CTE per risolvere complesse query SELECT, riducendone la complessità per risolverle con un semplice SELECT. Ma le CTE possono essere utilizzate anche con altri comandi. In questa sezione vedremo come utilizzare le CTE nei comandi INSERT e UPDATE.

Uso di una CTE con INSERT

Supponiamo di avere una tabella employees con le colonne empl_id (INT), empl_name (VARCHAR) e salary (DECIMAL). Poi abbiamo una tabella applicants con le stesse colonne di employees più la colonna aptitude (TINYINT) che indica se un candidato ha superato il test attitudinale (aptitude = 1) o meno (aptitude = 0). Solo i candidati che superano il test sono qualificati per diventare dipendenti.

Periodicamente, le righe della tabella applicants tabella che hanno il valore 1 nella colonna aptitude devono essere inserite nella tabella. employees tabella. A tale scopo, si utilizzerà una CTE chiamata qualified_applicants per filtrare i candidati che hanno superato il test attitudinale ma non sono ancora diventati dipendenti. I dati restituiti dalla CTE qualified_applicants sono i dati di origine per la CTE INSERT che li incorpora nella tabella. employees tabella.

INSERT INTO employees (empl_id, empl_name, salary)
WITH qualified_applicants (empl_id, empl_name, salary) AS (
SELECT 
    empl_id, 
    empl_name, 
    salary
FROM applicants AS a
WHERE a.aptitude = 1
AND NOT EXISTS 
       (SELECT * 
        FROM employees AS e 
        WHERE e.empl_id = a.empl_id)
)
SELECT 
  empl_id, 
  empl_name, 
  salary
FROM qualified_applicants;

Quando si usa una CTE in combinazione con una INSERT, tutto il codice della CTE, dalla clausola WITH alla clausola esterna SELECT, viene scritto dopo la riga INSERT INTO table (column1, column2, ...) dopo la riga. Per eseguire l'inserimento, MySQL eseguirà prima l'intera istruzione CTE e poi inserirà i risultati nella tabella specificata.

L'aspetto positivo dell'istruzione precedente è che può essere eseguita ripetutamente senza temere di creare dati duplicati o di violare una chiave primaria. Questo perché la condizione WHERE all'interno della definizione CTE include una clausola che impedisce a INSERT di tentare di reinserire dati già presenti nella tabella. employees tabella.

Uso di una CTE con UPDATE

Così come abbiamo usato una CTE di MySQL per inserire righe in una tabella, possiamo anche usare una CTE per aggiornare la tabella con nuove informazioni. Nell'esempio che segue, vedremo come utilizzare una CTE insieme a un comando UPDATE.

Utilizzeremo la stessa tabella employees tabella e una nuova salaries con le colonne empl_id (INT) e salary (DECIMALE). Questa tabella contiene gli stipendi aggiornati di ciascun dipendente. Il CTE restituirà le righe di salaries in cui lo stipendio del dipendente è più alto di quello della tabella employees tabella per lo stesso dipendente.

Ecco la query completa:

WITH raised_salaries (empl_id, salary) AS (
	SELECT s.empl_id, s.salary
	FROM salaries s
	INNER JOIN employees e 
      ON e.empl_id = s.empl_id
	WHERE s.salary > e.salary
)
UPDATE employees e
INNER JOIN raised_salaries rs 
ON rs.empl_id = e.empl_id
SET e.salary = rs.salary;

La sintassi richiesta per l'aggiornamento dei dati da una CTE segue la forma generale del comando MySQL UPDATE JOIN. Quando viene utilizzato insieme a una CTE, il comando UPDATE JOIN deve essere utilizzato in sostituzione del comando esterno SELECT della CTE. Il nome della CTE viene inserito nella clausola JOIN per associarla alla tabella da aggiornare.

Ovviamente, non è possibile aggiornare i campi della CTE, ma solo quelli delle tabelle unite alla CTE.

Come l'istruzione INSERT usata in precedenza, questa combinazione di CTE MySQL e di istruzione UPDATE può essere eseguita ripetutamente senza cambiare una sola lettera. Ogni volta che viene eseguita, aggiornerà solo le righe di employees con uno stipendio inferiore a quello indicato nella tabella salaries tabella.

CTE ricorsivi in MySQL

Oltre a semplificare e rendere più chiare le query complesse, le CTE consentono di implementare la ricorsione in MySQL. Potendo risolvere la ricorsione utilizzando solo istruzioni SQL, si evita di dover utilizzare altri linguaggi di programmazione. Questo ci permette di scrivere query più efficienti.

Le CTE ricorsive sono composte dai seguenti elementi:

• Un membro di ancoraggio che fornisce i valori iniziali della sequenza ricorsiva.
• Un membro ricorsivo che prende i risultati delle iterazioni precedenti e vi aggiunge nuove informazioni. I membri di ancoraggio e quelli ricorsivi sono combinati dalle clausole UNION.
• Una condizione di terminazione, che è una clausola WHERE (aggiunta al membro ricorsivo) che definisce la condizione che determinerà la fine del ciclo ricorsivo.
• Un'invocazione, ovvero l'esterno SELECT che fa riferimento al CTE ricorsivo per nome (come per qualsiasi altro CTE).

Informazioni più dettagliate sono disponibili in questo articolo sulle CTE ricorsive.

Un uso tipico delle funzioni ricorsive nella programmazione è la generazione di numeri di Fibonacci fino a un determinato valore. I numeri di Fibonacci sono utilizzati in ambiti diversi, come lo sviluppo di algoritmi di ricerca e la simulazione della crescita demografica. Nel seguente CTE MySQL ricorsivo, otteniamo i primi 10 numeri della sequenza di Fibonacci:

WITH RECURSIVE cte_fib AS (
SELECT 
  1 counter, 
  CAST(0 AS decimal) fibo, 
  CAST(0 AS decimal) prev

UNION ALL

SELECT 2, 1, 0

UNION ALL

SELECT 
  counter + 1, 
  fibo + prev, 
  fibo
FROM cte_fib
WHERE counter < 10
AND fibo > 0
)
SELECT counter, fibo
FROM cte_fib
ORDER BY counter;

Nell'esempio precedente, il membro di ancoraggio è formato dai due primi membri di UNION, perché forniscono gli elementi iniziali della serie di Fibonacci (le prime due righe dei dati risultanti):

SELECT 
  1 counter, 
  CAST(0 AS decimal) fibo, 
  CAST(0 AS decimal) prev

UNION ALL

SELECT 2, 1, 0

Il membro ricorsivo è la terza parte di UNION, perché aggiunge una riga ai dati restituiti dall'iterazione precedente dello stesso CTE:

SELECT 
  counter + 1, 
  fibo + prev, 
  fibo
FROM cte_fib
WHERE counter < 10
AND fibo > 0

La condizione di terminazione è la clausola WHERE all'interno della parentesi:

WHERE counter < 10
AND fibo > 0

Essa indica che la ricorsione deve interrompersi quando il contatore raggiunge il valore 10. E l'invocazione è la clausola SELECT fuori dalle parentesi, che restituisce il contatore e il numero di Fibonacci per ogni riga restituita dalla CTE.

Esistono molti altri usi delle CTE ricorsive in MySQL. Ad esempio, possono essere utilizzate per attraversare tabelle con informazioni ordinate gerarchicamente (ad esempio, organigrammi) o strutture di dati ad albero o a grafo. Possono anche essere utilizzate per compiti SQL non convenzionali, come disegnare un albero di Natale con i risultati di una query.

Le CTE di MySQL in breve

In questo articolo abbiamo fatto un giro delle CTE in MySQL. Abbiamo imparato che le espressioni di tabella comuni:

• Semplificano le query complesse suddividendole e organizzandole in parti più piccole e facili da capire. Questo migliora anche la leggibilità delle query.
• Ci aiutano a scrivere codice più efficiente. Una sottoquery definita come CTE può essere riutilizzata in diverse parti della stessa query, anche in altre sottoquery, senza doverla ripetere.
• Incapsulano la logica senza creare oggetti inutili. Le CTE incapsulano la logica delle sottoquery sotto un nome, proprio come le viste. Ma, a differenza delle viste, non implicano la creazione di oggetti permanenti nel database.
• Implementare la ricorsione. Questo è uno dei motivi principali della popolarità delle CTE, poiché senza di esse è piuttosto complicato implementare la ricorsione in MySQL.

A questo punto, dovreste avere un'idea di ciò che si può fare con le CTE ricorsive in MySQL. Dopo aver letto questo articolo, sarete pronti a consolidare le vostre conoscenze attraverso alcuni esercizi pratici sulle CTE.

Ma quanto visto in questo articolo è solo la punta dell'iceberg. Per scoprire tutto il potenziale delle CTE in MySQL, prendete in considerazione il corso LearnSQL.it Query ricorsive in MySQL. Con i suoi 114 esercizi interattivi, questo corso vi fornirà tutte le conoscenze necessarie per padroneggiare le query ricorsive in MySQL 8. Non è nemmeno necessario avere accesso a un server MySQL; l'ambiente del corso fornirà tutti gli strumenti necessari. Tutto ciò che serve è un browser web, una connessione a Internet e una conoscenza di base dell'SQL. Potete anche fare il quiz introduttivo gratuito per assicurarvi che il corso sia adatto alle vostre esigenze!

In conclusione, i CTE sono uno strumento potente in MySQL, in particolare quando si tiene conto dell'ordine e della leggibilità del codice SQL. Nei team multidisciplinari - dove ingegneri esperti di database SQL collaborano con analisti di dati, progettisti di database e DBA - i CTE sono una risorsa preziosa per aumentare la produttività del team e ottenere un software più manutenibile e duraturo.