Introducción a las funciones de ventana

Las funciones de ventana te permiten realizar cálculos sobre un conjunto de filas relacionadas con la fila actual, sin colapsar esas filas en una única salida como hace GROUP BY. Esto significa que conservas el detalle a nivel de fila original y, al mismo tiempo, obtienes agregados, rankings y comparaciones en paralelo. Los casos de uso más comunes incluyen clasificar los productos más vendidos por región, calcular totales acumulados para informes financieros y obtener medias móviles a lo largo del tiempo. La sintaxis clave combina una función de agregación con una cláusula OVER que define cómo se "divide" la ventana de filas.

Sintaxis básica y funciones principales

Una llamada a función de ventana se parece a una agregación normal, pero con una cláusula OVER añadida. Dentro de OVER defines PARTITION BY (cómo agrupar las filas en ventanas), ORDER BY (cómo ordenar dentro de cada ventana) y, opcionalmente, una cláusula de frame como ROWS BETWEEN. Las funciones de ventana más utilizadas son ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD, SUM, AVG y COUNT: cada una funciona como función de agregación o de navegación según el contexto.

SELECT
    employee_id,
    department,
    salary,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank
FROM employees;

ROW_NUMBER asigna un número secuencial único dentro de cada partición, reiniciándose en 1 cuando cambia la partición. RANK y DENSE_RANK se comportan de forma similar, pero asignan el mismo número a los empates y se diferencian en cómo gestionan los huecos tras los valores empatados.

Ranking y consultas Top-N

El ranking es una de las aplicaciones más prácticas. Supón que quieres los 3 empleados mejor pagados por departamento. Calculas una columna de rank y luego filtras por ella, ya sea con una subconsulta o un CTE para mayor claridad. Este patrón es mucho más eficiente que las subconsultas correlacionadas y se lee de forma natural en el código de la aplicación.

WITH ranked AS (
    SELECT
        employee_id,
        department,
        salary,
        RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rk
    FROM employees
)
SELECT *
FROM ranked
WHERE rk <= 3
ORDER BY department, rk;

Observa cómo PARTITION BY department reinicia el ranking para cada departamento, de modo que siempre obtienes exactamente 3 ganadores por grupo, incluso si hay salarios empatados.

Totales acumulados y frames móviles

Los totales acumulados y las medias móviles requieren una cláusula de frame dentro de OVER. ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW te da una suma acumulada que crece desde el inicio de la partición hasta la fila actual. Cambiar a una ventana fija como ROWS BETWEEN 6 PRECEDING AND CURRENT ROW produce una media móvil de 7 filas, algo muy habitual en analítica de series temporales.

SELECT
    order_date,
    daily_revenue,
    SUM(daily_revenue) OVER (
        ORDER BY order_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS cumulative_revenue,
    AVG(daily_revenue) OVER (
        ORDER BY order_date
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS moving_avg_7d
FROM daily_sales
ORDER BY order_date;

La primera columna crece de forma monótona, mientras que la media móvil suaviza los picos diarios para revelar la tendencia subyacente. Las definiciones de frame pueden usar ROWS (conteo físico) o RANGE (lógico, basado en valores), pero ROWS es más rápido y predecible para la mayoría de necesidades de reporting.

Errores comunes y consejos de rendimiento

Un error frecuente es olvidar que las funciones de ventana se ejecutan después de WHERE, HAVING y GROUP BY, por lo que no se pueden filtrar directamente en la misma consulta sin un CTE o una subconsulta. Otra trampa es mezclar funciones de ventana con SELECT DISTINCT o agregar el conjunto de resultados de formas que confundan al planificador. Para mejorar el rendimiento, indexa siempre las columnas utilizadas en PARTITION BY y ORDER BY, y prefiere ROWS sobre RANGE cuando no necesites frames basados en valores. Evita colocar funciones de ventana en cláusulas WHERE envolviendo primero la consulta en un CTE.

Resumen

Las funciones de ventana en PostgreSQL son la herramienta adecuada cuando necesitas detalle a nivel de fila junto con cálculos agrupados. La cláusula OVER con PARTITION BY y ORDER BY te da control total sobre cómo se define la ventana, y las cláusulas de frame extienden ese control a cálculos continuos. Dominar el ranking, los totales acumulados y las medias móviles cubre aproximadamente el 80 por ciento de las consultas analíticas del mundo real que escribirás como desarrollador backend.