PostgreSQL Window Functions — OVER, PARTITION BY, ROW_NUMBER per fare analisi senza sottomissioni

Hai mai provato a calcolare lo stipendio medio per reparto, ma tenendo visibili anche i dati di ogni singolo dipendente? Con GROUP BY perdi le righe. Con una subquery guadagni complessità e performance peggiori. È il momento di usare le window functions di PostgreSQL: OVER, PARTITION BY, ROW_NUMBER. Noi, di Meteora Web, le usiamo tutti i giorni per estrarre report dai database dei clienti senza moltiplicare le join o scaricare dati in memoria. In questa guida vediamo il perché, il come e i trucchi che ci hanno salvato più di una volta.

Perché le Window Functions PostgreSQL risolvono problemi che le GROUP BY non possono?

Immagina di dover confrontare il fatturato di ogni mese con la media mobile su 3 mesi. Con GROUP BY ottieni un aggregato — una riga per mese — ma perdi il confronto “riga per riga” con la riga precedente o successiva. Le window functions invece lavorano su un insieme di righe (la “finestra”) mantenendo le righe originali. Il risultato: puoi calcolare running total, ranking, differenze rispetto al massimo, tutto nella stessa query.

Esempio pratico: un cliente e-commerce (quelli che seguiamo dal 2017) voleva una classifica dei prodotti più venduti per categoria, mostrando anche il rank. Con window function una query sola. Senza? Subquery annidata, join su se stessa, lentezza.

La sintassi minima che devi sapere

SELECT colonna, funzione() OVER (
  [PARTITION BY colonna_di_gruppo]
  [ORDER BY colonna_ordinamento]
  [ROWS BETWEEN ...]
) FROM tabella;

Ogni pezzo ha un ruolo preciso. PARTITION BY divide i dati in gruppi (come GROUP BY, ma senza compattare le righe). ORDER BY definisce l’ordine dentro ogni gruppo. ROWS BETWEEN specifica i limiti della finestra (default: dall’inizio del gruppo fino alla riga corrente con ORDER BY, o tutto il gruppo senza).

Come funziona OVER e PARTITION BY nelle query analitiche?

OVER dice al motore: “calcola questa funzione su una finestra di righe”. PARTITION BY riduce la finestra a un sottoinsieme. Senza PARTITION BY, la finestra è l’intera tabella. Con PARTITION BY reparto, ogni reparto ha la sua finestra indipendente.

Errore comune: usare PARTITION BY quando serve l’intero dataset. Esempio: calcolare la percentuale di ogni vendita sul totale globale. In quel caso niente PARTITION BY, solo OVER().

SELECT
  mese,
  fatturato,
  ROUND(100.0 * fatturato / SUM(fatturato) OVER (), 2) AS percentuale_totale
FROM vendite_mensili;

Noi, di Meteora Web, abbiamo usato esattamente questa tecnica per un report di un negozio di abbigliamento (ricordi? gestivamo il loro ERP). Volevano sapere quanto ogni mese pesava sul totale stagione. Con PARTITION BY stagione avremmo avuto la percentuale sulla stagione, non sul totale. OVER() senza PARTITION è stata la scelta giusta.

ROW_NUMBER, RANK e DENSE_RANK — qual è la differenza pratica?

Queste tre funzioni assegnano un numero progressivo, ma con comportamenti diversi sui pareggi.

• ROW_NUMBER(): assegna un numero univoco a ogni riga, senza eccezioni. In caso di pareggio, l’ordine è arbitrario (dipende dall’ORDER BY e dalla tupla fisica).
• RANK(): in caso di pareggio, assegna lo stesso numero, ma il successivo viene saltato (es. 1,1,3).
• DENSE_RANK(): come RANK, ma senza salti (1,1,2).

Quando usarli: ROW_NUMBER per paginazione o deduplicazione (eliminare duplicati tenendo una sola riga). RANK per classifiche di vendita dove vuoi vedere quanti sono al primo posto. DENSE_RANK per graduatorie senza buchi (es. top 20 di un torneo).

SELECT
  prodotto,
  categoria,
  vendite,
  ROW_NUMBER() OVER (PARTITION BY categoria ORDER BY vendite DESC) AS row_num,
  RANK()       OVER (PARTITION BY categoria ORDER BY vendite DESC) AS rank,
  DENSE_RANK() OVER (PARTITION BY categoria ORDER BY vendite DESC) AS dense_rank
FROM prodotti_vendite;

Come usare ROW_NUMBER per deduplicazione e paginazione efficiente?

Uno dei pattern più potenti: eliminare duplicati da una tabella mantenendo solo la prima occorrenza per gruppo.

WITH dedup AS (
  SELECT
    *,
    ROW_NUMBER() OVER (PARTITION BY id_cliente ORDER BY data_ordine DESC) AS rn
  FROM ordini
)
DELETE FROM dedup WHERE rn > 1;

Attenzione: in PostgreSQL puoi usare la CTE modificabile (DELETE FROM dedup). Noi abbiamo risolto un problema di dati sporchi in un gestionale di una PMI siciliana con questo pattern: 30.000 righe duplicate eliminate in meno di un secondo.

Paginazione: invece di OFFSET (che scansiona tutte le righe scartate), puoi usare ROW_NUMBER per selezionare un range preciso:

SELECT * FROM (
  SELECT *, ROW_NUMBER() OVER (ORDER BY data DESC) AS rn
  FROM articoli
) sub
WHERE rn BETWEEN 21 AND 40;

Più veloce di OFFSET su grandi volumi, anche se non quanto il keyset pagination. Per il 95% delle PMI funziona benissimo.

Quali combinazioni di frame e ORDER BY danno i risultati giusti?

Le window functions con frame (ROWS BETWEEN) permettono di calcolare medie mobili, totali cumulativi, differenze rispetto al primo o ultimo valore del gruppo.

Running total (totale progressivo)

SELECT
  mese,
  entrate,
  SUM(entrate) OVER (ORDER BY mese ROWS UNBOUNDED PRECEDING) AS totale_progressivo
 FROM entrate_mensili;

ROWS UNBOUNDED PRECEDING parte dalla prima riga del gruppo (o dell’intera tabella se non c’è PARTITION BY) fino alla riga corrente. Per media mobile su 3 mesi:

SELECT
  mese,
  AVG(entrate) OVER (ORDER BY mese ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS media_mobile_3
 FROM entrate_mensili;

Attenzione all’ORDER BY: se usi ROWS BETWEEN, l’ORDER BY è obbligatorio. Se l’ORDER BY non è univoco, PostgreSQL può aggiungere una colonna di default per stabilità. Noi consigliamo sempre di specificare un ORDER BY con colonna univoca (es. id, data+ora) per evitare risultati non deterministici.

Cosa fare adesso: dalla teoria alla tua prossima query

1. Apri il tuo database PostgreSQL (anche con pgAdmin o psql) e crea una tabella di prova con dati di vendita: prodotto, categoria, importo, data.

2. Esegui la query con ROW_NUMBER per classificare i prodotti per categoria. Modifica l’ORDER BY e osserva i cambiamenti.

3. Prova il pattern di deduplicazione su una tabella reale (fai prima un backup!).

4. Leggi la documentazione ufficiale di PostgreSQL sulle window functions: Tutorial sulle Window Functions.

5. Se stai lavorando a un progetto complesso, dai un’occhiata alla nostra guida principale su PostgreSQL Avanzato per approfondire performance e indici.

6. Ricorda: una window function non è magica — è una funzione che esegue su un insieme di righe. Più è grande la finestra, più pesa la query. Usa indici sulle colonne di ORDER BY e PARTITION BY per tenere tutto veloce.

Noi, di Meteora Web, abbiamo visto query di report passare da 8 secondi a 80 millisecondi grazie a window functions ben scritte. Ora tocca a te.