Funciones de ventana SQL explicadas

Todas las funciones de ventana de SQL de uso común y las cláusulas que las controlan, agrupadas por tarea: clasificar filas, mirar a las vecinas, calcular totales sobre un marco y obtener el primer o el último valor. Cada fila empareja la función con un ejemplo de una línea.

Una función de ventana calcula un resultado sobre filas relacionadas con la fila actual, sin colapsarlas como hace GROUP BY. Toda función de ventana termina en una cláusula OVER que define la ventana: PARTITION BY agrupa las filas, ORDER BY las ordena y el marco opcional delimita el conjunto deslizante. Domina OVER y el resto es vocabulario.

Tabla de referencia · 27 entradas

27 of 27 rows


Clasificación
	Un entero secuencial único por fila dentro de la ventana.	ROW_NUMBER() OVER (ORDER BY total DESC)
	Puesto con huecos; los empates comparten puesto y se saltan los siguientes números.	RANK() OVER (ORDER BY total DESC)
	Puesto sin huecos; los empates comparten puesto y no se salta nada.	DENSE_RANK() OVER (ORDER BY total DESC)
	Divide la ventana ordenada en n cubos aproximadamente iguales.	NTILE(4) OVER (ORDER BY total DESC)
Mirar a las vecinas
	Un valor de n filas hacia atrás; el valor por defecto lo sustituye cuando no existe esa fila.	LAG(total, 1, 0) OVER (ORDER BY created_at)
	Un valor de n filas hacia adelante; el valor por defecto lo sustituye cuando no existe esa fila.	LEAD(total, 1, 0) OVER (ORDER BY created_at)
Agregación sobre una ventana
	Un total acumulado o por partición sin colapsar filas.	SUM(total) OVER (ORDER BY created_at)
	Una media acumulada o por partición.	AVG(total) OVER (PARTITION BY user_id)
	Un recuento de filas acumulado o por partición.	COUNT(*) OVER (PARTITION BY user_id)
	El valor más bajo de la ventana o de la partición.	MIN(total) OVER (PARTITION BY user_id)
	El valor más alto de la ventana o de la partición.	MAX(total) OVER (PARTITION BY user_id)
	SUM con ORDER BY y un marco abierto desde el inicio; cada fila suma todo hasta sí misma.	SUM(total) OVER (ORDER BY created_at ROWS UNBOUNDED PRECEDING)
	AVG sobre un marco de n filas antes y después de la fila actual.	AVG(total) OVER (ORDER BY created_at ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING)
Primeros, últimos y percentiles
	El primer valor según la ordenación de la ventana.	FIRST_VALUE(total) OVER (ORDER BY total DESC)
	El último valor del marco; sin UNBOUNDED FOLLOWING el marco se detiene en la fila actual.	LAST_VALUE(total) OVER (ORDER BY total ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
	El valor de la n-ésima fila según la ordenación de la ventana.	NTH_VALUE(total, 2) OVER (ORDER BY total DESC)
	El puesto relativo de una fila, de 0 a 1.	PERCENT_RANK() OVER (ORDER BY total)
	La proporción de filas en la fila actual o antes de ella, desde 1/n hasta 1.	CUME_DIST() OVER (ORDER BY total)
La cláusula OVER
	Define la ventana que lee cada función.	<fn>() OVER (PARTITION BY user_id ORDER BY created_at)
	Reinicia la ventana para cada grupo de filas.	PARTITION BY user_id
	Ordena las filas dentro de cada partición.	ORDER BY created_at
	Delimita el marco deslizante, p. ej. de filas anteriores a posteriores.	ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
	Delimita el marco por valores, no por posiciones de fila; CURRENT ROW incluye los empates, y este es el marco por defecto con ORDER BY.	RANGE BETWEEN 100 PRECEDING AND CURRENT ROW
	Delimita el marco por grupos de filas empatadas del ORDER BY.	GROUPS BETWEEN 1 PRECEDING AND CURRENT ROW
	Quita la fila actual del marco; EXCLUDE TIES quita en su lugar las filas empatadas.	ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING EXCLUDE CURRENT ROW
	Nombra una ventana una vez por consulta para que varias cláusulas OVER la reutilicen.	WINDOW w AS (PARTITION BY user_id ORDER BY created_at)
	Restringe las filas que lee una función de agregación antes de aplicar la ventana.	SUM(total) FILTER (WHERE status = 'paid') OVER (ORDER BY created_at)