Funciones de ventana

Las funciones de ventana son una de las herramientas más poderosas de SQL moderno. Permiten realizar cálculos sobre un conjunto de filas que están relacionadas con la fila actual, sin colapsar el resultado en una sola fila como hace GROUP BY. Dicho de otra forma, puedes obtener agregados como sumas, promedios o conteos y al mismo tiempo conservar cada fila individual del resultado.

MySQL incorporó las funciones de ventana a partir de la versión 8.0, alineándose con el estándar SQL:2003 que otros motores como PostgreSQL y SQL Server ya soportaban. Si trabajas con MySQL 8.0 o superior, tienes acceso completo a esta funcionalidad que transforma radicalmente la forma en que escribes consultas analíticas.

La diferencia fundamental con GROUP BY

Para entender por qué las funciones de ventana son tan útiles, compara estos dos enfoques. Supón que tienes una tabla ventas con las ventas de cada empleado por mes y quieres ver el total de ventas por departamento.

Con GROUP BY obtienes una fila por departamento, perdiendo el detalle individual:

SELECT
    departamento,
    SUM(monto) AS total_departamento
FROM ventas
GROUP BY departamento;

Con una función de ventana conservas cada fila y añades el total como columna adicional:

SELECT
    empleado,
    departamento,
    monto,
    SUM(monto) OVER (PARTITION BY departamento) AS total_departamento
FROM ventas;

Cada empleado mantiene su fila individual, pero ahora puedes ver el total de su departamento junto a su cifra personal. Esto permite calcular, por ejemplo, qué porcentaje del total departamental representa cada empleado, algo imposible con un simple GROUP BY.

La cláusula OVER

Toda función de ventana lleva la cláusula OVER() que define la ventana sobre la cual se calcula. Sin OVER, MySQL no sabe que estás usando una función de ventana. La sintaxis general es:

funcion_ventana() OVER (
    [PARTITION BY columnas]
    [ORDER BY columnas]
    [especificación_de_marco]
)

Si dejas OVER() vacío, la ventana abarca todas las filas del resultado. Es el equivalente a calcular un agregado global sin agrupación:

SELECT
    empleado,
    monto,
    SUM(monto) OVER () AS total_general,
    ROUND(monto / SUM(monto) OVER () * 100, 2) AS porcentaje
FROM ventas
LIMIT 4;

Observa que total_general es el mismo para todas las filas porque la ventana no tiene particiones ni orden: abarca todo el conjunto de resultados.

PARTITION BY: dividir en grupos

PARTITION BY divide las filas en grupos independientes, de forma similar a como GROUP BY crea grupos, pero sin colapsar filas. Cada partición se procesa por separado y la función se reinicia en cada una:

SELECT
    empleado,
    departamento,
    mes,
    monto,
    SUM(monto) OVER (PARTITION BY departamento) AS total_depto,
    COUNT(*) OVER (PARTITION BY departamento) AS registros_depto
FROM ventas
ORDER BY departamento, mes;

Puedes particionar por múltiples columnas. Por ejemplo, PARTITION BY departamento, YEAR(fecha) crearía una partición distinta para cada combinación de departamento y año.

ORDER BY dentro de OVER: establecer el orden

Cuando añades ORDER BY dentro de OVER, defines el orden en que la función procesa las filas dentro de cada partición. Para funciones como ROW_NUMBER, RANK o LAG, el orden determina directamente el resultado. Para funciones de agregación como SUM, el orden convierte el cálculo en un acumulado:

SELECT
    empleado,
    mes,
    monto,
    SUM(monto) OVER (ORDER BY mes) AS acumulado
FROM ventas
WHERE departamento = 'Ventas' AND empleado = 'Ana García'
ORDER BY mes;

Sin el ORDER BY, la suma sería siempre 45000 (el total). Con el ORDER BY, MySQL calcula la suma de forma progresiva: la primera fila solo incluye su propio valor, la segunda suma los dos primeros, y así sucesivamente. Esto ocurre porque al agregar ORDER BY se activa un marco de ventana implícito que va desde el inicio de la partición hasta la fila actual.

La combinación de PARTITION BY con ORDER BY es el patrón más habitual. Permite, por ejemplo, calcular acumulados por departamento:

SELECT
    departamento,
    mes,
    monto,
    SUM(monto) OVER (
        PARTITION BY departamento
        ORDER BY mes
    ) AS acumulado_depto
FROM ventas
ORDER BY departamento, mes;

El acumulado se reinicia a cero cuando cambia el departamento gracias al PARTITION BY.

Especificación de marco (frame)

El marco de ventana controla exactamente qué filas se incluyen en el cálculo para cada fila. Se define con la cláusula ROWS BETWEEN o RANGE BETWEEN después del ORDER BY:

funcion() OVER (
    PARTITION BY columna
    ORDER BY columna
    ROWS BETWEEN inicio AND fin
)

Los límites del marco pueden ser:

• UNBOUNDED PRECEDING significa desde la primera fila de la partición.
• N PRECEDING significa N filas antes de la fila actual.
• CURRENT ROW es la fila actual.
• N FOLLOWING significa N filas después de la fila actual.
• UNBOUNDED FOLLOWING significa hasta la última fila de la partición.

Un ejemplo práctico es calcular la media móvil de tres meses, que suaviza las fluctuaciones para revelar tendencias:

SELECT
    mes,
    ingresos,
    ROUND(AVG(ingresos) OVER (
        ORDER BY mes
        ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
    ), 2) AS media_movil_3m
FROM resumen_mensual
WHERE anio = 2024;

Para enero, la media solo incluye enero y febrero (no hay mes anterior), así que el marco se ajusta automáticamente. Para febrero en adelante, incluye el mes anterior, el actual y el siguiente.

Otro marco muy utilizado es el que acumula desde el inicio hasta la fila actual, que es el marco por defecto cuando especificas ORDER BY:

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

La diferencia entre ROWS y RANGE es sutil pero importante. ROWS cuenta filas físicas, mientras que RANGE agrupa filas con el mismo valor en la columna de orden. En la mayoría de los casos prácticos usarás ROWS.

Ventanas con nombre: la cláusula WINDOW

Cuando reutilizas la misma definición de ventana en varias funciones dentro de la misma consulta, puedes darle un nombre con la cláusula WINDOW para evitar repetición:

SELECT
    empleado,
    departamento,
    monto,
    ROW_NUMBER() OVER w AS num_fila,
    RANK() OVER w AS ranking,
    SUM(monto) OVER w AS acumulado
FROM ventas
WINDOW w AS (PARTITION BY departamento ORDER BY monto DESC);

La cláusula WINDOW se coloca después de HAVING (o WHERE si no hay GROUP BY) y antes de ORDER BY. Puedes definir múltiples ventanas con nombre separándolas por comas:

SELECT
    empleado,
    departamento,
    monto,
    SUM(monto) OVER w_depto AS total_depto,
    SUM(monto) OVER w_global AS total_global
FROM ventas
WINDOW
    w_depto AS (PARTITION BY departamento),
    w_global AS ()
ORDER BY departamento, empleado;

Tipos de funciones de ventana

MySQL 8.0 ofrece tres categorías de funciones de ventana:

Las funciones de numeración asignan un número o rango a cada fila según su posición. Incluyen ROW_NUMBER() para numeración secuencial, RANK() para rangos con huecos en empates, DENSE_RANK() para rangos sin huecos, y NTILE(n) para dividir filas en n grupos.

Las funciones de valor acceden a valores de otras filas sin necesidad de un JOIN. Incluyen LAG() para la fila anterior, LEAD() para la fila siguiente, FIRST_VALUE() para el primer valor de la ventana, LAST_VALUE() para el último, y NTH_VALUE() para el valor en la posición n.

Las funciones de distribución calculan métricas estadísticas como CUME_DIST() para la distribución acumulativa y PERCENT_RANK() para el rango porcentual.

Además, las funciones de agregación que ya conoces como SUM, AVG, COUNT, MIN y MAX también pueden usarse como funciones de ventana al añadirles OVER().

Manejo de NULL

Las funciones de ventana tratan los valores NULL de forma consistente con el estándar SQL. En PARTITION BY, todas las filas con NULL se agrupan en la misma partición. En ORDER BY, los valores NULL se ordenan juntos (por defecto aparecen primero en orden ascendente en MySQL):

SELECT
    empleado,
    departamento,
    monto,
    ROW_NUMBER() OVER (PARTITION BY departamento ORDER BY monto DESC) AS ranking
FROM ventas;

Si departamento es NULL para algunas filas, todas esas filas formarán una sola partición. Si monto es NULL, esas filas aparecerán al final del ranking cuando el orden es descendente.

Para las funciones de agregación como SUM y AVG, los valores NULL se ignoran en el cálculo, igual que en su versión sin ventana.

Restricciones importantes

Las funciones de ventana solo pueden aparecer en las cláusulas SELECT y ORDER BY. No puedes usarlas en WHERE, GROUP BY ni HAVING. Si necesitas filtrar por el resultado de una función de ventana, envuelve la consulta en una subconsulta o usa un CTE:

WITH ranking_ventas AS (
    SELECT
        empleado,
        departamento,
        monto,
        RANK() OVER (PARTITION BY departamento ORDER BY monto DESC) AS ranking
    FROM ventas
)
SELECT *
FROM ranking_ventas
WHERE ranking <= 3;

Este patrón de CTE con filtro es probablemente el más utilizado en la práctica, ya que te permite aprovechar las funciones de ventana y luego filtrar por sus resultados.

Combinación con otras funciones

Las funciones de ventana se combinan naturalmente con expresiones regulares de SQL. Puedes usar funciones de ventana dentro de ROUND, CONCAT, CASE y cualquier otra expresión:

SELECT
    empleado,
    departamento,
    monto,
    CONCAT(
        ROUND(monto / SUM(monto) OVER (PARTITION BY departamento) * 100, 1),
        '%'
    ) AS porcentaje_depto
FROM ventas
ORDER BY departamento, monto DESC;

En el siguiente artículo veremos ROW_NUMBER para numerar filas dentro de cada partición.