Che cos'è una CTE ricorsiva in SQL?

L'articolo che vi mostrerà esempi pratici di utilizzo delle CTE ricorsive in SQL.

Se avete sentito parlare delle CTE ricorsive di SQL ma non le avete mai usate, questo articolo è per voi. È anche per chi non si stanca mai degli esempi di CTE ricorsivi.

Prima di addentrarci nella ricorsione, vi ricorderò cosa sono le CTE e qual è la loro sintassi. Poi farò lo stesso per i CTE ricorsivi. Infine, vi mostrerò come funzionano i CTE ricorsivi in tre esempi.

Cosa sono le CTE?

La CTE (Common Table Expression), nota anche come clausola WITH, è una funzione SQL che restituisce un insieme di dati temporaneo che può essere utilizzato da un'altra query. Trattandosi di un risultato temporaneo, non viene memorizzato da nessuna parte, ma è comunque possibile farvi riferimento come a qualsiasi altra tabella.

Esistono due tipi di CTE: non ricorsivi e ricorsivi.

Ecco un bell'articolo che illustra cosa sono e come funzionano le CTE.

Sintassi delle CTE non ricorsive

La sintassi generale di un CTE non ricorsivo è la seguente:

WITH cte_name AS (cte_query_definition)

SELECT *
FROM   cte_name;

La prima parte della sintassi è la CTE. Inizia con la parola chiave WITH. Poi si assegna un nome alla CTE. Dopo averla seguita con la parola chiave AS, si può definire la CTE tra le parentesi.

La seconda parte della sintassi è una semplice istruzione SELECT. Viene scritta subito dopo il CTE ricorsivo, senza virgole, punti e virgola o segni simili. Come ho detto prima, la CTE viene utilizzata in un'altra query come qualsiasi altra tabella. Questo è esattamente ciò che fa l'istruzione SELECT.

Ecco l'articolo che può aiutarvi ulteriormente con la sintassi CTE e le sue regole. E se avete bisogno di altri esempi di CTE, questo articolo fa al caso vostro.

Sintassi CTE ricorsiva

Una CTE ricorsiva fa riferimento a se stessa. Restituisce il sottoinsieme dei risultati, poi fa ripetutamente riferimento a se stessa (ricorsivamente) e si ferma quando restituisce tutti i risultati.

La sintassi di una CTE ricorsiva non è molto diversa da quella di una CTE non ricorsiva:

WITH RECURSIVE cte_name AS (
    cte_query_definition (the anchor member)

    UNION ALL

    cte_query_definition (the recursive member)
)


SELECT *
FROM   cte_name;

Anche in questo caso, all'inizio della CTE si trova la clausola WITH. Tuttavia, se si desidera che la CTE sia ricorsiva, dopo WITH si scrive la parola chiave RECURSIVE. Poi si procede come al solito: AS è seguito dalle parentesi con la definizione della query CTE. Questa prima definizione di query è chiamata membro di ancoraggio.

Per collegare il membro di ancoraggio con il membro ricorsivo, è necessario utilizzare il comando UNION o UNION ALL. Il membro ricorsivo è, ovviamente, la parte ricorsiva di CTE che farà riferimento a CTE stesso. Vedremo presto come funziona in un esempio.

Le CTE ricorsive si usano soprattutto quando si vogliono interrogare dati o grafici gerarchici. Può trattarsi della struttura organizzativa di un'azienda, di un albero genealogico, di un menu di un ristorante o di vari percorsi tra città. Per capire come funzionano le CTE con le strutture gerarchiche e come interrogare i dati dei grafi, consultare questi articoli.

Ora che abbiamo capito come funzionano le CTE ricorsive, vediamo alcuni esempi.

Esempio 1 - Trovare i capi e il livello gerarchico di tutti i dipendenti

Per questo problema, utilizzerò i dati della tabella employeesche ha le seguenti colonne:

• id: ID del dipendente.
• first_name: Il nome del dipendente.
• last_name: Il cognome del dipendente.
• boss_id: L'ID del capo del dipendente.

Ecco come appaiono i dati:

Non è troppo complicato. Ad esempio, il capo di Domenic Leaver è il dipendente con ID 5; si tratta di Hermie Comsty. Lo stesso principio vale per tutti gli altri dipendenti, tranne Roxanna Fairlie. Non ha un capo; c'è un valore NULL nella colonna boss_id. Possiamo concludere che Roxanna è il presidente o il proprietario dell'azienda.

Scriviamo ora la CTE ricorsiva per elencare tutti i dipendenti e i loro capi diretti.

WITH RECURSIVE company_hierarchy AS (
  SELECT	id,
    		first_name,
    		last_name,
    		boss_id,
		0 AS hierarchy_level
  FROM employees
  WHERE boss_id IS NULL

  UNION ALL 
  
  SELECT	e.id,
    		e.first_name,
    		e.last_name,
    		e.boss_id, 
		hierarchy_level + 1
  FROM employees e, company_hierarchy ch
  WHERE e.boss_id = ch.id
)

SELECT   ch.first_name AS employee_first_name,
	   ch.last_name AS employee_last_name,
	   e.first_name AS boss_first_name,
	   e.last_name AS boss_last_name,
	   hierarchy_level
FROM company_hierarchy ch
LEFT JOIN employees e
ON ch.boss_id = e.id
ORDER BY ch.hierarchy_level, ch.boss_id;

Cosa fa questa query? È una query ricorsiva, quindi inizia con WITH RECURSIVE. Il nome della CTE è company_hierarchy. Dopo AS, la definizione della CTE è tra parentesi.

La prima istruzione SELECT seleziona tutte le employee tabella in cui la colonna boss_id è NULL. In breve, selezionerà Roxanna Fairlie, perché solo lei ha un valore NULL in quella colonna. Ancora più breve: sto iniziando la ricorsione dall'inizio della struttura organizzativa. C'è anche una colonna hierarchy_level con il valore 0. Ciò significa che il livello del proprietario/presidente è 0 - è in cima alla gerarchia.

Ho usato UNION ALL per collegare questa istruzione SELECT con la seconda, cioè con il membro ricorsivo. Nel membro ricorsivo, sto selezionando tutte le colonne della tabella employees e il CTE company_hierarchy dove la colonna boss_id è uguale alla colonna id. Si noti la parte hierarchy_level + 1. Ciò significa che a ogni ricorsione, la CTE aggiungerà 1 al livello gerarchico precedente e lo farà fino a raggiungere la fine della gerarchia. Si noti anche che sto trattando questa CTE come qualsiasi altra tabella. Per terminare la definizione della CTE, basta chiudere le parentesi.

Infine, c'è una terza istruzione SELECT, esterna alla CTE. Essa seleziona le colonne che mostreranno i dipendenti, i nomi dei loro capi e il livello gerarchico. I dati vengono presi dalla CTE e dalla tabella employees. Ho unito questi due dati con LEFT JOIN, poiché voglio tutti i dati della CTE, compresa Roxanna Fairlie, che ha il valore NULL nella colonna boss_id. Il risultato sarà mostrato in ordine crescente: prima per livello gerarchico, poi per ID del capo. Ecco come appare:

Roxanna Fairlie è il capo supremo; lo sapevate già. Ci sono due dipendenti al livello 1. Ciò significa che Bobbe Blakeway e Hermie Comsty sono i diretti subordinati di Roxanna Fairlie. Al livello 2, ci sono dipendenti i cui capi diretti sono Bobbe Blakeway e Hermie Comsty. C'è anche un terzo livello nella gerarchia. Si tratta dei dipendenti il cui capo diretto è Domenic Leaver.

Esempio 2 - Trovare l'importo dell'investimento per investitore

In questo esempio, utilizzerò la tabella investment:

• id: L'ID dell'investimento.
• investment_amount: L'importo dell'investimento.

I dati della tabella si presentano come segue:

Questi sono gli importi delle tre possibili opzioni di investimento. Saranno considerati dai tre investitori, che divideranno l'importo totale dell'investimento in parti uguali. Il vostro compito è quello di calcolare l'importo per ogni investitore a seconda del suo numero, cioè se uno, due, tre o nessun investitore in ogni investimento.

La query che risolve questo problema è:

WITH RECURSIVE per_investor_amount AS (
	SELECT	0 AS investors_number,
			0.00 AS investment_amount,
			0.00 AS individual_amount
	UNION 

	SELECT	investors_number + 1,
			i.investment_amount,
			i.investment_amount / (investors_number + 1)
	FROM investment i, per_investor_amount pia
	WHERE investors_number << 3
)

SELECT *
FROM per_investor_amount
ORDER BY  investment_amount, investors_number;

Ancora una volta, la CTE inizia con WITH RECURSIVE, seguita dal nome e dalla definizione della query. Questa volta, utilizzerò il membro di ancoraggio della query ricorsiva per creare alcuni dati. Le colonne sono investors_number, investment_amount e individual_amount. Questo è il punto da cui voglio che parta la ricorsione (come nell'esempio precedente, con hierarchy_level = 0).

Poi viene UNION e il membro ricorsivo. Questa parte della query aumenterà di uno la colonna investors_number a ogni ricorsione. Lo farà per ogni investment_amount. La terza colonna calcolerà l'importo dell'investimento per investitore, a seconda del numero di investitori partecipanti. La ricorsione verrà eseguita per un massimo di tre investitori (ovvero fino a raggiungere la condizione WHERE investors_number < 3).

A questo punto viene eseguita una semplice istruzione SELECT che restituisce tutte le colonne del CTE. Ed ecco il risultato:

Non è difficile da analizzare. Se non ci sono investitori, l'importo dell'investimento è zero, così come l'importo individuale. Se l'investimento è di 5.322.146,00 e c'è un solo investitore, allora l'importo per investitore sarà di 5.322.146,00. Se ci sono due investitori con lo stesso importo, ognuno di loro dovrà pagare 2.661.073,00. Se tutti e tre gli investitori decidono di investire, ognuno pagherà 1.774.048,67. Gli altri due importi di investimento seguono lo stesso schema, come si può vedere nella tabella.

Esempio 3 - Trovare le rotte tra le città

Nel terzo esempio, utilizzerò la tabella cities_routeche contiene dati sulle città olandesi:

• city_from: La città di partenza.
• city_to: La città di destinazione.
• distance: La distanza tra le due città, in chilometri.

Utilizzare questa tabella per trovare tutti i possibili itinerari da Groninga a Haarlem, indicando le città del percorso e la distanza totale.

Ecco la query per risolvere questo problema:

WITH RECURSIVE possible_route AS (
	SELECT	cr.city_to,
       		cr.city_from || '->' ||cr.city_to AS route,
       		cr.distance
      FROM cities_route cr
      WHERE cr.city_from = 'Groningen'

UNION ALL

SELECT 	cr.city_to,
       		pr.route || '->' || cr.city_to AS route,
        		CAST((pr.distance + cr.distance) AS DECIMAL(10, 2))
      FROM possible_route pr
INNER JOIN cities_route cr
      		ON cr.city_from = pr.city_to
)

SELECT 	pr.route,
		pr.distance
FROM possible_route pr
WHERE pr.city_to = 'Haarlem'
ORDER BY pr.distance;

Vediamo cosa fa questa query. La prima istruzione SELECT della definizione CTE selezionerà le colonne della tabella cities_route dove la città di partenza è Groningen. Si noti che c'è anche una nuova colonna chiamata percorso, che verrà utilizzata per concatenare le città sul percorso.

L'UNION ALL collega questa colonna con il membro ricorsivo. Questa istruzione SELECT selezionerà la città di arrivo, concatenerà le città del percorso e infine aggiungerà le distanze tra queste città al totale del percorso tra Groningen e Haarlem. Per ottenere tutto ciò, ho unito la CTE alla tabella cities_route.

Segue l'istruzione SELECT che estrae i dati dalla CTE. Selezionerà il percorso e la distanza quando la città di arrivo è Haarlem, ordinando i dati per distanza in ordine crescente.

Il risultato della query appare così:

Non è difficile capire questa tabella. Ci sono due percorsi da Groninga ad Haarlem. Comprendono diverse città intermedie e sono lunghi rispettivamente 222,6 km e 246,5 km.

Se si vuole continuare a imparare, si può verificare come utilizzare una CTE ricorsiva invece di una lunga query SQL. E dopo aver affrontato questo argomento, divertitevi a disegnare qualcosa utilizzando una CTE ricorsiva.

Continuare a esercitarsi con le CTE ricorsive

Questi tre esempi hanno dimostrato le possibilità delle CTE ricorsive in SQL. Ora è il momento di mettere a frutto quanto appreso.

L'opzione migliore è probabilmente quella di seguire il nostro corso Recursive Queries . Offre numerosi esempi, spiegazioni e possibilità di esercitarsi. Il corso fa parte del percorso Advanced SQL , dove è possibile approfondire altri argomenti avanzati di SQL come le funzioni finestra, le estensioni GROUP BY e le query ricorsive.

Divertitevi!