Funciones de ventana: agregados sin colapsar filas

Qué es

El tutorial de PostgreSQL define una función de ventana como una función que realiza un cálculo sobre un conjunto de filas de la tabla que están relacionadas de algún modo con la fila actual; es comparable a un agregado, pero sin colapsar las filas en una única fila de salida. La cláusula OVER define la ventana: PARTITION BY divide las filas en grupos, ORDER BY ordena las filas dentro de cada partición y un marco opcional (ROWS, RANGE o GROUPS BETWEEN ... AND ...) limita qué filas de la partición ve la función. La página de referencia enumera row_number, rank, dense_rank, percent_rank, ntile, lag, lead, first_value, last_value y nth_value; además, cualquier agregado como sum o avg puede usarse con OVER.

Por qué importa

Sin funciones de ventana, necesidades como "el salario de cada empleado junto al promedio de su departamento", "los tres últimos pedidos de cada cliente" o "un saldo acumulado" exigen autojoins o subconsultas correlacionadas, más difíciles de leer. Una función de ventana expresa cada una de ellas directamente en la lista del SELECT.

Cómo aplicarlo

• Total acumulado: sum(amount) OVER (PARTITION BY account_id ORDER BY created_at, id). Incluye un criterio de desempate en el ORDER BY: según el tutorial, cuando hay ORDER BY el marco por defecto va desde el inicio de la partición hasta la fila actual más las filas siguientes que sean iguales a ella, de modo que las claves de ordenación iguales se suman juntas.
• Top-N por grupo: row_number() OVER (PARTITION BY customer_id ORDER BY created_at DESC) AS rn en una subconsulta o CTE, y luego WHERE rn <= 3 en la consulta exterior; las funciones de ventana no pueden aparecer directamente en el WHERE.
• Comparación con la fila anterior: lag(value) OVER (ORDER BY ts) para calcular diferencias, y lead para el valor siguiente.
• Ranking: rank deja huecos tras los empates, dense_rank no, y row_number asigna el orden de forma arbitraria entre empates salvo que el ORDER BY sea total.
• Para compartir una ventana entre varias funciones, define WINDOW w AS (PARTITION BY ... ORDER BY ...) y escribe OVER w.

Trampas

Las funciones de ventana se evalúan después de WHERE, GROUP BY y HAVING, por lo que filtrar por su resultado requiere una consulta exterior. last_value con el marco por defecto devuelve el último par de la fila actual (la propia fila cuando el ORDER BY no tiene empates), no la última fila de la partición; la página de referencia advierte de que es probable que dé resultados poco útiles, así que conviene indicar ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING. Las particiones grandes se ordenan en memoria o en disco; busca los nodos WindowAgg y Sort en EXPLAIN.