Window functions: struttura mentale

Una tabella di ordini può contenere più righe per lo stesso cliente, con timestamp quasi identici e stati aggiornati in ritardo. Il problema non è quale riga vedere per prima, ma quale riga rappresenta il fatto che vuoi difendere. La struttura mentale delle window functions parte da qui: partition, order e frame diventano il modo per definire il perimetro del confronto prima ancora di scrivere la funzione.

Quando la query sembra corretta ma non lo è

Scrivere query analitiche corrette diventa difficile quando grain, finestre, coorti e casi limite si intrecciano. Capita spesso di usare una window function senza avere chiaro cosa si sta confrontando, in quale ordine e su quale porzione di dati. Il risultato esce, sembra plausibile, e proprio per questo è pericoloso: è fragile e a volte fuorviante. Il lavoro vero consiste nel trasformare quella complessità in una decisione chiara e ripetibile da un’altra persona.

Come ragionare prima di scrivere

Prima di toccare la sintassi conviene fissare quattro punti. Qual è la scelta reale che la query deve migliorare? Quale segnale osservabile rappresenta il problema, cioè quale metrica o dato sorgente stai davvero guardando? Quale baseline rende il numero interpretabile, perché un valore senza confronto non dice nulla? E cosa cambia dopo l’analisi, qual è il prossimo passo operativo? Ogni dettaglio tecnico che aggiungi dovrebbe rafforzare almeno uno di questi punti. Se non lo fa, hai perso di vista la decisione da supportare.

Rendere la logica riproducibile

Per analizzare la struttura mentale di una window function, definisci prima l’unità di lavoro: la riga, la partizione, la finestra, il join, la coorte o una metrica temporale. Collega questa unità a una metrica osservabile, che può essere la correttezza, la performance, il numero di duplicati, il grain o la stabilità del risultato. Poi dichiara la decisione attesa, cioè se il prodotto sarà una query, un modello intermedio, un test SQL o un pattern riusabile. Il rischio costante è scambiare un numero disponibile per una prova sufficiente.

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

Quando questi elementi sono espliciti, un altro analista può riprodurre la logica, criticare le assunzioni e arrivare alla stessa decisione partendo dagli stessi dati.

Le quattro componenti di una finestra

Torniamo alla tabella di ordini con più righe per lo stesso cliente. La domanda da cui partire è sempre la stessa: quale riga rappresenta il dato da difendere? Ogni window function lavora su tre leve. PARTITION BY segmenta i dati in gruppi indipendenti. ORDER BY definisce l’ordine dentro ogni gruppo. ROWS o RANGE BETWEEN ... AND ... delimitano il frame di righe da includere nel calcolo. Tenere a mente queste tre leve evita di usare ROW_NUMBER, il ranking e i cumulati come formule isolate, staccate dal problema che devono risolvere.

Quando leggi un risultato, conviene anche dubitarne in modo strutturato. Se un numero migliora, potrebbe trattarsi di un effetto reale oppure di normale variazione, e la cosa da fare è cercare un confronto e guardare i segmenti. Se un segmento cambia più degli altri, la media aggregata sta nascondendo una differenza, e allora vanno separate le coorti o i casi d’uso. Se il costo cresce insieme al risultato, l’impatto va letto sul margine, stimando il trade-off e la sostenibilità.

Mettere alla prova il ragionamento

Un modo semplice per fissare il metodo è scrivere una scheda sintetica sulla struttura mentale delle window functions: indica la decisione da supportare, la metrica primaria, la baseline, il rischio principale e l’azione da intraprendere se il segnale viene confermato. Da qui puoi salire di livello costruendo una tabella con tre segmenti, periodi o scenari, dove per ciascuno annoti cosa cambia, quale spiegazione alternativa è plausibile e quale controllo useresti prima di raccomandare un’azione. Il passaggio più ambizioso è preparare un decision memo completo, con ipotesi, dati richiesti, criteri di esclusione, controlli di qualità, soglia decisionale, rischio residuo e piano di monitoraggio dopo la decisione. In tutti i casi conviene lavorare su dati reali o sintetici con almeno 200 righe, una dimensione temporale, una dimensione segmento e una metrica di outcome.

L’errore più comune

L’errore più frequente è usare la window function come etichetta anziché come processo. Succede quando si mostra un grafico senza decisione, una metrica senza baseline o una conclusione senza indicare quale assunzione potrebbe invalidarla. La domanda di controllo è diretta: se questo risultato fosse instabile, quale scelta sbaglierei? Se non riesci a rispondere in modo concreto, manca ancora il collegamento tra analisi e azione.

Prima di considerare chiuso un ragionamento, vale la pena verificare di saper dire quale decisione concreta dovrebbe migliorare, quale unità di analisi rende il problema misurabile, quale baseline evita una lettura ingenua, quale errore tipico potrebbe cambiare la conclusione e quale output consegneresti a uno stakeholder non tecnico.

Riepilogo operativo

La struttura mentale delle window functions è utile solo se produce una decisione più chiara, non se aggiunge terminologia fine a se stessa. Tieni insieme problema, modello concettuale, formalizzazione, esempio, esercizio e verifica finale: è così che la lezione diventa pratica verificabile invece di un elenco di funzioni.

---

La forma di una window function

Ogni window function ha una struttura precisa:

FUNCTION_NAME(...) OVER (
  PARTITION BY colonna_1, colonna_2
  ORDER BY colonna_3 [ASC|DESC]
  ROWS/RANGE BETWEEN ... AND ...
)

PARTITION BY segmenta i dati in gruppi indipendenti senza collassare le righe. È come appoggiare un foglio di acetato trasparente su ogni gruppo: la funzione vede solo quel gruppo, ma le righe restano tutte visibili.

L’ORDER BY dentro la finestra è indipendente dall’ORDER BY della query finale. Puoi ordinare per data dentro la finestra e mostrare poi i risultati ordinati per fatturato.

Il frame (ROWS o RANGE) delimita quali righe includere nel calcolo. In assenza di indicazione esplicita, con un ORDER BY il default è RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, che è ciò che rende possibili le somme cumulative.

Le quattro funzioni di ranking

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: ordine arbitrario ma numero unico
RANK()	Assegna lo stesso numero ai tie, salta i numeri successivi	Classifica con gap per i tie	1, 2, 2, 4 (salta 3)
DENSE_RANK()	Assegna lo stesso numero ai tie, senza salti	Classifica senza gap	1, 2, 2, 3
NTILE(n)	Divide in n bucket di dimensioni quasi uguali	Percentili, quartili, decili	Bucket possono differire di 1

Caso reale: LinkedIn e la classifica dei recruiter

LinkedIn voleva mostrare i “Top 10 recruiter” per risposte positive. Usarono ROW_NUMBER(), ma tre recruiter con lo stesso punteggio ricevettero posizioni 10, 11 e 12. Solo uno entrò nella top 10, gli altri no, per un ordine arbitrario. Dopo proteste, passarono a RANK(), che assegnò a tutti e tre la posizione 10, mostrando effettivamente 12 recruiter nella top 10.

La lezione: ROW_NUMBER() serve per deduplicazione, RANK() per classifiche con gap, DENSE_RANK() per classifiche senza gap, NTILE() per segmentazioni.

Deduplicazione: il pattern più usato

Il pattern più comune è prendere una sola riga per gruppo secondo un criterio di priorità:

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 prende l’evento più recente per ogni cliente.

Caso reale: Wise e la deduplicazione delle transazioni

Wise doveva identificare l’ultimo documento caricato da ogni utente per i controlli KYC. La query iniziale prendeva il primo documento in ordine alfabetico e generava falsi negativi. La deduplicazione con ROW_NUMBER() ha risolto il problema in una riga.

Esercitarsi sulla deduplicazione

Per provare i pattern sopra puoi partire da un dataset simulato con transazioni duplicate e aggiornate:

txn_id	user_id	amount	status	source_system	created_at
T001	1	100.00	completed	web	2024-01-15 10:00:00
T001	1	100.00	completed	mobile	2024-01-15 10:00:05
T002	1	50.00	pending	web	2024-01-15 11:00:00
T002	1	50.00	completed	web	2024-01-15 11:05:00
T003	2	200.00	failed	mobile	2024-01-15 12:00:00
T003	2	200.00	completed	mobile	2024-01-15 12:01:00

Il primo esercizio è deduplicare prendendo la riga più recente per ogni txn_id con ROW_NUMBER(). Poi puoi alzare l’asticella prioritizzando lo status nell’ordine completed, pending, failed, e usando il created_at più recente come spareggio in caso di parità. Infine prova a mantenere tutte le righe con lo status migliore per ogni txn_id usando RANK() al posto di ROW_NUMBER(), così vedi sul vivo come cambiano i risultati quando i tie vengono trattati in modo diverso.

Quattro domande aiutano a controllare se il modello mentale ha attecchito: qual è la differenza tra GROUP BY e PARTITION BY, quando conviene usare RANK() invece di ROW_NUMBER(), qual è il pattern standard per deduplicare righe con una window function e cosa succede al ranking in caso di tie con le quattro funzioni.

Controllo di qualità e segmenti

Prima di usare una window function dentro una decisione, verifica completezza, duplicati, timezone, definizioni che sono cambiate nel tempo e segmenti esclusi. Conviene poi separare i dati per canale, coorte, piano, paese, device e maturità utente, perché la media aggregata può nascondere differenze importanti tra segmenti e portare a una scelta sbagliata.