Total móvil en SQL: Qué es y Cómo Calcularlo

Calcular un total móvil en SQL es sencillo si se utilizan las funciones de ventana. Estas útiles métricas son similares a los totales acumulados y las medias móviles, y te ayudarán a crear una imagen clara de tus datos. En este artículo, explicaremos y exploraremos los totales móviles.

En SQL, un total móvil es una métrica importante que ayuda a ver cómo cambian las tendencias a lo largo del tiempo, proporcionando una imagen clara de los datos. Calcula una suma sobre una "ventana" o subconjunto de filas que se desplaza por el conjunto de resultados. Por ejemplo, para hacer un seguimiento de las visitas a un sitio web, puedes consultar el total de visitas de cada día y sumar también los 7 días anteriores para detectar tendencias. Si utiliza las funciones de ventana de SQL, podrá ver estas tendencias en cada fila de los resultados de la consulta, sin necesidad de recurrir a herramientas externas.

Para calcular fácilmente totales móviles en SQL, necesitará funciones de ventana. Nuestro curso Funciones de ventana es perfecto si desea profundizar en este tema. Cuando tome este curso, completará 218 ejercicios que cubren la sintaxis y semántica de las funciones ventana. Aprenderá todo el potencial de las funciones de ventana y los casos de uso típicos. Además, adquirirá conocimientos de SQL avanzado que le permitirán realizar análisis más complejos y eficientes.

Un simple total móvil en SQL

Veamos una situación empresarial común en la que el total móvil demuestra su utilidad. Consideremos una tabla denominada daily_sales que registra los totales diarios de ventas de una tienda:

Si observamos estos datos, o incluso si creamos un gráfico a partir de ellos, esto es lo que vemos:

Podemos analizar eventos específicos, como encontrar fechas con ventas inusualmente bajas. Por ejemplo, las ventas del 2024-08-03 son más bajas que las de otros días. Sin embargo, esta forma de visualizar la información no es eficaz para detectar tendencias en la evolución de las ventas. Para eso, tenemos que fijarnos en los totales móviles. Y para ello, necesitaremos funciones de ventana SQL.

Si necesita una guía de referencia rápida sobre las funciones de ventana SQL, tenga a mano esta Funciones de ventana Cheat Sheet. Agilizará tus tareas diarias de análisis de datos.

Cálculo de un total móvil en SQL

Escribamos una consulta SQL que devuelva las dos columnas de daily_sales y añada un total móvil de tres días al conjunto de resultados:

SELECT
    purchase_date,
    total_sales,
    SUM(total_sales) OVER (
        	ORDER BY purchase_date
        	ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
     ) AS rolling_total
FROM daily_sales;

En el ejemplo anterior, la cláusula OVER después de la función SUM() define la ventana (es decir, el grupo de filas) que queremos sumar para cada fila. En este caso, indica a SQL que la suma debe incluir los datos de las dos filas anteriores y la actual. El orden se basa en la fecha de compra. Esta es nuestra ventana móvil: el día actual y los dos días anteriores.

La consulta arroja estos datos:

En la primera fila, observe que el total móvil sólo repite el valor de la columna total_sales: no hay filas anteriores que sumar. En la segunda fila, suma el valor de total_sales de la primera y la segunda filas; a partir de la tercera fila, devuelve la suma de los valores de ventas de las dos filas anteriores y la actual.

Hagamos un gráfico con los datos del total móvil para entender cómo nos ayuda a analizar las tendencias:

Lo primero que salta a la vista cuando miramos este gráfico es que debemos descartar los dos primeros puntos de datos (las dos primeras fechas), porque no representan un total de tres días como el resto de las fechas. A partir de 2024-08-03, la línea se vuelve casi horizontal, lo que indica que los totales de ventas de tres días se mantienen estables. Esto es así hasta la última fecha, que muestra un ligero repunte porque el total ya no se ve afectado por la "caída" de las ventas a partir de 2024-08-03.

Con un conjunto de datos tan pequeño, incluso las pequeñas fluctuaciones afectan a la tendencia general; con un conjunto de datos grande, esto no ocurriría. Sin embargo, podemos concluir que la combinación de funciones agregadas y funciones de ventana "suaviza" el efecto de las fluctuaciones o anomalías en los datos. Esto permite una visualización clara de las tendencias de los datos.

Total móvil, total corrido y media móvil

Además del total móvil, existen otros dos casos de uso común para las funciones de ventana: el total acumulado y la media móvil.

• Un total acumulado es un cálculo acumulativo que recorre un conjunto de resultados. Para cada fila, calcula la suma de todos los valores de una columna, desde la primera fila hasta la fila actual. Es similar al total móvil, pero siempre suma a partir de la primera fila del conjunto de resultados.
• Una media móvil se parece a un total móvil en que toma valores de la ventana definida en relación con la fila actual. La diferencia es que devuelve la media de estos valores en lugar del total.

El cálculo de estas métricas es bastante similar al cálculo de un total móvil. Veamos un par de ejemplos.

Cálculo de un total móvil en SQL

Este es un ejemplo de total acumulado:

SELECT
    purchase_date,
    total_sales,
    SUM(total_sales) OVER (ORDER BY purchase_date) AS running_total
FROM
    daily_sales;

La diferencia entre el total acumulado y el total móvil en SQL es la definición de la ventana en la cláusula OVER. En lugar de definir un subconjunto que abarque un número de filas relativo a la fila actual, sólo tenemos que especificar el criterio de orden; se entiende que el total abarca desde la primera fila hasta la actual. Puede encontrar más información sobre totales corridos en nuestro artículo Qué es un total corrido y cómo se calcula en SQL.

Cálculo de una Media Móvil en SQL

El concepto de media móvil es muy similar al de total móvil. La función agregada AVG() opera sobre una ventana que se desplaza sobre un número definido de filas. De hecho, la consulta para crear una media móvil en SQL es prácticamente la misma que la que utilizamos para el total móvil. Sólo tenemos que cambiar la función SUM() por AVG():

SELECT
    purchase_date,
    total_sales,
    AVG(total_sales) OVER (
        	ORDER BY purchase_date
     ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
        ) AS moving_average
FROM
    daily_sales;

De este modo, obtendremos la media de las ventas en un periodo de tres días: el día actual y los dos días anteriores. Encontrará más información sobre las medias móviles en este artículo.

Otros casos de uso de los totales móviles

Consumo semanal de energía

Existen muchos otros escenarios en los que se puede aplicar un total móvil. La mayoría de los ejemplos se refieren a tablas de orden cronológico, en las que la ventana de datos representa un intervalo de tiempo (por ejemplo, 7 días, 30 días, 3 meses, etc.).

Un ejemplo podría ser analizar la evolución del consumo energético de un hogar a lo largo del tiempo. Esta información podría almacenarse en una tabla (denominada daily_consumption_per_day) que registre el total de kilovatios hora consumidos por el hogar cada día.

Esta es la consulta:

SELECT
    measure_date,
    daily_consumption,
    SUM(daily_consumption) OVER (
        ORDER BY measure_date
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
        ) AS weekly_consumption
FROM
    daily_consumption_per_day;

Para cada fecha, el resultado muestra la suma del consumo de los últimos 7 días (incluido el día actual). Esto nos permite ver la variación de las tendencias en el consumo de energía a lo largo del tiempo.

Las funciones ventana tienen mucho potencial. Aprenda todo lo que puede hacer con ellas en nuestra completa guía sobre las funciones ventana de SQL.

Uso de los totales móviles más allá del análisis de series temporales

La mayoría de los casos de uso de los totales móviles están relacionados con el análisis de series temporales. Sin embargo, estas estadísticas pueden aplicarse a cualquier conjunto de datos ordenados por una dimensión en el que tenga sentido realizar cálculos agregados (por ejemplo, totales, medias) sobre ventanas deslizantes que aíslen subconjuntos de esa dimensión.

Un ejemplo sería un análisis del coste de las prestaciones médicas por edad. Supongamos que tenemos una tabla que registra el coste de las prestaciones médicas recibidas por personas de distintas edades. Si ordenamos las filas por edad, un total móvil nos permitirá ver cómo varían los costes médicos por rango de edad.

Supongamos que la tabla se llama medical_costs_by_age y que tiene dos columnas: edad y total_medical_cost. Para analizar esta información, escribiremos una consulta con un total móvil que abarque una ventana de cinco años centrada en la edad de la fila actual (de dos años menos a dos años más):

SELECT
    age,
    total_medical_cost,
    SUM(total_medical_cost) OVER (
        ORDER BY age
        ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING
        ) AS weighted_medial_cost
FROM
    medical_costs_by_age;

A partir de este ejemplo, podemos obtener una comprensión clara y bastante granular de la relación entre los grupos de edad y los costes sanitarios.

¿Desea obtener más información sobre los totales móviles en SQL?

A lo largo de este artículo, hemos visto varias formas de utilizar un total móvil en SQL. Lo hemos comparado con métricas similares, sobre todo con el total acumulado y la media móvil.

Si no conocías las funciones ventana de SQL antes de leer este artículo, probablemente estés impresionado con lo que pueden hacer. Si quieres aprender sobre las funciones ventana desde cero, te recomiendo nuestro Funciones de ventana curso. Si ya estás familiarizado con ellas, toma el Funciones de ventana Practice Set. Tiene 100 ejercicios interactivos de funciones de ventana que utilizan tres bases de datos reales diferentes. Y nuestra plataforma de aprendizaje te proporciona todas las herramientas necesarias: no necesitas instalar nada. Sólo tienes que utilizar tu navegador y una conexión a Internet, ¡y listo!

Por último, puedes mantenerte al tanto de los totales móviles en SQL con este conjunto de ejercicios prácticos gratuitos de funciones de ventana. Y, si aspiras a un trabajo que requiera dominio de SQL y quieres prepararte para la entrevista de trabajo, no olvides leer sobre estas preguntas de entrevista sobre la función ventana en SQL. Gracias por leer este artículo, ¡y feliz aprendizaje!