Come scrivere un CTE ricorsivo in SQL Server

Guida alla comprensione e all'uso dei CTE ricorsivi in SQL Server

SQL Server offre molti strumenti potenti per lavorare con i dati, tra cui le Common Table Expressions (CTE). Una CTE è un insieme di risultati temporaneo con nome che può essere referenziato all'interno di un'istruzione SELECT, INSERT, UPDATE, o DELETE. Le CTE possono suddividere le query più lunghe in parti di logica più piccole e gestibili, rendendole più leggibili.

SQL Server offre espressioni di tabella comuni ricorsive. Una Common Table Expression (CTE) ricorsiva in SQL Server consente di eseguire query ricorsive su strutture di dati gerarchiche o grafiche, come organigrammi, alberi genealogici, reti di trasporto, ecc. Le query ricorsive vengono utilizzate per eseguire un ciclo di relazioni tra gli elementi dei dati.

In questo articolo, inizieremo con la sintassi CTE standard e poi esploreremo come scrivere CTE ricorsive in SQL Server.

Un ottimo modo per approfondire la conoscenza delle CTE ricorsive è seguire il nostro corso su Recursive Queries in MS SQL Server. Vi insegnerà a padroneggiare il tipo di query più impegnativo e a organizzare in modo efficace le query SQL più lunghe.

Nozioni di base sulle CTE in SQL Server

Per prima cosa, esaminiamo le basi delle CTE. Una CTE inizia con la clausola WITH, seguita dal nome della CTE e dall'istruzione SELECT che la definisce. La sintassi di una CTE è la seguente:

WITH cte_name AS (
   SELECT 
     column1,
     column2,
     …
   FROM table
   … 
) 
SELECT … 
FROM cte_name, …;

Cominciamo dall'inizio. La prima cosa da fare è dare un nome alla nostra CTE, cte_name. Questa CTE seleziona alcune colonne (column1, column2, ...) da table. Il resto della query (la query esterna) può fare riferimento alla CTE e alle sue colonne come se la CTE fosse una normale tabella.

Si può pensare a una CTE come a una vista temporanea a cui fare riferimento nella query esterna, come stiamo facendo nell'esempio precedente.

Supponiamo di voler scrivere una query che restituisca gli acquisti gestiti dai cinque dipendenti con lo stipendio più alto nel reparto vendite. Le tabelle che utilizzeremo in questo esempio si chiamano employees (contiene le colonne employee_id, employee_name e salary) e purchase (contiene le colonne id, date, customer_id e total_amount).

WITH top_5_sales_employees AS (
   SELECT TOP 5
     employee_id,
     employee_name 
   FROM employees
   WHERE department_id = ‘Sales’ 
   ORDER BY salary DESC
) 
SELECT 
  p.id AS purchase_id,
  p.date,
  p.total_amount,
  e.id AS employee_id
  e.employee_name 
FROM top_5_sales_employees e
JOIN purchase p
ON p.employee_id = e.id;

Questa CTE inizia con la parola chiave WITH e il nome della CTE, top_5_sales_employees. Tra le parentesi, si selezionano le colonne che si desidera includere nella CTE e si specificano le condizioni della query nella clausola WHERE. Infine, si utilizza l'istruzione SELECT per selezionare le colonne appropriate dalla CTE come se fosse una normale tabella.

Per saperne di più su cosa sono le CTE e su come si scrivono, consultate il nostro articolo Cos'è una CTE in SQL Server? Se cercate altri esempi di CTE, li troverete in questo articolo.

Come utilizzare le CTE ricorsive in SQL Server

Che cos'è un CTE ricorsivo in SQL Server? Un CTE ricorsivo è un tipo di CTE che fa riferimento a se stesso nell'istruzione SELECT, creando un ciclo. Le CTE ricorsive sono utilizzate per attraversare strutture di dati gerarchiche, come diagrammi organizzativi o reti.

Supponiamo di avere una tabella chiamata employees con colonne per il nome del dipendente, il reparto e il manager. Questo è mostrato nella tabella seguente, dove ogni record include un dipendente e il suo responsabile all'interno dell'organizzazione.

A colpo d'occhio, è abbastanza facile vedere chi fa capo a chi e come si presenta la gerarchia organizzativa. Tuttavia, se avessimo centinaia di dipendenti, sarebbe molto più difficile dare un senso ai dati.

Possiamo utilizzare una CTE ricorsiva per generare un albero gerarchico dei dipendenti dell'azienda. Per fare ciò, si deve eseguire questa query:

WITH employee_manager_cte AS (
  SELECT 
    id, 
    name,
    department,
    manager_id,
    manager_name,
    1 AS level 
  FROM employees 
  WHERE manager_id IS NULL 
  UNION ALL 
  SELECT 
    e.id, 
    e.name,
    e.department,
    e.manager_id,
    e.manager_name,
    level + 1 
  FROM employees e 
  INNER JOIN employee_manager_cte r 
	ON e.manager_id = r.id 
) 
SELECT * 
FROM employee_manager_cte;

Analizziamo questa query passo per passo.

Innanzitutto, definiamo la CTE ricorsiva con il nome employee_manager_cte. Selezioniamo le colonne che vogliamo includere nella query: id, name, department, manager_id, manager_name e level. La colonna level viene utilizzata per tracciare la profondità dell'albero. Inizieremo con il livello 1; man mano che si procede nel ciclo, questo numero aumenterà.

La sezione prima di UNION ALL viene chiamata membro di ancoraggio. Nel membro di ancoraggio iniziamo il nostro ciclo. Nel nostro esempio, selezioniamo tutti i dipendenti il cui manager è NULL. Nel nostro organigramma, si tratta dei dipendenti che si trovano in cima alla lista. In questo caso, c'è un solo dipendente a questo livello: Sarah Smith, l'amministratore delegato.

Il pezzo dopo UNION ALL è chiamato membro ricorsivo. Nel membro ricorsivo, si aggiungono nuove righe a quelle già calcolate. Nel nostro esempio, uniamo la tabella employees con la CTE employee_manager_cte sulla colonna manager_id. Questo crea un ciclo che attraversa l'albero dall'alto verso il basso. Aggiungiamo 1 alla colonna level per tenere traccia della profondità di ogni nodo.

Infine, si selezionano tutte le colonne della CTE employee_manager_cte.

Quando si esegue questa query, SQL Server elabora prima il membro di ancoraggio, che seleziona Sarah Smith come radice dell'albero. Quindi elabora il membro ricorsivo, che unisce Sarah Smith al suo rapporto diretto (Jane Miller). Quindi unisce Jane Miller con il suo rapporto diretto (John Doe) e John Doe con il suo rapporto diretto (nessuno). Poiché non ci sono più righe da aggiungere al set di risultati, SQL Server interrompe l'elaborazione della CTE e restituisce il risultato finale.

Ecco come appare il set di risultati:

Un altro ottimo articolo esplicativo che potete consultare è Che cos'è una CTE ricorsiva in SQL? Qui troverete altri esempi pratici di CTE ricorsive in SQL.

Suggerimenti per la scrittura di CTE ricorsivi in SQL Server

1. Iniziare con il membro di ancoraggio

Il membro di ancoraggio è il punto di partenza della CTE ricorsiva. È la parte della query che definisce il caso base, ovvero la prima serie di righe che verranno restituite. Nel nostro esempio di organigramma, si tratta del livello più alto di gestione. Assicurarsi che il membro di ancoraggio restituisca tutte le colonne necessarie nel set di risultati finale.

2. Assicurarsi che il numero di colonne corrisponda

Il membro ricorsivo e i membri di ancoraggio devono avere lo stesso numero di colonne e gli stessi tipi di dati corrispondenti: In una CTE ricorsiva, il membro ricorsivo fa riferimento alla CTE stessa, quindi è importante assicurarsi che il membro ricorsivo e il membro di ancoraggio abbiano lo stesso numero di colonne e gli stessi tipi di dati nelle colonne corrispondenti.

3. Sempre UNION ALL

In SQL Server è possibile utilizzare UNION ALL solo per combinare un membro di ancoraggio e un membro ricorsivo: Quando si combinano il membro di ancoraggio e il membro ricorsivo, è necessario utilizzare UNION ALL e non UNION. UNION ALL conserva tutte le righe, compresi i duplicati, mentre UNION rimuove i duplicati. UNION non è consentita nelle query ricorsive in SQL Server.

4. Attenzione ai cicli infiniti!

Assicuratevi di scrivere un controllo di terminazione nella vostra query. Un controllo di terminazione è una condizione che impedisce al CTE ricorsivo di andare in loop all'infinito. Senza un controllo di terminazione, per impostazione predefinita la query eseguirà un massimo di 100 ricorsioni e poi darà errore.

Il controllo di terminazione è tipicamente incluso nella clausola WHERE del membro ricorsivo e specifica quando la ricorsione deve terminare.

Seguendo questi suggerimenti, è possibile scrivere CTE ricorsive efficienti ed efficaci che aiutano a risolvere problemi gerarchici complessi in SQL Server. Per ulteriori dettagli, consultate questo articolo sulle migliori pratiche CTE.

CTE ricorsivi in SQL Server - Altri esempi

Esempio 1: CTE ricorsiva per una rete di trasporti

Un altro esempio di utilizzo di una CTE ricorsiva è la rappresentazione di una rete di trasporti. Supponiamo di avere una tabella chiamata routes che contiene informazioni sui percorsi di trasporto tra le città. La tabella include la città di origine, la città di destinazione e la distanza tra le città. Vogliamo scrivere una query che restituisca tutte le città raggiungibili da una data città di partenza, insieme alla distanza totale da ciascuna città.

I nostri dati si trovano in una tabella chiamata routes:

Ecco la query:

WITH recursive_cte AS (
  SELECT 
    source_city, 
    destination_city, 
    distance,
    source_city AS visited_cities 
   FROM routes 
   WHERE source_city = ‘New York’
   
   UNION ALL 
   
   SELECT 
     r.source_city, 
     r.destination_city, 
     r.distance + rc.distance,
     rc.visited_cities + ‘,’ + r.destination_city
   FROM routes r 
   INNER JOIN recursive_cte rc 
	ON r.source_city = rc.destination_city 
   WHERE rc.distance < 2000
AND CHARINDEX(',' + r.destination_city + ',', ',' +                                 rc.visited_cities + ',') = 0
) 
SELECT 
  destination_city, 
  distance
FROM recursive_cte

Questa CTE ricorsiva inizia con il membro di ancoraggio, che seleziona tutti i percorsi che partono da New York. Nel membro ricorsivo, uniamo la tabella dei percorsi con la CTE recursive_cte sulla colonna source_city per trovare tutte le città raggiungibili da New York.

Abbiamo aggiunto una nuova colonna chiamata visited_cities che memorizza l'elenco delle città visitate come stringa separata da virgole. Nel membro di ancoraggio della CTE inizializziamo questa colonna impostandola sulla città di origine. Nel membro ricorsivo, si concatena la città corrente all'elenco delle città visitate e si controlla se la città di destinazione è già stata visitata utilizzando la funzione CHARINDEX. Se la città di destinazione non è stata visitata, la aggiungiamo all'elenco delle città visitate e continuiamo la ricorsione.

Continuiamo ad aggiungere righe all'insieme di risultati fino a quando non ci sono più città da aggiungere o fino a quando non viene raggiunto il controllo di terminazione. L'insieme dei risultati mostra tutte le città raggiungibili da New York e la distanza totale da ciascuna città.

Esempio 2: Uso di CTE ricorsivi per le dipendenze dei task in un progetto

Un altro scenario in cui si potrebbe usare una CTE ricorsiva è quello di capire le dipendenze dei compiti di un progetto.

Supponiamo di avere una tabella chiamata tasks che memorizza informazioni sulle attività di un progetto, tra cui task_id, task_name, l'ID dell'attività da cui dipende (depends_on_task_id) e time_required per completare l'attività. Vogliamo scrivere una query che calcoli il tempo totale necessario per completare un determinato compito, compresi tutti i compiti dipendenti.

I dati sono mostrati di seguito:

Scriviamo una query che estragga il tempo totale richiesto per l'attività di sviluppo e le sue dipendenze.

WITH recursive_cte AS (
  SELECT 
    task_id, 
    task_name, 
    depends_on_task_id, 
    time_required
  FROM tasks 
  WHERE task_id = 2
  
  UNION ALL 

  SELECT 
    t.task_id, 
    t.task_name, 
    depends_on_task_id, 
    t.time_required + rc.time_required
  FROM tasks t 
  INNER JOIN recursive_cte rc 
	ON t.depends_on_task_id = rc.task_id
WHERE rc.total_time < 20) 
SELECT 
  task_name, 
  time_required as total_time
FROM recursive_cte
GROUP BY task_name;

Questa CTE ricorsiva inizia con il membro di ancoraggio, che seleziona la riga nella tabella tasks con task_id = 2 (Sviluppo). Nel membro ricorsivo, si unisce alla tabella tasks con la CTE recursive_cte sulle colonne depends_on_task_id e task_id per trovare tutte le attività che dipendono da Sviluppo. Si calcola il tempo totale richiesto per ogni attività sommando il tempo richiesto per l'attività corrente al tempo totale richiesto per le attività precedenti.

Infine, interroghiamo i risultati per task_name e time_required per ogni attività. Ecco i risultati:

La colonna task_name mostra il nome di ogni attività e la colonna total_time mostra il tempo totale richiesto per completare l'attività e tutte le attività precedenti. Ad esempio, la prima riga mostra che il tempo totale richiesto per completare Sviluppo e tutte le sue dipendenze è 15, che è la somma del tempo richiesto per Sviluppo (10) e Test (5).

Le altre righe mostrano lo stesso concetto, con le colonne task_name e total_time che indicano il nome di ogni attività e il relativo tempo totale richiesto.

Per saperne di più sulle CTE ricorsive in SQL Server

Le CTE ricorsive in SQL Server possono essere utili in diversi scenari, ad esempio:

• Gerarchie di dipendenti: Una CTE ricorsiva può essere utilizzata per attraversare un albero di dipendenti e dei loro manager per calcolare la retribuzione totale di tutti i dipendenti in un determinato ramo.
• Categorie di prodotti: Una CTE ricorsiva può essere utilizzata per attraversare un albero di categorie di prodotti per trovare tutti i prodotti di una determinata categoria e delle sue sottocategorie.
• Reti sociali: Una CTE ricorsiva può essere usata per attraversare un grafo di connessioni di social network per trovare tutti gli amici di un dato utente e le loro connessioni.
• Dipendenze da attività: Una CTE ricorsiva può essere utilizzata per attraversare un albero di dipendenze di attività per calcolare il tempo necessario per completare un progetto.

In generale, qualsiasi scenario in cui i dati sono organizzati gerarchicamente o in una struttura a grafo può potenzialmente trarre vantaggio dall'uso di una CTE ricorsiva. Seguendo le migliori pratiche, come iniziare con il membro di ancoraggio, assicurarsi che i membri ricorsivi e di ancoraggio abbiano lo stesso numero di colonne e tipi di dati e scrivere un controllo di terminazione, è possibile scrivere query efficienti e accurate che attraversano strutture di dati gerarchiche e risolvono problemi complessi.

Rafforzate la vostra comprensione dei CTE ricorsivi con il corso Recursive Queries in MS SQL Server. I suoi 112 esercizi interattivi vi aiuteranno a diventare padroni di questo argomento avanzato!