Window functions: struttura mentale

Una tabella di ordini contiene tre righe per lo stesso cliente, due timestamp quasi identici e un campo di stato aggiornato in ritardo. La domanda non è quale riga vedere per prima, ma quale riga rappresenta il fatto che vuoi difendere. Window functions: struttura mentale parte da qui: partition, order e frame diventano il modo per decidere il perimetro del confronto prima di scrivere la funzione.

Una scena da cui partire

Leggi questa lezione come una revisione di query: ogni OVER (...) deve rispondere a chi stai confrontando, in quale ordine e con quale porzione di righe. Se una di queste tre decisioni resta implicita, il risultato può sembrare corretto e comunque essere fragile.

• Contesto: Quale entità definisce una finestra coerente?
• Metodo: Quale ordinamento rende deterministica la scelta della riga?
• Applicazione: Come spiegheresti una deduplicazione senza nascondere le righe scartate?

La struttura mentale della finestra

Ogni window function ha quattro componenti, che vanno pensate in questo ordine:

FUNCTION_NAME(...) OVER (
  PARTITION BY colonna_1, colonna_2    ← segmenta i dati in gruppi indipendenti
  ORDER BY colonna_3 [ASC|DESC]        ← definisce l'ordine dentro ogni gruppo
  ROWS/RANGE BETWEEN ... AND ...       ← delimita il frame (quali righe includere)
)

L’errore più comune tra gli analisti è confondere PARTITION BY con GROUP BY. GROUP BY collassa le righe in una sola riga per gruppo: dopo un GROUP BY, hai tante righe quanti sono i gruppi. PARTITION BY non collassa: hai ancora tutte le righe originali, ma la funzione sulla finestra calcola il suo valore usando solo le righe dello stesso gruppo.

PARTITION BY è come mettere un foglio di acetato trasparente su ogni gruppo. Le righe restano tutte lì, visibili, ma quando la funzione calcola, “vede” solo attraverso il suo acetato.

ORDER BY dentro la finestra è indipendente dall’ORDER BY della query. Puoi ordinare le righe per booking_date ASC dentro la finestra (per calcolare il primo ordine) ma ordinare il risultato finale per total_revenue DESC (per mostrare prima i clienti migliori). Sono due ordinamenti separati con scopi diversi.

Il frame (ROWS/RANGE) è la parte più potente e più ignorata. Di default, senza specificare un frame, ORDER BY nella finestra imposta un frame implicito: RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. Questo significa che una SUM() cumulativa parte dall’inizio del PARTITION e arriva fino alla riga corrente. Se non metti ORDER BY, il frame è ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING — tutta la partizione in una botta.

Joe Celko, nel suo testo canonico SQL for Smarties (5a edizione, 2014), descrive le window function come “il ponte tra il modello relazionale e l’analisi sequenziale”: permettono di fare operazioni che richiedono un ordine (ranking, lag, running sum) senza violare il principio fondamentale che le righe in una tabella non hanno ordine intrinseco. L’ordine è una proprietà della query, non dei dati.

Le quattro funzioni di ranking: differenze sottili e costose

Esistono quattro funzioni di ranking, e la scelta tra di esse determina se la tua analisi è corretta o fuorviante:

Funzione	Cosa fa	Quando usarla	Comportamento sui tie
ROW_NUMBER()	Assegna un numero univoco progressivo a ogni riga	Deduplicazione, prendere l’ennesima riga	Mai tie: anche a parità di ORDER BY, l’ordine è arbitrario ma il numero è unico
RANK()	Assegna lo stesso numero ai tie, salta i numeri successivi	Classifica con gap per i tie	1, 2, 2, 4 (il 3 è saltato)
DENSE_RANK()	Assegna lo stesso numero ai tie, NON salta	Classifica senza gap	1, 2, 2, 3
NTILE(n)	Divide il PARTITION in n bucket di dimensioni uguali	Percentili, quartili, decili	I bucket possono differire di 1 se la divisione non è esatta

Caso reale: LinkedIn e la classifica dei recruiter

Nel 2020, LinkedIn Talent Solutions introdusse una classifica dei recruiter basata sul numero di InMail con risposta positiva. Il product manager voleva mostrare “Top 10 recruiter del mese”. Il team usò ROW_NUMBER():

SELECT recruiter_id, positive_responses,
  ROW_NUMBER() OVER (ORDER BY positive_responses DESC) AS rank
FROM monthly_recruiter_stats;

Tre recruiter avevano esattamente 147 risposte positive. ROW_NUMBER() assegnò loro posizioni 10, 11 e 12 — uno entrò nella top 10, due no, per puro caso (l’ordine tra pari valore è non deterministico in molti database). I due esclusi notarono che recruiter con il loro stesso numero erano in classifica e protestarono. LinkedIn dovette correggere con RANK(), che assegna la stessa posizione ai tie: tutti e tre divennero #10, e la top 10 mostrò effettivamente 12 recruiter. La versione con DENSE_RANK() avrebbe dato posizione 10 a tutti e tre, senza gap.

La lezione di LinkedIn: ROW_NUMBER() è per deduplicazione, RANK() è per classifiche. DENSE_RANK() è per “top N senza salti visibili”. NTILE() è per segmentazione in fasce.

La deduplicazione: il pattern più usato

Il pattern window function più comune in produzione è la deduplicazione: prendere una sola riga per gruppo secondo un criterio di priorità. La struttura è sempre la stessa:

WITH ranked AS (
  SELECT *,
    ROW_NUMBER() OVER (
      PARTITION BY customer_id
      ORDER BY created_at DESC
    ) AS rn
  FROM customer_events
)
SELECT * FROM ranked WHERE rn = 1;

Questo pattern prende l’evento più recente per ogni cliente. L’ordine può essere qualsiasi cosa: ORDER BY amount DESC per la transazione più grande, ORDER BY status = 'confirmed' DESC, created_at per l’ultima transazione confermata.

Caso reale: Wise (TransferWise) e la deduplicazione delle transazioni

Wise processa oltre 6 miliardi di sterline in trasferimenti internazionali ogni mese. Nel 2021, il team di compliance doveva identificare l’ultimo documento di identità caricato da ogni utente, perché i controlli KYC (Know Your Customer) devono usare il documento più recente. Ogni utente poteva caricare passaporto, patente o carta d’identità in momenti diversi.

La query di deduplicazione:

WITH latest_docs AS (
  SELECT user_id, doc_type, doc_id, uploaded_at,
    ROW_NUMBER() OVER (
      PARTITION BY user_id
      ORDER BY uploaded_at DESC
    ) AS rn
  FROM kyc_documents
)
SELECT user_id, doc_type, doc_id, uploaded_at
FROM latest_docs
WHERE rn = 1;

Il team scoprì che il 3.7% degli utenti aveva caricato un documento, poi un secondo documento dopo che il primo era stato rifiutato. La query precedente (senza window function) prendeva il primo documento in ordine alfabetico di doc_id (un UUID), che era essenzialmente casuale. Questo causava il 3.7% di falsi negativi nei controlli KYC, rallentando le approvazioni e aumentando i ticket di supporto. La deduplicazione con ROW_NUMBER() risolse il problema in una riga.

Laboratorio pratico: ranking e deduplicazione

Ecco un dataset simulato di transazioni con duplicati parziali — stesso txn_id ma con status diversi dovuti a ritardi di sincronizzazione tra sistemi.

CREATE TABLE transactions_raw (
  txn_id VARCHAR,
  user_id INT,
  amount DECIMAL(10,2),
  status VARCHAR,       -- 'pending', 'completed', 'failed'
  source_system VARCHAR, -- 'web', 'mobile', 'pos'
  created_at TIMESTAMP
);

INSERT INTO transactions_raw VALUES
  ('T001', 1, 100.00, 'completed', 'web',    '2024-01-15 10:00:00'),
  ('T001', 1, 100.00, 'completed', 'mobile', '2024-01-15 10:00:05'), -- duplicate
  ('T002', 1,  50.00, 'pending',   'web',    '2024-01-15 11:00:00'),
  ('T002', 1,  50.00, 'completed', 'web',    '2024-01-15 11:05:00'), -- updated
  ('T003', 2, 200.00, 'failed',    'mobile', '2024-01-15 12:00:00'),
  ('T003', 2, 200.00, 'completed', 'mobile', '2024-01-15 12:01:00'); -- retry

Esercizio a tre livelli

Livello 1 — Deduplicazione base: Scrivi una query che restituisce una riga per txn_id, prendendo la riga con created_at più recente. Usa ROW_NUMBER().

Risposta:

WITH deduped AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY txn_id ORDER BY created_at DESC) AS rn
  FROM transactions_raw
)
SELECT * FROM deduped WHERE rn = 1;

Livello 2 — Priorità di status: Modifica la query in modo che, a parità di txn_id, preferisca la riga con status = 'completed' su 'pending' e 'pending' su 'failed', e solo a parità di status prenda il created_at più recente.

Risposta:

WITH ranked AS (
  SELECT *,
    ROW_NUMBER() OVER (
      PARTITION BY txn_id
      ORDER BY
        CASE status
          WHEN 'completed' THEN 1
          WHEN 'pending' THEN 2
          WHEN 'failed' THEN 3
        END,
        created_at DESC
    ) AS rn
  FROM transactions_raw
)
SELECT * FROM ranked WHERE rn = 1;

Livello 3 — RANK con tie: Se invece volessi tenere tutte le righe con lo status migliore (se ci sono più ‘completed’ per lo stesso txn_id), quale funzione useresti e come?

Risposta: RANK() invece di ROW_NUMBER(). WHERE rnk = 1 terrà tutte le righe con lo status migliore, incluse eventuali multiple ‘completed’.

WITH ranked AS (
  SELECT *, RANK() OVER (
    PARTITION BY txn_id
    ORDER BY CASE status
      WHEN 'completed' THEN 1 WHEN 'pending' THEN 2 WHEN 'failed' THEN 3 END
  ) AS rnk
  FROM transactions_raw
)
SELECT * FROM ranked WHERE rnk = 1;

🖥️ Prova in Sandbox

[SQL]Prova tu

Usando la tabella `ordini`, scrivi una query che trova l'ordine più recente per ogni utente (usa ROW_NUMBER con PARTITION BY utente_id ORDER BY data_ordine DESC). Mostra nome utente, prodotto, importo e data.

SELECT u.nome, o.prodotto, o.importo, o.data_ordine FROM ordini o JOIN utenti u ON o.utente_id = u.id ORDER BY u.nome, o.data_ordine DESC

EseguiCtrl+Enter per eseguireSuggerimentoSoluzione

[SQL]Prova tu

Classifica gli utenti per spesa totale (somma di importo da ordini) usando RANK(). Se due utenti hanno la stessa spesa totale, devono avere lo stesso rank.

SELECT u.nome, SUM(o.importo) AS totale_speso FROM utenti u LEFT JOIN ordini o ON u.id = o.utente_id GROUP BY u.nome ORDER BY totale_speso DESC

EseguiCtrl+Enter per eseguireSuggerimentoSoluzione

Checkpoint operativi

1. Qual è la differenza fondamentale tra GROUP BY e PARTITION BY in una window function?

2. Quando useresti RANK() invece di ROW_NUMBER() e perché?

3. Scrivi il pattern standard per deduplicare righe usando le window function.

4. Cosa succede al ranking se due righe hanno lo stesso valore nell’ORDER BY della finestra con ciascuna delle quattro funzioni?

---

Riferimenti accademici:

• Celko, J. (2014). Joe Celko’s SQL for Smarties, 5th ed. Morgan Kaufmann. Capitolo 23: “Window Functions.”
• Zandbergen, E. (2021). “Window Functions: A Developer’s Survival Guide.” PostgreSQL Conference Europe 2021.
• Eisenberg, A. & Melton, J. (2000). “SQL:1999, Formerly Known as SQL3.” ACM SIGMOD Record, 28(1), pp. 131-138.

Controllo di qualità

Prima di usare window functions: struttura mentale in una decisione, controlla sempre completezza, duplicati, timezone, definizioni cambiate e segmenti esclusi. Molte analisi apparentemente sofisticate falliscono perché il dato di partenza misura un comportamento diverso da quello che il team crede di osservare.

Interpretazione per segmenti

La media aggregata è solo il punto di partenza. Segmenta per canale, coorte, piano, paese, device e maturità dell’utente. Se due segmenti si muovono in direzioni opposte, la media non rappresenta nessuno dei due e può portare a una decisione sbagliata.

Problema reale

Nel dominio di advanced SQL, ‘Window functions: struttura mentale’ serve a risolvere questo problema: scrivere query analitiche corrette anche quando grain, finestre, coorti e casi limite diventano complessi. La lezione non va trattata come teoria isolata, ma come un modo per migliorare una scelta concreta con dati, assunzioni esplicite e controlli minimi.

Obiettivo operativo: Comprendere il problema analitico e il contesto decisionale; Applicare esempi, metriche e controlli a casi reali. Se alla fine non sai indicare quale decisione cambia, quale dato osservi e quale errore vuoi evitare, la lezione non è ancora diventata competenza applicata.

Modello concettuale

Fase	Cosa chiarire	Output
Domanda	Quale scelta reale deve migliorare?	Decisione da prendere
Misura	Quale segnale osservabile rappresenta il problema?	Metrica o dato sorgente
Controllo	Quale baseline rende il risultato interpretabile?	Confronto credibile
Azione	Che cosa cambia dopo l’analisi?	Prossimo passo operativo

Il modello concettuale è intenzionalmente semplice: decisione, dato, controllo, azione. Ogni approfondimento tecnico deve rafforzare almeno uno di questi quattro punti.

Formalizzazione rigorosa

Per rendere ‘Window functions: struttura mentale’ analizzabile, definisci prima l’unità di lavoro: riga, partizione, finestra, join, coorte o metrica temporale. Poi collega questa unità a una metrica osservabile: correttezza, performance, duplicati, grain e stabilità del risultato. Infine dichiara la decisione attesa: query, modello intermedio, test SQL o pattern riusabile.

Elemento	Specifica richiesta
Unità di analisi	riga, partizione, finestra, join, coorte o metrica temporale
Segnale principale	correttezza, performance, duplicati, grain e stabilità del risultato
Baseline	Periodo precedente, gruppo comparabile, benchmark o scenario controfattuale
Decisione	query, modello intermedio, test SQL o pattern riusabile
Rischio	Scambiare un numero disponibile per una prova sufficiente

La formalizzazione e solida quando un altro analista può riprodurre la logica, criticare le assunzioni e ottenere la stessa decisione partendo dagli stessi dati.

Esempio o caso studio

Le window functions diventano chiare quando separi tre domande: su quali righe guardo, in quale ordine, e quale valore calcolo senza perdere il dettaglio originale. Questa struttura mentale evita di usare ROW_NUMBER, ranking e cumulati come formule isolate.

Evidenza osservata	Lettura prudente	Azione consigliata
Il numero migliora	Potrebbe essere effetto reale o variazione normale	Cercare confronto e segmento
Un segmento cambia più degli altri	La media aggregata nasconde una differenza	Separare coorti o casi d’uso
Il costo cresce insieme al risultato	L’impatto va letto sul margine	Stimare trade-off e sostenibilità

Lab / esercizio

Livello base

Scrivi una scheda di una pagina per ‘Window functions: struttura mentale’: decisione da supportare, metrica primaria, baseline, rischio principale e azione se il segnale e confermato.

Livello intermedio

Costruisci una tabella con tre segmenti, periodi o scenari. Per ciascuno indica cosa cambia, quale spiegazione alternativa e plausibile e quale controllo useresti prima di raccomandare un azione.

Livello research-grade

Prepara un decision memo: ipotesi, dati richiesti, criteri di esclusione, controlli di qualità, soglia decisionale, rischio residuo e piano di monitoraggio dopo la decisione.

Dataset e materiali consigliati

Usa warehouse SQL, dati ordini, eventi, sessioni, coorti, revenue e tabelle calendario. Se non hai accesso a dati reali, crea un dataset sintetico con almeno 200 righe, una dimensione temporale, una dimensione segmento e una metrica di outcome.

Errore tipico da evitare

L’errore più comune e usare ‘Window functions: struttura mentale’ come etichetta invece che come processo. Succede quando il team mostra un grafico senza decisione, una metrica senza baseline, o una conclusione senza indicare quale assunzione potrebbe invalidarla.

La domanda di controllo è: se questo risultato fosse instabile, quale scelta sbaglierei? Se la risposta non è concreta, manca ancora il collegamento tra analisi e azione.

Quiz o checkpoint

1. Quale decisione concreta dovrebbe migliorare questa lezione?
2. Quale unità di analisi rende il problema misurabile?
3. Quale baseline useresti per evitare una lettura ingenua?
4. Quale errore tipico potrebbe cambiare la conclusione?
5. Quale output consegneresti a uno stakeholder non tecnico?

Riepilogo operativo

‘Window functions: struttura mentale’ diventa utile quando produce una decisione più chiara, non quando aggiunge terminologia. Usa il framework problema, modello, formalizzazione, esempio, lab e checkpoint per trasformare la lezione in pratica verificabile. Categoria: Decisione. Difficoltà: advanced. Tempo stimato: 18 min.