Che cos'è un'espressione di tabella comune (CTE) in SQL?

L'espressione di tabella comune (CTE) è un potente costrutto di SQL che aiuta a semplificare una query. Le CTE funzionano come tabelle virtuali (con record e colonne), create durante l'esecuzione di una query, utilizzate dalla query ed eliminate dopo l'esecuzione della stessa. Spesso le CTE fungono da ponte per trasformare i dati delle tabelle di origine nel formato previsto dalla query.

Un'espressione di tabella comune, o CTE, è un insieme di risultati temporanei con nome creato da una semplice istruzione SELECT che può essere utilizzata in una successiva istruzione SELECT. Ogni CTE SQL è come una query denominata, il cui risultato viene memorizzato in una tabella virtuale (una CTE) a cui fare riferimento successivamente nella query principale.

Il modo migliore per imparare le espressioni di tabella comuni è la pratica. Consiglio il corso interattivo di LearnSQL.itQuery ricorsive di . Contiene oltre 100 esercizi che insegnano le CTE partendo dalle basi e arrivando ad argomenti avanzati come le espressioni ricorsive di tabelle comuni.

Le CTE aiutano a semplificare le query

Cominciamo con la sintassi di un'espressione di tabella comune.

WITH my_cte AS (
  SELECT a,b,c
  FROM T1
)
SELECT a,c
FROM my_cte
WHERE ....

Il nome di questa CTE è my_ctee la query CTE è SELECT a,b,c FROM T1. La CTE inizia con la parola chiave WITH, dopo la quale si specifica il nome della CTE, quindi il contenuto della query tra parentesi. La query principale viene dopo la parentesi di chiusura e fa riferimento alla CTE. In questo caso, la query principale (nota anche come query esterna) è SELECT a,c FROM my_cte WHERE ….

In questo eccellente articolo introduttivo sulle CTE sono presenti molti esempi di base. Altri articoli introduttivi includono "Migliorare la leggibilità delle query con le espressioni di tabella comuni" e "Quando dovrei usare un'espressione di tabella comune (CTE)?" che spiegano le espressioni di tabella comuni.

Imparare le espressioni di tabella comuni di SQL tramite esempi

In questa sezione vengono presentati alcuni esempi di query SQL che utilizzano le espressioni di tabella comuni. Tutti gli esempi si basano su un database di una catena di negozi di cellulari. La tabella sales, mostrata di seguito, ha un record per ogni prodotto venduto:

Nel primo esempio, otteniamo un report con gli stessi record della tabella, ma aggiungiamo una colonna in più con il nome del prodotto venduto. sales ma aggiungiamo una colonna in più con il prezzo dell'articolo più costoso venduto nella stessa filiale quel giorno. Per ottenere il prezzo dell'articolo più caro, utilizziamo una comune espressione di tabella come questa:

WITH highest AS (
  SELECT
    branch,
    date,
    MAX(unit_price) AS highest_price
  FROM sales
  GROUP BY branch, date
)
SELECT
  sales.*,
  h.highest_price
FROM sales
JOIN highest h
  ON sales.branch = h.branch
    AND sales.date = h.date

Questa query definisce una CTE SQL chiamata highest il cui risultato crea una tabella virtuale. La tabella virtuale ha le colonne filiale, data e highest_price, che contengono rispettivamente il nome della filiale, la data e il prezzo unitario più alto venduto quel giorno in questa filiale.

Viene quindi eseguita la query esterna, che utilizza la tabella virtuale come se fosse una tabella regolare. highest tabella virtuale come se fosse una tabella normale. Infine, si uniscono i risultati della CTE highest con la tabella sales tabella.

Il risultato dell'intera query è mostrato di seguito:

Nell'esempio successivo, generiamo un report con i maggiori ricavi giornalieri per filiale.

WITH daily_revenue AS (
  SELECT
    branch,
    date,
    SUM(unit_price * quantity) AS daily_revenue
  FROM   sales
  WHERE EXTRACT(YEAR FROM date) = 2021
  GROUP BY 1,2
)
SELECT
  branch,
  MAX(daily_revenue) max_daily_revenue
FROM daily_revenue
GROUP BY 1
ORDER BY 2 DESC

In questa query, una CTE chiamata daily_revenue ha le colonne filiale, data e daily_revenue per ogni data del 2021. Quindi, nella query esterna, si ottiene l'importo più alto delle entrate per ogni filiale nel 2021. Il report è ordinato per max_daily_revenue in ordine decrescente.

Di seguito sono riportati i risultati di questa query.

Uso delle CTE nelle query di SQL avanzato

È possibile definire due o più CTE e utilizzarle nella query principale. Nel prossimo esempio, mostriamo come dividere e organizzare una lunga query utilizzando le CTE SQL. Dando un nome alle diverse parti della query, le CTE ne facilitano la lettura.

Supponiamo di volere un report con il fatturato mensile totale di Londra nel 2021, ma di volere anche il fatturato di ogni filiale di Londra nello stesso report. In questo caso, creiamo due CTE e le uniamo nella query principale.

WITH london1_monthly_revenue AS (
  SELECT
    EXTRACT(MONTH FROM date) as month,
    SUM(unit_price * quantity) AS revenue
  FROM sales
  WHERE EXTRACT(YEAR FROM date) = 2021
    AND branch = 'London-1'
  GROUP BY 1
),
london2_monthly_revenue AS (
  SELECT
    EXTRACT(MONTH FROM date) as month,
    SUM(unit_price * quantity) AS revenue
  FROM sales
  WHERE EXTRACT(YEAR FROM date) = 2021
    AND branch = 'London-2'
  GROUP BY 1
)
SELECT
  l1.month,
  l1.revenue + l2.revenue AS london_revenue,
  l1.revenue AS london1_revenue,
  l2.revenue AS london2_revenue
FROM london1_monthly_revenue l1, london2_monthly_revenue l2
WHERE l1.month = l2.month

Nella query precedente, definiamo due CTE, london1_monthly_revenue e london2_monthly_revenueper ottenere le entrate mensili nel 2021 per ogni filiale di Londra. Infine, uniamo entrambe le CTE utilizzando la colonna mese e calcoliamo il fatturato totale di Londra sommando i ricavi delle due filiali.

Il risultato della query è riportato di seguito:

Nell'esempio seguente, otteniamo un report per informare ogni filiale della data in cui è stato venduto il biglietto più grande (cioè, la quantità della combinazione articolo-quantità) e l'importo di questo biglietto. A tal fine, è necessario creare una CTE che classifica i biglietti (la colonna position è la classifica) per ogni filiale in base all'importo del biglietto.

WITH tickets AS (
  SELECT distinct
    branch,
    date,
    unit_price * quantity AS ticket_amount,
    ROW_NUMBER() OVER (
      PARTITION BY branch
      ORDER by unit_price * quantity DESC
    ) AS position
  FROM sales
  ORDER BY 3 DESC
)
SELECT
  branch,
  date,
  ticket_amount
FROM tickets
WHERE position =1

Nella query precedente, creiamo una CTE con le colonne branch, date, ticket_amount e position. Poi, nella query esterna, filtriamo solo i record con position = 1 per ottenere ciò che vogliamo, il biglietto più grande per ramo.

Il risultato della query è mostrato di seguito:

CTE annidate nelle query SQL

Il prossimo esempio mostra una CTE annidata. L'idea è quella di generare un report con tutti gli articoli di prezzo superiore a 90 dollari e la quantità di questi articoli venduti dalla filiale Londra-2.

WITH over_90_items AS (
  SELECT DISTINCT
    item,
    unit_price
  FROM sales
  WHERE unit_price >=90
),
london2_over_90 AS (
  SELECT
    o90.item,
    o90.unit_price,
    coalesce(SUM(s.quantity), 0) as total_sold
  FROM over_90_items o90
  LEFT JOIN sales s
  ON o90.item = s.item AND s.branch = 'London-2'
  GROUP BY o90.item, o90.unit_price
)
SELECT item, unit_price, total_sold
FROM   london2_over_90;

La prima CTE è over_90_itemsche seleziona tutti gli articoli con prezzo maggiore o uguale a 90 dollari. La seconda CTE è london2_over_90che seleziona la quantità venduta da Londra-2 per ogni articolo incluso in over_90_items. Questa query ha una CTE annidata - si noti il FROM nella seconda CTE che fa riferimento alla prima. Si usa LEFT JOIN sales perché Londra-2 potrebbe non aver venduto tutti gli articoli presenti in . over_90_items.

Il risultato della query è:

Prima di passare alla sezione successiva, ho alcuni articoli da suggerire sulle espressioni di tabella comuni. Sia "SQL CTEs Explained with Examples" che "Where Can I Find Good SQL CTE Exercises?" contengono molti esempi ed esercizi.

Query ricorsive e Espressioni di tabella comuni

Nei database relazionali è comune avere tabelle che rappresentano gerarchie di dati come dipendenti-gestori, parti-sottoparti o genitori-figli. Per attraversare queste gerarchie in qualsiasi direzione (dall'alto verso il basso o dal basso verso l'alto), i database utilizzano un costrutto chiamato CTE ricorsivo.

RECURSIVE è una parola riservata per definire una CTE per l'attraversamento di una struttura di dati ricorsiva. La forma della query ricorsiva è la seguente:

WITH RECURSIVE  cte_name AS (
     CTE_query_definition  -- non recursive query term
UNION ALL
     CTE_query_definition  -- recursive query term
)
SELECT * FROM cte_name;

Le query ricorsive esulano dallo scopo di questo articolo introduttivo, ma ne ho altri tre da suggerire a chi vuole approfondire l'argomento: "Come organizzare le query SQL con le CTE", "Fallo in SQL: L'attraversamento ricorsivo di alberi in SQL" e "Conoscerela potenza di SQL Query ricorsive", che spiegano in dettaglio le query ricorsive con molti esempi.

Le CTE di SQL sono una risorsa potente

Le espressioni di tabella comuni sono una potente risorsa del linguaggio SQL. Permettono di creare query più leggibili e di gestire le differenze di formato tra i dati delle tabelle e quelli dei report. In questo articolo abbiamo spiegato cos'è una CTE e come utilizzarla in diversi tipi di query. Abbiamo anche accennato al fatto che le CTE possono essere utilizzate nelle query ricorsive.

Consiglio il corso Query ricorsive all'indirizzo LearnSQL.itdove si impara in modo interattivo a lavorare con le espressioni di tabella comuni in SQL. Si impara anche a elaborare strutture di dati ricorsive come grafi e alberi in SQL utilizzando le CTE ricorsive.

Inoltre, date un'occhiata alla traccia SQL avanzatoin cui si va oltre le nozioni di base per diventare un maestro di SQL. Se avete bisogno di prepararvi per un colloquio SQL, c'è un interessante articolo "Top 5 SQL CTE Interview Questions" che tratta le comuni espressioni di tabella da un'altra prospettiva.

Se avete imparato a usare le CTE, siete già un passo avanti nel percorso che vi porterà a diventare uno sviluppatore SQL. Continuate a crescere!