La cláusula RANGE en las funciones de ventana de SQL: 5 Ejemplos Prácticos

¿Qué es una cláusula RANGE en las funciones ventana de SQL? Cinco ejemplos prácticos le mostrarán cómo y cuándo utilizarla.

La cláusula RANGE se utiliza muy poco en las funciones de ventana de SQL. No sé por qué; tal vez la gente no está acostumbrada a ella. Es una pena, porque está lejos de ser una cláusula inútil; puede ser muy útil, y te lo mostraré en cinco ejemplos.

Sintaxis de la cláusula RANGE

Cuando hablo de la cláusula RANGE, me refiero a la que se utiliza en las funciones de ventana de SQL, que tienen la siguiente sintaxis:

OVER (   
  [ <PARTITION BY clause> ]
  [ <ORDER BY clause> ]
  [ <ROW or RANGE clause> ]
) 

Cuando observas la sintaxis anterior, ves que tanto ROW como RANGE pueden formar parte de la función ventana. Su sintaxis es la siguiente:

[<cláusula RWS o RANGE> BETWEEN <Start expr> AND <End expr>]

El marco de la ventana por defecto sin el ORDER BY es toda la partición. Pero cuando se utiliza el ORDER BY, el marco de la ventana por defecto es RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

Es normal olvidar esta sintaxis, especialmente si es nueva para ti o no la usas muy a menudo. Siéntase libre de tener su Funciones de ventana Cheat Sheet abierta mientras lee este artículo.

¿Listo para sumergirte? Muy bien. Primero, hablemos de RANGE y ROW.

¿Son iguales las cláusulas RANGE y ROW?

No, no lo son. Sin embargo, tienen el mismo propósito: especificar los puntos inicial y final dentro de la partición, con el objetivo de limitar las filas. Sin embargo, cada cláusula lo hace de forma diferente. La cláusula ROW lo hace especificando un número fijo de filas que preceden o siguen a la fila actual.

La cláusula RANGE, por otro lado, limita las filas de forma lógica; especifica el rango de valores en relación con el valor de la fila actual.

¿Qué tipos de datos puedo utilizar en la cláusula RANGE?

Puede utilizar dos tipos de datos con la cláusula RANGE: tipos numéricos y de fecha/hora.

Puede utilizar los tipos de datos numéricos y la cláusula RANGE en casi todas las bases de datos habituales. Lamentablemente, Microsoft SQL Server no lo soporta.

En cuanto a los tipos de datos de fecha/hora, sólo unas pocas bases de datos populares admiten su uso con la cláusula RANGE. Estas son PostgreSQL, MySQL y Oracle DB.

Vayamos ahora a los ejemplos y veamos cómo funciona RANGE en la práctica. Si eres nuevo en las funciones de ventana de SQL, tal vez debas revisar primero qué son las funciones de ventana y cómo se comparan con las funciones agregadas. Vuelve cuando hayas terminado de leer y continuaremos.

Ejemplo 1 - Calcular el Total Corrido

Los datos con los que trabajaré están en la tabla revenue. Las columnas son:

• id - El ID de la fecha y la clave primaria de la tabla (PK).
• date - La fecha de los ingresos.
• revenue_amount - El importe de los ingresos.

Su tarea es calcular los totales corrientes de los ingresos utilizando la cláusula RANGE. Hagámoslo primero sin SQL. Si tiene los siguientes datos, ¿cuál será el total acumulado (también conocido como suma acumulada)?

El total acumulado para el 2021-05-01 es igual a los ingresos para esa fecha: 12.573,25. Eso es porque no hay filas anteriores que incluir en el cálculo. Luego viene el 2021-05-02. El total actual es el ingreso de hoy sumado al ingreso del día anterior: 11,348.22 + 12,573. 25 = 23,921.47.

Observe que hay otra fila con una cantidad diferente de ingresos para el 2021-05-02. Tal vez sea para otra sucursal, país, producto o lo que sea. Funciona igual: 14.895,13 + 23.921, 47 = 38.816,60. (La cláusula RANGE funcionará incluso si hay varias filas con la misma fecha). A continuación viene el 2021-05-03. El total de esta fecha será 14.388,14 + 38 .816,60 = 53.204,74. Por último, el total de 2021-05-04 será de 18.847,54 + 53.204 = 72.052,28.

¿Cómo se hace lo mismo utilizando la cláusula RANGE? Se podría hacer así:

SELECT
  id,
  date,
  revenue_amount,
  SUM(revenue_amount) OVER (
    ORDER BY date
    RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_total
FROM revenue;

He utilizado la función SUM() en la columna revenue_amount; esta es la operación necesaria para obtener el total corrido. Para que la función SUM() se convierta en una función ventana, se necesita la cláusula OVER(). El cálculo de la función ventana se hace en orden ascendente; eso es porque quiero asegurarme de que los ingresos se suman desde la fecha más antigua a la más reciente. Luego viene la cláusula RANGE. Limita la ventana a las fechas anteriores a la fecha actual (BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) y a la fecha actual. Esas son las filas que se incluirán en el cálculo del total acumulado.

Esto es lo que se obtiene al ejecutar el código:

Observe que cuando hay varios valores para una fecha (2021-05-02) el código incluirá ambas filas en el cálculo del total acumulado para esa fecha. Por eso hay 38.816,60 en la columna running_total para esa fecha.

Ejemplo 2 - Calcular la media móvil

Veamos ahora cómo utilizar la cláusula RANGE con tipos de datos de fecha/hora - ¡y sin la columna id para ayudar! Utilizaremos una tabla ligeramente modificada, que ahora se llama revenue_per_shop. Contiene los datos de ingresos de dos tiendas. Las columnas son:

• date - La fecha de los ingresos.
• shop - El nombre de la tienda.
• revenue_amount - El importe de los ingresos de esa tienda en esa fecha.

En primer lugar, asegurémonos de que entiendes lo que es una media móvil. Una media móvil de dos días incluye el día actual y el día anterior. Aquí hay algunos datos de ejemplo para mostrarte cómo funciona una media móvil:

La media móvil de dos días para el 2021-05-01 es el propio ingreso diario: 12,573.25. Esto se debe a que no hay otras filas que incluir en el cálculo. El cálculo del 2021-05-02 incluye dos fechas: (12.573,25 + 11.348,22)/2 = 11.960,74. Las demás filas siguen la misma lógica de dos fechas: la fecha actual y la fecha anterior.

Entonces, ¿cómo se calcula la misma métrica para cada tienda por separado? Así:

SELECT
  shop,
  date,
  revenue_amount,
  AVG(revenue_amount) OVER (
    PARTITION BY shop
    ORDER BY date ASC
    RANGE BETWEEN INTERVAL '1' DAY PRECEDING AND CURRENT ROW
  ) AS moving_avg
FROM revenue_per_shop;

El código selecciona primero algunas columnas de la tabla. Luego viene la parte divertida. Estoy usando la función AVG() en la columna revenue_amount porque quiero el promedio de ingresos. De nuevo, esta es una función de ventana, por lo que debe tener la cláusula OVER(). Utilizo la cláusula PARTITION BY para especificar la columna sobre la que quiero agregar los datos; es la columna tienda porque quiero la media móvil de cada tienda por separado. La operación se vuelve a ordenar por fecha. En la cláusula RANGE, me limito a especificar qué filas incluir en el cálculo. Como estoy trabajando con fechas, obtendré la fecha anterior indicando: BETWEEN INTERVAL '1' DAY PRECEDING.

El trabajo con datos de fecha/hora difiere entre las bases de datos. Es posible que en algunas bases de datos tenga que escribirlo así: RANGE BETWEEN 1 DAY PRECEDING AND CURRENT ROW. No obstante, el resultado debería ser el mismo:

Media móvil para bases de datos que no admiten el uso de RANGE con tipos de datos de fecha/hora

¿Qué debe hacer si su base de datos no admite el uso de RANGE con tipos de datos de fecha/hora? Hay una forma de "engañar" a su base de datos; de hecho, probablemente haya varias formas. Aquí hay una forma que calcula la diferencia entre cada fecha y 2021-05-01 (es decir, la primera fecha en los datos). Obtendrá la diferencia como un entero, que podrá utilizar en lugar de la fecha. Este es el código:

SELECT
  shop,
  date,
  revenue_amount,
  date - '2021_05_01' AS day_difference,
  AVG(revenue_amount) OVER (
    PARTITION BY shop
    ORDER BY (date - '2021_05_01')
    RANGE BETWEEN 1 PRECEDING AND CURRENT ROW
  ) AS moving_avg
FROM revenue_per_shop;

He incluido intencionadamente la columna day_difference en el resultado para que entiendas la lógica. He utilizado la misma diferencia en la cláusula ORDER BY para poder utilizar un entero con la cláusula RANGE. Y la media móvil es la misma que en el ejemplo anterior; compruébalo tú mismo. (Sólo muestro las cinco primeras filas para ahorrar espacio).

Ejemplo 3 - Encontrar el último valor dentro de un rango

Este uso de la cláusula RANGE permite encontrar el último valor dentro de un rango definido. Por ejemplo, utilizando la tabla revenue_by_shoppuedo obtener el último valor de cada tienda por separado. El último valor, en este caso, significa el último dato disponible, que es el ingreso para el 2021-05-15. ¿Cómo se obtienen esos datos?

Utilizando la cláusula RANGE, por supuesto:

SELECT
  shop,
  date,
  revenue_amount,
  LAST_VALUE(revenue_amount) OVER (
    PARTITION BY shop
    ORDER BY date
    RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS last_value
FROM revenue_per_shop;

La función de ventana que he utilizado esta vez es LAST_VALUE(). Una vez más, la utilizo en la columna revenue_amount. He particionado los datos por tienda, igual que antes. Y los he ordenado por fecha, de nuevo igual que antes. Para obtener el último valor, he utilizado RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING. Recuerda que el rango por defecto con la cláusula ORDER BY es RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. Si no lo cambias, obtendrás un resultado erróneo. El resultado correcto es:

Ejemplo 4 - Encontrar el número de elementos dentro de un rango

Aquí hay otra forma divertida y útil de usar la cláusula RANGE. ¿Cómo encontrar el número de veces que los ingresos diarios de cualquier tienda están entre 1.000 (dólares, euros...) por debajo y por encima de su valor actual?

Este código podría ayudar:

SELECT
  shop,
  date,
  revenue_amount,
  COUNT(*) OVER (
    ORDER BY revenue_amount ASC
    RANGE BETWEEN 1000 PRECEDING AND 1000 FOLLOWING
  ) AS number_of_days
FROM revenue_per_shop;

Estoy utilizando la función de ventana COUNT(). Como no me interesa separar los ingresos por tiendas, no hay PARTITION BY. El recuento se realizará en orden ascendente según el importe de los ingresos. El rango está definido por RANGE BETWEEN 1000 PRECEDING AND 1000 FOLLOWING.

Esto es lo que devolverá el código:

Permítanme explicarles lo que les dice este resultado. Si tomas la primera fila, el resultado en la columna number_of_days es 2. Hay dos casos en los que los ingresos están entre 3.987,56 y 5.987,56. ¿Por qué este rango? Los ingresos para 2021-05-13 son 4.987,56. Así que 4.987,56 - 1.000 = 3.987,56 y 4.987,56 + 1.000 = 5.987,56. ¿Quieres comprobar el resultado? ¿Qué dos casos están entre este rango? Obviamente, las dos primeras:

¿Quieres comprobar la segunda línea? Dice que hay tres casos entre 4.894,12 y 6.894,12: estos tres:

La misma lógica se aplica al resto de los datos.

Ejemplo 5 - Encontrar el valor máximo

Esto no es tan sencillo como parece; no estoy hablando del valor máximo común. Para encontrarlo, no necesitaría la cláusula RANGE. Pero, ¿qué le parece encontrar el valor máximo (o los ingresos, en este caso) a lo largo de cinco días? Esos cinco días incluirán la fecha actual, hasta tres días antes y un día después de la fecha actual. Probablemente ya conozca la lógica después de todos estos ejemplos de uso de RANGE. Esta es mi solución:

SELECT
  shop,
  date,
  revenue_amount,
  MAX(revenue_amount) OVER (
    ORDER BY DATE
    RANGE BETWEEN INTERVAL '3' DAY PRECEDING AND INTERVAL '1' DAY FOLLOWING
  ) AS max_revenue
FROM revenue_per_shop;

Estoy usando la función MAX() como una función de ventana. De nuevo, la estoy usando con la columna revenue_amount. No hay PARTITION BY en la cláusula OVER() porque no me interesa separar los datos en ningún nivel. Definir el rango no es tan difícil: RANGE BETWEEN INTERVAL '3' DAY PRECEDING AND INTERVAL '1' DAY FOLLOWING. Esto incluirá la fecha actual, tres días antes y un día después. Este es el resultado:

Comprobemos el resultado de 2021-05-05 - marcado en rosa. El rango está marcado en amarillo. Para obtener el ingreso máximo en ese rango, SQL comparará los valores 14,388.14, 18,847.54, 9,845.29, 14,574.56, 11,500.63, 16,897.21, 9,634.56, 14,255.87, 11,248.33, 21,489.22. ¿Cuál es la más alta? Es 21.489,22.

Después de aprender a encontrar el valor máximo utilizando la cláusula RANGE, he llegado al número máximo de ejemplos previstos para este artículo. Si quieres más ejemplos de funciones de ventana, siempre puedes leer este artículo.

RANGE realmente tiene una gran variedad de usos, ¿no es así?

Creo que estos cinco ejemplos le muestran una buena gama de posibilidades de la cláusula RANGE. No es todo lo que puedes hacer con ella. Su uso depende de los datos que tengas y probablemente de un poco de imaginación. No se habla mucho de esta cláusula, lo que es una pena. Aconsejo a todo el mundo que la aprenda. Podría ahorrarte tiempo en la creación de soluciones alternativas. La cláusula RANGE es elegante y realmente no es muy complicada.

¿Dónde se puede aprender sobre RANGE? En nuestro curso Funciones de ventana , por supuesto. No sólo aprenderás la cláusula RANGE, sino todos los aspectos de las funciones de la ventana. También puedes utilizar el curso para practicar tus conocimientos de SQL. Si quieres saber más, en este artículo te contamos todo sobre el curso y lo que ofrece.