Come utilizzare 2 CTE in un'unica query SQL

Vi siete mai chiesti come utilizzare più CTE in un'unica query SQL? Leggete questo articolo e scoprite le CTE ricorsive.

Dopo aver appreso le comuni espressioni di tabella o CTE, viene spontaneo chiedersi : "Posso usare più CTE in un'unica query?". Sì, è possibile! E lo si può fare facilmente, soprattutto se si ha già una conoscenza di base delle CTE. Sia che conosciate un po' le CTE, sia che siate completamente nuovi al mondo delle CTE, leggere cosa sono le CTE è sempre un buon inizio.

Quanto appreso in questo articolo può essere messo in pratica nel corso LearnSQL.it Recursive Queries, che è stato il corso del mese di agosto.

Inizierò spiegando come utilizzare due CTE in una query. Poi vi insegnerò a usare le CTE in cui la seconda CTE fa riferimento alla prima. Per dare un po' di pepe a questo articolo, concluderò mostrandovi due query con CTE, una delle quali ricorsiva.

2 CTE, 1 query SQL, 0 problemi

Immaginiamo che ci sia una tabella denominata logins che memorizza login dati. Contiene le colonne:

• id - L'ID del login.
• username - L'utente che ha effettuato l'accesso.
• login_date - La data dell'accesso.
• login_start - Quando l'utente ha effettuato l'accesso.
• login_end - Quando l'utente si è disconnesso.

Se si osserva la tabella, si noterà che ogni nome utente compare almeno una volta.

Se il compito è quello di calcolare il tempo medio (in minuti) in cui ogni utente ha effettuato l'accesso, come si può fare utilizzando le CTE?

Avrete bisogno di due CTE, il cui codice sarà simile a questo:

WITH distinct_user AS (
	SELECT DISTINCT username
FROM logins),

minutes_logged AS (
	SELECT	username,
			DATEDIFF (minute, login_start, login_end) AS minutes
FROM logins)

SELECT	u.username,
		AVG (minutes) AS avg_time_logged
FROM distinct_user AS u 
JOIN minutes_logged AS m 
ON u.username = m.username
GROUP BY u.username;

Per prima cosa, ricordiamo che è necessario avere nomi utente distinti perché questo funzioni. Ora iniziamo a scrivere la prima CTE! Non c'è niente di strano: come per ogni CTE, la si definisce con WITH; non per niente si chiamano query WITH!

Quello che segue è il nome della CTE; in questo caso, la prima CTE si chiama distinct_user. La si fa seguire dalla parola chiave AS e poi si definisce semplicemente la normale istruzione SELECT all'interno delle parentesi. Questa istruzione SELECT seleziona semplicemente i nomi utente unici dalla tabella logins.

Una volta che abbiamo nomi di utenti distinti, dobbiamo calcolare per quanto tempo l'utente è stato connesso. Questa è la parte critica: scrivere la seconda CTE. La mia seconda CTE si chiama minutes_logged.

Noterete che non c'è WITH prima della seconda CTE. Questo è estremamente importante! Dopo aver definito la prima CTE, questa è separata dalla seconda solo dalla virgola, cioè si scrive WITH solo una volta. Dopodiché, non importa quante CTE si definiscono; è importante solo separarle con la virgola e iniziare ogni CTE con il suo nome.

Analizziamo ora cosa fa il CTE minutes_logged. Per prima cosa prende la colonna username dalla tabella logins. Poi calcola la differenza tra login_start e login_end in minuti. Questo nuovo valore verrà mostrato nella colonna minutes.

Dovrete calcolare il tempo medio (in minuti) trascorso da ogni utente durante l'accesso. Per questa parte del calcolo, ho usato la query esterna. Essa seleziona il nome utente dalla CTE distinct_user e poi calcola i minuti medi di accesso usando la colonna minutes dalla CTE minutes_logged.

Queste due CTE vengono unite utilizzando la colonna username. Infine, il risultato viene raggruppato in base alla stessa colonna, poiché si desidera un risultato a livello di utente.

Ma è possibile utilizzare una CTE all'interno di un'altra CTE?

Una domanda semplice merita una risposta semplice: sì, è possibile. Ora che sapete come usare più CTE, scrivere una CTE che fa riferimento a un'altra CTE è solo una variante di ciò che avete imparato. Poiché si tratta di una variante, credo sia meglio mostrarvi come farlo utilizzando un esempio che già conoscete.

Torniamo all'esempio precedente. Modificherò un po' il codice per mostrare come scrivere una CTE che faccia riferimento alla prima CTE. Questa è la nuova versione del codice precedente:

WITH difference AS (
	SELECT 	username,
		 	DATEDIFF (minute, login_start, login_end) AS minutes
FROM logins),

average_logged AS (
SELECT	username,
			AVG (minutes) AS average
FROM difference
GROUP BY username)

SELECT DISTINCT 		username,
				average
FROM average_logged;

La logica è la stessa del primo esempio, solo che i passaggi sono in ordine different. La prima CTE, difference, calcola i minuti trascorsi con ogni login; è la stessa del primo esempio. Ora che ho i minuti, devo calcolare il tempo medio (in minuti) trascorso da ciascun utente. Per questo utilizzerò la media CTE. Come ricorderete, mettiamo una virgola tra le due CTE e iniziamo la seconda con il suo nome - non c'è bisogno di usare di nuovo WITH! Questa seconda CTE calcola il tempo medio per ogni login; a tale scopo, utilizza la colonna minutes della prima CTE e memorizza il risultato nella colonna average.

Per fare riferimento alla CTE difference nella seconda CTE, la si tratta come una tabella: FROM difference. Infine, si raggruppa il risultato in base al nome utente, poiché non si vuole la media di tutti gli utenti.

La query esterna seleziona semplicemente le colonne username e media dalla CTE average_logged. In questo modo, si ottiene lo stesso risultato del primo esempio:

Se questi esempi non vi bastano, c'è un articolo che ne fornisce altri per fare pratica. Questo include anche l'uso di più di una CTE.

Utilizzo di CTE ricorsivi con CTE non ricorsivi

C'è un motivo per cui le CTE vengono insegnate nel nostro corso. Recursive Queries corso. Perché i CTE possono essere anche ricorsivi. Questa è la parte più complicata dell'apprendimento dei CTE. (Presumo che sappiate già cos'è un CTE ricorsivo e come scriverlo. In caso contrario, si veda Query SQL lunga vs. Query SQL ricorsiva per un'introduzione e Fallo in SQL: Recursive SQL Tree Traversal per un esempio approfondito. ) Tuttavia, forse non vi siete resi conto che è possibile utilizzare più di una CTE, anche se una di esse è ricorsiva. Vediamo come fare.

Per questo esempio, immaginiamo di voler acquistare un'azienda con i nostri amici. Ci sono quattro opzioni per l'investimento, ognuna delle quali richiede una somma di denaro diversa. Siete ancora in fase di trattativa. Non siete sicuri di quanti amici parteciperanno; la loro partecipazione dipende dalla quantità di denaro che hanno da investire. Tale importo dipende dal numero totale di investitori coinvolti e dall'opzione di investimento scelta.

Per aiutarli a decidere, avete deciso di calcolare l'importo richiesto per ogni investitore per tutte e quattro le opzioni di investimento con un numero di investitori da uno a dieci.

In questo esempio, abbiamo prima una CTE non ricorsiva e poi una CTE ricorsiva:

WITH RECURSIVE investment AS (
	SELECT amount 5897645 AS investment_amount
	UNION 
SELECT 4536841 AS investment_amount
	UNION 
SELECT 3852457 AS investment_amount
	UNION 
SELECT 3452115 AS investment_amount
),

per_investor AS (
	SELECT	0 AS investors_number,
			0 AS amount,
			0 AS individual_amount
	UNION 
	SELECT	investors_number + 1,
			investment_amount,
			investment_amount/(investors_number + 1)
	FROM investment, per_investor
	WHERE investors_number < 10)

SELECT *
FROM per_investor
ORDER BY  amount, investors_number;

La distinzione principale è evidente fin dall'inizio! Quando si scrivono query ricorsive, è necessario iniziare il codice utilizzando WITH RECURSIVE. Come si noterà, non è necessario iniziare con la CTE ricorsiva stessa; in questo esempio, una CTE non ricorsiva viene prima, anche se usiamo WITH RECURSIVE subito prima di definirla. È possibile scrivere le CTE nell'ordine che si desidera; quella ricorsiva può essere la prima o l'ultima. Ma è importante ricordare che se si ha almeno una query ricorsiva, la scrittura di WITH RECURSIVE è obbligatoria.

Nell'esempio attuale, la prima CTE (non ricorsiva) si chiama investimento. Poiché non ho una tabella con tutti gli importi degli investimenti, ho deciso di usare la CTE come tabella temporanea. Come si può vedere, i possibili importi degli investimenti sono i seguenti:

• 5,897,645
• 4,536,841
• 3,852,457
• 3,452,115

Utilizzando UNION con SELECT nel primo CTE, sto virtualmente creando una tabella che contiene le quattro possibilità di investimento. Esse saranno visualizzate nella colonna investment_amount. La tabella risultante per questa CTE è:

La seconda CTE è ricorsiva. Tuttavia, non cambia nulla rispetto alla scrittura di due CTE non ricorsive: la virgola separa nuovamente le CTE.

Analizziamo un po' la seconda CTE. La prima istruzione SELECT definisce tre colonne che hanno il valore 0: investors_number, amount e individual_amount. Come ho detto, la CTE ricorsiva si riferisce a se stessa, quindi è necessario l'operatore UNION.

L'operatore UNION è seguito da una o più istruzioni SELECT, che eseguiranno l'operazione desiderata sulla prima istruzione SELECT. Ciò significa che aggiungerà 1 alla colonna investors_number. Quindi inserirà investment_amount dalla prima CTE nella colonna importo. Dividerà l'importo dell'investimento con il numero di investitori coinvolti; il risultato verrà mostrato nella colonna individual_amount. Eseguirà questa operazione per tutti e quattro gli importi di investimento fino a raggiungere dieci investitori.

La query esterna seleziona semplicemente tutti i dati dalla CTE per_investor, ordinandoli in base alle colonne importo e investors_number. Eseguite questa query e godetevi tutte le possibilità che avete calcolato:

Davvero impressionante, non è vero?

La combinazione di due o più CTE funziona davvero

Le espressioni comuni di tabella SQL sono uno strumento potente. Sono utili se si vuole che il codice sia più leggibile, ma il loro valore non è solo estetico; le loro reali possibilità brillano se si sa come usare diverse CTE in una query o addirittura scrivere una CTE ricorsiva in SQL.

Come si è visto, non è difficile combinare due o più CTE. Una volta che lo si sa, si aprono le possibilità di utilizzo delle CTE in varie situazioni. Ora sta a voi trovare i casi in cui dovrete usare le CTE. E fate pratica, pratica, pratica! È sempre consigliabile utilizzare il nostro corso Recursive Queries per questo. Vi fornirà le tabelle su cui esercitarvi, così non dovrete preoccuparvi di creare da soli gli scenari.

Non esitate a condividere la vostra esperienza con le CTE SQL nella sezione commenti.