5 Ejemplos prácticos del uso de ROWS BETWEEN en SQL

Las funciones de ventana de SQL son tremendamente útiles para calcular agregaciones complejas como promedios móviles o totales corridos. La cláusula ROWS le permite especificar filas para sus cálculos, lo que permite marcos de ventana aún más sofisticados. A continuación se presentan cinco ejemplos prácticos de cómo aprovechar la cláusula ROWS BETWEEN en SQL.

Las funciones de ventana (también llamadas funciones OVER) calculan su resultado basándose en un marco de ventana deslizante (es decir, un conjunto de filas). Son similares a las funciones de agregación en el sentido de que se puede calcular el valor medio, total o mínimo/máximo de un grupo de filas. Sin embargo, hay algunas diferencias importantes:

• Las funciones de ventana no contraen las filas como lo hacen las funciones de agregación. Por lo tanto, todavía puede mezclar los atributos de una fila individual con los resultados de una función de ventana.
• Las funciones de ventanapermiten marcos de ventana deslizantes, lo que significa que el conjunto de filas utilizado para el cálculo de una función de ventana puede ser diferente para cada fila individual.

La sintaxis de una función ventana se muestra en texto azul a continuación:

SELECT , ,
  OVER (
 	PARTITION BY <...>
   	ORDER BY <...>
    	) 
FROM ;

Cuando se utiliza una función ventana en la sentencia SELECT, básicamente se calcula otra columna con esta función:

• Se empieza especificando una función (por ejemplo, AVG(), SUM(), o COUNT()).
• A continuación, se utiliza la palabra clave OVER para definir un conjunto de filas. Opcionalmente, puede:
  • Agrupe las filas con PARTITION BY para que las funciones se calculen dentro de estos grupos en lugar de todo el conjunto de filas.
  • Ordene las filas dentro de un marco de ventana utilizando ORDER BY si el orden de las filas es importante (por ejemplo, cuando se calculan los totales de ejecución).
  • Especificar la relación del marco de la ventana con la fila actual (por ejemplo, el marco debe ser la fila actual y dos anteriores, o la fila actual y todas las siguientes, etc.).

Un marco de ventana se define mediante las cláusulas ROWS, RANGE, y GROUPS. En este artículo, nos centraremos en la cláusula ROWS y sus opciones. Para aprender más sobre las funciones de ventana y la definición de marcos de ventana, revisa este artículo con ejemplos de funciones de ventana, esta guía explicativa y, por supuesto, nuestra hoja de trucos de dos páginas de SQL Funciones de ventana .

Cláusula ROWS: Sintaxis y Opciones

El propósito de la cláusula ROWS es especificar el marco de la ventana en relación con la fila actual. La sintaxis es:

ROWS BETWEEN lower_bound AND upper_bound

Los límites pueden ser cualquiera de estas cinco opciones:

• UNBOUNDED PRECEDING - Todas las filas antes de la fila actual.
• n PRECEDING - n filas antes de la fila actual.
• CURRENT ROW - Sólo la fila actual.
• n FOLLOWING - n filas después de la fila actual.
• UNBOUNDED FOLLOWING - Todas las filas después de la fila actual.

Fuente: SQL Funciones de ventana Hoja de trucos

Aquí hay un par de cosas a tener en cuenta cuando se definen marcos de ventana con la cláusula ROWS:

• El marco de la ventana se evalúa por separado dentro de cada partición.
• La opción por defecto depende de si se utiliza ORDER BY:
  • Con ORDER BY, el marco por defecto es RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.
  • Sin ORDER BY, el marco por defecto es ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.
• Si uno de sus límites es una fila actual, puede omitir la especificación de este límite y utilizar una versión más corta de la definición del marco de la ventana:
  • UNBOUNDED PRECEDING es el mismo que BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.
  • n PRECEDING es el mismo que BETWEEN n PRECEDING AND CURRENT ROW.
  • n FOLLOWING es el mismo que BETWEEN CURRENT ROW AND n FOLLOWING.
  • UNBOUNDED FOLLOWING es el mismo que BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING.

Pasemos a los ejemplos para ver cómo funciona esto en la práctica.

5 Ejemplos prácticos de utilización de las filas en Funciones de ventana

Ejemplo 1

Para empezar con la cláusula ROWS, utilizaremos la siguiente tabla con datos de ventas de una librería.

En nuestro primer ejemplo, queremos añadir otra columna que muestre el total de ingresos desde la primera fecha hasta la fecha de la fila actual (es decir, el total acumulado). Esta es la consulta que podemos utilizar:

SELECT date, revenue,
	SUM(revenue) OVER (
      ORDER BY date
      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) running_total
FROM sales
ORDER BY date;

Para calcular el total acumulado utilizando una función de ventana, seguimos los siguientes pasos:

• Calcular el total de ingresos utilizando la función de agregación SUM().
• Ordenar los registros en el marco de la ventana por fecha (el valor predeterminado es en orden ascendente), ya que el orden de las filas es importante cuando se calcula un total acumulado.
• Especificar el marco de la ventana definiendo el límite inferior como UNBOUNDED PRECEDING y el límite superior como CURRENT ROW. Esto incluirá todas las filas hasta la actual, inclusive. Tenga en cuenta que el comportamiento por defecto sin la cláusula ROWS especificada sería el mismo en este caso. El marco por defecto utiliza RANGE, no ROWS. Como cada día aparece sólo una vez en la tabla, el resultado será el mismo para RANGE y ROWS. Por lo tanto, también podríamos utilizar la siguiente consulta para obtener los mismos resultados:

SELECT date, revenue,
	SUM(revenue) OVER (
      ORDER BY date) running_sum
FROM sales
ORDER BY date;

Como ves, la consulta ha funcionado como estaba previsto y hemos obtenido el total de la tercera columna. En el primer día, es igual a las ventas de este día - 1515,45 dólares; en el segundo día, es igual a la suma de las ventas del primer y segundo día - 3860,80 dólares; en la siguiente fila, obtenemos la suma de las ventas de los tres primeros días - 4764,79 dólares, etc.

En nuestros próximos ejemplos, veremos cómo funciona la cláusula ROWS cuando los registros se dividen en varios grupos.

Para practicar la definición de marcos de ventana, consulte este curso interactivo Funciones de ventana con más de 200 desafíos de codificación.

Ejemplo 2

Para los próximos ejemplos, utilizaremos la siguiente tabla. Contiene datos ficticios sobre la temperatura media (en °C) y la precipitación total (en mm) en dos ciudades italianas (Roma y Florencia) durante cinco días consecutivos.

Queremos calcular la temperatura media móvil de tres días por separado para cada ciudad. Para separar los cálculos de las dos ciudades, incluiremos la cláusula PARTITION BY. Luego, al especificar el marco de la ventana, consideraremos el día actual y los dos días anteriores:

Observe también que hemos puesto nuestra función de ventana dentro de la función ROUND() para que la media móvil de tres días se redondee a un decimal. Este es el resultado:

La media móvil se ha calculado por separado para Florencia y Roma. Para el 1 de septiembre, la media móvil es igual a la temperatura media diaria, ya que no tenemos ningún registro anterior. Luego, el 2 de septiembre, la media móvil se calcula como la temperatura media de los días 1 y 2 (17,5 °C en Florencia y 18,8 °C en Roma, respectivamente). El 3 de septiembre, por fin tenemos datos suficientes para calcular la temperatura media de tres días (los dos anteriores y el actual), que resulta ser de 18,0 °C en Florencia y 19,2 °C en Roma. A continuación, la media móvil de tres días para el 4 de septiembre se calcula como la temperatura media de los días 2, 3 y 4, y así sucesivamente.

Otra cosa que hay que tener en cuenta: el orden de los registros en el marco de la ventana tiene un papel fundamental a la hora de especificar qué filas hay que tener en cuenta.

En la consulta anterior, hemos ordenado los registros del marco de la ventana por fecha en orden ascendente (utilizando la configuración por defecto), es decir, empezamos por la fecha más antigua. Luego, para incluir dos días antes del día actual en nuestros cálculos, hemos establecido el límite inferior como 2 PRECEDING.

Sin embargo, podríamos obtener exactamente el mismo marco de ventana ordenando los registros en orden descendente y cambiando la opción ROWS para incluir 2 FOLLOWING en lugar de 2 PRECEDING:

SELECT city, date, temperature,
	ROUND(AVG(temperature) OVER (
      PARTITION BY city
      ORDER BY date DESC
      ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING), 1) mov_avg_3d_city
FROM weather
ORDER BY city, date;

Esta consulta produce exactamente el mismo resultado.

Ejemplo 3

En este ejemplo, calcularemos la precipitación total de los últimos tres días (es decir, un total de tres días) por separado para dos ciudades.

SELECT city, date, precipitation,
	SUM(precipitation) OVER (
      PARTITION BY city
      ORDER BY date
      ROWS 2 PRECEDING) running_total_3d_city
FROM weather
ORDER BY city, date;

En esta consulta, volvemos a dividir los datos por ciudades. Utilizamos la función SUM() para calcular el nivel total de precipitaciones de los últimos tres días, incluyendo el día actual. Además, observe que utilizamos una abreviatura al definir el marco de la ventana especificando sólo el límite inferior: 2 PRECEDING.

He aquí el resultado de la consulta anterior:

A partir del 3 de septiembre, obtenemos un total de precipitaciones de tres días consecutivos en Florencia: 30 mm. Es la suma de los 5 mm de precipitación del 1 de septiembre, los 15 mm del 2 y los 10 mm del 3.

¿Sabe cómo hemos obtenido el total de 12 mm en Roma el 5 de septiembre? Intente seguir los resultados en nuestra tabla de salida para asegurarse de que entiende cómo funcionan las funciones de ventana con marcos de ventana específicos.

Ahora pasemos a nuevos datos y ejemplos.

Ejemplo 4

Para los dos siguientes ejemplos, utilizaremos los datos que se muestran a continuación. Incluye información diaria sobre el número de nuevos suscriptores en tres redes sociales: Instagram, Facebook y LinkedIn.

Comencemos por calcular los totales de la cantidad de nuevos suscriptores por separado para cada red. Básicamente, para cada día, queremos ver cuántas personas se han suscrito desde que empezamos a recopilar datos hasta la fecha de la fila actual.

He aquí una consulta SQL que responde a esta petición:

SELECT social_network, date, new_subscribers,
	SUM(new_subscribers) OVER (
      PARTITION BY social_network
      ORDER BY date
      ROWS UNBOUNDED PRECEDING) running_total_network
FROM subscribers
ORDER BY social_network, date;

Comenzamos calculando el número total de nuevos suscriptores utilizando la función agregada SUM(). A continuación, utilizamos la cláusula PARTITION BY para realizar cálculos por separado para cada red. También ordenamos los registros por fecha en orden ascendente (por defecto). Por último, definimos el marco de la ventana como UNBOUNDED PRECEDING para incluir todos los registros hasta el actual inclusive.

El resultado es el siguiente:

En la tabla de resultados se puede ver cómo el número de nuevos suscriptores se añade al total acumulado para cada nuevo registro. El total acumulado se calcula por separado para cada red, como se especifica en la función de ventana.

Ejemplo 5

En nuestro último ejemplo, quiero demostrar cómo podemos mostrar el primer y el último valor de un conjunto específico de registros utilizando funciones de ventana y la cláusula ROWS. Esta vez, vamos a añadir dos columnas a la salida:

• El número de nuevos suscriptores añadidos el primer día, y
• El número de nuevos suscriptores añadidos el último día.

Con esta información calculada por separado para cada red social, podemos ver cómo se compara el rendimiento de cada día con el punto de partida y el punto actual.

Aquí está la consulta SQL para obtener la salida requerida:

SELECT social_network, date, new_subscribers,
    FIRST_VALUE(new_subscribers) OVER(
      PARTITION BY social_network
      ORDER BY date) AS first_day,
    LAST_VALUE(new_subscribers) OVER(
      PARTITION BY social_network
      ORDER BY date
      ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_day
FROM subscribers
ORDER BY social_network, date;

Como ves, estamos utilizando las funciones FIRST_VALUE() y LAST_VALUE() para obtener la información de los primeros y últimos días, respectivamente. Observe también cómo especificamos el marco de la ventana para cada una de las funciones:

• No incluimos la cláusula ROWS con la función FIRST_VALUE() porque el comportamiento por defecto (es decir, RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) está bien para nuestros propósitos.
• Sin embargo, especificamos el marco de la ventana con la función LAST_VALUE() porque la opción por defecto utilizaría el valor de la fila actual como último valor de cada registro; esto no es lo que buscamos en este ejemplo. Especificamos el marco de la ventana como ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING para asegurarnos de que se tienen en cuenta todos los registros.

Y aquí está el conjunto de resultados:

Tal y como se solicitó, tenemos el número de nuevos suscriptores del primer y del último día calculados por separado para cada red social.

Practicar el uso de las filas en SQL Funciones de ventana

Después de ver los ejemplos anteriores, esperamos que tenga la motivación para aprender las funciones de ventana de SQL y las opciones de ROWS más a fondo. Este conjunto de herramientas le permite especificar un marco de ventana deslizante y permite el cálculo de agregaciones complejas como promedios móviles y totales corridos.

Si quieres sentirte realmente cómodo con las funciones de ventana, te recomiendo el curso interactivo de LearnSQL.es Funciones de ventana interactivo de . Te muestra cómo calcular totales y promedios en funcionamiento, construir diferentes tipos de clasificaciones, investigar tendencias a través del tiempo y mucho más. Y lo que es mejor, harás los ejercicios tú mismo, que es la mejor manera de aprender.

Si quiere dominar realmente el uso de SQL para el análisis de datos, nuestra SQL Avanzado ruta de aprendizaje también incluye Modificadores de GROUP BY en SQL y expresiones comunes de tabla (CTE). Es una forma estupenda de ampliar tus conocimientos sobre las funciones de ventana.

¿Quieres empezar leyendo un poco? Aquí están los 8 mejores artículos que cubren las funciones de ventana de SQL.

Gracias por leer, y feliz aprendizaje.