Marcos ROWS frente a RANGE

Una vez que sabe escribir un total acumulado, la pregunta de seguimiento natural es: «¿Cuál es la diferencia entre ROWS y RANGE en un marco de ventana?» Es un indicador preciso del nivel intermedio. Muchos candidatos usan marcos a diario sin darse cuenta de que estas dos palabras clave se comportan de forma diferente.

Ambos definen el conjunto de filas que ve la función de agregación, pero cuentan ese conjunto de maneras fundamentalmente distintas. Domine esta diferencia y destacará.

ROWS es posicional. ROWS BETWEEN 2 PRECEDING AND CURRENT ROW significa literalmente la fila actual más las dos filas físicas que aparecen inmediatamente antes en la secuencia ordenada.

No importa si las filas vecinas comparten el mismo valor de ORDER BY. Tres filas son tres filas, sin excepciones. Este es el marco que casi siempre querrá usar para promedios móviles y totales acumulados estrictamente fila a fila.

RANGE se basa en valores. Incluye todas las filas cuyo valor de ORDER BY se encuentra dentro de un rango lógico respecto al valor de la fila actual, no un número determinado de filas.

La consecuencia más importante es que, con RANGE, todas las filas que tienen un empate en el valor de ORDER BY se tratan como un único grupo de filas con el mismo valor. Todas reciben el mismo marco y, por tanto, el mismo resultado.

Suponga que dos ventas se realizan el 2024-03-02, por importes de 30 y 40, precedidas por una venta del 2024-03-01 por un importe de 100.

• Con RANGE hasta CURRENT ROW: ambas filas del 03-02 tienen el mismo valor, por lo que ambas muestran 100 + 30 + 40 = 170.
• Con ROWS hasta CURRENT ROW: la primera fila del 03-02 muestra 130 y la segunda muestra 170, porque cada fila amplía el marco una posición cada vez.

Mismos datos, números diferentes. Esa divergencia cuando hay empates es el núcleo de la pregunta.

Recuerde que OVER (ORDER BY ...) sin un marco explícito utiliza de forma predeterminada RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

Esto significa que un total acumulado ingenuo usa RANGE silenciosamente. Con claves de ordenación únicas, es idéntico a ROWS, por lo que el error permanece oculto. En cuanto aparecen valores de ordenación duplicados, el total acumulado agrupa silenciosamente las filas empatadas. Por eso los ingenieros con experiencia escriben ROWS explícitamente.

RANGE puede aceptar un desplazamiento de valor, no solo UNBOUNDED. RANGE BETWEEN 7 PRECEDING AND CURRENT ROW aplicado a una columna de fecha o numérica incluye todas las filas cuyo valor está como máximo a 7 unidades del valor actual.

Aplicado a una fecha, esto proporciona una ventana real de "los últimos 7 días" que omite correctamente los días que faltan, mientras que ROWS 7 PRECEDING tomaría las 7 filas anteriores independientemente de los huecos. Conocer este matiz es una respuesta sólida.

El caso opuesto: ROWS no tiene en cuenta los huecos de los datos. Si a su tabla de ventas le faltan los fines de semana, ROWS BETWEEN 6 PRECEDING AND CURRENT ROW abarca 7 días registrados, que en realidad pueden cubrir dos semanas naturales.

Por tanto, la elección depende de la intención: los últimos N registros significa ROWS; las últimas N unidades de valor (días, dólares) significa RANGE con un desplazamiento.

Una respuesta honesta en una entrevista señala las diferencias de compatibilidad:

• PostgreSQL admite RANGE con desplazamientos (desde la versión 11) y admite ROWS por completo.
• SQL Server admite ROWS y RANGE, pero RANGE solo con UNBOUNDED/CURRENT ROW, no con desplazamientos numéricos.
• MySQL 8 admite ambos, con tipos de desplazamiento de RANGE limitados.

Si no se dispone de un desplazamiento numérico de RANGE, puede emularlo con una autocombinación o con un calendario generado. Mencionarlo demuestra que tiene en cuenta los entornos de producción.

Existe un tercer modo de marco menos conocido: GROUPS. Cuenta grupos de filas con el mismo valor en lugar de filas o valores. GROUPS BETWEEN 1 PRECEDING AND CURRENT ROW incluye el grupo actual y el grupo anterior.

Rara vez es necesario, pero mencionarlo cuando le pregunten "¿hay otros modos de marco?" demuestra un conocimiento profundo. PostgreSQL 11 y versiones posteriores, además de algunos otros sistemas, lo admiten.

Resuma todo el tema en una frase que pueda decir en una entrevista:

"Use ROWS cuando se refiera a un número fijo de filas físicas, use RANGE cuando se refiera a un intervalo lógico del valor de ordenación y recuerde que los empates hacen que diverjan, porque el RANGE predeterminado agrupa los valores iguales."

Esta única frase responde a la pregunta de forma completa y convincente.

Colocar ambos marcos en una misma consulta hace visible la diferencia. Ejecute esta consulta con datos que contengan fechas duplicadas y compare las dos columnas fila por fila.

Con fechas únicas, las columnas coinciden exactamente; con fechas empatadas, la columna RANGE repite el mismo total para las filas empatadas, mientras que la columna ROWS aumenta un paso cada vez.

Dos filas tienen el mismo valor de ORDER BY con un marco predeterminado. ¿Qué ocurre?

ROWS cuenta filas físicas; RANGE cuenta según el valor lógico de la columna de ordenación y agrupa los empates como filas del mismo grupo. El marco predeterminado de OVER (ORDER BY ...) es RANGE, por lo que un total acumulado ingenuo puede agrupar valores de ordenación duplicados.

Elija ROWS para "los últimos N registros" y para ventanas móviles que no dependan de los huecos; elija RANGE con un desplazamiento para "los últimos N días/dólares" cuando deba respetar los huecos. Saber que existe GROUPS es un punto adicional. A continuación, aplicaremos el marco ROWS para crear promedios móviles.