Cómo analizar una serie temporal en SQL

Los valores ordenados por tiempo se denominan series temporales. En este artículo, te mostraré los patrones más frecuentes y te enseñaré a escribir consultas para series temporales en SQL con la ayuda de las funciones de ventana.

Quizá hayas tenido la oportunidad de analizar algunas variables en las que cada valor estaba asociado a un valor temporal. Este tipo de datos, en los que los valores están ordenados por tiempo, se denominan datos de series temporales. Dado que se utilizan con tanta frecuencia en los negocios, es importante aprender los patrones comunes utilizados para analizar este tipo de datos.

Ejemplos de datos de series temporales

Los datos de series temporales son variables con un componente adicional: el tiempo. Esto significa que cada valor de un atributo está asociado a un valor de fecha o de tiempo. Estos son algunos ejemplos de datos de series temporales:

• El precio diario de las acciones de una determinada empresa en el último año. (Cada precio de las acciones está asociado a un día concreto).
• La media diaria del índice Dow Jones de los últimos 10 años. (Cada valor del índice se asocia a un día concreto).
• Visitas únicas a un sitio web durante un mes. (Cada mes, el número de visitas es diferente).
• Los usuarios registrados de un sitio web para cada día.
• Cifras de ventas semanales.
• Ingresos y gastos anuales de la empresa a lo largo de una década. (El año es el valor temporal).
• Accesos diarios a una aplicación durante dos meses. (El día es el valor temporal).

En este artículo, analizaremos la popularidad de dos sitios web ficticios mediante una medida denominada "número total de visitas diarias". Las consultas SQL que discutiremos pueden utilizarse para otros análisis de series temporales; es decir, son aplicables a otros conjuntos de datos de series temporales.

Observaremos el periodo comprendido entre el 01 de julio de 2019 y el 31 de diciembre de 2019.

Aquí están los datos:

tabladaily_visits

Esta tabla se llama daily_visits y contiene los siguientes atributos:

• fecha - Cualquier día entre el 01 de julio y el 31 de diciembre de 2019.
• visitas - El número total de visitas al sitio web en una fecha específica.
• fin de semana - Este valor es 'N' si la fecha es un día de la semana y 'Y' si es un sábado o un domingo.
• sitio web - El nombre de dominio del sitio web ('www.sqlanalysts.com' o 'www.sqldevelopers.com').

Como puede observar, cada fila de nuestra tabla daily_visits tabla (es decir, cada valor del atributo visitas ) está asociado a un día (el atributo fecha ). Este es un ejemplo de datos de series temporales.

Ejecución de los totales con SQL Funciones de ventana

Comenzaremos nuestro análisis utilizando un patrón llamado total acumulado. Un total acumulado es la suma de todos los números anteriores de una columna.

A continuación se muestra un total acumulado para el número de visitas desde el 1 de julio hasta un día concreto. Tenga en cuenta que se calcula para cada sitio:

En la imagen de arriba, puede ver cómo el 01 de julio el total acumulado es 2.805. (Es igual al número de visitas de ese día.) Esto se debe a que no hay datos para las fechas anteriores al 01 de julio; estamos empezando el cálculo desde esta fecha.

Al día siguiente (02 de julio) se añade el valor de la fila anterior al número de visitas actual. El total de este día es de 7.203, es decir, el número de visitas del 1 de julio más el número de visitas del 2 de julio. Al día siguiente, el 03 de julio, se suma esa cifra (6.744) al total anterior (7.203) y se obtiene 13.947. Y así sucesivamente.

Éste es sólo un ejemplo de negocio en el que se utiliza el patrón de totales corridos; otros ejemplos son los siguientes:

• Número total de artículos vendidos desde el primer día de un mes hasta el día observado del mismo mes.
• El total acumulado de las transacciones de débito/crédito de una cuenta bancaria en el último trimestre o año.
• Total de ingresos en lo que va de enero hasta el mes observado del mismo año.

Ahora que sabemos lo que es un total acumulado, vamos a escribir una consulta SQL que lo calcule. Un total acumulado (o suma acumulada) puede ser calculado en SQL utilizando las funciones de ventana apropiadas.

Las funciones de ventana son funciones especiales de SQL que trabajan en un conjunto de filas. Son similares a la cláusula GROUP BY pero el resultado se muestra de forma diferente al final. En un simple GROUP BY, las filas se colapsan (cada grupo se muestra como una fila). Con las funciones de ventana, las filas no se colapsan; cada fila se devuelve y el valor del cálculo se asigna a cada fila de la tabla. Esto es exactamente lo que necesitamos cuando se muestra el total de ejecución: un valor adicional en una tabla existente.

A continuación, se presenta una consulta SQL que calcula el total de la serie temporal en los datos daily_visit datos:

SELECT
   *, 
  SUM(visits) OVER (PARTITION BY website ORDER BY date) AS running_total 
FROM daily_visits;

Al ejecutar esta consulta, se muestran todas las filas y se crea una columna adicional, running_total. He aquí una breve explicación de lo que ocurre:

• La cláusula OVER, con PARTITION BY, le dice a SQL que ésta será una función de ventana.
• PARTITION BY divide las filas en grupos lógicos. En nuestro ejemplo, las filas se agrupan en el nivel del sitio web.
• ORDER BY establece el orden de las filas. En nuestro ejemplo, todas las filas se ordenan por la columna de la fecha.
• ROW/RANGE no se define explícitamente en este ejemplo. Estas palabras clave opcionales significan que cada marco de ventana dentro de una partición comprende todas las filas desde el inicio de la partición hasta la fila actual. En otras palabras, para cada fila, la suma total se calcula como la suma de los valores desde la primera fila de la partición hasta la fila actual.

Con esta sentencia SQL (OVER en combinación con PARTITION BY y ORDER BY), establecemos un total de visitas en funcionamiento a nivel de sitio web.

No voy a profundizar en esta explicación y sintaxis aquí. Si quieres aprender las funciones de ventana de forma más detallada, te recomiendo el LearnSQL.es curso sobre funciones de ventana. Echa un vistazo al artículo complementario, SQL Course of the Month - Funciones de ventana; responde a preguntas como por qué deberías aprender las funciones ventana, qué son, y por qué este curso es una gran elección.

Las funciones de ventana se utilizan en otros cálculos de patrones. En la siguiente sección, echaré un vistazo a la hoja de trucos de las funciones de ventana y le mostraré cómo escribir consultas SQL para el cambio porcentual y las medias móviles.

Cambio porcentual en las visitas diarias al sitio web

A menudo necesitará describir cómo cambia su negocio a través del tiempo. Hay otro patrón muy común utilizado en el análisis de series temporales llamado "porcentaje de cambio" (o porcentaje de cambio). Responde a preguntas como:

• ¿Cómo se comparan los ingresos de este mes con los del mes pasado? ¿Han aumentado o disminuido?
• ¿Ha aumentado o disminuido el número de usuarios registrados en nuestro sitio web este trimestre?
• ¿He vendido hoy más artículos que hace una semana?
• ¿Están aumentando o disminuyendo nuestras ventas en comparación con el año pasado?

A continuación, le mostraré cómo utilizar SQL para calcular los cambios porcentuales. En el siguiente ejemplo, encontraremos el porcentaje de cambio en el número total de visitas al sitio web (hoy comparado con ayer y hoy comparado con hace una semana). Una vez que aprenda a realizar cambios porcentuales con este ejemplo, podrá aplicarlo a cualquier otro caso de negocio. La estructura de la consulta es la misma; sólo los nombres de las tablas y las columnas - y tal vez un argumento LAG(), que explicaré en un segundo - serán diferentes.

Uso de la función LAG() para recuperar el valor de una fila anterior

El primer paso para calcular el porcentaje de cambio es recuperar un valor de una fila anterior. ¿Por qué necesitamos esto? Porque el porcentaje de cambio se calcula mediante la fórmula:

(current_value - previous_value)/previous value * 100.

Esta fórmula significa que para calcular el aumento o la disminución del número de visitas, es necesario que ambos valores se presenten en la misma fila.

Por lo tanto, nuestra primera tarea para calcular este patrón es recuperar un valor de una fila anterior. Esta tarea se puede realizar con la ayuda de la función de ventana LAG(). Proporciona acceso a un valor de una fila determinada que es anterior a la fila actual. A continuación se muestra una sentencia SELECT que asigna el valor de daily_visits de la fila anterior a una nueva columna(previous_day_visits) en la fila actual:

SELECT
  *,
  LAG(visits) OVER(PARTITION BY website ORDER BY date) AS previous_day_visits
FROM daily_visits;

Esta es una función típica de ventana: dentro de la cláusula OVER, se define la partición y el ordenamiento deseados. La función LAG() toma un argumento (el nombre de la columna que contiene los valores deseados) y asigna el valor de la fila anterior a cada fila:

LAG() también puede utilizarse para asignar valores de n filas hacia atrás, no sólo de la fila anterior. Por ejemplo, suponga que quiere calcular el porcentaje de cambio para el mismo día de la semana pasada. En ese caso, tendrá que asignar a cada fila un valor de hace siete días.

Para ello, utilizamos el parámetro opcional de desplazamiento de LAG. Compruebe la siguiente consulta:

SELECT
  *,
  LAG(visits,7) OVER(PARTITION BY website ORDER BY date) AS previous_day_visits
FROM daily_visits;

El valor de desplazamiento por defecto de LAG es 1 (la fila anterior), pero puede cambiarlo por cualquier otro valor. En nuestro ejemplo, hemos utilizado un valor de 7, que asigna a cada fila el número de visitas de 7 días atrás:

Aumento/disminución de 1 día en el número total de visitas

Ahora podemos calcular fácilmente el aumento/disminución de 1 día con este código SQL:

WITH daily_visits_lag AS (
 SELECT
  *,
  LAG(visits) OVER(PARTITION BY website ORDER BY date) AS previous_day_visits
  FROM daily_visits
)
SELECT
    *,
   COALESCE(round((visits — previous_day_visits)/previous_day_visits *100),0) AS percent_change
FROM daily_visits_lag;

Esta declaración parece complicada, pero en realidad no lo es. Esto es lo que ocurre:

• El SELECT que asigna a cada fila un valor de la fila anterior está escrito dentro de una sentencia WITH. Esto significa que estamos utilizando una expresión de tabla común o CTE (es decir, un conjunto de resultados temporales con nombre). A continuación, utilizamos este resultado temporal en el siguiente SELECT.
• El conjunto de resultados temporal se denomina daily_visits_lag. Contiene los valores necesarios para el cálculo del porcentaje de cambio (es decir, el número de visitas de la fila actual y el número de visitas del día anterior).
• daily_visits_lag se utiliza en la consulta principal. Una columna adicional, percent_change, se calcula en COALESCE() con la fórmula(visitas-día_anterior_visitas)/día_anterior_visitas * 100.
• Una vez ejecutada esta sentencia, el motor SQL elimina el conjunto de resultados temporal; no se puede utilizar más en el código. (Así es como funciona una CTE).

No hay suficiente espacio para hablar de CTEs aquí, pero nuestro Consultas recursivas y expresiones de tabla comunes es un buen recurso para aprender más sobre el uso y la sintaxis de las CTE.

Después de ejecutar esta consulta, se muestran los siguientes valores:

Aumento/disminución en 7 días del número total de visitas

Ahora que ya sabe cómo calcular un cambio porcentual de 1 día, se puede utilizar una sentencia muy similar para calcular una disminución/incremento de 7 días en el número total de visitas:

WITH daily_visits_lag AS (
 SELECT
  *,
  LAG(visits,7) OVER(PARTITION BY website ORDER BY date) AS previous_7day_visits
  FROM daily_visits
)
SELECT
    *,
   COALESCE(round((visits — previous_7day_visits)/previous_7day_visits *100),0) AS percent_change
FROM daily_visits_lag;

La única diferencia aquí es que usamos LAG() con un parámetro de desplazamiento de 7 - estamos recuperando valores de visitas de 7 días antes (una semana atrás) y calculando el porcentaje de cambio con esos valores (día actual vs. una semana antes). En otras palabras, estamos calculando el aumento o disminución de 7 días en el número de visitas:

Tenga en cuenta que la consulta que utilizamos aquí puede ser utilizada en otros ejemplos de negocios. Sólo hay que ajustar los nombres de las tablas y las columnas; el resto puede permanecer igual.

Promedios móviles simples: 7 días

Otro patrón muy utilizado en el análisis de series temporales se llama media móvil simple (SMA). Una SMA es la media no ponderada de los n valores anteriores de una fila; se calcula para cada valor de una columna determinada.

Las SMA se utilizan a menudo para determinar las tendencias en el análisis del precio de las acciones o de las criptomonedas. Esta información nos ayuda a entender el comportamiento de nuestra variable: en lugar de un solo valor, obtenemos mejores estimaciones utilizando los valores medios de una medida específica. Estamos suavizando las fluctuaciones para obtener una visión global.

En nuestro ejemplo del sitio web, cada día tendrá dos cifras de interés:

• El número de visitas que se produjeron en ese día concreto
• El número medio de visitas de los últimos 7 días.

Una vez más, utilizaremos una función de ventana SQL para calcular nuestro SMA. A diferencia del ejemplo anterior, donde utilizamos LAG() para recuperar los valores de las filas anteriores, aquí utilizaremos el parámetro ROW/RANGE dentro de la cláusula OVER:

SELECT
   *, 
   AVG(visits) OVER(PARTITION BY website ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) as SMA7
FROM daily_visits ;

Anteriormente en el artículo, dijimos que ROW dentro de una cláusula OVER define una ventana dentro de cada partición. Cuando calculamos el total de ejecución, utilizamos valores por defecto para los parámetros de fila/rango. Esto significa que el punto inicial de cada ventana dentro de la partición era la primera fila de esa partición y el punto final era la fila actual.

Ahora, cada ventana se define como 7 filas (los 6 valores de la fila anterior + el valor de la fila actual). Lo hicimos con las filas entre las 6 filas anteriores y la fila actual. Es decir, el punto de partida no es la primera fila de la partición. Aquí, el valor medio(avg(visitas)) se va a calcular sobre las últimas 7 filas, incluida la actual. Esa es la definición de un SMA.

Una vez que ejecute esta sentencia, obtendrá un valor medio asociado a cada fila. Esto le permitirá observar las tendencias de las visitas de una manera más detallada. Este es el resultado:

Las funciones de ventana de SQL son una función realmente potente para el análisis de tendencias, y el SMA es sólo uno de los indicadores que se pueden obtener con las funciones de ventana. Si desea ver más ejemplos relacionados con el análisis de tendencias, pruebe nuestro curso Análisis de tendencias de ingresos en SQL. En él se discute cómo puede usar SQL para analizar cualquier tendencia de series de tiempo.

Uso de RANK() para encontrar el mayor número de visitas

Nuestro último patrón de análisis es la clasificación. Como puede adivinar, esto ordena los resultados basados en una variable dada. Supongamos que queremos ver qué fechas tuvieron el mayor número de visitas para cada uno de nuestros sitios web. Para hacer esto, necesitamos clasificar nuestras visitas diarias para cada sitio web por separado. Podemos hacerlo utilizando la función de ventana RANK():

SELECT *,
  RANK() OVER (PARTITION by website ORDER BY visits DESC) AS rank 
FROM daily_visits;

Esto es lo que ocurre en este código:

• El rango se determina por la columna visitas. Esto se define en ORDER BY, que está en la cláusula OVER). La fila con el mayor valor de visitas obtiene el rango más alto.
• Las filas (visitas) se agrupan por separado para cada sitio web, por lo que el rango de cada sitio web se determina individualmente. Esto se define en PARTITION BY en la cláusula OVER.
• Las filas con criterios de clasificación iguales (es decir, con el mismo valor de visitas ) reciben la misma clasificación.

Una vez que se ejecuta este SELECT, el motor SQL devuelve un conjunto de resultados con una columna adicional llamada rango. Ahora podemos ver fácilmente qué días han tenido el mayor número de visitas. Lo mejor es que el rango se define para cada sitio, así que no estamos comparando los dos sitios juntos.

El rango más alto para sqldevelopers.com

El rango más alto para sqlanalysts.com

Más información sobre el análisis de series temporales con SQL

Ahora puede utilizar las funciones de ventana de SQL para realizar algunos análisis básicos de series temporales. Ya sabe lo que son las series temporales y cómo puede utilizar las funciones de ventana de SQL para obtener algunas ideas bastante buenas. Incluso ha sido introducido a los CTEs.

¿Qué es lo siguiente? Definitivamente recomiendo tomar un enfoque organizado para aprender más sobre el análisis de series de tiempo SQL. Los cursos Funciones de ventana y Revenue Trend Analysis in SQL que ya he mencionado son buenos. ¡Recuerde, es importante poner en práctica sus habilidades, así que asegúrese de que está recibiendo algunos ejercicios del mundo real para resolver!