CTE di PostgreSQL: cos'è e come usarlo

Le CTE, o Common Table Expressions, sono un potente strumento di PostgreSQL spesso ignorato. Questo articolo analizza le varie CTE di PostgreSQL. PostgreSQL, comprese le CTE nidificate e ricorsive, e cosa si può fare con esse.

Se scrivete query complesse in SQL, vi accorgerete presto che il vostro codice diventa disordinato e difficile da leggere. Le CTE, note anche come clausole WITH, sono principalmente un modo per semplificare le query. Tuttavia, consentono anche di utilizzare la ricorsione. La ricorsione, tra le altre cose, consente di navigare facilmente nelle strutture gerarchiche.

Le CTE (o Common Table Expressions) di PostgreSQL sono molto simili alle subquery; la differenza è che le CTE sono denominate e definite all'inizio del codice. Ciò consente di suddividere una query di grandi dimensioni in piccole sezioni.

In questo articolo, illustrerò diversi esempi di CTE di PostgreSQL. Presumo che abbiate già familiarità con la scrittura di query in PostgreSQL. In caso contrario, vale la pena scaricare il nostro PostgreSQL Cheat Sheet.

Se pensate che le CTE di PostgreSQL possano aiutarvi nel vostro lavoro, potreste dare un'occhiata al nostro corso interattivo Common Table Expressions in PostgreSQL. Questo corso è pensato per chi ha già familiarità con l'SQL di base. Potrete fare molta pratica con le CTE di PostgreSQL, grazie a oltre cento esercizi interattivi.

Scopriamo le espressioni di tabella comuni in Postgres!

Espressioni di tabella comuni in PostgreSQL

Sintassi delle CTE in PostgreSQL

Diamo ora un'occhiata più da vicino alla sintassi delle CTE. Nella sua forma più semplice, si presenta così:

WITH cte_name AS (query_1)
query_2;

• cte_name è il nome che si assegna alla CTE. Si può fare riferimento a questo nome nella query principale o nelle sottoquery, proprio come per una tabella.
• query_1 è una qualsiasi SELECT valida
• query_2 è un'istruzione SQL valida. Può essere una SELECT, una UPDATE, una INSERT o una DELETE.

I risultati di query_1 saranno disponibili come se fossero una tabella. Il nome della tabella sarà quello specificato in cte_name. È possibile utilizzarla nel resto della query nello stesso modo in cui si utilizzano le altre tabelle.

Esempio di CTE in PostgreSQL

Vediamo un esempio. Alpha Sales è un rivenditore online. Vuole sapere se la sua ultima strategia di marketing è stata efficace e quale tipo di cliente ha risposto meglio.

Ecco un esempio della loro tabella order_summary che contiene il valore di ogni ordine effettuato nei mesi di aprile, maggio e giugno del 2024.

Come primo passo per analizzare il successo della campagna di marketing, i dirigenti dell'azienda vogliono confrontare le vendite di giugno per cliente con le vendite medie mensili per cliente di aprile e maggio e calcolare la variazione percentuale.

Naturalmente, si potrebbe ottenere questo risultato utilizzando delle subquery, ma il codice sarebbe piuttosto complesso. Si desidera visualizzare la media del mese precedente nel report, ma anche utilizzarla nel calcolo della variazione percentuale.

Utilizzando una CTE, la query avrebbe il seguente aspetto:

WITH april_may_sales AS
(SELECT 
   customer_id, 
   SUM(value) / 2 AS prev_avg
 FROM order_summary 
 WHERE EXTRACT (MONTH FROM order_date) in (4,5)
 GROUP BY customer_id;
)
SELECT 
  order_summary.customer_id, 
  prev_avg, 
  SUM(value) AS jun_total, 
  (SUM(value) - prev_avg) * 100 / prev_avg AS percent_change
FROM order_summary  
JOIN april_may_sales
ONapril_may_sales.customer_id = order_summary.customer_id
WHERE EXTRACT (MONTH FROM order_date) = 6
GROUP BY order_summary.customer_id, prev_avg
ORDER BY customer_id;

Questa query utilizza la clausola WITH per creare una tabella virtuale denominata april_may_sales. Estrae il totale delle vendite per cliente per i mesi di aprile e maggio, divide il risultato per 2 per ottenere una media mensile e memorizza queste informazioni in una colonna denominata prev_avg.

Questa tabella viene unita alla tabella order_summary nella query principale, in modo da poter esaminare il totale di giugno insieme alla media di aprile e maggio.

La query produce il seguente set di risultati:

Definizione dei nomi delle colonne CTE

È possibile definire in modo specifico i nomi delle colonne per la tabella CTE utilizzando la seguente sintassi:

WITH cte_name (column_name_list)
AS (query_1)
query_2;

column_name_list è un elenco di nomi di colonne separati da virgole.

Modificando l'esempio precedente con questa sintassi si ottiene la seguente query:

WITH april_may_sales (customer_id, prev_avg)
AS (
  SELECT 
 	customer_id, 
      SUM(value) /2 
  FROM order_summary 
  WHERE EXTRACT (MONTH FROM order_date) in (4,5)
  GROUP BY customer_id
)
SELECT 
order_summary.customer_id,
prev_avg, 
SUM(value) AS jun_total, 
(SUM(value) - prev_avg) * 100/prev_avg AS percent_change
FROM order_summary  
JOIN april_may_sales
ON april_may_sales.customer_id = order_summary.customer_id
WHERE EXTRACT (MONTH FROM order_date) = 6
GROUP BY order_summary.customer_id, prev_avg
ORDER BY customer_id;

Questo non cambia l'output della query, che rimane lo stesso della query di esempio precedente. Tuttavia, rende più facile la comprensione della query da parte di altri.

Query annidate con le CTE di PostgreSQL

In PostgreSQL è possibile definire due o più CTE utilizzando un'unica parola chiave WITH. È sufficiente iniziare con la parola chiave WITH e poi specificare ogni CTE separata da virgole. La sintassi è la seguente:

WITH 
cte_name_1 AS (query_1),
cte_name_2 AS (query_2)
query_3;

Ogni CTE ha il proprio nome e la propria istruzione select. Ogni CTE può fare riferimento a qualsiasi CTE definita in precedenza per prelevare i dati di cui ha bisogno. Si noti che non si ripete la parola chiave WITH: si elencano solo le CTE separate da virgole.

Vediamo come funziona. Supponiamo che Alpha Sales voglia fare un ulteriore passo avanti nell'analisi. Vorrebbero estrarre i dati demografici dei clienti che hanno acquistato di più a giugno rispetto alla media degli acquisti di aprile e maggio.

Per farlo, devono combinare i dati estratti nella query precedente con i dati della loro tabella. customer tabella. Ecco un esempio di dati:

Per fare ciò, è possibile

• Spostare la query principale precedente in primo piano come CTE annidata. In questo modo si crea una tabella virtuale che contiene customer_id, la media precedente, il totale di giugno e la variazione percentuale.
• Scrivere una nuova query principale che unisce questa tabella con la tabella customer per calcolare l'età del cliente ed estrarre il suo stato.

La nuova query si presenta come segue:

WITH april_may_sales AS
  (SELECT 
     customer_id, 
     SUM(value) / 2 AS prev_avg
   FROM order_summary 
   WHERE EXTRACT (MONTH FROM order_date) in (4,5)
   GROUP BY customer_id
),
comparison AS
  (
    SELECT 
      order_summary.customer_id, 
      prev_avg, 
      SUM(value) AS jun_total, 
      (SUM(value) - prev_avg) * 100/prev_avg AS percent_change
    FROM order_summary  
    JOIN april_may_sales
    ON april_may_sales.customer_id = order_summary.customer_id
    WHERE EXTRACT (MONTH FROM order_date) = 6
    GROUP BY order_summary.customer_id, prev_avg
  )
SELECT 
  customer.customer_id,
  name,
  EXTRACT(YEAR from CURRENT_DATE) - 
		EXTRACT(YEAR from date_of_birth) AS age,
  state,
  prev_avg, 
  jun_total,
  percent_change
FROM customer
JOIN comparison 
    ON comparison.customer_id = customer.customer_id
WHERE percent_change > 0;

Come in precedenza, la query definisce la CTE denominata april_may_sales come una tabella virtuale contenente le vendite medie di aprile e maggio.

Quindi definisce una nuova CTE denominata comparison, che contiene un confronto dei totali di giugno per cliente con il contenuto di april_may_sales.

Infine, la query principale combina i dati della tabella virtuale comparison con i dati della tabella di confronto. customer tabella.

L'insieme dei risultati appare così:

PostgreSQL CTE in linguaggio di manipolazione dei dati

Vediamo ora le istruzioni di manipolazione dei dati come INSERT, UPDATE e DELETE.

Una delle limitazioni delle CTE è che non si possono usare direttamente al posto di un valore in un'istruzione UPDATE, come si può fare con una subquery.

Supponiamo di voler aggiornare il bilancio della tabella customer aggiungendo il valore di tutti gli ordini di giugno. Con le normali subquery si può fare una cosa del genere:

UPDATE customer 
SET balance = balance + 
(select SUM(value) FROM order_summary 
WHERE customer.customer_id = order_summary.customer_id
   AND EXTRACT (MONTH from order_date) = 6);

Non è possibile farlo con una CTE. Tuttavia, è possibile utilizzare la seguente sintassi:

WITH cte_name AS (select_statement)
UPDATE tablename 
SET column_name_1 = column_name_2
FROM cte_name 
WHERE join_clause;

• cte_name è il nome che si userà per riferirsi alla 'tabella' creata dalla CTE.
• select_statement è l'istruzione che si utilizzerà per popolare la CTE.
• column_name_1 è il nome della colonna della tabella principale che si desidera aggiornare.
• column_name_2 è il nome della colonna nella CTE che si utilizzerà per impostare il nuovo valore.
• join_clause specifica la condizione da usare per unire le due tabelle.

La seguente query aggiunge il totale degli ordini di giugno dalla tabella order_summary al saldo della tabella customer della tabella:

WITH june_total AS
(SELECT 
   customer_id, 
   SUM(value) AS jun_tot
 FROM order_summary WHERE EXTRACT(MONTH FROM order_date) = 6
 GROUP BY customer_id
)
UPDATE customer
SET balance = balance + jun_tot
FROM june_total 
WHERE customer.customer_id = june_total.customer_id;

Innanzitutto, la clausola WITH crea una pseudo-tabella denominata june_total. Contiene i totali per customer_id degli ordini in cui il mese di order_date è 6.

Successivamente, la colonna jun_tot di questa tabella viene utilizzata per aumentare il saldo dove customer_id corrisponde tra le due tabelle.

La tabella customer contiene ora i seguenti dati:

È possibile utilizzare le CTE per inserire o eliminare righe nello stesso modo.

Query ricorsive

Le query ricorsive sono una caratteristica delle CTE. Queste query consentono di eseguire un ciclo a partire da una singola query di base per eseguire ripetutamente un'attività specifica. Sono particolarmente utili per l'interrogazione di dati gerarchici, come le strutture organizzative e le distinte dei materiali.

Una trattazione completa delle query ricorsive va oltre lo scopo di questo articolo. Ci limiteremo a vedere la sintassi e un semplice esempio. Per maggiori dettagli, consultate Cos'è una CTE ricorsiva in SQL, che fornisce una spiegazione completa e diversi esempi.

La sintassi delle query ricorsive in PostgreSQL è la seguente:

WITH RECURSIVE cte_name AS 
(query_1 UNION query_2)
query_3;

• La parola chiave RECURSIVE indica che si tratta di una query ricorsiva.
• query_1 è la query di base, o punto di partenza. Ad esempio, si supponga di lavorare con una struttura organizzativa. In questo caso, query_1 potrebbe essere una query che seleziona il manager di livello superiore da un file di dipendenti.
• query_2 è la query ricorsiva. Questa query viene ripetuta finché non ci sono più righe che soddisfano i criteri specificati in WHERE. Può fare riferimento all'ultima riga aggiunta al set di risultati per prelevare i dati. Si può usare per trovare tutti i dipendenti che fanno capo a un manager.
• UNION combina i risultati. Se si utilizza UNION ALL, i duplicati verranno conservati, altrimenti verranno omessi.
• query_3 viene utilizzato per restituire l'insieme dei risultati finali. Può fare riferimento alla tabella virtuale creata dalla CTE.

Pensiamo a un esempio di tabella employee in cui i record dei dipendenti hanno un campo che identifica il manager a cui fanno capo. Cosa succede se si usa una query ricorsiva per navigare in questa gerarchia?

I risultati della query di base vengono aggiunti alla tabella virtuale. La query di base estrae il record del dipendente dell'amministratore delegato. Il motore del database utilizza questa riga per trovare tutte le righe che corrispondono ai criteri della parte ricorsiva della query. Si tratta di tutti i dipendenti che riportano direttamente al top manager.

Per ognuno di questi record, il motore troverà tutti i dipendenti che fanno capo a quella persona. Questa operazione viene ripetuta finché non ci sono più dipendenti che soddisfano la condizione.

Vediamo un semplice esempio. Una società di consulenti informatici ha diversi progetti in corso e la sua politica prevede riunioni settimanali sullo stato di avanzamento di ciascun progetto. Una tabella denominata projects contiene i dettagli dei nuovi progetti. Un esempio di questa tabella si presenta come segue:

L'azienda vuole creare i dettagli delle riunioni programmate in una tabella denominata meetings; queste informazioni saranno utilizzate per inviare promemoria e prenotare una sede ogni settimana. La colonna meet_day contiene il giorno della settimana in cui saranno programmate le riunioni. È memorizzata come numero di giorno all'interno della settimana, dove 0 rappresenta la domenica.

È possibile ottenere questo risultato con la seguente query ricorsiva:

WITH RECURSIVE date_list
   (proj_name, meet_date, end_date, meet_day, meet_time)
AS (
    SELECT proj_name, start_date, end_date, meet_day, meet_time
	FROM projects
    UNION ALL
    SELECT 
proj_name, 
meet_date + 1,
	end_date, 
meet_day, 
meet_time
    FROM date_list
    WHERE meet_date + 1 <= end_date
	
)
INSERT INTO meetings
SELECT proj_name, meet_date, meet_time
FROM date_list 
WHERE meet_day = EXTRACT (DOW from meet_date)
ORDER BY proj_name, meet_date;

Dopo l'esecuzione della query, la tabella meetings contiene i seguenti dati:

Vediamo cosa fa la query.

Innanzitutto, definisce le colonne che saranno incluse nella CTE date_list:

WITH RECURSIVE date_list
   (proj_name, meet_date, end_date, meet_day, meet_time)

Quindi stabilisce i dati di base per la ricorsione, ovvero il contenuto della tabella dei progetti:

AS (
    SELECT proj_name, start_date, end_date, meet_day, meet_time
	from projects

Quindi specifica quali dati devono essere inclusi in ogni ricorsione, con una condizione che assicura che la ricorsione termini una volta completata:

    UNION ALL
    SELECT proj_name, 
	meet_date + 1,
	end_date, meet_day, meet_time
	FROM date_list
    WHERE meet_date + 1 <= end_date

Infine, la query principale inserisce i risultati contenuti nella tabella virtuale nella tabella meetings.

Vi sembra utile? Per saperne di più sulle query ricorsive e per fare pratica con alcuni esempi reali, potete seguire il nostro corso online CTE in PostgreSQL.

Per saperne di più sulle CTE in PostgreSQL

Anche se le CTE in PostgreSQL non migliorano le prestazioni delle query, di certo rendono le query complesse più facili da scrivere e da capire. Suddividendo una lunga query in parti componenti, è possibile organizzare i propri pensieri e mantenere la codifica semplice. Le CTE facilitano anche il lavoro con le strutture gerarchiche utilizzando la clausola RECURSIVE.

Questo articolo utilizza specificamente la sintassi e gli esempi di PostgreSQL, ma le CTE funzionano allo stesso modo anche per altri dialetti SQL come MS SQL Server.

Se desiderate acquisire familiarità con le CTE, il corso Common Table Expressions in PostgreSQL di LearnSQL contiene oltre 100 esercizi pratici che vi aiuteranno a comprendere questo strumento.

Se volete fare un po' di pratica, provate questi 11 esercizi gratuiti sulle espressioni delle tabelle comuni di SQL. Ogni esercizio rappresenta il tipo di sfida che dovrete affrontare nel mondo reale, con tanto di soluzioni e spiegazioni. E se vi state preparando per un colloquio, ecco alcuni esempi di domande e risposte sulle CTE.

Spero che questo articolo vi abbia dato una buona idea di ciò che PostgreSQL CTE può fare per voi. Se volete imparare altri concetti avanzati di PostgreSQL, questo articolo è un buon punto di partenza.

Ora tocca a voi! Ricordate che la pratica rende perfetti, quindi date un'occhiata alla nostra traccia di apprendimento SQL avanzato Practice per fare più pratica con le funzioni SQL avanzate!