CTE semplici per la preparazione dei dati: esempi e casi reali

CTE semplici per la preparazione dei dati: esempi e casi reali

Sembra che tu abbia già imparato le basi dei CTE e magari scrivi WITH quasi in automatico. Oggi andiamo un po' più a fondo — e vediamo come usare i CTE per preparare i dati in situazioni reali. Immagina di dover creare un report o una query SQL complessa: prima devi scomporre gli ingredienti — e solo dopo cucinare una bella “zuppa” analitica.

Qui il CTE è uno strumento top per i passaggi intermedi: filtraggio, conteggi, aggregazioni, calcolo delle medie — tutto quello che serve per preparare i dati in modo sensato. Puoi spezzare una query complessa in blocchi logici chiari, ognuno dei quali fa solo una cosa: seleziona i record giusti, calcola la media o prepara i dati per la selezione finale. Così il codice si legge meglio, eviti ripetizioni e spesso non hai nemmeno bisogno di tabelle temporanee.

Il metodo CTE è particolarmente utile quando prepari dati per report, costruisci filtraggi complessi o vuoi “pulire” i dati prima di altre elaborazioni. In questo senso il CTE non è solo una tecnica, ma una vera strategia — costruisci la logica passo dopo passo, senza perdere il controllo su quello che succede.

Pronti? Passiamo subito agli esempi.

Filtrare i dati con i CTE

Il CTE è un modo super per “tirare fuori” solo i dati che ti servono da una tabella, così poi lavori solo con quelli che ti interessano davvero. Invece di scrivere query annidate complicate, prima filtri i dati, dai un nome a questo step — e poi lavori con il risultato come fosse una tabella normale.

Immagina di avere una tabella students dove sono salvati i voti degli studenti:

Tabella students

Supponiamo che vuoi selezionare tutti quelli che hanno un voto sopra 85. Con il CTE è super trasparente:

WITH excellent_students AS (
    SELECT student_id, first_name, last_name, grade
    FROM students
    WHERE grade > 85
)
SELECT * FROM excellent_students;

Risultato:

Cosa c’è di comodo qui?

Hai già selezionato le righe che ti servono e dato un nome a questo step — excellent_students. Ora puoi usare questo risultato per altro: unirlo a un’altra tabella, fare un altro filtro o calcolare la media dei voti. Tutto leggibile, semplice e non ti incasina, soprattutto se la query è lunga.

Aggregare i dati con i CTE

Ora vediamo un caso in cui devi contare i record o calcolare delle medie. Per esempio, hai una tabella enrollments dove sono salvati i dati su quali studenti sono iscritti a quali corsi.

Tabella enrollments

Vogliamo sapere quanti studenti sono iscritti a ogni corso.

Esempio di query:

WITH course_enrollments AS (
    SELECT course_id, COUNT(student_id) AS student_count
    FROM enrollments
    GROUP BY course_id
)
SELECT * FROM course_enrollments;

Risultato:

Qui è importante:

• Abbiamo raggruppato i dati per course_id e contato gli studenti per ogni corso.
• La tabella course_enrollments ora contiene queste info, e puoi usarla per altre analisi.

Preparare i dati per i report

Se devi mettere insieme un report dettagliato, basato su più step di elaborazione dati, il CTE è una vera chicca. Ti permette di spezzare tutta la logica in blocchi chiari senza creare tabelle temporanee inutili. Immagina di avere una tabella grades con i voti e una tabella students con le info sugli studenti. Devi fare un report con solo gli studenti che hanno una media sopra 80.

Tabella grades

Tabella students

Invece di una query annidata gigante, puoi fare tutto step by step:

WITH avg_grades AS (
    SELECT student_id, AVG(grade) AS avg_grade
    FROM grades
    GROUP BY student_id
    HAVING AVG(grade) > 80
),
students_with_grades AS (
    SELECT s.student_id, s.first_name, s.last_name, ag.avg_grade
    FROM students s
    JOIN avg_grades ag ON s.student_id = ag.student_id
)
SELECT * FROM students_with_grades;

Nel primo step (avg_grades) abbiamo calcolato la media dei voti per ogni studente e filtrato solo quelli con risultati buoni — sopra 80. Nel secondo step (students_with_grades) abbiamo unito questi dati con la tabella students per avere nomi e cognomi. Così il SELECT finale ti dà una tabella pronta da mettere nel report — tutto già calcolato, filtrato e bello ordinato.

Risultato:

Questo è proprio il bello dei CTE: puoi concentrarti sulla logica e sulla struttura, senza perdere tempo a creare e cancellare tabelle temporanee.

Calcolo di metriche complesse

A volte devi combinare dati diversi in una sola query. Per esempio, vogliamo calcolare per ogni corso:

1. Il numero di studenti.
2. La media dei voti per corso.

Esempio di query:

WITH course_counts AS (
    SELECT course_id, COUNT(student_id) AS student_count
    FROM enrollments
    GROUP BY course_id
),
course_avg_grades AS (
    SELECT e.course_id, AVG(g.grade) AS avg_grade
    FROM enrollments e
    JOIN grades g ON e.student_id = g.student_id
    GROUP BY e.course_id
)
SELECT cc.course_id, cc.student_count, cag.avg_grade
FROM course_counts cc
JOIN course_avg_grades cag ON cc.course_id = cag.course_id;

Errori da evitare

Lavorando con i CTE, è facile incasinarsi e fare un paio di errori classici.

Il primo — materializzazione eccessiva. Se crei troppi CTE, PostgreSQL potrebbe salvarli come tabelle temporanee anche se ti servono solo una volta. Alla fine la query va più lenta di quanto vorresti.

Il secondo errore — filtri messi male. Se applichi i filtri nell’ordine sbagliato o in step diversi, il risultato finale potrebbe non essere quello che ti aspetti. Tipo, rischi di scartare dati importanti troppo presto.

Quindi i CTE usali dove i dati passano più trasformazioni di fila — è lì che danno il meglio e ti aiutano a scrivere codice pulito, chiaro ed efficace.