Funzioni finestra SQL spiegate

Tutte le comuni funzioni finestra SQL e le clausole che le controllano, raggruppate per lavoro: classificare le righe, sbirciare i vicini, totali progressivi su un frame e primi o ultimi valori. Ogni riga affianca alla funzione un esempio di una riga.

Una funzione finestra calcola un risultato sulle righe correlate alla riga corrente, senza compattarle come fa GROUP BY. Ogni funzione finestra termina con una clausola OVER che definisce la finestra: PARTITION BY raggruppa le righe, ORDER BY le ordina e il frame facoltativo delimita l'insieme scorrevole. Padroneggia OVER e il resto è vocabolario.

Tabella di riferimento · 27 voci

27 of 27 rows


Classificazione
	Un numero intero sequenziale univoco per ogni riga nella finestra.	ROW_NUMBER() OVER (ORDER BY total DESC)
	Rango con salti; le parità condividono un rango e i numeri successivi vengono saltati.	RANK() OVER (ORDER BY total DESC)
	Rango senza salti; le parità condividono un rango e nulla viene saltato.	DENSE_RANK() OVER (ORDER BY total DESC)
	Divide la finestra ordinata in n bucket all'incirca uguali.	NTILE(4) OVER (ORDER BY total DESC)
Sbirciare i vicini
	Un valore di n righe indietro; il default lo sostituisce quando quella riga non esiste.	LAG(total, 1, 0) OVER (ORDER BY created_at)
	Un valore di n righe avanti; il default lo sostituisce quando quella riga non esiste.	LEAD(total, 1, 0) OVER (ORDER BY created_at)
Aggregazione su una finestra
	Un totale progressivo o per partizione senza compattare le righe.	SUM(total) OVER (ORDER BY created_at)
	Una media progressiva o per partizione.	AVG(total) OVER (PARTITION BY user_id)
	Un conteggio di righe progressivo o per partizione.	COUNT(*) OVER (PARTITION BY user_id)
	Il valore più basso nella finestra o partizione.	MIN(total) OVER (PARTITION BY user_id)
	Il valore più alto nella finestra o partizione.	MAX(total) OVER (PARTITION BY user_id)
	SUM con ORDER BY e un frame aperto all'inizio; ogni riga somma tutto fino a se stessa.	SUM(total) OVER (ORDER BY created_at ROWS UNBOUNDED PRECEDING)
	AVG su un frame di n righe prima e dopo la riga corrente.	AVG(total) OVER (ORDER BY created_at ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING)
Primi, ultimi e percentili
	Il primo valore nell'ordinamento della finestra.	FIRST_VALUE(total) OVER (ORDER BY total DESC)
	L'ultimo valore nel frame; senza UNBOUNDED FOLLOWING il frame si ferma alla riga corrente.	LAST_VALUE(total) OVER (ORDER BY total ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
	Il valore alla n-esima riga dell'ordinamento della finestra.	NTH_VALUE(total, 2) OVER (ORDER BY total DESC)
	Il rango relativo di una riga, da 0 a 1.	PERCENT_RANK() OVER (ORDER BY total)
	La quota di righe fino alla corrente inclusa, da 1/n fino a 1.	CUME_DIST() OVER (ORDER BY total)
La clausola OVER
	Definisce la finestra che ogni funzione legge.	<fn>() OVER (PARTITION BY user_id ORDER BY created_at)
	Riavvia la finestra per ogni gruppo di righe.	PARTITION BY user_id
	Ordina le righe dentro ogni partizione.	ORDER BY created_at
	Delimita il frame scorrevole, es. dalle righe precedenti alle seguenti.	ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
	Delimita il frame per valori, non per posizioni di riga; CURRENT ROW include le righe a pari merito, ed è il frame predefinito con ORDER BY.	RANGE BETWEEN 100 PRECEDING AND CURRENT ROW
	Delimita il frame per gruppi di righe a pari merito dell'ORDER BY.	GROUPS BETWEEN 1 PRECEDING AND CURRENT ROW
	Rimuove la riga corrente dal frame; EXCLUDE TIES rimuove invece le sue pari merito.	ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING EXCLUDE CURRENT ROW
	Assegna un nome a una finestra una volta per query, così più clausole OVER possono riutilizzarla.	WINDOW w AS (PARTITION BY user_id ORDER BY created_at)
	Riduce le righe che un aggregato di finestra legge, prima che la finestra venga applicata.	SUM(total) FILTER (WHERE status = 'paid') OVER (ORDER BY created_at)