Cómo agrupar datos por semanas en SQL Server

Este es un contenido adicional para el curso LearnSQL.es Análisis del comportamiento de los clientes en SQL Server.

En este curso, le mostramos cómo puede analizar el ciclo de vida del cliente (adquisición de clientes, conversión, actividad, retención y abandono) con SQL. Hablamos de las cohortes de registro de clientes, o grupos de clientes que se registraron durante el mismo período (por ejemplo, la misma semana, el mismo mes). El análisis de las cohortes de registro de clientes le permite ver las tendencias de registro y relacionar las cohortes de registro con las campañas de marketing.

En la práctica, las cohortes de registro más convenientes son las semanales. Las cohortes de registro anuales, trimestrales o mensuales son demasiado imprecisas para un análisis significativo. Por otro lado, las cohortes de registro diarias o por hora son demasiado específicas. Las cohortes de registro semanales suelen ser las adecuadas.

En este artículo, veremos diferentes formas de agrupar datos por semanas en SQL Server.

¿Cómo se agrupan los datos por semanas en SQL Server?

SQL Server proporciona una función llamada DATEPART(), que devuelve una parte especificada(año, trimestre, mes, semana, hora, minuto, etc.) de una fecha especificada.

Para agrupar los clientes que se registraron en 2018 por la semana, puede utilizar esta consulta:

SELECT
  DATEPART(week, RegistrationDate) AS Week,
  COUNT(CustomerID) AS Registrations
FROM Customers
WHERE '20180101' <= RegistrationDate
  AND RegistrationDate < '20190101'
GROUP BY DATEPART(week, RegistrationDate)
ORDER BY DATEPART(week, RegistrationDate);

Como puede ver, la función DATEPART() toma dos argumentos: datepart (es decir, el identificador de la parte deseada) y la fecha de la que se extrae la parte.

La función DATEPART() tiene dos argumentos datepart que devolverán los datos de la semana:

• week (también abreviado wk, ww).
• iso_week (también abreviado isowk, isoww).

En breve explicaremos las diferencias entre estos dos tipos. Pero primero, necesitamos discutir otra configuración.

La configuración DATEFIRST

La configuración de DATEFIRST indica a SQL Server qué día de la semana debe considerar como el primer día de la semana. DATEFIRST puede ser uno de los siguientes valores:

El ajuste DATEFIRST depende de la versión del idioma de SQL Server. El valor por defecto para el inglés de Estados Unidos es 7 (es decir, el domingo).

Puede cambiar el valor de DATEFIRST de la siguiente manera:

SET DATEFIRST 1

DATEFIRST es una configuración de sesión, lo que significa que puede cambiarla sin afectar a otros usuarios.

Puede encontrar el valor actual del ajuste DATEFIRST con la función @@DATEFIRST:

SELECT @@DATEFIRST;

Resultado:

7

El primer día de la semana es el 7, es decir, el domingo.

Uso de DATEPART() con la semana

Ahora que conocemos la configuración de DATEFIRST, veamos cómo funciona DATEPART() con semana.

El comportamiento de DATEPART() con semana depende del día que DATEFIRST haya establecido como primer día de la semana. Las semanas de cada año se numeran por separado. La semana 1 es la que contiene (pero no necesariamente comienza con) el 1 de enero.

Veamos un ejemplo. La imagen muestra tres calendarios diferentes para enero de 2019; cada uno especifica un día de la semana diferente como primer día de la semana:

• El primer calendario tiene el domingo como primer día de la semana, como se suele utilizar en Estados Unidos.
• El segundo calendario muestra el lunes como primer día de la semana, como se suele utilizar en Europa.
• El tercer calendario utiliza el jueves como primer día de la semana. (Sólo como ejemplo de una semana no típica).

DATEPART() con la semana utiliza la numeración de la semana que se muestra en la imagen:

• Si el domingo es el primer día de la semana (DATEFIRST = 7), la semana 2 comienza el domingo 6 de enero y termina el sábado 12 de enero.
• Si el lunes es el primer día de la semana (DATEFIRST = 1), la Semana 2 comienza el lunes 7 de enero y termina el domingo 13 de enero.
• Si el jueves es el primer día de la semana (DATEFIRST = 4), la semana 2 comienza el jueves 3 de enero y termina el miércoles 9 de enero.

Un uso típico de DATEPART() con semana es agrupar los datos por semana mediante la cláusula GROUP BY. También lo utilizamos en la cláusula SELECT para mostrar el número de la semana. Observe la siguiente consulta y su resultado:

SELECT
  DATEPART(week, RegistrationDate) AS Week,
  COUNT(CustomerID) AS Registrations
FROM Customers
WHERE '20180101' <= RegistrationDate
  AND RegistrationDate < '20190101'
GROUP BY DATEPART(week, RegistrationDate)
ORDER BY DATEPART(week, RegistrationDate);

Si los números de la semana le resultan ilegibles, consulte el artículo Cómo obtener el primer día de la semana.

Observe que en DATEPART() con semana, la semana en la que termina el año y comienza el siguiente suele estar dividida. En otras palabras, los últimos días de diciembre se colocan en la semana 52/53 del año anterior, mientras que los primeros días de enero están en la semana 1 del nuevo año.

El uso de DATEPART() con iso_week

DATEPART() tiene otro argumento de parte de fecha para las semanas: iso_week. Este argumento maneja las semanas según la norma ISO 8601, que es un estándar internacional que cubre el intercambio de datos de fecha y hora.

En la norma ISO 8601, las semanas comienzan el lunes. La semana 1 de un año es la semana en la que ocurre el primer jueves de ese año. Este estilo de numeración de la semana se suele utilizar en los países europeos.

Por ejemplo, la semana 1 de 2017 fue del lunes 2 de enero al domingo 8 de enero. Es importante tener en cuenta que si el 1 de enero cae en viernes, sábado o domingo, se considera parte de la semana 52/53 del año anterior.

Este es el calendario de enero de 2017:

Una vez más, se suele utilizar DATEPART() con iso_week en GROUP BY y SELECT:

SELECT
  DATEPART(iso_week, RegistrationDate) AS Week,
  COUNT(CustomerID) AS Registrations
FROM Customers
WHERE '20180101' <= RegistrationDate
  AND RegistrationDate < '20190101'
GROUP BY DATEPART(iso_week, RegistrationDate)
ORDER BY DATEPART(iso_week, RegistrationDate);

Si los números de las semanas le resultan ilegibles, consulte el artículo sobre Cómo obtener el primer día de la semana.