Uso dei CTE per rendere più leggibili le query complesse

Immagina di dover scrivere una mega query SQL che fa subito un sacco di operazioni collegate tra loro. Potresti semplicemente annidare un sacco di subquery una dentro l'altra, ma il risultato sembrerà spaghetti code. Un vero labirinto SQL dove è facile perdersi, anche per chi l'ha scritto.

Il CTE è il tuo salvagente! Il CTE ti permette di spezzare una query complessa in parti logiche, ognuna delle quali è una sezione nominata a parte. Così la tua query diventa chiara e facile da mantenere.

Confronto: Subquery vs CTE

A prima vista, entrambi i metodi fanno la stessa cosa — filtrano i voti per corso e calcolano la media per ogni studente. Ma guarda meglio: nella versione con la subquery la logica è "nascosta" tra le parentesi, mentre col CTE è fuori e ha un nome chiaro, filtered_grades. Ora immagina se i passaggi intermedi non fossero due, ma dieci!

Subquery:

SELECT student_id, AVG(grade) AS avg_grade
FROM (
    SELECT student_id, grade
    FROM grades
    WHERE course_id = 101
) subquery
GROUP BY student_id;

CTE:

WITH filtered_grades AS (
    SELECT student_id, grade
    FROM grades
    WHERE course_id = 101
)
SELECT student_id, AVG(grade) AS avg_grade
FROM filtered_grades
GROUP BY student_id;

Trova 10 differenze. Ovviamente, il CTE vince in quanto a leggibilità!

Spezzare le query complesse in step con i CTE

Il CTE ti permette di costruire la query passo dopo passo, così che ad ogni step il risultato sia il più chiaro possibile. Per esempio, se vuoi ottenere una lista di studenti con la loro media per corso e aggiungere anche i dati dei loro insegnanti, dividi il problema in più parti.

Esempio:

WITH avg_grades AS (
    SELECT student_id, course_id, AVG(grade) AS avg_grade
    FROM grades
    GROUP BY student_id, course_id
),
course_teachers AS (
    SELECT course_id, teacher_id
    FROM courses
)

SELECT ag.student_id, ag.avg_grade, ct.teacher_id
FROM avg_grades ag
JOIN course_teachers ct ON ag.course_id = ct.course_id;

Non è leggibile? Anche se torni su questa query tra un mese, la struttura sarà ancora chiarissima.

Uso di più CTE per un report grosso

Dai, vediamo un esempio di report più complesso. Immagina di avere un database universitario e vuoi creare un report sugli studenti più bravi, i loro corsi e i loro insegnanti. Il piano è questo:

1. Prima troviamo gli studenti con una media alta (sopra 90).
2. Poi li colleghiamo ai corsi.
3. Infine aggiungiamo i dati degli insegnanti.

Query con più CTE:

WITH high_achievers AS (
    SELECT student_id, AVG(grade) AS avg_grade
    FROM grades
    GROUP BY student_id
    HAVING AVG(grade) > 90
),
student_courses AS (
    SELECT e.student_id, c.course_name, c.teacher_id
    FROM enrollments e
    JOIN courses c ON e.course_id = c.course_id
),
teachers AS (
    SELECT teacher_id, name AS teacher_name
    FROM teachers
)

SELECT ha.student_id, ha.avg_grade, sc.course_name, t.teacher_name
FROM high_achievers ha
JOIN student_courses sc ON ha.student_id = sc.student_id
JOIN teachers t ON sc.teacher_id = t.teacher_id;

Questa query è bella perché ogni parte del problema è un blocco logico separato. Vuoi sapere chi sono gli studenti che hanno fatto faville? Guarda nel CTE high_achievers. Ti interessa il collegamento con i corsi? È in student_courses. I prof che ti servono? Tutto in teachers. Questo modo di lavorare rende molto più facile mantenere e modificare il codice.

Spezzare in step i calcoli complessi

A volte le tue query includono calcoli o filtri complicati. Invece di provare a ficcare tutto in una query chilometrica, dividila in più CTE.

Esempio:

WITH course_stats AS (
    SELECT course_id, COUNT(student_id) AS student_count, AVG(grade) AS avg_grade
    FROM grades
    GROUP BY course_id
),
popular_courses AS (
    SELECT course_id
    FROM course_stats
    WHERE student_count > 50
)
SELECT c.course_name, cs.student_count, cs.avg_grade
FROM popular_courses pc
JOIN course_stats cs ON pc.course_id = cs.course_id
JOIN courses c ON c.course_id = pc.course_id;

Qui prima raccogliamo le statistiche dei corsi in course_stats, poi filtriamo i corsi popolari in popular_courses, e solo dopo uniamo tutto con la tabella dei corsi. Questo metodo ti permette di evidenziare i passaggi intermedi, rendendo la query molto più comprensibile.

Quando il CTE è insostituibile?

Ecco alcune situazioni dove il CTE è davvero una bomba:

1. Analisi e reportistica. Tipo, calcolare indicatori complessi con filtri per gruppi.
2. Lavorare con strutture gerarchiche. CTE ricorsivi per costruire alberi di categorie o strutture organizzative.
3. Riutilizzo dei dati. Per esempio, se la stessa selezione serve in più step della query.

Errori tipici con i CTE

Ovviamente, come ogni strumento potente, anche il CTE ha le sue trappole nascoste.

Materializzazione eccessiva dei dati. In PostgreSQL i CTE per default vengono "materializzati", cioè il risultato viene calcolato e salvato temporaneamente. Questo può rallentare tutto se i dati sono tanti. Per evitarlo, usa gli indici e cerca di selezionare solo le colonne che ti servono davvero.

Join sbagliati. A volte le query complesse con tanti CTE diventano difficili da ottimizzare. Controlla sempre le tue query con EXPLAIN o EXPLAIN ANALYZE.

Uso eccessivo dei CTE. Se i tuoi CTE diventano troppo lunghi e incasinati, forse è il caso di spezzare la query in più operazioni separate.