Narcis Miclaus

8 min di lettura

SQL Server, dalle fondamenta Lezione 12 / 40

Funzioni di finestra: ROW_NUMBER, LAG, LEAD, SUM OVER

La feature SQL che trasforma self-join e sottoquery correlate in una sola riga. Ranking, totali progressivi, delta periodo su periodo, e la clausola OVER spiegata.

Le funzioni di finestra sono la feature che rimpiangi di più di non aver imparato prima. Ogni volta che le presento a uno sviluppatore di metà carriera, vedo l’esatto momento in cui realizza che metà del suo codice passato avrebbe potuto essere cinque volte più corto. Sono così d’impatto.

Una funzione di finestra calcola un valore per ogni riga basandosi su una “finestra” di righe correlate — senza collassare il risultato in aggregati come fa GROUP BY. Totali progressivi, ranking, confronti con la riga precedente, medie mobili, calcoli periodo-su-periodo — tutti diventano espressioni di una riga invece di self-join o sottoquery correlate.

La lezione di oggi è la singola lezione SQL con il rapporto valore/sforzo più alto di tutto il corso. Leggila due volte.

La forma: funzione aggregata + clausola OVER

Una funzione di finestra è una qualsiasi funzione di aggregazione o ranking seguita da una clausola OVER (...):

SELECT OrderId,
       CountryCode,
       Total,
       SUM(Total) OVER (PARTITION BY CountryCode) AS country_revenue,
       ROW_NUMBER() OVER (PARTITION BY CountryCode ORDER BY Total DESC) AS rank_in_country
FROM Sales.Orders;

SUM(Total) OVER (PARTITION BY CountryCode) significa: “per ogni riga, calcola la SUM di Total per le righe con lo stesso CountryCode di questa riga”. Ogni riga conserva i propri dati E ottiene la somma a livello di paese. Nessun collasso. Nessuna join.

La clausola OVER ha tre parti opzionali:

  • PARTITION BY <cols> — divide le righe in gruppi (finestre indipendenti).
  • ORDER BY <cols> — ordina all’interno di ogni partizione (conta per i totali progressivi e per ROW_NUMBER).
  • ROWS BETWEEN ... AND ... — il frame, controlla quali righe della partizione vede la funzione.

Ogni combinazione di queste tre ti dà una finestra diversa.

Funzioni di ranking

ROW_NUMBER()

Assegna 1, 2, 3… alle righe ordinate all’interno di una partizione.

-- Classifica i clienti per LTV all'interno del loro paese
SELECT
    c.CustomerId,
    c.Name,
    c.Country,
    SUM(o.Total) AS ltv,
    ROW_NUMBER() OVER (
        PARTITION BY c.Country
        ORDER BY SUM(o.Total) DESC
    ) AS country_rank
FROM Sales.Customer c
JOIN Sales.Orders   o ON o.CustomerId = c.CustomerId
GROUP BY c.CustomerId, c.Name, c.Country;

Top 3 clienti per paese: avvolgi quanto sopra in una CTE e filtra WHERE country_rank <= 3. Questo è il pattern “top N per gruppo” che abbiamo anticipato nella lezione 8.

RANK() e DENSE_RANK()

Come ROW_NUMBER ma gestiscono i pareggi diversamente:

  • RANK() — i pareggi ottengono lo stesso rank, la riga successiva salta. 1, 2, 2, 4.
  • DENSE_RANK() — i pareggi ottengono lo stesso rank, la riga successiva non salta. 1, 2, 2, 3.
  • ROW_NUMBER() — i pareggi ottengono rank arbitrariamente diversi. 1, 2, 3, 4.

Usa RANK quando “due clienti pari merito al 2° posto, il successivo è 4°” è la semantica che vuoi. ROW_NUMBER quando hai bisogno di numeri di rank unici e densi per paginazione o deduplicazione.

NTILE(n)

Divide la partizione in n bucket approssimativamente uguali:

-- Dividi i clienti in 4 quartili per LTV
SELECT CustomerId, ltv,
       NTILE(4) OVER (ORDER BY ltv DESC) AS ltv_quartile
FROM customer_ltv;

Il quartile 1 è il 25% migliore per LTV, il quartile 4 è il 25% peggiore. Il marketing lo usa di continuo per la segmentazione.

Funzioni di offset: LAG e LEAD

LAG(col, n, default) restituisce la colonna da n righe prima nella partizione. LEAD è lo specchio — da n righe dopo.

-- Per ogni mese, mostra il fatturato e la variazione mese su mese
WITH monthly AS (
    SELECT DATEFROMPARTS(YEAR(OrderDate), MONTH(OrderDate), 1) AS month_start,
           SUM(Total) AS revenue
    FROM Sales.Orders
    GROUP BY DATEFROMPARTS(YEAR(OrderDate), MONTH(OrderDate), 1)
)
SELECT month_start,
       revenue,
       LAG(revenue, 1) OVER (ORDER BY month_start)      AS prev_month,
       revenue - LAG(revenue, 1) OVER (ORDER BY month_start) AS delta,
       (revenue - LAG(revenue, 1) OVER (ORDER BY month_start))
           * 100.0 / NULLIF(LAG(revenue, 1) OVER (ORDER BY month_start), 0) AS mom_pct
FROM monthly
ORDER BY month_start;

Crescita mese su mese. Prima delle funzioni di finestra questo richiedeva di fare join della tabella aggregata con sé stessa su un mese di offset. Con LAG, una sola passata.

Pattern pratico: “anno su anno” è LAG(revenue, 12) su dati mensili. “Delta trimestre precedente” è LAG(revenue, 1) su dati trimestrali. Stessa forma, offset diversi.

Totali progressivi e medie mobili

Le funzioni aggregate con ORDER BY nella clausola OVER diventano cumulative di default:

-- Totale progressivo del fatturato per giorno
SELECT CAST(OrderDate AS DATE) AS day,
       SUM(Total) OVER (ORDER BY CAST(OrderDate AS DATE)
                        ROWS UNBOUNDED PRECEDING) AS revenue_running
FROM Sales.Orders
ORDER BY day;

ROWS UNBOUNDED PRECEDING dice a SQL Server: “per ogni riga, somma dalla prima riga della partizione fino alla riga corrente”. Questo è il default quando usi ORDER BY senza un frame esplicito, ma lo scrivo esplicitamente perché rende l’intento ovvio.

Media mobile sugli ultimi 7 giorni:

SELECT day,
       daily_revenue,
       AVG(daily_revenue) OVER (
           ORDER BY day
           ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
       ) AS moving_avg_7d
FROM daily_revenue;

“Da 6 righe prima alla riga corrente” = una finestra di 7 giorni. La metrica “media mobile a 7 giorni” della tua dashboard è letteralmente questa singola riga.

Specifica del frame

Il frame controlla quali righe vede la funzione. Quattro pattern comuni:

-- Totale progressivo (default con ORDER BY)
ROWS UNBOUNDED PRECEDING

-- Finestra di dimensione fissa che precede
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW

-- Finestra centrata
ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING

-- L'intera partizione (ignora l'ordine)
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

Puoi anche usare RANGE BETWEEN invece di ROWS BETWEEN — RANGE è basato sul valore (per esempio “tutte le righe entro 3 giorni da questa”) mentre ROWS è basato sul conteggio delle righe. Sottile e leggermente avanzato; ROWS è quello che vuoi nel 90% dei casi.

Totale progressivo con reset: il pattern “cumulativo per categoria”

-- Totale progressivo del fatturato per cliente, ordinato per la sua storia ordini
SELECT o.CustomerId,
       o.OrderDate,
       o.Total,
       SUM(o.Total) OVER (
           PARTITION BY o.CustomerId
           ORDER BY o.OrderDate, o.OrderId
           ROWS UNBOUNDED PRECEDING
       ) AS ltv_at_this_point
FROM Sales.Orders AS o;

LTV progressivo di ogni cliente dopo ogni ordine, che si resetta per cliente grazie al PARTITION BY. Utile per analisi di coorte, curve di retention e ogni domanda del tipo “al momento dell’ordine N, la spesa totale di questo cliente era X”.

FIRST_VALUE e LAST_VALUE

Restituiscono il primo o l’ultimo valore nel frame. Comunemente usate per portare un attributo per-partizione accanto alla riga:

-- Ogni ordine, più la data del primo ordine in assoluto del cliente
SELECT o.CustomerId,
       o.OrderId,
       o.OrderDate,
       FIRST_VALUE(o.OrderDate) OVER (
           PARTITION BY o.CustomerId
           ORDER BY o.OrderDate, o.OrderId
       ) AS first_order_date,
       DATEDIFF(DAY,
           FIRST_VALUE(o.OrderDate) OVER (
               PARTITION BY o.CustomerId
               ORDER BY o.OrderDate, o.OrderId
           ),
           o.OrderDate
       ) AS days_since_first_order
FROM Sales.Orders AS o;

Ogni riga ora sa quanti giorni dopo il primo ordine del cliente è stata piazzata. Perfetto per i report sul ciclo di vita del cliente.

Trappola con LAST_VALUE: il frame di default per la maggior parte delle funzioni di finestra è “dalla prima riga alla riga corrente”, quindi LAST_VALUE senza un frame esplicito restituisce la riga corrente, non l’ultima della partizione. Usa sempre il frame esplicito:

LAST_VALUE(OrderDate) OVER (
    PARTITION BY CustomerId
    ORDER BY OrderDate
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
)

Deduplicazione con ROW_NUMBER

Una delle migliori applicazioni di ROW_NUMBER: eliminare righe duplicate mantenendone una specifica.

Diciamo che l’import dei clienti di Runehold ha creato per errore più righe per email. Mantieni la più vecchia:

WITH ranked AS (
    SELECT CustomerId, Email, Name, CreatedAt,
           ROW_NUMBER() OVER (
               PARTITION BY Email
               ORDER BY CreatedAt ASC, CustomerId ASC
           ) AS rn
    FROM Sales.Customer
)
DELETE FROM ranked WHERE rn > 1;

Incredibilmente utile. Anche un posto dove il tiebreaker deterministico conta: includi sempre la primary key nell’ORDER BY così la decisione “tieni questo” è riproducibile.

Funzioni di finestra vs GROUP BY

Confusione comune dei principianti:

-- GROUP BY: collassa le righe, una riga in uscita per paese
SELECT CountryCode, SUM(Total) AS revenue
FROM Sales.Orders
GROUP BY CountryCode;

-- Window: tiene ogni riga, ogni riga mostra il totale del proprio paese
SELECT OrderId, CountryCode, Total,
       SUM(Total) OVER (PARTITION BY CountryCode) AS country_revenue
FROM Sales.Orders;

Usa GROUP BY quando vuoi il risultato aggregato come righe. Usa le finestre quando vuoi che ogni riga conosca l’aggregato del proprio gruppo.

Combinarle con le CTE per leggibilità

La logica di finestra complessa diventa rapidamente illeggibile. Le CTE aiutano:

WITH order_stats AS (
    SELECT o.OrderId,
           o.CustomerId,
           o.Total,
           o.OrderDate,
           ROW_NUMBER() OVER (PARTITION BY o.CustomerId
                              ORDER BY o.OrderDate, o.OrderId) AS order_seq,
           SUM(o.Total) OVER (PARTITION BY o.CustomerId
                              ORDER BY o.OrderDate, o.OrderId
                              ROWS UNBOUNDED PRECEDING) AS cumulative_ltv,
           LAG(o.OrderDate, 1) OVER (PARTITION BY o.CustomerId
                                      ORDER BY o.OrderDate, o.OrderId) AS prev_order_date
    FROM Sales.Orders AS o
)
SELECT CustomerId,
       OrderId,
       order_seq,
       OrderDate,
       cumulative_ltv,
       DATEDIFF(DAY, prev_order_date, OrderDate) AS days_since_prev_order
FROM order_stats
ORDER BY CustomerId, order_seq;

Ogni riga: “Questo è il 3° ordine di Maria, il suo LTV progressivo è 245 euro, e questo ordine è stato piazzato 28 giorni dopo il precedente”. Quello è un report di analisi di coorte scritto in circa 15 righe. Prima delle funzioni di finestra, lo stesso report era 50+ righe di self-join e la gente piangeva.

Esegui questo sulla tua macchina

USE Runehold;
GO

-- Classifica i clienti per LTV all'interno del loro paese
WITH customer_ltv AS (
    SELECT c.CustomerId, c.Name, c.Country,
           COALESCE(SUM(o.Total), 0) AS ltv
    FROM Sales.Customer AS c
    LEFT JOIN Sales.Orders AS o ON o.CustomerId = c.CustomerId
    GROUP BY c.CustomerId, c.Name, c.Country
)
SELECT Name, Country, ltv,
       ROW_NUMBER() OVER (PARTITION BY Country ORDER BY ltv DESC) AS country_rank,
       NTILE(4)     OVER (ORDER BY ltv DESC)                    AS global_quartile
FROM customer_ltv
ORDER BY Country, country_rank;

-- Delta giornaliero del fatturato
WITH daily AS (
    SELECT CAST(OrderDate AS DATE) AS day, SUM(Total) AS revenue
    FROM Sales.Orders
    GROUP BY CAST(OrderDate AS DATE)
)
SELECT day,
       revenue,
       LAG(revenue) OVER (ORDER BY day) AS prev_day,
       revenue - LAG(revenue) OVER (ORDER BY day) AS delta_eur
FROM daily
ORDER BY day;

-- Totale progressivo per cliente
SELECT o.CustomerId,
       o.OrderDate,
       o.Total,
       SUM(o.Total) OVER (
           PARTITION BY o.CustomerId
           ORDER BY o.OrderDate, o.OrderId
           ROWS UNBOUNDED PRECEDING
       ) AS ltv_at_order_time
FROM Sales.Orders AS o
ORDER BY o.CustomerId, o.OrderDate;

-- Media mobile su 3 ordini per ogni cliente
SELECT o.CustomerId,
       o.OrderDate,
       o.Total,
       AVG(o.Total) OVER (
           PARTITION BY o.CustomerId
           ORDER BY o.OrderDate, o.OrderId
           ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
       ) AS three_order_avg
FROM Sales.Orders AS o
ORDER BY o.CustomerId, o.OrderDate;

Esegui ognuna, leggi ogni riga e guarda come cambiano le colonne di finestra. Una volta che la forma diventa naturale, metà delle tue query future saranno il 30% più corte e 10 volte più espressive.

Prossima lezione: UNION vs UNION ALL. Breve. Una trappola. Poi nella lezione 14 arriviamo alle funzioni stringa — STRING_AGG, STRING_SPLIT, e la morte degli aggiramenti CLR.

Cerca