Marcos de ventana, RANGE y ROWS

En el contexto de las funciones de ventana de SQL, el «marco de ventana» define el subconjunto de filas que la función SQL tiene en cuenta al hacer sus cálculos.

Dicho de otro modo, el marco de ventana es un conjunto dinámico de filas que «se desliza» por el resultado de tu consulta y forma conjuntos de datos distintos para cada fila, según la definición de marco que hayas indicado.

Marco de ventana vs. particiones

Aunque los términos «marco de ventana» y «partición» pueden parecer similares, representan conceptos diferentes:

• Partición (PARTITION BY). Es la división de todo el conjunto de resultados en subconjuntos que no se solapan, donde cada subconjunto contiene las filas con los mismos valores en una o varias columnas. Las funciones de ventana se aplican por separado a cada partición, como si cada una fuera un conjunto de datos independiente.

  

• Marco de ventana. Define qué filas concretas de cada partición se usarán para calcular la función de ventana en cada fila. El marco puede cambiar de una fila a otra.

  Por ejemplo, si se usa la regla ROWS BETWEEN 1 PRECEDING AND CURRENT ROW, para cada fila el marco estará formado por la propia fila y una fila anterior. Es como una «subpartición» dentro de una partición ya existente.

  

  Es decir:

  • El primer marco está formado solo por el 1.er registro, porque no hay ningún registro anterior. Ese único registro se pasa a la función de agregación AVG(price) y el resultado se añade a la columna avg_price.
  • El segundo marco ya contiene los registros 1 y 2, que se envían a AVG(price) y devuelven (170 + 220) / 2 = 195.
  • El tercer marco contiene los registros 2 y 3, y el resultado es (220 + 150) / 2 = 185.
  • y así sucesivamente.

Nota sobre el marco de ventana sin ROWS/RANGE

Si en la definición de la función de ventana no aparece ROWS/RANGE, el marco de ventana por defecto depende de si hay ORDER BY.

Si no hay ORDER BY, el marco de ventana coincide con la partición. En ese caso, la función de ventana procesa todas las filas de la partición, sin limitarse a un subconjunto. Esto significa que el resultado de la función será el mismo para todas las filas de una misma partición.

Si hay ORDER BY, por defecto se aplica la regla RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. En ese caso, el marco de ventana empieza en la primera fila y termina en la fila actual, incluidas todas las filas que tienen los mismos valores en las columnas de ordenación que la fila actual.

Definición de los límites del marco de ventana

Con la sintaxis ROWS o RANGE podemos definir qué marco de ventana concreto se pasará a la función de ventana para calcular el valor de la fila actual.

La sintaxis para definir los límites del marco de ventana consiste en indicar un rango respecto a la fila actual.

SELECT funcion_ventana(campo_tabla)
OVER (
    [PARTITION BY columna_particion [, ...]]
    [ORDER BY columna_orden [, ...]]
    {ROWS | RANGE} BETWEEN inicio_marco AND fin_marco
)

Por ejemplo, si queremos que en los cálculos de la función de ventana entren solo los dos registros anteriores y la fila actual, la sintaxis será la siguiente:

MySQL 8.4
... ROWS|RANGE BETWEEN 2 PRECEDING AND CURRENT ROW

Si queremos que a la función de ventana se le pasen la fila actual y todas las siguientes, la sintaxis será esta:

MySQL 8.4
... ROWS|RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING

Posibles definiciones de los límites del marco

• UNBOUNDED PRECEDING, todas las filas anteriores a la actual
• N PRECEDING, N filas antes de la fila actual
• CURRENT ROW, la fila actual
• N FOLLOWING, N filas después de la fila actual
• UNBOUNDED FOLLOWING, todas las filas siguientes

Esquema de definición de los límites del marco

Diferencia entre ROWS y RANGE

Para definir los límites del marco se usan las palabras clave ROWS y RANGE. Funcionan de forma distinta:

ROWS

• Se basa en filas físicas:

  Con ROWS, la definición del marco se basa en la posición física de las filas respecto a la fila actual. Por ejemplo, 1 PRECEDING significa una fila antes de la actual.

• Límite exacto:

  Definir el marco con ROWS limita con precisión el número de filas que entran en él, lo que lo hace predecible y concreto.

RANGE

• Se basa en valores:

  A diferencia de ROWS, RANGE define los límites del marco a partir de los valores de las columnas, ordenados según el ORDER BY de la función de ventana.

• Límites dinámicos:

  Los límites definidos con RANGE pueden variar según los datos, lo que hace que el marco sea flexible, pero potencialmente menos predecible.

Vamos a comprobar si has entendido bien la diferencia entre ROWS y RANGE:

¿Cuál de las siguientes afirmaciones describe mejor la diferencia entre usar ROWS y RANGE en el contexto de las funciones de ventana de SQL?