Una funzione finestra calcola un valore per ogni riga a partire da un insieme di righe collegate, la sua finestra, senza unire le righe come fa GROUP BY. Si scrive function() OVER (PARTITION BY ... ORDER BY ...). PARTITION BY divide le righe in gruppi e ORDER BY stabilisce l'ordine dentro ogni gruppo.
Ogni riga resta nel risultato e porta con sé il totale della sua regione: 1000 per north, 500 per south. La quota di ogni venditore diventa così una sola espressione (ana ha il 50 per cento di north). Qualsiasi funzione di aggregazione (sum, count, avg, min, max) diventa una funzione finestra quando aggiungi OVER. Un OVER () vuoto trasforma l'intero risultato in un'unica finestra.
Numerare le righe con ROW_NUMBER, RANK e DENSE_RANK
Le tre funzioni di ranking si differenziano solo quando le righe sono a pari merito sul valore di ORDER BY:
ben e cy hanno entrambi 85. row_number dà comunque loro 2 e 3; chi riceve il 2 non è definito, a meno che l'ORDER BY non risolva il pareggio. rank dà a entrambi 2 e salta a 4 per dee. dense_rank dà a entrambi 2 e continua con 3.
| Funzione | Pari merito | Buchi dopo i pari merito |
|---|---|---|
row_number() | numeri diversi | no |
rank() | stesso numero | sì |
dense_rank() | stesso numero | no |
Ottenere le prime N righe per gruppo
Le funzioni finestra vengono calcolate dopo WHERE, quindi non puoi filtrare direttamente su di esse. Numera le righe in una subquery o in una CTE, poi filtra all'esterno:
Restituisce i due migliori venditori di ogni regione. Usa rank() invece di row_number() per tenere ogni riga a pari merito al secondo posto. Se ti serve solo la prima riga per gruppo, DISTINCT ON è più breve.
Calcolare un totale progressivo o una media mobile
Con ORDER BY dentro OVER, una funzione di aggregazione lavora su un frame. Per impostazione predefinita il frame va dalla prima riga della partizione alla riga corrente, e questo dà un totale progressivo. Una clausola ROWS BETWEEN imposta un frame diverso, per esempio la riga corrente e le due precedenti:
Il totale progressivo arriva a 130 il giorno 5, e la media su tre giorni del giorno 5 è (30 + 20 + 50) / 3 = 33.3.
Il frame predefinito è RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. Include ogni riga a pari merito con quella corrente sul valore di ORDER BY, quindi due righe con lo stesso giorno ricevono lo stesso totale progressivo. Scrivi ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW per sommarle una alla volta.
Confrontare con la riga precedente: LAG e LEAD
lag(col) legge un valore dalla riga che precede quella corrente nell'ordine della finestra, e lead(col) dalla riga che la segue. Entrambe restituiscono NULL oltre il bordo, a meno che tu non passi un valore predefinito come terzo argomento.
Il giorno 1 non ha una riga precedente, quindi previous e change sono NULL. La clausola WINDOW definisce una finestra una volta sola, con un nome, perché più funzioni la condividano.
first_value funziona con il frame predefinito, last_value no: il frame predefinito finisce alla riga corrente, quindi restituisce il valore della riga corrente. Aggiungi ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING per far coincidere il frame con l'intera partizione. Altre funzioni disponibili solo come funzioni finestra sono ntile(n), percent_rank(), cume_dist() e nth_value(); il riferimento delle funzioni finestra di PostgreSQL le elenca tutte.
Filtrare le righe che un aggregato conta
FILTER (WHERE ...) funziona anche sugli aggregati finestra, per avere totali condizionali su ogni riga:
SELECT day,
count(*) FILTER (WHERE status = 'error') OVER (ORDER BY day) AS errors_so_far
FROM requests;
Domande frequenti
Che differenza c'è tra GROUP BY e PARTITION BY?
GROUP BY restituisce una riga per gruppo. PARTITION BY dentro OVER tiene ogni riga e assegna a ciascuna un valore calcolato sul suo gruppo, così puoi mostrare l'importo di una riga accanto al totale del suo gruppo.
Posso usare una funzione finestra nel WHERE?
No. Le funzioni finestra vengono eseguite dopo WHERE, GROUP BY e HAVING, quindi sono ammesse solo nella lista della select e in ORDER BY. Calcola il valore in una subquery o in una CTE e filtra nella query esterna.
Perché LAST_VALUE restituisce il valore della riga corrente?
Con ORDER BY in OVER, il frame predefinito finisce alla riga corrente (e alle righe a pari merito), quindi l'ultima riga del frame è quella corrente. Aggiungi ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING per far coincidere il frame con l'intera partizione.
Che differenza c'è tra ROWS e RANGE in un frame?
ROWS conta le righe fisiche: ROWS BETWEEN 2 PRECEDING AND CURRENT ROW è la riga corrente più le due precedenti. RANGE lavora sul valore di ORDER BY, quindi le righe con valori uguali (peer) sono sempre insieme nel frame.