Esempi di query complesse con più CTE

In questa lezione facciamo un po' di vera magia con le query! Creeremo diversi esempi usando più CTE per farti vedere come si possono integrare tra loro per costruire query complesse e a più step. Questi esempi ti torneranno utili nella vita reale, soprattutto quando hai a che fare con analisi complicate.

Esempio 1: Analisi del rendimento degli studenti

Immagina di avere un database universitario con tre tabelle:

Tabella students:

CREATE TABLE students (
    student_id SERIAL PRIMARY KEY,
    student_name TEXT NOT NULL
);

Tabella grades:

CREATE TABLE grades (
    grade_id SERIAL PRIMARY KEY,
    student_id INT REFERENCES students(student_id),
    course_id INT NOT NULL,
    grade NUMERIC(3, 1) NOT NULL
);

Tabella courses:

CREATE TABLE courses (
    course_id SERIAL PRIMARY KEY,
    course_name TEXT NOT NULL
);

Obiettivo: ottenere la lista degli studenti con una media superiore a 85, indicando la loro media e i nomi dei corsi che frequentano.

Query:

WITH high_achievers AS (
    -- Troviamo gli studenti con una media alta
    SELECT 
        student_id, 
        AVG(grade) AS avg_grade
    FROM grades
    GROUP BY student_id
    HAVING AVG(grade) > 85
),
student_courses AS (
    -- Troviamo i corsi a cui è iscritto ogni studente
    SELECT 
        s.student_id, 
        c.course_name
    FROM grades g
    JOIN courses c ON g.course_id = c.course_id
    JOIN students s ON g.student_id = s.student_id
)
-- Uniamo i risultati
SELECT 
    s.student_name, 
    ha.avg_grade, 
    sc.course_name
FROM high_achievers ha
JOIN student_courses sc ON ha.student_id = sc.student_id
JOIN students s ON s.student_id = ha.student_id;

Spiegazione:

1. Nel primo CTE (high_achievers) calcoliamo la media per ogni studente e selezioniamo quelli con media sopra 85.
2. Nel secondo CTE (student_courses) associamo gli studenti ai loro corsi.
3. Nella query principale uniamo i dati dei due CTE per ottenere la lista degli studenti, la loro media e i corsi che frequentano.

Esempio 2: Report vendite per un e-commerce

Immagina di lavorare per un e-commerce e di avere queste tabelle:

Tabella orders (ordini):

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INT NOT NULL,
    order_date DATE NOT NULL,
    total_amount NUMERIC(10, 2) NOT NULL
);

Tabella customers (clienti):

CREATE TABLE customers (
    customer_id SERIAL PRIMARY KEY,
    customer_name TEXT NOT NULL
);

Tabella order_items (prodotti negli ordini):

CREATE TABLE order_items (
    order_item_id SERIAL PRIMARY KEY,
    order_id INT REFERENCES orders(order_id),
    product_id INT NOT NULL,
    quantity INT NOT NULL,
    price NUMERIC(10, 2) NOT NULL
);

Obiettivo: creare un report che mostri per ogni cliente:

• Il numero totale di ordini.
• La somma totale di tutti gli ordini dell’ultimo mese.
• La lista di tutti i prodotti unici che ha acquistato.

Query:

WITH recent_orders AS (
    -- Selezioniamo gli ordini dell’ultimo mese
    SELECT 
        order_id, 
        customer_id, 
        total_amount 
    FROM orders
    WHERE order_date >= CURRENT_DATE - INTERVAL '1 month'
),
customer_summary AS (
    -- Calcoliamo il numero totale di ordini e la somma per ogni cliente
    SELECT 
        ro.customer_id,
        COUNT(ro.order_id) AS total_orders,
        SUM(ro.total_amount) AS total_spent
    FROM recent_orders ro
    GROUP BY ro.customer_id
),
customer_products AS (
    -- Selezioniamo i prodotti unici acquistati da ogni cliente
    SELECT DISTINCT
        ro.customer_id,
        oi.product_id
    FROM recent_orders ro
    JOIN order_items oi ON ro.order_id = oi.order_id
)
-- Uniamo i risultati
SELECT 
    c.customer_name,
    cs.total_orders,
    cs.total_spent,
    ARRAY_AGG(cp.product_id) AS purchased_products
FROM customer_summary cs
JOIN customers c ON cs.customer_id = c.customer_id
JOIN customer_products cp ON cp.customer_id = c.customer_id
GROUP BY c.customer_name, cs.total_orders, cs.total_spent;

Spiegazione:

1. Nel primo CTE (recent_orders) selezioniamo gli ordini dell’ultimo mese.
2. Nel secondo CTE (customer_summary) calcoliamo il numero totale di ordini e la somma per ogni cliente.
3. Nel terzo CTE (customer_products) otteniamo i prodotti unici acquistati da ogni cliente.
4. Nella query finale uniamo i dati e usiamo ARRAY_AGG() per creare la lista dei prodotti unici.

Esempio 3: Analisi della gerarchia dei dipendenti

Abbiamo una tabella dei dipendenti:

Tabella employees:

CREATE TABLE employees (
    employee_id SERIAL PRIMARY KEY,
    employee_name TEXT NOT NULL,
    manager_id INT NULL
);

Obiettivo: costruire la gerarchia dei dipendenti a partire dal CEO. Indicare il livello di ogni dipendente nella gerarchia.

Query:

WITH RECURSIVE employee_hierarchy AS (
    -- Partiamo dai dipendenti senza manager (il CEO)
    SELECT 
        employee_id, 
        employee_name, 
        manager_id, 
        1 AS level
    FROM employees
    WHERE manager_id IS NULL
    UNION ALL
    -- Aggiungiamo tutti i subordinati del livello attuale
    SELECT 
        e.employee_id,
        e.employee_name,
        e.manager_id,
        eh.level + 1
    FROM employees e
    JOIN employee_hierarchy eh ON e.manager_id = eh.employee_id
)
-- Mostriamo la gerarchia
SELECT 
    employee_id, 
    employee_name, 
    manager_id, 
    level
FROM employee_hierarchy
ORDER BY level, employee_id;

1. Nella query ricorsiva partiamo dal CEO (dipendenti senza manager).
2. Ad ogni step aggiungiamo i subordinati del livello attuale, aumentando il level di uno.
3. Nella query principale selezioniamo tutta la gerarchia, ordinata per livello e id dipendente.

Consigli utili ed errori tipici

• Troppi CTE: non usarli dove basta una subquery. I CTE a volte possono rallentare le performance perché materializzano i dati.
• Nomi dei CTE: dai nomi chiari e corti ai tuoi CTE, così le query restano leggibili.
• Ordine di esecuzione: ricorda che i CTE vengono eseguiti esattamente nell’ordine in cui li dichiari.
• Raggruppamento dati: usa GROUP BY solo dove serve, per evitare operazioni inutili.

Tutti questi esempi ti fanno vedere come i CTE possono essere usati per spezzare problemi complessi in step più semplici, migliorando la leggibilità e la manutenzione delle query. Ora hai gli strumenti per affrontare anche le analisi più toste con PostgreSQL!