Guida SQLite interattiva: CTE

Q: Posso avere più CTE in una sola query SQLite?

Sì. Dopo il primo WITH, separa le CTE aggiuntive con delle virgole, senza ripetere WITH. Ogni CTE può fare riferimento a quelle definite prima, quindi puoi costruire una pipeline di passaggi con un nome. La SELECT principale segue l'ultima CTE.

Una CTE è una subquery con un nome

Una Common Table Expression (CTE) è una subquery che hai estratto e a cui hai dato un nome. Invece di annidare una SELECT dentro un'altra SELECT, la definisci in cima con WITH, le dai un nome e poi usi quel nome nella query principale come se fosse una tabella.

La struttura è sempre la stessa:

CREATE TABLE orders (id INTEGER PRIMARY KEY, customer TEXT, amount REAL);
INSERT INTO orders (customer, amount) VALUES
    ('Ada', 120), ('Boris', 45), ('Ada', 80),
    ('Carla', 200), ('Boris', 30), ('Ada', 50);

WITH customer_totals AS (
    SELECT customer, SUM(amount) AS total
    FROM orders
    GROUP BY customer
)
SELECT customer, total
FROM customer_totals
WHERE total > 100;

Leggila dall'alto verso il basso: prima costruisci un risultato con nome chiamato customer_totals, poi interroghi quel risultato. La CTE si comporta come una vista temporanea che esiste solo per la durata di questa singola istruzione.

La stessa query senza CTE

Ecco la stessa logica scritta come subquery, così vedi cosa sta sostituendo la CTE:

CREATE TABLE orders (id INTEGER PRIMARY KEY, customer TEXT, amount REAL);
INSERT INTO orders (customer, amount) VALUES
    ('Ada', 120), ('Boris', 45), ('Ada', 80),
    ('Carla', 200), ('Boris', 30), ('Ada', 50);

SELECT customer, total
FROM (
    SELECT customer, SUM(amount) AS total
    FROM orders
    GROUP BY customer
)
WHERE total > 100;

Stessa risposta. Ma fai caso all'ordine di lettura: l'occhio deve tuffarsi tra le parentesi, capire cosa viene calcolato, poi tornare fuori. La versione con la CTE si legge nell'ordine in cui avviene il lavoro: definisci il risultato intermedio, poi lo usi. In una query piccola non cambia molto. In una query con tre o quattro passaggi, è la differenza tra codice che puoi scorrere e codice che devi decifrare.

Più CTE nella stessa query

Puoi concatenare diverse CTE, separate da virgole. Ognuna può fare riferimento a quelle definite prima, così costruisci una pipeline di passaggi con un nome:

CREATE TABLE orders (id INTEGER PRIMARY KEY, customer TEXT, amount REAL);
INSERT INTO orders (customer, amount) VALUES
    ('Ada', 120), ('Boris', 45), ('Ada', 80),
    ('Carla', 200), ('Boris', 30), ('Ada', 50);

WITH
    customer_totals AS (
        SELECT customer, SUM(amount) AS total
        FROM orders
        GROUP BY customer
    ),
    big_spenders AS (
        SELECT customer, total
        FROM customer_totals
        WHERE total > 100
    )
SELECT customer, total
FROM big_spenders
ORDER BY total DESC;

Un solo WITH, poi le definizioni delle CTE separate da virgole. La seconda CTE (big_spenders) legge dalla prima (customer_totals) proprio come leggerebbe da una tabella. La SELECT principale segue l'ultima definizione di CTE.

Un errore frequente: scrivere di nuovo WITH davanti alla seconda CTE. Non farlo: è un errore di sintassi. Un solo WITH le copre tutte.

Usare una CTE più di una volta

È qui che le CTE superano davvero le subquery. Se ti serve lo stesso risultato intermedio in due punti, una CTE ti permette di calcolarlo una volta e usarlo due volte:

CREATE TABLE orders (id INTEGER PRIMARY KEY, customer TEXT, amount REAL);
INSERT INTO orders (customer, amount) VALUES
    ('Ada', 120), ('Boris', 45), ('Ada', 80),
    ('Carla', 200), ('Boris', 30), ('Ada', 50);

WITH customer_totals AS (
    SELECT customer, SUM(amount) AS total
    FROM orders
    GROUP BY customer
)
SELECT
    customer,
    total,
    total - (SELECT AVG(total) FROM customer_totals) AS diff_from_avg
FROM customer_totals
ORDER BY diff_from_avg DESC;

La CTE viene citata due volte: una per calcolare la media, una come sorgente principale. Senza la CTE dovresti duplicare la query con GROUP BY, e ogni modifica andrebbe fatta in due punti.

CTE con INSERT, UPDATE e DELETE

Le CTE non servono solo per la SELECT. Puoi mettere una clausola WITH davanti a INSERT, UPDATE o DELETE per usare una subquery con nome in un'operazione di scrittura:

CREATE TABLE orders (id INTEGER PRIMARY KEY, customer TEXT, amount REAL);
CREATE TABLE flagged_customers (customer TEXT, total REAL);
INSERT INTO orders (customer, amount) VALUES
    ('Ada', 120), ('Boris', 45), ('Ada', 80),
    ('Carla', 200), ('Boris', 30);

WITH customer_totals AS (
    SELECT customer, SUM(amount) AS total
    FROM orders
    GROUP BY customer
)
INSERT INTO flagged_customers (customer, total)
SELECT customer, total
FROM customer_totals
WHERE total > 100;

SELECT * FROM flagged_customers;

La CTE descrive quali righe segnalare. L'INSERT ... SELECT la usa come sorgente. Lo stesso trucco funziona con DELETE FROM ... WHERE id IN (SELECT id FROM cte) per cancellazioni a tappe in cui la logica di selezione è complicata.

Quando usare una CTE

Alcune regole pratiche:

La query ha più di un passaggio logico. Aggregare, poi filtrare sull'aggregato, poi fare il JOIN del risultato: è una pipeline, e una CTE per passaggio la rende leggibile.
Altrimenti ripeteresti la stessa subquery. Definiscila una volta, usala due volte.
La subquery merita un nome. Se metteresti un commento sopra la subquery per spiegare cosa rappresenta, il nome della CTE è quel commento, e la sintassi lo rende obbligatorio.
Stai per scrivere una query ricorsiva. È possibile solo con WITH RECURSIVE, che vedrai nella prossima pagina.

Quando non vale la pena:

Una singola subquery semplice usata in un solo punto. WHERE id IN (SELECT id FROM ...) va benissimo così.
Query critiche per le prestazioni in cui hai già verificato che scrivere la logica in linea aiuta. SQLite di solito tratta una CTE come barriera di ottimizzazione in modo meno aggressivo di altri database, ma sui percorsi critici conviene controllare con EXPLAIN QUERY PLAN.

Un esempio completo

Mettiamo tutto insieme: un piccolo report che trova l'ordine più grande di ogni cliente e come si confronta con la sua media:

CREATE TABLE orders (id INTEGER PRIMARY KEY, customer TEXT, amount REAL);
INSERT INTO orders (customer, amount) VALUES
    ('Ada', 120), ('Boris', 45), ('Ada', 80),
    ('Carla', 200), ('Boris', 30), ('Ada', 50),
    ('Carla', 75), ('Boris', 90);

WITH
    per_customer AS (
        SELECT customer, MAX(amount) AS biggest, AVG(amount) AS average
        FROM orders
        GROUP BY customer
    ),
    with_ratio AS (
        SELECT customer, biggest, average, biggest / average AS ratio
        FROM per_customer
    )
SELECT customer, biggest, ROUND(average, 2) AS average, ROUND(ratio, 2) AS ratio
FROM with_ratio
ORDER BY ratio DESC;

Due CTE, ognuna fa una cosa sola. La SELECT principale formatta il risultato. Puoi leggere la query dall'alto verso il basso e capire ogni passaggio da solo: ed è proprio questo il senso delle CTE.

Prossimo passo: CTE ricorsive

Finora hai visto solo CTE normali: una subquery con nome, valutata una volta. SQLite supporta anche WITH RECURSIVE, in cui una CTE fa riferimento a sé stessa per percorrere gerarchie, generare sequenze o attraversare grafi. È l'argomento della prossima pagina.

Domande frequenti

Cos'è una CTE in SQLite?

Una Common Table Expression è una subquery con un nome che si trova in cima a una SELECT, INSERT, UPDATE o DELETE. La introduci con la parola chiave WITH, le dai un nome e poi usi quel nome nella query principale come se fosse una tabella. Le CTE rendono leggibili le query complesse perché ti permettono di costruire il risultato un passo alla volta.

Che differenza c'è tra una CTE e una subquery in SQLite?

Possono produrre risultati identici: una CTE è in sostanza una subquery estratta e a cui è stato dato un nome. La differenza sta nella leggibilità e nel riuso: una CTE può essere citata più volte nella stessa query, e il suo nome documenta cosa rappresenta il risultato intermedio. Per un filtro isolato va bene una subquery; per una logica in più passaggi vince la CTE.

Posso avere più CTE in una sola query SQLite?

Sì. Dopo il primo WITH, separa le CTE aggiuntive con delle virgole, senza ripetere WITH. Ogni CTE può fare riferimento a quelle definite prima, quindi puoi costruire una pipeline di passaggi con un nome. La SELECT principale segue l'ultima CTE.

Concetti correlati