Cómo calcular la longitud de una serie con SQL

¿Cómo se calcula la longitud de una serie en SQL? Puedo responder a eso en dos palabras: ¡funciones de ventana! Sí, necesitarás algunos conocimientos de las funciones de ventana de SQL para calcular la longitud de las series. Pero, ¿qué es una serie temporal y por qué querrías averiguar su longitud?

¿Qué es una serie?

Aunque hay algunas variaciones en la definición de "serie temporal", básicamente se trata de una secuencia de datos enumerados en orden temporal.

En una base de datos, esto se suele representar con eventos separados por un tiempo igual. Por ejemplo, los inicios de sesión en el sitio web de un usuario en días consecutivos se consideran una serie. La siguiente tabla muestra estas fechas de inicio de sesión:

Si quisiera analizar la duración de esta serie, contaría el número de inicios de sesión consecutivos. La duración de la primera serie es de tres días, ya que el usuario se conectó en 2020-06-01, 2020-06-02 y 2020-06-03. La longitud de la segunda serie es de seis días (el usuario se conectó todos los días entre el 2020-06-06 y el 2020-06-11). Siguiendo la misma lógica, las longitudes de las dos series siguientes son de cuatro y dos días, respectivamente.

¿Por qué calcular la longitud de una serie?

Las series de tiempo son muy utilizadas, y hay muchas situaciones en las que se necesita calcular la longitud de las series. Algunos ejemplos de cálculo de longitudes de series incluyen:

• Medir una racha de ingresos en Stack Overflow.
• Ver tu racha de actividad en Duolingo.
• Seguir el número de días que has utilizado una aplicación de fitness.
• Analizar una racha de ventas en un sitio de comercio electrónico.
• Encontrar el aumento o la disminución consecutiva del valor de una moneda.

Básicamente, en cualquier lugar donde tengas una serie temporal, probablemente necesitarás calcular su longitud.

La pregunta principal sigue siendo: ¿Cómo se calcula la longitud de una serie temporal y cómo se hace en SQL? Como muestra la tabla anterior, las series temporales no son un concepto difícil de entender. En el momento en que viste este ejemplo, supiste intuitivamente cómo obtener la longitud de la serie.

Sin embargo, una cosa es encontrar la longitud de la serie manualmente en varias líneas de datos. Pero no puedes hacer eso en una base de datos con cientos, miles o millones de filas. Por suerte, las funciones de ventana de SQL están aquí para salvar el día.

Si necesita refrescar sus conocimientos sobre las funciones de ventana, pruebe esta hoja de trucos sobre las funciones de ventana. Puede ser especialmente útil cuando lleguemos a la parte de los cálculos.

Si ni siquiera sabes lo que son las funciones de ventana, te recomiendo que leas ¿Por qué debo aprender SQL Funciones de ventana? antes de seguir adelante. La mejor manera de obtener un conocimiento completo de las funciones ventana es el curso LearnSQL.es Window Functions.

Cómo Calcular la Longitud de una Serie en SQL

Para este ejemplo, imaginemos que estás aprendiendo Alto Valyrio en Duolingo. La plataforma Duolingo tiene una cosa llamada racha. Como explica el sitio, una racha es "el número de días seguidos que has completado una lección". Una vez que completes una lección en la aplicación o el sitio web, tu racha aumentará en un día. Recibirás tu recompensa diaria cuando alcances tu objetivo de XP". (XP es "puntos de experiencia", por cierto.) El seguimiento de tu racha te permite recibir incentivos específicos de la plataforma. Entonces, ¿cómo sabe Duolingo cuán larga es tu racha?

Tu racha de aprendizaje para julio de 2020 puede ser presentada por la tabla lesson_completed. Contiene las siguientes columnas:

• id: El ID de la lección.
• date_completed: La fecha en que completó la lección.

Ahora vamos a escribir una consulta para encontrar tu racha. Utilizaré una Expresión de Tabla Común (CTE) para ayudar a organizar esta consulta. Para aquellos que no estén familiarizados, nuestro artículo sobre CTEs es un buen punto de partida. Al ejecutar esta consulta se calculará la longitud de la serie:

WITH groups AS (
	SELECT	RANK() OVER (ORDER BY date_completed) AS row_number,
			date_completed,
			DATEADD (day, -RANK() OVER (ORDER BY date_completed), 
date_completed) AS date_group
FROM lesson_completed)

SELECT	COUNT(*) AS days_streak,
		MIN (date_completed) AS min_date,
		MAX (date_completed) AS max_date
FROM groups
GROUP BY date_group;

Déjeme explicar lo que hice. La consulta se puede dividir en dos partes:

• Creación de la CTE.
• Seleccionar los datos del CTE.

Creación del CTE

La parte de la consulta que crea el CTE se muestra de nuevo a continuación:

WITH groups AS (
	SELECT	RANK() OVER (ORDER BY date_completed) AS row_number,
			date_completed,
			DATEADD (day, -RANK() OVER (ORDER BY date_completed), 
date_completed) AS date_group
FROM lesson_completed)

El CTE está definido por la cláusula WITH. He decidido que el nombre de este CTE sea grupos. Todo lo que está escrito entre los paréntesis después de la palabra clave AS es simplemente una declaración bastante regular de SELECT.

Ahora, ¿qué hace esto? Primero, he añadido el número de filas a la tabla. Para ello, he utilizado la función RANK(). Esta es una función de ventana, por lo que está definida por la cláusula OVER(). Quiero que las filas se añadan secuencialmente según las fechas, por lo que el resultado de esta función se ordena por la columna date_completed.

El CTE selecciona entonces la columna date_completed. He utilizado la función DATEADD (SQL Server) para deducir el número de fila de la date_completed. En esta sentencia ...

DATEADD (day, -RANK() OVER (ORDER BY date_completed), date_completed) AS date_group 

... He tenido que definir el intervalo que se sumará (o deducirá), que es el día. ¿Cuántos días quiero deducir? Los días que sean iguales al número de filas - por eso sólo he copiado la función de ventana RANK() que ya tenía definida y le he añadido el signo negativo delante. ¿De qué quiero que se deduzca esto? Del date_completed, por supuesto.

Esta parte de la consulta dará el siguiente resultado:

¿Por qué necesito esto? Me ayudará a calcular la longitud de la serie. Mira la tabla. ¿Te has dado cuenta de que los días consecutivos pertenecen al mismo grupo de fechas? ¿A qué se debe? Si las fechas son consecutivas y le restas el número de fila, siempre obtendrás la misma fecha. Fíjate en esto:

Deduce el número de fila (1) de la fecha (2020-07-01) y obtendrás 2020-06-30. Eso es precisamente lo que tengo en la tabla.

Y luego la siguiente fila. Si deducimos el número de fila (2) de la fecha (2020-07-02), ¡el resultado es 2020-06-30 de nuevo! Ahora veamos qué ocurre cuando las fechas no son consecutivas.

Para la fila 5, el resultado sigue siendo el mismo, 2020-06-30. Eso está bien, pero ¿qué pasa con la siguiente fila? Deduzca la fila número 6 de la fecha (2020-07-08), ¿y qué obtiene? 2020-07-02. Este es ahora un nuevo grupo de fechas, lo cual no es sorprendente ya que el 2020-07-08 no es consecutivo al 2020-07-05 en ningún calendario.

Un bonito truco, ¿verdad? Con respecto a las fechas en la columna date_group, realmente no importa qué fechas obtenga. Sólo servirán como valores, que se contarán en la segunda parte de la consulta. Cuente cuántas veces ocurre cada grupo y tendrá la longitud de su serie.

Seleccionando los datos del CTE

La segunda parte de la consulta selecciona los datos del CTE que he definido anteriormente:

SELECT	COUNT(*) AS days_streak,
		MIN (date_completed) AS min_date,
		MAX (date_completed) AS max_date
FROM groups
GROUP BY date_group;

Esta simple sentencia SELECT cuenta el número de filas y muestra los resultados en la nueva columna days_streak. Luego selecciona la fecha mínima y máxima en la columna date_completed con los resultados mostrados en las columnas min_date y max_date, respectivamente. Todos estos datos se seleccionarán desde el CTE denominado grupos.

Finalmente, los datos tienen que ser agrupados por el date_group. ¿Por qué? Porque no necesito el número total de filas; quiero el número de filas de cada grupo de fechas.

Esta es una forma de calcular la longitud de una serie ...

Este pequeño ejemplo debería darte una idea de cómo calcular la longitud de una serie con SQL. Sin embargo, no hay una sola manera de abordarlo. Y, como has visto, no hay una simple función SQL que te dé lo que quieres. Tus datos y lo que necesitas calcular determinarán tu código SQL. Por lo general, se requiere un pequeño truco; tu experiencia y conocimientos de SQL te ayudarán a decidir cuál.

Lo que casi seguro necesitarás saber para calcular la longitud de la serie son las funciones de ventana de SQL. Si todavía no estás convencido de que deberías aprender las funciones de ventana, lee esta entrevista con la creadora del curso: explica todas las ventajas de las funciones de ventana.

Este artículo te ha mostrado sólo un vistazo a lo que son las series temporales. Si estás interesado en aprender a analizar este tipo específico de datos, LearnSQL tiene una guía muy detallada.

Si este artículo te ha ayudado a resolver algunos problemas de series temporales, házmelo saber en la sección de comentarios.