Funções de Janela SQL explicadas

Todas as funções de janela SQL comuns e as cláusulas que as controlam, agrupadas por função: classificar linhas, espiar vizinhos, totais acumulados numa moldura e primeiros/últimos valores. Cada linha associa a função a um exemplo de uma linha.

Uma função de janela calcula um resultado através de linhas relacionadas com a linha atual, sem as colapsar como o GROUP BY faz. Toda a função de janela termina numa cláusula OVER que define a janela: PARTITION BY agrupa linhas, ORDER BY as sequencia e a moldura opcional limita o conjunto deslizante. Domine o OVER e o resto é vocabulário.

Tabela de referência · 27 entradas

27 of 27 rows


Classificação
	Um inteiro sequencial único por linha dentro da janela.	ROW_NUMBER() OVER (ORDER BY total DESC)
	Classificação com lacunas; os empates partilham a posição e saltam os números seguintes.	RANK() OVER (ORDER BY total DESC)
	Classificação sem lacunas; os empates partilham a posição e nada é saltado.	DENSE_RANK() OVER (ORDER BY total DESC)
	Divide a janela ordenada em n grupos aproximadamente iguais.	NTILE(4) OVER (ORDER BY total DESC)
Espiar vizinhos
	Um valor de n linhas atrás; o predefinido substitui-o quando essa linha não existe.	LAG(total, 1, 0) OVER (ORDER BY created_at)
	Um valor de n linhas à frente; o predefinido substitui-o quando essa linha não existe.	LEAD(total, 1, 0) OVER (ORDER BY created_at)
Agregados sobre uma janela
	Um total acumulado ou particionado sem colapsar linhas.	SUM(total) OVER (ORDER BY created_at)
	Uma média acumulada ou particionada.	AVG(total) OVER (PARTITION BY user_id)
	Uma contagem de linhas acumulada ou particionada.	COUNT(*) OVER (PARTITION BY user_id)
	O menor valor na janela ou partição.	MIN(total) OVER (PARTITION BY user_id)
	O maior valor na janela ou partição.	MAX(total) OVER (PARTITION BY user_id)
	SUM com ORDER BY e uma moldura aberta no início; cada linha soma tudo até si própria.	SUM(total) OVER (ORDER BY created_at ROWS UNBOUNDED PRECEDING)
	AVG sobre uma moldura de n linhas antes e depois da linha atual.	AVG(total) OVER (ORDER BY created_at ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING)
Primeiros, últimos e percentis
	O primeiro valor na ordenação da janela.	FIRST_VALUE(total) OVER (ORDER BY total DESC)
	O último valor na moldura; sem UNBOUNDED FOLLOWING a moldura para na linha atual.	LAST_VALUE(total) OVER (ORDER BY total ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
	O valor na n-ésima linha da ordenação da janela.	NTH_VALUE(total, 2) OVER (ORDER BY total DESC)
	A posição relativa de uma linha, de 0 a 1.	PERCENT_RANK() OVER (ORDER BY total)
	A fração de linhas até (e incluindo) a linha atual, de 1/n até 1.	CUME_DIST() OVER (ORDER BY total)
A cláusula OVER
	Define a janela que toda a função lê.	<fn>() OVER (PARTITION BY user_id ORDER BY created_at)
	Reinicia a janela para cada grupo de linhas.	PARTITION BY user_id
	Sequencia as linhas dentro de cada partição.	ORDER BY created_at
	Limita a moldura deslizante, p. ex. linhas anteriores a seguintes.	ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
	Limita a moldura por valores, não por posições; CURRENT ROW inclui os pares empatados, e esta é a moldura predefinida sob ORDER BY.	RANGE BETWEEN 100 PRECEDING AND CURRENT ROW
	Limita a moldura por grupos de linhas empatadas do ORDER BY.	GROUPS BETWEEN 1 PRECEDING AND CURRENT ROW
	Remove a linha atual da moldura; EXCLUDE TIES remove os seus pares.	ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING EXCLUDE CURRENT ROW
	Nomeia uma janela uma vez por consulta para que várias cláusulas OVER a reutilizem.	WINDOW w AS (PARTITION BY user_id ORDER BY created_at)
	Restringe as linhas que um agregado de janela lê, antes de a janela ser aplicada.	SUM(total) FILTER (WHERE status = 'paid') OVER (ORDER BY created_at)