Qué es una media móvil y cómo calcularla en SQL

¿Quieres profundizar en los datos de las series temporales y examinar las tendencias a largo plazo? ¿Quieres saber qué es una media móvil y cómo calcularla en SQL? Entonces este artículo es para ti. Te explicaré una potente característica de SQL llamada funciones de ventana y te mostraré cómo puedes calcular medias móviles con ellas.

La mejor manera de aprender sobre las funciones de ventana de SQL es el curso interactivo Funciones de ventana curso en LearnSQL.es. Contiene más de 200 ejercicios prácticos que le ayudarán a ganar confianza en sus conocimientos de SQL. Y este curso es sólo uno de los más de 30 cursos interac tivos de SQL con distintos niveles de dificultad que ofrecemos. Inscríbase ahora de forma gratuita.

¿Qué es una media móvil?

La media móvil es una técnica de series temporales para analizar y determinar las tendencias de los datos. A veces se denominan medias móviles, promedios móviles o promedios corridos, y se calculan como la media del valor actual y de un número específico de valores inmediatamente anteriores para cada punto en el tiempo. La idea principal es examinar cómo se comportan estas medias a lo largo del tiempo en lugar de examinar el comportamiento de los puntos de datos originales o brutos.

Trabajar con medias móviles nos proporciona una mejor representación de la serie temporal, ya que las tendencias a largo plazo son mucho más fáciles de ver con las medias móviles que con los puntos de datos brutos. Dado que las medias móviles se utilizan a menudo en el análisis financiero, utilizaré los datos de las cotizaciones diarias de una empresa concreta como ejemplo para explicar en qué consisten.

A continuación se muestra la tabla denominada stock_price que utilizaremos en este artículo:

En esta tabla, tenemos dos columnas (date y price). Se trata de un conjunto de datos de series temporales, ya que cada precio de las acciones está asociado a un punto concreto en el tiempo; es decir, en esta tabla se almacena el precio de cada día.

El gráfico de líneas de estos precios a lo largo del tiempo tiene este aspecto:

De la tabla y el gráfico podemos deducir algunas cosas antes de hacer ningún cálculo:

• Los precios de nuestra tabla stock_price son para el período comprendido entre el 7 de enero de 2020 y el 30 de junio de 2020, ambos inclusive.
• Los precios oscilan aproximadamente entre 1.000 y 1.400 (véase el eje Y).
• Hay fluctuaciones en los precios; hay algunos picos en febrero, varios picos en junio, etc.

Calculemos una media móvil para la columna price y generemos un gráfico de líneas de las medias para ver qué ocurre. Para este ejemplo, trabajaremos con una media móvil de tres días. Para ello, calculamos la media de las cotizaciones de tres días consecutivos -el día en cuestión y los dos anteriores- y repetimos lo mismo para cada día del conjunto de datos. Se trata de una media móvil de tres días, porque hacemos la media durante un periodo de tres días.

Así es como se calcula una media móvil de tres días para el 9 de enero de 2020:

Para el 9 de enero de 2020, la media móvil de tres días se calcula como la media de los precios de ese día (1.300) y de los dos días anteriores: 8 de enero (1.300) y 7 de enero (1.320). Por lo tanto, la media móvil del 9 de enero de 2020 es la media de estos tres valores, es decir, 1.306,66 como se muestra en la imagen anterior.

La media móvil se calcula del mismo modo para cada una de las fechas restantes, sumando los tres precios de las acciones de la fecha en cuestión y de los dos días anteriores y dividiendo ese total por 3. Para el 30 de junio, la media móvil de tres días es 1.070, la media de los precios de las fechas 30 de junio (1.067), 29 de junio (1.067) y 28 de junio (1.076).

Si representamos los puntos de datos originales y la media móvil en un gráfico de líneas, obtenemos lo siguiente:

La línea roja representa la media móvil, y la línea azul representa los puntos de datos originales. Puede observar que la línea roja es más suave y no tiene los picos que se ven en la línea azul. Este alisamiento es el principal objetivo de la técnica de la media móvil... se utiliza para eliminar el ruido de los datos. Con menos ruido, las tendencias reales de los datos de las series temporales son más fáciles de ver.

En este ejemplo hemos calculado la media móvil de tres días. Sin embargo, podemos calcularla en cualquier periodo que queramos, como la media móvil de siete días, la media móvil de diez días, etc.

Por qué y dónde se utilizan las medias móviles

Las medias móviles se utilizan ampliamente en el comercio financiero y técnico, como en el análisis del precio de las acciones, para examinar las tendencias a corto y largo plazo. Si el precio de las acciones se mantiene por encima de la media móvil, entonces tenemos una tendencia alcista; si se mantiene por debajo, los operadores dicen que estamos en una tendencia bajista. Señales como las tendencias alcistas y bajistas informan a los operadores a la hora de tomar decisiones sobre la compra o venta de acciones.

Dicho esto, el análisis del precio de las acciones no es el único caso de uso de la media móvil. Otras aplicaciones comerciales son:

• El análisis de las ventas: Las medias móviles suavizan las fluctuaciones y los picos de las ventas semanales o diarias.
• Análisis de casos confirmados de COVID-19: Las medias móviles ayudan a mostrar la evolución del número de casos confirmados a lo largo del tiempo.
• Análisis del tráfico web: Las medias móviles nos ayudan a ver las tendencias a largo plazo del número de visitas y páginas vistas.
• Finanzas personales: Las medias móviles ayudan a mostrar las tendencias de nuestros gastos (por ejemplo, mirando las medias de diez días de los últimos dos años).

Medias móviles en SQL

Ahora que ya sabes lo que es una media móvil, vamos a ver cómo calcularla. En SQL, es fácil con las funciones de ventana, una característica especial de SQL que le permite hacer agregaciones a través de las filas.

Aunque son similares a GROUP BY, las funciones ventana mantienen todas las filas al mostrar el resultado; no hay colapso de filas. En cambio, con las funciones de ventana, definimos un marco o una "ventana" de filas de un tamaño determinado alrededor de la fila actual y luego realizamos algún cálculo a través de esa ventana. Así, se realiza una agregación para cada fila de una tabla; cada fila tiene su propia ventana sobre la que se realiza un cálculo.

A continuación se muestra cómo se calcula en SQL la columna moving_average de nuestro ejemplo:

select *,
  avg(Price) OVER(ORDER BY Date 
     ROWS BETWEEN 2 PRECEDING AND CURRENT ROW ) 
     as moving_average 
from stock_price;

Para explicar el código en detalle:

• Utilizamos una función de ventana, denotada con una cláusula OVER. Como se ha explicado anteriormente, las filas no están contraídas, y cada fila tiene su propia ventana sobre la que se realiza un cálculo.
• El tamaño de la ventana en nuestro ejemplo es de tres. Para cada fila determinada, tomamos la propia fila y las dos filas anteriores, y calculamos el precio medio de esas tres filas. Esto se indica con la palabra clave ROW en la sentencia: ROWS BETWEEN 2 PRECEDING AND CURRENT ROW. Esta afirmación dice que, para cada fila de la tabla, se calcula algo como una agregación de la fila actual y las dos filas anteriores. Esto significa que la media móvil de cada fila se calcula como el precio medio del día en cuestión y de los dos días anteriores.
• Tenemos un marco de ventana diferente para cada día. A continuación, puede ver una ilustración del marco de ventana utilizado para la fila correspondiente al 9 de enero (en verde) y el marco de ventana utilizado para la fila correspondiente al 27 de junio (en azul):
• Es importante que los datos no tengan huecos en las fechas. Para cada día, tenemos que calcular la media de los precios de ese día y de los dos días anteriores. Si faltan fechas en los datos, este análisis no tendrá sentido.
• La palabra clave ORDER BY dentro de la cláusula OVER define el orden de las filas sobre las que se debe calcular la media móvil. En nuestro ejemplo, las filas se ordenan primero por la columna de la fecha, luego se define el marco de la ventana y se realiza el cálculo.
• Para este ejemplo, no utilizamos la palabra clave PARTITION BY en la cláusula OVER. PARTITION BY agrupa las filas en trozos lógicos por alguna categoría, pero aquí no estamos agrupando las filas de esa manera. En efecto, todo nuestro conjunto de datos es sólo una gran partición. Más adelante en este artículo, veremos un ejemplo con PARTITION BY.

Así pues, ¡ahora ya sabes cómo calcular medias móviles en SQL! El código SQL anterior puede utilizarse en muchos otros escenarios empresariales; sólo hay que sustituir la tabla y los nombres de las columnas y ajustar el número de filas para las que se quieren calcular las medias. Todo lo demás puede permanecer igual.

Las funciones de ventana tienen una sintaxis específica, y se necesita algo de tiempo y práctica para familiarizarse con su uso. Para aprender y practicar más, recomiendo el curso de funciones de ventana en LearnSQL.es. Es interactivo y con muchos ejercicios, lo que te da la oportunidad de practicar y aprender los nuevos materiales rápidamente, ¡porque se aprende haciendo! Para más información sobre el contenido del curso y las funciones de ventana en sí, puedes leer el artículo Curso SQL del mes: Funciones de ventana. En la misma plataforma, también puedes encontrar excelentes artículos sobre cuándo utilizar las funciones de ventana y explorar algunos ejemplos.

Media móvil sobre un número específico de días

En el ejemplo anterior, hemos calculado una media móvil de tres días. También puede calcular otras medias, tomando cualquier número de valores anteriores que desee. Cuanto mayor sea el número de valores anteriores, más suave será nuestra curva. Cuanto menor sea el número de filas utilizadas para calcular las medias, más se acercará la gráfica de la media móvil a la de los valores originales. Hay una gran diferencia entre, por ejemplo, las medias móviles de dos días y las de 30 días.

Antes de crear un gráfico para demostrarlo, vamos a calcular estas dos medias móviles:

select *,
  avg(Price) OVER(ORDER BY Date 
      ROWS BETWEEN 1 PRECEDING AND CURRENT ROW ) 
     as 2day_moving_average,
  avg(Price) OVER(ORDER BY Date 
      ROWS BETWEEN 29 PRECEDING AND CURRENT ROW ) 
      as 30day_moving_average
from stock_price;

Y ahora podemos visualizar en qué se diferencian las dos líneas:

La línea verde representa la media móvil de 30 días (se utilizan 30 cotizaciones para calcular cada media), y la línea roja representa la media de dos días. La línea roja se parece a la línea azul de los puntos de datos originales. La línea verde es más suave y permite ver más fácilmente las tendencias a largo plazo.

Ahora te estarás preguntando, ¿cómo sabes el número correcto de filas que debes tomar? Bueno, no puedo ayudarte en eso... realmente depende de las necesidades y la situación del propio negocio y de por qué estás analizando la métrica en primer lugar.

Ejemplo: Media móvil de siete días de los casos de COVID

Ahora vamos a practicar un poco más para recapitular lo que hemos aprendido hasta ahora. El COVID-19 sigue siendo muy real en nuestras vidas, así que calcularemos la media móvil de siete días para el número total de casos confirmados por país. El número de casos confirmados de cada día para cada país se almacena en una tabla llamada confirmed_coviden la columna confirmed_day:

Para calcular la media móvil de siete días del número de casos confirmados, podemos hacer lo siguiente:

SELECT *,
      avg(confirmed_day) OVER(
          PARTITION BY country 
          ORDER BY date 
          ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) 
          AS 7day_moving_average
FROM confirmed_covid;

Puede que haya notado que este código se parece al que escribimos para la media móvil del precio de las acciones. La única diferencia clave es que aquí tenemos particiones... cada país es una partición, porque estamos calculando las medias de siete días por separado para cada país. Por lo demás, sólo la tabla y los nombres de las columnas son diferentes. Puede adaptar fácilmente este código a cualquier otro escenario empresarial.

Una vez que ejecutamos este código y calculamos la media móvil, podemos generar el gráfico con la línea que muestra la tendencia para el país específico, desde el inicio de la pandemia hasta el mes de julio. A continuación se muestra el gráfico para el país de Croacia:

De nuevo, vemos cómo la media móvil suaviza y reduce los picos y las fluctuaciones en comparación con los puntos de datos originales.

Puedes utilizar otras funciones de ventana para obtener una visión más potente de los datos de COVID-19. Si le interesa saber más, consulte nuestro reciente artículo sobre cómo analizar los datos de COVID-19 utilizando funciones de ventana.

Promedios móviles y Funciones de ventana en SQL

En este artículo, vimos cómo se pueden calcular medias móviles en SQL utilizando funciones de ventana. Estos promedios nos ayudan a ver mejor las tendencias reales al reducir la cantidad de ruido.

Si quieres practicar y aprender más sobre las funciones ventana, te recomiendo el curso interactivo en línea sobre funciones ventana publicado por LearnSQL.es. Este curso contiene más de 200 ejercicios, por lo que seguro que pondrás en práctica la habilidad recién aprendida. Y créeme, cuando se trata de SQL y de habilidades de codificación, es importante practicar: ¡se aprende haciendo!