SQL Academy · Lezione

Scrivere query analitiche

Segmenti, analizzi e aggreghi le metriche.

Scrivere query analitiche è una lezione SQL Academy gratuita su CoddyKit. Questa è la lezione 4 di 4. Puoi leggere la lezione completa qui gratuitamente — poi esercitati direttamente nel browser con un editor di codice integrato e un tutor IA disponibile 24/7. Fa parte del percorso di apprendimento SQL Academy, e i tuoi progressi si sincronizzano tra il web e l'app CoddyKit. Il corso SQL Academy include 4 lezioni in totale.

Che cosa sono le query analitiche?

Le query analitiche vanno oltre le semplici ricerche di righe. Invece di chiedere quale ordine ha effettuato il cliente 42?, chiedono qual è il fatturato totale per area geografica e trimestre? oppure in che modo questo mese si confronta con il mese scorso?

In un data warehouse basato su uno schema a stella, le query analitiche eseguono slicing (filtrano una dimensione), dicing (filtrano più dimensioni) e roll-up (aggregano a una granularità più grossolana) sui fatti per mettere in evidenza informazioni utili per il business.

Ripasso dello schema a stella

Uno schema a stella contiene una tabella dei fatti centrale, ad esempio fact_sales, circondata da tabelle delle dimensioni, ad esempio dim_date, dim_product e dim_store. Le query analitiche collegano la tabella dei fatti alle dimensioni necessarie per l'analisi corrente.

SELECT
    s.store_name,
    d.year,
    d.quarter,
    SUM(f.revenue)   AS total_revenue,
    SUM(f.units_sold) AS total_units
FROM fact_sales f
JOIN dim_store  s ON s.store_id  = f.store_id
JOIN dim_date   d ON d.date_id   = f.date_id
GROUP BY
    s.store_name,
    d.year,
    d.quarter
ORDER BY
    d.year,
    d.quarter,
    s.store_name;

Slicing: filtrare una dimensione

Lo slicing consiste nel limitare il set di risultati a un singolo valore di una dimensione, ad esempio considerando solo i dati dell'anno 2024. La clausola WHERE è lo strumento per eseguire lo slicing.

Eseguendo lo slicing in anticipo si riduce il numero di righe che il database deve aggregare, mantenendo veloci le query sulle tabelle dei fatti di grandi dimensioni.

-- Slice: only year 2024
SELECT
    p.category,
    SUM(f.revenue) AS total_revenue
FROM fact_sales f
JOIN dim_product p ON p.product_id = f.product_id
JOIN dim_date   d ON d.date_id    = f.date_id
WHERE d.year = 2024
GROUP BY p.category
ORDER BY total_revenue DESC;

Dicing: filtrare più dimensioni

Il dicing consiste nell'applicare contemporaneamente filtri a due o più dimensioni, ad esempio analizzando le vendite di articoli elettronici nella regione Nord durante il Q1. Ogni condizione WHERE aggiuntiva ritaglia un cubo di dati più piccolo.

-- Dice: category = 'Electronics', region = 'North', Q1
SELECT
    d.month,
    SUM(f.revenue)    AS revenue,
    SUM(f.units_sold) AS units
FROM fact_sales f
JOIN dim_product p ON p.product_id = f.product_id
JOIN dim_store   s ON s.store_id   = f.store_id
JOIN dim_date    d ON d.date_id    = f.date_id
WHERE
    p.category  = 'Electronics'
    AND s.region = 'North'
    AND d.year   = 2024
    AND d.quarter = 1
GROUP BY d.month
ORDER BY d.month;

Roll-up: aggregare a una granularità più grossolana

Il roll-up consiste nel passare da una granularità dettagliata (vendite giornaliere per negozio) a una granularità più grossolana (vendite mensili per regione). Per farlo, si rimuovono le colonne di livello inferiore dalla clausola GROUP BY e si esegue una nuova aggregazione.

Il modificatore ROLLUP consente di produrre subtotali e totali complessivi con una sola query, invece di scrivere più blocchi UNION ALL.

-- Roll up from store/month to region/quarter with subtotals
SELECT
    s.region,
    d.quarter,
    SUM(f.revenue) AS revenue
FROM fact_sales f
JOIN dim_store s ON s.store_id = f.store_id
JOIN dim_date  d ON d.date_id  = f.date_id
WHERE d.year = 2024
GROUP BY ROLLUP(s.region, d.quarter)
ORDER BY s.region NULLS LAST, d.quarter NULLS LAST;

Confronti tra periodi con LAG

Uno dei pattern analitici più comuni consiste nel confrontare una metrica con la stessa metrica in un periodo precedente. La funzione finestra LAG() consente di inserire direttamente nella riga corrente il valore della riga precedente, senza un self-join.

In questo esempio si calcola la crescita del fatturato mese su mese in percentuale.

WITH monthly AS (
    SELECT
        d.year,
        d.month,
        SUM(f.revenue) AS revenue
    FROM fact_sales f
    JOIN dim_date d ON d.date_id = f.date_id
    GROUP BY d.year, d.month
)
SELECT
    year,
    month,
    revenue,
    LAG(revenue) OVER (ORDER BY year, month) AS prev_month_revenue,
    ROUND(
        100.0 * (revenue - LAG(revenue) OVER (ORDER BY year, month))
             / NULLIF(LAG(revenue) OVER (ORDER BY year, month), 0),
    2) AS mom_growth_pct
FROM monthly
ORDER BY year, month;

Totali progressivi con SUM OVER

Un totale progressivo (somma cumulativa) aggiunge il valore di ogni riga al totale di tutte le righe precedenti in un ordine definito. È ideale per monitorare il fatturato cumulativo nel corso di un anno o il consumo progressivo di un budget.

La clausola frame ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW rende la finestra esplicita e non ambigua.

SELECT
    d.year,
    d.month,
    SUM(f.revenue)                                      AS monthly_revenue,
    SUM(SUM(f.revenue)) OVER (
        PARTITION BY d.year
        ORDER BY d.month
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    )                                                   AS ytd_revenue
FROM fact_sales f
JOIN dim_date d ON d.date_id = f.date_id
GROUP BY d.year, d.month
ORDER BY d.year, d.month;

Classificare le dimensioni con DENSE_RANK

La classificazione consente di trovare gli elementi con le prestazioni migliori o peggiori all'interno di un gruppo. DENSE_RANK() assegna posizioni consecutive senza interruzioni in caso di pari merito, rendendola la scelta preferita per le classifiche nei report BI.

Racchiudere il risultato classificato in una CTE e filtrare in base alla posizione rende il pattern top-N chiaro e leggibile.

WITH ranked_products AS (
    SELECT
        p.product_name,
        p.category,
        SUM(f.revenue) AS revenue,
        DENSE_RANK() OVER (
            PARTITION BY p.category
            ORDER BY SUM(f.revenue) DESC
        ) AS rnk
    FROM fact_sales f
    JOIN dim_product p ON p.product_id = f.product_id
    JOIN dim_date   d ON d.date_id     = f.date_id
    WHERE d.year = 2024
    GROUP BY p.product_name, p.category
)
SELECT *
FROM ranked_products
WHERE rnk <= 3
ORDER BY category, rnk;

Percentuale di contributo con SUM su una finestra

Conoscere il fatturato assoluto di un prodotto è utile, ma sapere che contribuisce per il 38 % al fatturato della categoria è più significativo per prendere decisioni. Una SUM() su una finestra che comprende l'intera partizione fornisce il denominatore senza un join a una sottoquery.

SELECT
    p.category,
    p.product_name,
    SUM(f.revenue)                               AS product_revenue,
    SUM(SUM(f.revenue)) OVER (PARTITION BY p.category) AS category_revenue,
    ROUND(
        100.0 * SUM(f.revenue)
             / SUM(SUM(f.revenue)) OVER (PARTITION BY p.category),
    1)                                           AS pct_of_category
FROM fact_sales f
JOIN dim_product p ON p.product_id = f.product_id
JOIN dim_date   d ON d.date_id     = f.date_id
WHERE d.year = 2024
GROUP BY p.category, p.product_name
ORDER BY p.category, pct_of_category DESC;

Medie mobili per attenuare l'andamento

I dati sulle vendite giornaliere o settimanali sono rumorosi. Una media mobile attenua le fluttuazioni a breve termine, consentendo di vedere la tendenza sottostante. In questo esempio si calcola una media mobile di 3 mesi utilizzando un intervallo di finestra scorrevole.

WITH monthly_rev AS (
    SELECT
        d.year,
        d.month,
        SUM(f.revenue) AS revenue
    FROM fact_sales f
    JOIN dim_date d ON d.date_id = f.date_id
    GROUP BY d.year, d.month
)
SELECT
    year,
    month,
    revenue,
    ROUND(
        AVG(revenue) OVER (
            ORDER BY year, month
            ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
        ),
    2) AS moving_avg_3m
FROM monthly_rev
ORDER BY year, month;

CUBE per tutte le combinazioni delle dimensioni

CUBE estende ROLLUP calcolando i subtotali per ogni possibile combinazione delle dimensioni elencate, non solo per il percorso gerarchico di roll-up. In questo modo si ottiene il riepilogo completo tra le dimensioni in un'unica passata, utile per dashboard multidimensionali in cui gli utenti possono cambiare liberamente prospettiva.

NULL in una colonna di raggruppamento significa tutti i valori di quella dimensione: utilizzi GROUPING() per distinguere i NULL intenzionali nei dati dai NULL generati dal roll-up.

SELECT
    CASE WHEN GROUPING(s.region)   = 1 THEN 'ALL REGIONS'    ELSE s.region        END AS region,
    CASE WHEN GROUPING(p.category) = 1 THEN 'ALL CATEGORIES' ELSE p.category      END AS category,
    CASE WHEN GROUPING(d.quarter)  = 1 THEN 'ALL QUARTERS'   ELSE d.quarter::TEXT END AS quarter,
    SUM(f.revenue) AS revenue
FROM fact_sales f
JOIN dim_store   s ON s.store_id   = f.store_id
JOIN dim_product p ON p.product_id = f.product_id
JOIN dim_date    d ON d.date_id    = f.date_id
WHERE d.year = 2024
GROUP BY CUBE(s.region, p.category, d.quarter)
ORDER BY s.region NULLS LAST, p.category NULLS LAST, d.quarter NULLS LAST;

Quale operazione limita i risultati a un singolo valore di una dimensione?

Metta alla prova la propria comprensione della terminologia delle query analitiche usata nel data warehousing.

Riepilogo: scrivere query analitiche

In questa lezione ha esplorato i pattern fondamentali per scrivere query analitiche su uno schema a stella:

Slice — filtrare una dimensione con WHERE per concentrarsi su un segmento specifico.
Dice — filtrare contemporaneamente più dimensioni per ritagliare un cubo di dati preciso.
Roll-up — aggregare a una granularità più grossolana; utilizzare ROLLUP o CUBE per ottenere subtotali su più livelli.
LAG / LEAD — confronti tra periodi senza self-join.
Totali progressivi & medie mobili — metriche cumulative e uniformate tramite frame di finestra.
DENSE_RANK — classifiche top-N ordinate e chiare all'interno delle partizioni.
Percentuale di contributo — SUM su una finestra come denominatore per i calcoli delle quote.

La combinazione di questi pattern copre la grande maggioranza dei requisiti di BI e reporting che incontrerà nei data warehouse di produzione.

Domande Frequenti

La lezione «Scrivere query analitiche» è gratuita?

Sì — il testo completo di «Scrivere query analitiche» è gratuito qui sul web. Per esercitarvi in modo interattivo (un editor di codice integrato e un tutor IA 24/7) e sbloccare il resto del corso SQL Academy, passa a CoddyKit PRO. Il corso SQL Academy include 4 lezioni in totale.

Cosa imparerò in «Scrivere query analitiche»?

Segmenti, analizzi e aggreghi le metriche. Eserciti SQL Academy con codice pratico che esegui direttamente nel browser, e un tutor IA 24/7 risponde alle tue domande mentre lavori sulla lezione.

Ho bisogno di esperienza per iniziare SQL Academy?

Non è richiesta alcuna esperienza precedente. SQL Academy su CoddyKit è strutturato per principianti e studenti avanzati, quindi puoi iniziare da qui o dall'inizio e procedere al tuo ritmo. Questa è la lezione 4 di 4.

Quanto tempo richiede la lezione «Scrivere query analitiche»?

La maggior parte delle lezioni CoddyKit richiede circa 5–10 minuti. Ogni lezione è breve e interattiva, quindi fai progressi costanti e riprendi esattamente da dove hai lasciato su web e app.

Posso scrivere ed eseguire codice in questa lezione SQL Academy?

Sì. Ogni lezione SQL Academy include un editor di codice integrato, quindi scrivi ed esegui codice reale direttamente nel tuo browser e ricevi feedback istantaneo dall'IA — nessuna configurazione locale necessaria.

Tutte le lezioni di questo corso

← Torna a SQL Academy