Marcos de ventana en SQL: ROWS vs RANGE

Lección 7.3 · Tiempo de lectura: ~8 min · Tareas de práctica ↓

Un marco de ventana decide qué filas alrededor de la actual usa realmente una función de ventana de agregación: todas las filas desde el inicio, las tres últimas filas o los últimos siete días. En esta lección aprenderás la sintaxis de los marcos ROWS y RANGE, construirás totales acumulados y medias móviles, verás por qué "las últimas 3 filas" y "los últimos 3 días" dan respuestas distintas con datos reales, y evitarás la trampa del marco predeterminado.

PARTITION BY divide las filas en grupos y ORDER BY las ordena. El marco es la tercera parte de OVER: dentro de la partición actual, selecciona un rango deslizante de filas relativo a la fila actual. Las funciones de agregación (SUM, AVG, COUNT, MIN, MAX) y de valor (FIRST_VALUE, LAST_VALUE) respetan el marco; las funciones de ranking y LAG/LEAD lo ignoran.

Los ejemplos se ejecutan en la base de datos de ejemplo Sakila (MySQL 8.0).

Sintaxis del marco

function_name(expression) OVER (
    PARTITION BY partition_columns
    ORDER BY sort_column
    { ROWS | RANGE } BETWEEN frame_start AND frame_end
)

• ROWS cuenta filas físicas: 2 PRECEDING son las dos filas encima de la actual, sean cuales sean sus valores.
• RANGE trabaja con los valores de la columna del ORDER BY: INTERVAL 2 DAY PRECEDING son todas las filas cuya fecha es como mucho dos días anterior. Un marco RANGE con desplazamiento necesita exactamente una columna numérica o de fecha en ORDER BY.

Los datos: ingresos diarios

Todos los ejemplos usan los ingresos diarios del 26 de julio al 23 de agosto de 2005. Fíjate en el hueco: después del 2 de agosto, el siguiente día con pagos es el 16 de agosto.

WITH daily AS (
    SELECT DATE(payment_date) AS pay_day, SUM(amount) AS revenue
    FROM payment
    WHERE payment_date >= '2005-07-26'
      AND payment_date < '2005-08-24'
    GROUP BY DATE(payment_date)
)
SELECT pay_day, revenue
FROM daily
ORDER BY pay_day;

Resultado: 16 días: del 26 de julio al 2 de agosto y del 16 al 23 de agosto.

Total acumulado y total restante

UNBOUNDED PRECEDING AND CURRENT ROW acumula desde la primera fila; CURRENT ROW AND UNBOUNDED FOLLOWING suma lo que todavía queda:

WITH daily AS (
    SELECT DATE(payment_date) AS pay_day, SUM(amount) AS revenue
    FROM payment
    WHERE payment_date >= '2005-07-26'
      AND payment_date < '2005-08-24'
    GROUP BY DATE(payment_date)
)
SELECT
    pay_day,
    revenue,
    SUM(revenue) OVER (ORDER BY pay_day
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total,
    SUM(revenue) OVER (ORDER BY pay_day
        ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS remaining_total
FROM daily
ORDER BY pay_day
LIMIT 3;

Resultado: el 26 de julio el total acumulado es 160.67 y el total restante es todo el periodo, 37971.56; el 28 de julio son 5464.98 y 35084.38.

El total acumulado se trata con más detalle, incluidos los totales por grupo y la solución sin funciones de ventana, en la lección 12.7.

Media móvil: 3 filas no son 3 días

Una media móvil suaviza las oscilaciones diarias. El primer intento suele ser "la fila actual y las dos anteriores", un marco ROWS. Un marco RANGE con INTERVAL toma en cambio "el día actual y los dos días de calendario anteriores":

WITH daily AS (
    SELECT DATE(payment_date) AS pay_day, SUM(amount) AS revenue
    FROM payment
    WHERE payment_date >= '2005-07-26'
      AND payment_date < '2005-08-24'
    GROUP BY DATE(payment_date)
)
SELECT
    pay_day,
    revenue,
    ROUND(AVG(revenue) OVER (ORDER BY pay_day
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), 2) AS avg_3_rows,
    ROUND(AVG(revenue) OVER (ORDER BY pay_day
        RANGE BETWEEN INTERVAL 2 DAY PRECEDING AND CURRENT ROW), 2) AS avg_3_days
FROM daily
ORDER BY pay_day;

Resultado: 16 filas; las dos medias coinciden en todas partes salvo justo después del hueco: el 16 de agosto la media de 3 filas es 1885.21 y la de 3 días es 111.77.

El 16 de agosto, "las últimas 3 filas" son el 1 de agosto, el 2 de agosto y el 16 de agosto: dos de ellas tienen dos semanas. "Los últimos 3 días" contienen solo el propio 16 de agosto, porque el 14 y el 15 de agosto no hubo pagos. Cuando las fechas tienen huecos, usa RANGE con INTERVAL para ventanas de calendario, o primero une los datos a un calendario completo (lección 6.6) para que cada día tenga una fila.

MariaDB no acepta INTERVAL en un marco. Allí ordena por el número de día y usa un desplazamiento numérico, que da el mismo resultado: AVG(revenue) OVER (ORDER BY TO_DAYS(pay_day) RANGE BETWEEN 2 PRECEDING AND CURRENT ROW).

Al inicio de los datos, ambos marcos simplemente contienen menos filas: el primer día se promedia sobre un valor y el segundo sobre dos.

El marco predeterminado

Si OVER tiene ORDER BY pero no tiene marco, la base de datos usa RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. Es un cálculo acumulado y, como es RANGE, las filas con el mismo valor de orden se suman juntas, en bloque. Sin ORDER BY, el marco predeterminado es la partición completa.

Dos consecuencias prácticas:

• SUM(x) OVER (ORDER BY day) es un total acumulado, no un total general. Quita ORDER BY para obtener el total.
• Cuando la columna de orden tiene repetidos, el total acumulado salta de una vez todo el grupo de filas iguales. La lección 12.7 lo muestra con dos pagos hechos en el mismo segundo. Escribe ROWS BETWEEN ... explícitamente siempre que el marco importe.

Ponerle nombre a una ventana con WINDOW

Cuando varias funciones usan la misma ventana, defínela una sola vez con la cláusula WINDOW y haz referencia a ella por su nombre:

WITH daily AS (
    SELECT DATE(payment_date) AS pay_day, SUM(amount) AS revenue
    FROM payment
    WHERE payment_date >= '2005-07-26'
      AND payment_date < '2005-08-24'
    GROUP BY DATE(payment_date)
)
SELECT
    pay_day,
    revenue,
    MIN(revenue) OVER last_3_days AS min_3_days,
    MAX(revenue) OVER last_3_days AS max_3_days,
    COUNT(*) OVER last_3_days AS days_in_window
FROM daily
WINDOW last_3_days AS (
    ORDER BY pay_day
    RANGE BETWEEN INTERVAL 2 DAY PRECEDING AND CURRENT ROW
)
ORDER BY pay_day;

Resultado: 16 filas; el 16 de agosto days_in_window es 1, el 17 de agosto es 2 y desde el 18 de agosto vuelve a ser 3.

La cláusula WINDOW va después de HAVING y antes de ORDER BY. La admiten MySQL 8.0, MariaDB 10.2+, PostgreSQL y SQLite; SQL Server desde la versión 2022.

Lo que MySQL no admite

El estándar SQL también tiene marcos GROUPS, que cuentan grupos de valores iguales, y una opción EXCLUDE. PostgreSQL y SQLite los admiten; MySQL y MariaDB no.

Conclusiones clave:

• Un marco selecciona las filas alrededor de la actual que usa una función de ventana de agregación: ROWS o RANGE BETWEEN start AND end.
• UNBOUNDED PRECEDING AND CURRENT ROW da un total acumulado; n PRECEDING AND CURRENT ROW da una ventana deslizante.
• ROWS cuenta filas y RANGE compara valores: con huecos en las fechas, "3 filas" y "3 días" dan medias distintas.
• Con ORDER BY y sin marco, el predeterminado es RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW; escribe el marco explícitamente cuando importe.
• La cláusula WINDOW le da nombre a una ventana y permite que varias funciones la reutilicen.

Preguntas frecuentes

¿Cuál es la diferencia entre ROWS y RANGE en SQL?

ROWS cuenta filas físicas: 2 PRECEDING son las dos filas encima de la actual. RANGE compara valores de la columna del ORDER BY: INTERVAL 2 DAY PRECEDING son todas las filas cuya fecha está dentro de los dos días anteriores a la actual, sean cuantas sean.

¿Cómo calculo una media móvil en SQL?

Usa AVG con un marco: AVG(revenue) OVER (ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) promedia la fila actual y las seis anteriores. Si algunos días no tienen filas, usa RANGE BETWEEN INTERVAL 6 DAY PRECEDING AND CURRENT ROW o rellena primero los huecos con un calendario.

¿Cuál es el marco de ventana predeterminado?

Con ORDER BY en OVER y sin cláusula de marco, es RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, un cálculo acumulado en el que las filas con valores de orden iguales entran juntas. Sin ORDER BY, el marco es la partición completa.

¿Los marcos de ventana funcionan con ROW_NUMBER y RANK?

No. Las funciones de ranking (ROW_NUMBER, RANK, DENSE_RANK, NTILE) y LAG/LEAD siempre trabajan sobre la partición completa e ignoran el marco. Los marcos afectan a las funciones de agregación y a FIRST_VALUE, LAST_VALUE y NTH_VALUE.