Como agrupar dados por semana no SQL Server

Este é um conteúdo adicional para o curso LearnSQL.com.br Análise do Comportamento do Cliente no SQL Server.

Neste curso, mostramos como você pode analisar o ciclo de vida do cliente (aquisição, conversão, atividade, retenção e rotatividade do cliente) com SQL. Discutimos coortes de registro de clientes, ou grupos de clientes que se registraram durante o mesmo período (por exemplo, na mesma semana, no mesmo mês). A análise das coortes de registro de clientes permite ver as tendências de registro e relacionar as coortes de registro com as campanhas de marketing.

Na prática, as coortes de registro mais convenientes são as coortes de registro semanais. As coortes de registro anuais, trimestrais ou mensais são muito imprecisas para uma análise significativa. Por outro lado, as coortes de registro diárias ou horárias são muito específicas. As coortes de registro semanais geralmente são as mais adequadas.

Neste artigo, analisaremos diferentes formas de agrupar os dados por semana no SQL Server.

Como agrupar os dados por semana no SQL Server?

O SQL Server fornece uma função chamada DATEPART(), que retorna uma parte específica(ano, trimestre, mês, semana, hora, minuto, etc.) de uma data específica.

Para agrupar clientes que se registraram em 2018 até a semana, você pode usar 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 você pode ver, a função DATEPART() leva dois argumentos: parte de data (ou seja, o identificador da peça desejada) e a data a partir da qual você extrai a peça.

A função DATEPART() tem dois argumentos de parte de data que retornarão os dados da semana:

• week (também abreviado wk, ww).
• iso_week (também abreviado isowk, isoww).

Explicaremos em breve as diferenças entre estes dois tipos. Mas primeiro, precisamos discutir outro cenário.

O PRIMEIRO Cenário

A configuração DATEFIRST diz ao SQL Server qual dia da semana deve ser considerado como o primeiro dia da semana. DATEFIRST pode ser um dos seguintes valores:

A configuração DATEFIRST depende da versão da linguagem do SQL Server. O valor padrão para o inglês dos EUA é 7 (ou seja, domingo).

Você pode alterar o valor de DATEFIRST desta forma:

SET DATEFIRST 1

DATEFIRST é um ambiente de sessão, o que significa que você pode mudá-lo sem impactar outros usuários.

Você pode encontrar o valor atual da configuração DATEFIRST com a função @@DATEFIRST:

SELECT @@DATEFIRST;

Resultado:

7

O primeiro dia da semana é 7, ou seja, domingo.

Usando DATEPART() com a semana

Agora que conhecemos o cenário DATEFIRST, vamos dar uma olhada em como funciona DATEPART() com semana.

O comportamento do DATEPART() com semana depende do dia que o DATEFIRST definiu como o primeiro dia da semana. As semanas de cada ano são numeradas separadamente. A semana 1 é a semana que contém (mas não necessariamente começa com) 1º de janeiro.

Vejamos um exemplo. A imagem mostra três calendários diferentes para janeiro de 2019; cada um especifica um dia da semana diferente como o primeiro dia da semana:

• O primeiro calendário tem o domingo como primeiro dia da semana, como tipicamente utilizado nos Estados Unidos.
• O segundo calendário mostra a segunda-feira como o primeiro dia da semana, como tipicamente usado na Europa.
• O terceiro calendário usa a quinta-feira como o primeiro dia da semana. (Apenas como um exemplo de uma semana não típica).

DATEPART() com a semana usa a numeração da semana mostrada na imagem:

• Se o domingo é o primeiro dia da semana (DATEFIRST = 7), a Semana 2 começa no domingo 6 de janeiro e termina no sábado 12 de janeiro.
• Se segunda-feira é o primeiro dia da semana (DATEFIRST = 1), a Semana 2 começa na segunda-feira 7 de janeiro e termina no domingo 13 de janeiro.
• Se quinta-feira é o primeiro dia da semana (DATEFIRST = 4), a Semana 2 começa na quinta-feira 3 de janeiro e termina na quarta-feira 9 de janeiro.

Um uso típico do DATEPART() com semana é agrupar os dados por semana através da cláusula GROUP BY. Também o utilizamos na cláusula SELECT para exibir o número da semana. Dê uma olhada na consulta abaixo e em seu 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);

Se você achar o número da semana ilegível, veja o artigo sobre Como obter o primeiro dia da semana.

Observe que para DATEPART() com semana, a semana em que o ano termina e o próximo começa é muitas vezes dividida. Em outras palavras, os últimos dias de dezembro são colocados na semana 52/53 do ano anterior, enquanto os primeiros dias de janeiro são na semana 1 do novo ano.

Usando DATEPART() com iso_week

DATEPART() tem outro argumento de data parte durante semanas: iso_week. Este argumento trata de semanas de acordo com a ISO 8601, que é uma norma internacional que abrange o intercâmbio de dados de data e hora.

Na norma ISO 8601, as semanas começam na segunda-feira. A semana 1 de um ano é a semana em que ocorre a primeira quinta-feira desse ano. Este estilo de numeração semanal é tipicamente utilizado nos países europeus.

Por exemplo, a semana 1 de 2017 foi de segunda-feira, 2 de janeiro a domingo, 8 de janeiro. É importante observar que se o dia 1 de janeiro for numa sexta-feira, sábado ou domingo, é considerado como parte da semana 52/53 do ano anterior.

Aqui está o calendário para janeiro de 2017:

Mais uma vez, você normalmente usa DATEPART() com iso_week em GROUP BY e 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);

Se você achar os números da semana ilegíveis, veja o artigo sobre como obter o primeiro dia da semana.