CTE in SQL: Una Guida Completa con Esempi

Come Creare un CTE SQL

Quando si crea un CTE, si utilizza la parola chiave WITH per avviare la definizione del CTE. La sintassi generale di un CTE è la seguente:

WITH cte_name (column1, column2, ...)
AS (
    -- Query che definisce il CTE
    SELECT ...
    FROM ...
    WHERE ...
)
-- Query principale
SELECT ...
FROM cte_name;

Dove:

• WITH: Avvia la definizione del CTE, indicando che il nome seguente rappresenta un insieme di risultati temporaneo.

• cte_name: Il nome viene assegnato al CTE per fare riferimento ad esso nella query principale.

• Elenco facoltativo delle colonne (colonna1, colonna2, …): Specifica i nomi delle colonne per l’insieme di risultati del CTE. Utile quando i nomi delle colonne devono essere regolati.

• Query che definisce il CTE: La query interna che seleziona i dati e modella il set di risultati temporaneo.

• Query principale: Fa riferimento al CTE dal suo nome, utilizzandolo come se fosse una tabella.

Diamo un’occhiata all’esempio seguente di creazione di un CTE utilizzando un approccio stratificato. Supponiamo di avere una tabella Employees, e vogliamo creare un CTE che selezioni dipendenti che guadagnano uno stipendio superiore a $50,000.

Passo 1: Scrivere la query di base

Cominciamo scrivendo la query di base SELECT:

SELECT EmployeeID, FirstName, LastName, Salary
FROM Employees
WHERE Salary > 50000;

Passo 2: Incapsulare la query utilizzando la parola chiave WITH per creare un CTE

Usare la parola chiave WITH per dare un nome al CTE.

WITH HighEarningEmployees AS (
    SELECT EmployeeID, FirstName, LastName, Salary
    FROM Employees
    WHERE Salary > 50000
)

Passo 3: Utilizzare il CTE nella query principale

Infine, fare riferimento al CTE in una istruzione SELECT chiamando il nome del CTE definito in precedenza.

-- Definire una Espressione Tabellare Comune (CTE)
WITH HighEarningEmployees AS (
    SELECT EmployeeID, FirstName, LastName, Salary
    FROM Employees
    WHERE Salary > 50000
)
-- Utilizzare il CTE per selezionare dipendenti con alti guadagni
SELECT EmployeeID, FirstName, LastName
FROM HighEarningEmployees;

Per riassumere i passaggi sopra, abbiamo utilizzato la parola chiave WITH per definire il CTE chiamato HighEarningEmployees. La query interna è stata utilizzata per generare il set di dati temporaneo. La query principale fa riferimento a HighEarningEmployees per visualizzare le colonne specificate EmployeeID, FirstName e LastName.

Perché le CTE di SQL sono Utili

Dall’esempio sopra, potresti chiederti perché utilizziamo i CTE quando anche le query semplici producono gli stessi risultati. Le seguenti sono le ragioni:

Semplificare le query complesse

I CTE suddividono le dichiarazioni SQL complesse in parti più piccole e gestibili, rendendo il codice più facile da leggere, scrivere e mantenere. 

Supponiamo di avere tre tabelle: Ordini, Clienti e Prodotti. Vogliamo trovare il totale dei ricavi generati da ciascun cliente che ha effettuato acquisti nel 2024. Quando scriviamo la query senza utilizzare i CTE, risulta disordinata e difficile da leggere e comprendere.

-- Selezionare i nomi dei clienti e il totale dei ricavi dai loro ordini
SELECT c.CustomerName, SUM(p.Price * o.Quantity) AS TotalRevenue
FROM Orders o
-- Join per ottenere le tabelle clienti e prodotti
JOIN Customers c ON o.CustomerID = c.CustomerID
JOIN Products p ON o.ProductID = p.ProductID
WHERE YEAR(o.OrderDate) = 2024
GROUP BY c.CustomerName
HAVING SUM(p.Price * o.Quantity) > 1000;

Utilizzando un CTE, possiamo separare la logica in un formato più leggibile:

-- Definire il CTE
WITH OrderDetails AS (
    SELECT o.OrderID, c.CustomerName, p.Price, o.Quantity, o.OrderDate
    FROM Orders o
    JOIN Customers c ON o.CustomerID = c.CustomerID
    JOIN Products p ON o.ProductID = p.ProductID
    WHERE YEAR(o.OrderDate) = 2024
)
--Query principale
SELECT CustomerName, SUM(Price * Quantity) AS TotalRevenue
FROM OrderDetails
GROUP BY CustomerName
HAVING SUM(Price * Quantity) > 1000;

Riusabilità del codice

I CTE aiutano a evitare la duplicazione consentendo di riutilizzare lo stesso set di risultati in diverse parti di una query. Se più calcoli o operazioni si basano sullo stesso set di dati, è possibile definirlo una volta in un CTE e fare riferimento ad esso secondo necessità.

Supponiamo di dover calcolare la media e le vendite totali per ciascuna categoria di prodotti in un database di e-commerce. Possiamo utilizzare un CTE per definire i calcoli una volta e riutilizzarli nelle query successive.

-- Definire un CTE per calcolare le vendite totali e medie per ciascuna categoria
WITH CategorySales AS (
    SELECT Category, SUM(SalesAmount) AS TotalSales, AVG(SalesAmount) AS AverageSales
    FROM Products
    GROUP BY Category
)
-- Selezionare la categoria, le vendite totali e le vendite medie dal CTE
SELECT Category, TotalSales, AverageSales
FROM CategorySales
WHERE TotalSales > 5000;

Altre applicazioni

Oltre a semplificare le query e favorire la riutilizzabilità del codice, i CTE hanno anche altri utilizzi. Non sono in grado di coprire ogni possibile uso dei CTE in dettaglio. Il nostro corso Manipolazione dei Dati in SQL è un’ottima opzione se desideri continuare a praticare. Tuttavia, documenterò qui alcune delle principali altre ragioni:

• Organizzazione e Leggibilità delle Query: Le CTE migliorano la leggibilità del codice SQL dividendo le query in passaggi logici e sequenziali. Ogni passaggio nel processo di query può essere rappresentato dalla propria CTE, rendendo l’intera query più facile da seguire.
• Traversamento dei Dati Gerarchici: Le CTE possono aiutare a navigare le relazioni gerarchiche, come le strutture organizzative, le relazioni genitore-figlio o qualsiasi modello di dati che coinvolga livelli nidificati. Le CTE ricorsive sono utili per interrogare dati gerarchici perché consentono di attraversare i livelli in modo iterativo.
• Aggregazioni a più livelli: Le CTE possono aiutare a eseguire aggregazioni a più livelli, ad esempio calcolando le cifre di vendita a diverse granularità (ad esempio, per mese, trimestre e anno). Utilizzare le CTE per separare questi passaggi di aggregazione garantisce che ogni livello venga calcolato in modo indipendente e logico.
• Combinare Dati da Tabelle Diverse: È possibile utilizzare più CTE per combinare dati da tabelle diverse, rendendo il passaggio finale di combinazione più strutturato. Questo approccio semplifica le join complesse e garantisce che i dati di origine siano organizzati logicamente per una migliore leggibilità.

Tecniche avanzate delle CTE SQL

Le CTE supportano tecniche avanzate di SQL, rendendole versatili e utili per diversi casi d’uso. Di seguito sono riportate alcune delle applicazioni avanzate delle CTE.

Utilizzo di più CTE in una singola query

È possibile definire più CTE in una singola query, il che consente trasformazioni e calcoli complessi. Questo metodo è utile quando un problema richiede più fasi di elaborazione dei dati, dove ogni CTE rappresenta una fase distinta.

Supponiamo di avere dati sulle vendite in una tabella chiamata Vendite e di voler calcolare le vendite totali per ogni prodotto, identificare i prodotti con vendite totali superiori alla media e classificare questi prodotti in base alle loro vendite totali.

WITH ProductSales AS (
    -- Passaggio 1: Calcolare le vendite totali per ogni prodotto
    SELECT ProductID, SUM(SalesAmount) AS TotalSales
    FROM Sales
    GROUP BY ProductID
),
AverageSales AS (
    -- Passaggio 2: Calcolare la media delle vendite totali tra tutti i prodotti
    SELECT AVG(TotalSales) AS AverageTotalSales
    FROM ProductSales
),
HighSalesProducts AS (
    -- Passaggio 3: Filtrare i prodotti con vendite totali superiori alla media
    SELECT ProductID, TotalSales
    FROM ProductSales
    WHERE TotalSales > (SELECT AverageTotalSales FROM AverageSales)
)
-- Passaggio 4: Classificare i prodotti ad alta vendita
SELECT ProductID, TotalSales, RANK() OVER (ORDER BY TotalSales DESC) AS SalesRank
FROM HighSalesProducts;

Nell’esempio sopra;

• Il primo CTE (VenditeProdotto) calcola le vendite totali per prodotto.

• Il secondo CTE (AverageSales) calcola la media delle vendite totali di tutti i prodotti.

• Il terzo CTE (HighSalesProducts) filtra i prodotti le cui vendite totali superano la media.

• La query finale classifica questi prodotti in base alle loro vendite totali.

CTE nelle dichiarazioni di UPDATE, DELETE e MERGE

Quando incorporate nelle operazioni UPDATE, DELETE e MERGE, le CTE possono semplificare le attività di manipolazione dei dati, specialmente quando si tratta di filtri complessi o dati gerarchici.

Utilizzo di CTE con una dichiarazione di UPDATE

Supponiamo di avere una tabella Employees con una colonna EmployeeSalary. Vogliamo dare un aumento del 10% a tutti i dipendenti che lavorano per l’azienda da più di 5 anni.

-- Definire una CTE per trovare i dipendenti assunti più di 5 anni fa
WITH LongTermEmployees AS (
    SELECT EmployeeID
    FROM Employees
    WHERE DATEDIFF(YEAR, HireDate, GETDATE()) > 5
)
-- Aggiornare i salari del 10% per i dipendenti a lungo termine identificati nella CTE
UPDATE Employees
SET EmployeeSalary = EmployeeSalary * 1.1
WHERE EmployeeID IN (SELECT EmployeeID FROM LongTermEmployees);

La CTE LongTermEmployees identifica i dipendenti che lavorano da più di cinque anni. La dichiarazione UPDATE utilizza questa CTE per aumentare selettivamente i salari.

Utilizzo di CTE con un’istruzione DELETE

Ora supponiamo di avere una tabella chiamata Prodotti e vogliamo eliminare tutti i prodotti che non sono stati venduti negli ultimi 2 anni. Possiamo utilizzare un CTE per filtrare i prodotti:

-- Definire un CTE per identificare i prodotti non venduti negli ultimi 2 anni
WITH OldProducts AS (
    SELECT ProductID
    FROM Products
    -- Usare DATEADD per trovare i prodotti con una data di ultima vendita più vecchia di 2 anni fa
    WHERE LastSoldDate < DATEADD(YEAR, -2, GETDATE())
)
-- Eliminare i prodotti identificati come vecchi dalla tabella principale
DELETE FROM Products
WHERE ProductID IN (SELECT ProductID FROM OldProducts);

Il CTE ProdottiVecchi identifica i prodotti che non sono stati venduti negli ultimi due anni, e quindi l’istruzione DELETE utilizza questo CTE per rimuovere tali prodotti.

Utilizzo di CTE con un’istruzione MERGE

L’istruzione MERGE in SQL consente aggiornamenti condizionali, inserimenti o eliminazioni in una tabella di destinazione in base ai dati in una tabella di origine. Nell’esempio seguente, il CTE InventarioUnito combina dati di inventario nuovi ed esistenti. L’istruzione MERGE quindi aggiorna le quantità per i prodotti esistenti o inserisce nuovi prodotti in base ai dati del CTE.

-- CTE per unire i dati dell'inventario nuovi ed esistenti
WITH MergedInventory AS (
    SELECT ni.ProductID, ni.Quantity AS NewQuantity, i.Quantity AS CurrentQuantity
    FROM NewInventoryData ni
    -- Utilizzare LEFT JOIN per includere tutti i nuovi dati, anche se non presenti nell'inventario attuale
    LEFT JOIN Inventory i ON ni.ProductID = i.ProductID
)
-- Unire i dati preparati nella tabella Inventario
MERGE INTO Inventory AS i
USING MergedInventory AS mi
ON i.ProductID = mi.ProductID
-- Aggiornare i prodotti esistenti con le nuove quantità
WHEN MATCHED THEN
    UPDATE SET i.Quantity = mi.NewQuantity
-- Inserire nuovi prodotti se non esistono nell'inventario
WHEN NOT MATCHED BY TARGET THEN
    INSERT (ProductID, Quantity) VALUES (mi.ProductID, mi.NewQuantity);

Comuni Espressioni Tabellari Ricorsive (CTE)

Le CTE ricorsive aiutano a eseguire operazioni avanzate e ripetute.

Introduzione alle CTE ricorsive

Le CTE ricorsive sono un tipo speciale di CTE che si riferisce a se stessa nella sua definizione, consentendo alla query di eseguire operazioni ripetute. Questo le rende ideali per lavorare con dati gerarchici o a struttura ad albero, come organigrammi, strutture di directory o assemblaggi di prodotti. La CTE ricorsiva elabora iterativamente i dati, restituendo i risultati passo dopo passo fino a quando non viene soddisfatta una condizione di terminazione.

Membri ancorati e ricorsivi

Una CTE ricorsiva è composta da due parti principali:

• Membro ancorato: La parte che definisce la query di base che avvia la ricorsione.
• Membro Ricorsivo: La parte che fa riferimento all’CTE stessa, consentendole di eseguire le operazioni “ricorsive”.

Supponiamo di avere una tabella Employees, dove ogni riga contiene un EmployeeID, EmployeeName e ManagerID. Se vogliamo trovare tutti i report diretti e indiretti per un manager specifico, partiamo con il membro ancoraggio che identifica il manager di livello superiore. Il membro ancoraggio inizia con l’impiegato con EmployeeID = 1.

Il membro ricorsivo trova dipendenti il cui ManagerID corrisponde all’EmployeeID dall’iterazione precedente. Ogni iterazione recupera il livello successivo della gerarchia.

WITH EmployeeHierarchy AS (
    -- Membro ancoraggio: seleziona il manager di livello superiore
    SELECT EmployeeID, EmployeeName, ManagerID, 1 AS Level
    FROM Employees
    WHERE EmployeeID = 1  -- Iniziando con il manager di livello superiore
    UNION ALL
    -- Membro ricorsivo: trova dipendenti che riportano ai manager attuali
    SELECT e.EmployeeID, e.EmployeeName, e.ManagerID, eh.Level + 1
    FROM Employees e
    INNER JOIN EmployeeHierarchy eh ON e.ManagerID = eh.EmployeeID
)
SELECT EmployeeID, EmployeeName, Level
FROM EmployeeHierarchy;

Potenziali problemi o limitazioni delle CTE in SQL

Comprendere le funzionalità e le limitazioni delle CTE è importante per scrivere query logiche e leggibili. Esaminiamo alcune limitazioni e potenziali problemi nell’uso delle CTE in database diversi.

Limitazioni di SQL Server e Azure

Ci sono alcune limitazioni specifiche dell’ambiente per le CTE di SQL quando si lavora con SQL Server o Azure Synapse Analytics. Queste includono:

• SQL Server: Il livello massimo predefinito di ricorsione per le CTE ricorsive è 100, che può essere modificato utilizzando l’indicazione OPTION (MAXRECURSION). Se questo limite viene superato senza adeguamento, si verifica un errore. Le CTE non possono essere annidate direttamente l’una dentro l’altra o definite all’interno di un’altra CTE.

• Azure Synapse Analytics: I CTE hanno un supporto limitato per alcune operazioni SQL come INSERT, UPDATE, DELETE e MERGE. Inoltre, i CTE ricorsivi non sono supportati negli ambienti basati su cloud di Azure Synapse Analytics, limitando la possibilità di eseguire determinate operazioni dati gerarchiche.

Se ti trovi a lavorare con SQL Server, sappi che DataCamp ha moltissime risorse utili. Per iniziare, ti consiglio di seguire il corso di DataCamp Introduzione a SQL Server per padroneggiare le basi di SQL Server per l’analisi dei dati. Puoi provare il nostro percorso professionale SQL Server Developer, che copre tutto, dalle transazioni e la gestione degli errori all’analisi delle serie temporali. Il nostro corso Query gerarchiche e ricorsive in SQL Server va dritto al punto su come scrivere query avanzate in SQL Server, inclusi metodi che coinvolgono le CTE.

Altri potenziali problemi

Sebbene i CTE siano utili per semplificare query complesse, ci sono alcuni problemi comuni di cui dovresti essere consapevole. Questi includono i seguenti:

• Cicli Infiniti nei CTE Ricorsivi: Se la condizione di terminazione per un CTE ricorsivo non viene soddisfatta, può risultare in un ciclo infinito, causando l’esecuzione indefinita della query. Per evitare che il CTE ricorsivo venga eseguito all’infinito, utilizza l’opzione OPTION (MAXRECURSION N) per limitare il numero massimo di iterazioni ricorsive, dove N è un limite specificato.

• Considerazioni sulle prestazioni: Le CTE ricorsive possono diventare risorse-intensive se la profondità della ricorsione è elevata o se vengono elaborate grandi quantità di dati. Per ottimizzare le prestazioni, limitare i dati elaborati in ogni iterazione ed assicurarsi di filtrare in modo appropriato per evitare livelli eccessivi di ricorsione.

Quando utilizzare le CTE rispetto ad altre tecniche

Anche se le CTE sono appropriate per semplificare le query che coinvolgono compiti ripetuti, tabelle derivate, viste e tabelle temporanee servono a scopi simili. La tabella seguente mette in evidenza i vantaggi e gli svantaggi di ciascun metodo e quando utilizzare ciascuno.

Conclusione

Dominare le CTE richiede pratica, come per qualsiasi cosa: ti consiglio di provare il percorso professionale Associate Data Analyst in SQL di DataCamp per diventare un analista dati competente. Il corso Reporting in SQL ti aiuterà anche a diventare competente nella creazione di report complessi e dashboard per una presentazione efficace dei dati. Infine, dovresti ottenere la SQL Associate Certification per mostrare la tua padronanza nell’uso di SQL per risolvere problemi aziendali e distinguerti tra gli altri professionisti.