Come scrivere più CTE in SQL

Sfruttate tutte le potenzialità delle CTE combinandone due o più in un'unica query SQL.

Le espressioni di tabella comuni, o CTE, possono essere un potente strumento SQL. Quando si scrivono due (o anche più) CTE insieme, questa potenza si moltiplica. In questo articolo vi mostrerò tre modi per scrivere più CTE:

• Utilizzo di due CTE indipendenti in una query SQL.
• Utilizzo di due CTE in cui la seconda CTE fa riferimento alla prima.
• Utilizzo di due CTE in cui una CTE è ricorsiva.

Se non avete familiarità con le CTE, vi consiglio il nostro corso interattivo Common Table Expressions, che copre tutti i tipi di CTE, compresi quelli ricorsivi, in 114 esercizi pratici. Potete leggere il corso in un episodio della nostra serie "Corso del mese".

Se volete rinfrescare rapidamente le vostre conoscenze, questi articoli sulle CTE e su quando us arle sono un buon inizio.

Introduzione ai dati

Vi mostrerò gli esempi di CTE su un set di dati composto da due tabelle.

La prima tabella è cars. Contiene dati sulle automobili prodotte da Renault e Nissan. Le sue colonne sono:

• id - L'ID dell'auto e la chiave primaria (PK) della tabella.
• car_make - Il produttore dell'auto.
• model - Il modello dell'auto.
• motor_type - I dettagli sul tipo di motore del modello.
• year_of_production - L'anno di produzione dell'auto.

Ecco un esempio di dati della tabella:

La seconda tabella, auto_vendite, ha queste colonne:

• id - L'ID delle informazioni di vendita e la chiave primaria (PK) della tabella.
• report_period - La data del rapporto di vendita.
• sales - Il numero di auto vendute.
• cars_id - La chiave esterna (FK) che fa riferimento alla tabella cars.

Alcuni dati di esempio della tabella:

Due CTE: Indipendente

In questo primo esempio, vi mostrerò come produrre le vendite totali e quelle per marca con due CTE indipendenti. Il codice è qui:

WITH sales_per_make AS (
	SELECT car_make,
		 SUM(sales) AS total_sales_per_make
	FROM cars c
JOIN car_sales cs
		ON c.id = cs.cars_id
GROUP BY car_make
),

sales_sum AS (
	SELECT SUM(sales) AS total_sales
	FROM car_sales
)
	
SELECT car_make,
	 total_sales_per_make,
	 total_sales
FROM sales_per_make, sales_sum ss;

Inizio a scrivere la prima CTE come se fosse la sola e unica CTE della mia query. Il nome della CTE, sales_per_makesegue la parola chiave WITH, quindi la parola chiave AS. Dopo di che, scrivo tra parentesi ciò che voglio che la CTE faccia.

In questo caso, utilizzo la funzione aggregata SUM() per trovare le vendite per marca di auto. Per farlo, devo unire le due tabelle che ho a disposizione.

Dopo di che viene il secondo CTE. L'aspetto principale è che le due CTE devono essere separate da una virgola. La seconda CTE non inizia con la parola chiave WITH, ma con il nome della seconda CTE. Io l'ho chiamata sales_sum. L'istruzione SELECT tra le parentesi calcola le vendite totali di tutte le marche di auto.

Queste due CTE sono indipendenti perché la seconda CTE non fa riferimento alla prima.

Per utilizzare queste query, devo scrivere un'istruzione SELECT (la query principale) che faccia riferimento ad esse. È come quando si scrive una sola CTE. L'istruzione SELECT unisce i risultati delle due CTE per restituire questo risultato:

Il risultato significa che i concessionari hanno venduto 176.569 auto Renault e 185.359 auto Nissan, per un totale di 361.928 auto.

Come si vede, scrivere due CTE non è poi così difficile. Tuttavia, ci sono alcune insidie quando si utilizzano più CTE in una query. Le cose principali da tenere d'occhio quando si scrivono più CTE sono:

• Utilizzare un solo WITH.
• Separare le CTE con le virgole.
• Non utilizzare una virgola prima della query principale.
• C'è solo una query principale.

Le CTE multiple funzionano solo se si scrive la parola chiave WITH una sola volta. Ma non è possibile scriverla ovunque si voglia. Bisogna scriverla prima della prima CTE. La seconda CTE e le successive iniziano con il nome delle rispettive CTE, a differenza della prima CTE che inizia con la parola chiave WITH.

La prima CTE è separata dalla seconda da una virgola. Lo stesso vale se si scrivono più di due CTE: tutte le CTE sono separate da una virgola. Tuttavia, indipendentemente dal numero di CTE, non c'è una virgola tra l'ultima CTE e la query principale.

Infine, c'è una sola query principale. Qualsiasi cosa si voglia calcolare, funziona solo se c'è una query principale. Sembra logico, perché si può fare riferimento a tutte le CTE che si vogliono unire come a qualsiasi altra tabella. Questo è uno dei vantaggi delle CTE, quindi approfittatene!

Due CTE: Una fa riferimento all'altra

In questo calcolo leggermente più complesso, utilizzo nuovamente due CTE. Questa volta, la seconda fa riferimento alla prima. Questa è l'unica opzione se si vuole che una CTE faccia riferimento a un'altra. È possibile fare riferimento solo alle CTE precedenti a quella corrente e non a quelle successive.

Le scriverò per calcolare le vendite effettive nel 2022 (cioè nei mesi di gennaio e febbraio), per fare il budget delle vendite annuali del 2022 utilizzando le vendite medie e infine per trovare le vendite ancora da realizzare nel 2022.

Di seguito vi illustrerò passo per passo il codice, in modo che possiate capire cosa sta succedendo:

WITH sales_per_car AS (
	SELECT c.id,
		 c.car_make,
	   	 c.model,
	       c.motor_type,
	       c.year_of_production,
	       AVG(cs.sales)::INT AS average_sales_2022
	FROM cars c JOIN car_sales cs ON c.id = cs.cars_id
	WHERE c.year_of_production = 2022
	GROUP BY c.id, c.car_make, c.model, c.motor_type, c.year_of_production
),

budget AS (
	SELECT *,
		 average_sales_2022 * 12 AS annual_planned_sales_2022
	FROM sales_per_car
)
	
SELECT b.car_make,
	 b.model,
	 b.motor_type,
	 b.year_of_production,
	 SUM(cs.sales) AS actual_ytd_sales_2022,
	 b.annual_planned_sales_2022,
	 b.annual_planned_sales_2022 - SUM(cs.sales) AS remaining_annual_sales_2022
FROM budget b
JOIN car_sales cs
ON b.id = cs.cars_id
GROUP BY b.car_make, b.model, b.motor_type, b.year_of_production, b.annual_planned_sales_2022;

La sintassi è la stessa dell'esempio precedente. La CTE sales_per_car restituisce alcune colonne della tabella cars. Utilizzo anche la funzione AVG() per calcolare le vendite medie nel 2022. Questo numero viene convertito in un numero intero perché si tratta di automobili e quindi voglio vedere la media come un numero intero di automobili.

Questo CTE mi fornisce le vendite medie per ogni modello prodotto nel 2022. Inoltre, suggerisce il motivo per cui so che le vendite sono solo per il 2022: le auto prodotte nel 2022 non potevano essere vendute nel 2021. Almeno questa è l'ipotesi.

Una virgola separa questa CTE dalla seconda, che inizia con il suo nome, budget. Questa CTE ora fa riferimento alla prima CTE come a qualsiasi altra tabella. Lo si può vedere nella clausola FROM. Uso la colonna average_sales_2022 perché è la vendita media mensile effettiva per il 2022. Supponiamo che il metodo di budgeting consista nel moltiplicare le vendite mensili medie di quell'anno per 12 per ottenere le vendite annuali pianificate. Questo è esattamente ciò che fa la seconda CTE, ed è il motivo per cui deve fare riferimento alla prima CTE.

La query principale unisce la CTE budget e la tabella car_sales. La query serve a trovare le vendite effettive per modello nel 2022. Poi, mostro la colonna annual_planned_sales_2022 dalla seconda CTE. Infine, calcolando la differenza tra queste due colonne, ottengo il numero di vendite ancora da effettuare per il resto del 2022.

Ecco il report che ottengo eseguendo la query:

Due CTE: Una delle CTE è ricorsiva

Ora mi sposterò da questi dati, ma rimanendo nell'ambito di questi marchi automobilistici. Immaginiamo che Renault stia valutando tre investimenti: acquistare Jaguar per 2.300.000.000, Volvo per 1.548.470.000 o Alfa Romeo per 2.450.000.000. Sta valutando se farlo da sola, insieme a Nissan o sia con Nissan che con Citroën.

Questo esempio è perfetto per scrivere due CTE, di cui una ricorsiva:

WITH RECURSIVE company_purchase AS (
	SELECT 2300000000 AS amount
UNION
	SELECT 1548470000 AS amount
UNION
	SELECT 2450000000 AS amount
),

per_buyer AS (
	SELECT  0 AS number_of_buyers,
		  0::DECIMAL AS purchase_amount,
		  0::DECIMAL AS amount_per_buyer
UNION
	SELECT  number_of_buyers + 1,
		  amount,
		  amount/(number_of_buyers + 1)::DECIMAL
	FROM company_purchase, per_buyer
	WHERE number_of_buyers <= 3)

SELECT *
FROM per_buyer
ORDER BY purchase_amount, number_of_buyers;

Ogni volta che si desidera una CTE ricorsiva, è necessario iniziare a scrivere le CTE con WITH RECURSIVE. Si annuncia sempre l'intenzione di scrivere una CTE ricorsiva, sia che la query ricorsiva sia la prima o la seconda CTE. In questo caso, la mia prima CTE non è ricorsiva.

Utilizzo la company_purchase CTE per creare diversi investimenti in Jaguar, Volvo o Alfa Romeo.

Poi viene la query ricorsiva. Il principio è lo stesso: separare le CTE da una virgola e iniziare la seconda CTE senza la parola chiave WITH. In questa seconda CTE, il punto di partenza è nessun investimento e nessun acquirente. I valori saranno ovunque pari a zero. Quindi utilizzo la ricorsione e la query calcola l'importo per acquirente per uno, due o tre investitori per il primo investimento. La ricorsione ripete poi lo stesso calcolo per il secondo e il terzo investimento. Naturalmente, per farlo, devo unire la query ricorsiva con quella non ricorsiva.

Infine, la query principale seleziona tutti i dati dalla CTE. per_buyer CTE, con il seguente risultato:

Cosa mi dicono questi dati? Ad esempio, se tre acquirenti (Renault, Nissan e Citroën) acquistano Volvo per 1.548.470.000, ogni azienda dovrebbe investire 516.156.666,67.

Ulteriori esempi si possono trovare nell'articolo sulle 5 migliori domande di colloquio sul CTE SQL e in un ulteriore articolo che parla dell'utilizzo di due CTE.

Moltiplicare ulteriormente la potenza delle CTE

Questi tre sono solo esempi di ciò che le CTE possono fare, in particolare di ciò che possono fare se si combinano più CTE in modi diversi. Questo concetto non è facile e richiede molta pratica.

Invece di inventare i vostri dati e scenari, seguite il nostro corso interattivo sulle Espressioni di tabelle comuni! Vi offre tutto questo ed elimina tutte le preoccupazioni su come esercitarsi con le CTE, con oltre 100 esercizi pratici!