Menu

Funciones de ventana en PostgreSQL: OVER y PARTITION BY

Las funciones de ventana calculan un valor para cada fila a partir de un grupo de filas relacionadas sin agruparlas: rankings, totales acumulados, medias móviles y comparaciones con la fila anterior.

Esta página incluye editores ejecutables: edita, ejecuta y ve el resultado al instante.

Una función de ventana calcula un valor para cada fila a partir de un conjunto de filas relacionadas, su ventana, sin fusionar las filas como hace GROUP BY. Se escribe function() OVER (PARTITION BY ... ORDER BY ...). PARTITION BY divide las filas en grupos y ORDER BY fija el orden dentro de cada grupo.

Todas las filas siguen en el resultado y llevan el total de su región: 1000 en north, 500 en south. La parte de cada comercial sale entonces con una sola expresión (ana tiene el 50 por ciento de north). Cualquier agregado (sum, count, avg, min, max) se convierte en función de ventana cuando le añades OVER. Un OVER () vacío convierte todo el resultado en una sola ventana.

Numerar filas con ROW_NUMBER, RANK y DENSE_RANK

Las tres funciones de ranking solo se diferencian cuando las filas empatan en el valor del ORDER BY:

ben y cy tienen 85. row_number aun así les da 2 y 3; cuál recibe el 2 no está definido salvo que el ORDER BY deshaga el empate. rank les da 2 a los dos y salta a 4 para dee. dense_rank les da 2 a los dos y sigue con 3.

FunciónEmpatesHuecos después de los empates
row_number()números distintosno
rank()el mismo númerosí
dense_rank()el mismo númerono

Obtener las N primeras filas de cada grupo

Las funciones de ventana se evalúan después del WHERE, así que no puedes filtrar por ellas directamente. Numera las filas en una subconsulta o un CTE y filtra fuera:

Devuelve los dos mejores comerciales de cada región. Usa rank() en lugar de row_number() para conservar todas las filas que empatan en el segundo puesto. Si solo quieres la primera fila de cada grupo, DISTINCT ON es más corto.

Calcular un total acumulado o una media móvil

Con ORDER BY dentro de OVER, un agregado se calcula sobre un marco. Por defecto el marco va desde la primera fila de la partición hasta la fila actual, lo que da un total acumulado. Una cláusula ROWS BETWEEN fija otro marco, como la fila actual y las dos anteriores:

El total acumulado llega a 130 el día 5, y la media de tres días del día 5 es (30 + 20 + 50) / 3 = 33.3.

El marco por defecto es RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. Incluye todas las filas que empatan con la actual en el valor del ORDER BY, así que dos filas con el mismo día reciben el mismo total acumulado. Escribe ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW para sumarlas de una en una.

Comparar con la fila anterior: LAG y LEAD

lag(col) lee un valor de la fila anterior a la actual en el orden de la ventana, y lead(col) de la fila siguiente. Las dos devuelven NULL fuera del borde salvo que pases un valor por defecto como tercer argumento.

El día 1 no tiene fila anterior, así que previous y change son NULL. La cláusula WINDOW define una ventana una sola vez, con nombre, para que varias funciones la compartan.

first_value funciona con el marco por defecto, pero last_value no: el marco por defecto termina en la fila actual, así que devuelve el valor de la fila actual. Añade ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING para que el marco sea toda la partición. Otras funciones exclusivas de ventana son ntile(n), percent_rank(), cume_dist() y nth_value(); la referencia de funciones de ventana de PostgreSQL las lista todas.

Filtrar qué filas cuenta un agregado

FILTER (WHERE ...) también funciona con los agregados de ventana, para totales condicionales en cada fila:

SELECT day,
       count(*) FILTER (WHERE status = 'error') OVER (ORDER BY day) AS errors_so_far
FROM requests;

Preguntas frecuentes

¿Qué diferencia hay entre GROUP BY y PARTITION BY?

GROUP BY devuelve una fila por grupo. PARTITION BY dentro de OVER conserva todas las filas y le da a cada una un valor calculado sobre su grupo, así que puedes mostrar el importe de una fila junto al total de su grupo.

¿Puedo usar una función de ventana en el WHERE?

No. Las funciones de ventana se ejecutan después de WHERE, GROUP BY y HAVING, así que solo se permiten en la lista del select y en ORDER BY. Calcula el valor en una subconsulta o un CTE y filtra en la consulta exterior.

¿Por qué LAST_VALUE devuelve el valor de la fila actual?

Con ORDER BY dentro de OVER, el marco por defecto termina en la fila actual (y sus empates), así que la última fila del marco es la actual. Añade ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING para que el marco sea toda la partición.

¿Qué diferencia hay entre ROWS y RANGE en un marco?

ROWS cuenta filas físicas: ROWS BETWEEN 2 PRECEDING AND CURRENT ROW es la fila actual y las dos anteriores. RANGE trabaja sobre el valor del ORDER BY, así que las filas con valores iguales (pares) siempre entran juntas en el marco.

Ilustración de los lenguajes de programación de Coddy

Aprende a programar con Coddy

Comenzar