Como calcular o comprimento de uma série com SQL

Como calculamos o comprimento de uma série em SQL? Eu posso responder isso em duas palavras: funções de janela! Sim, você vai precisar de algum conhecimento das funções de janela SQL para calcular o comprimento da série. Mas o que é uma série cronológica, e por que você gostaria de descobrir qual é o seu comprimento?

O que é uma série?

Embora haja alguma variação na definição de "série cronológica", é basicamente uma seqüência de dados listados em ordem cronológica.

Em um banco de dados, isto é normalmente representado por eventos separados por um tempo igual. Por exemplo, os logins do site do usuário em dias consecutivos são considerados uma série. A tabela abaixo mostra tais datas de login:

Se eu quisesse analisar a duração desta série, eu estaria contando o número de logins consecutivos. A duração da primeira série é de três dias, desde que o usuário efetuou login em 2020-06-01, 2020-06-02, e 2020-06-03. A duração da segunda série é de seis dias (o usuário logou todos os dias entre 2020-06-06 e 2020-06-11). Seguindo a mesma lógica, a duração das duas séries seguintes é de quatro e dois dias, respectivamente.

Por que calcular o comprimento de uma série?

As séries cronológicas são amplamente utilizadas, e há muitas situações em que seria necessário calcular o comprimento da série. Alguns exemplos de como calcular o comprimento de uma série incluem:

• Medição de uma sequência de login no Stack Overflow.
• Ver sua série de atividades em Duolingo.
• Rastrear quantos dias você usou um aplicativo de fitness.
• Analisar uma faixa de vendas em um site de comércio eletrônico.
• Encontrar o aumento ou diminuição consecutiva do valor de uma moeda.

Basicamente, em qualquer lugar onde você tenha uma série cronológica, provavelmente precisará calcular sua duração.

A questão principal permanece: Como se calcula a duração de uma série cronológica e como se faz em SQL? Como mostra a tabela acima, as séries cronológicas não são um conceito difícil de se entender. No momento em que você viu este exemplo, você intuitivamente soube como obter a duração da série.

Entretanto, uma coisa é encontrar o comprimento de uma série manualmente através de várias linhas de dados. Mas você não pode fazer isso em um banco de dados com centenas, milhares, ou milhões de linhas. Por sorte, as funções da janela SQL estão aqui para salvar o dia!

Se você precisar atualizar seu entendimento das funções de janela, tente esta folha de consultas com as funções de janela. Ela pode ser especialmente útil quando chegarmos à parte de cálculo.

Se você nem sequer sabe o que são funções de janela, recomendo que leia Why Should I Learn SQL Funções de Janela (Window Functions) em SQL? antes de ir mais longe. A melhor maneira de realmente obter um conhecimento abrangente das funções de janela é o curso LearnSQL.com.br Window Functions (Funções de Janela).

Como Calcular o Comprimento de uma Série em SQL

Para este exemplo, imaginemos que você esteja aprendendo o Alto Valiriano em Duolingo. A plataforma de Duolingo tem uma coisa chamada de raia. Como o site explica, uma raia é "o número de dias seguidos em que você completou uma lição". Uma vez que você complete uma lição no aplicativo ou no site, sua série aumentará em um dia. Você receberá sua recompensa diária quando atingir sua meta de EXP". (XP é "pontos de experiência", a propósito.) Monitorar sua maré permite que você receba incentivos específicos da plataforma. Então, como a Duolingo sabe quanto tempo é sua maré de sorte?

Sua série de aprendizagem para julho de 2020 pode ser apresentada pela tabela lesson_completed. Ela contém as seguintes colunas:

• id: A identificação da lição.
• date_completed: A data em que você completou a lição.

Agora vamos escrever uma pergunta para encontrar a sua sequência. Vou usar uma Expressão de Tabela Comum (CTE) para ajudar a organizar esta consulta. Para aqueles que não estão familiarizados, nosso artigo sobre CTEs é um bom ponto de partida. A execução desta consulta calculará a extensão da série:

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;

Deixe-me explicar o que eu fiz. A consulta pode ser dividida em duas partes:

• Criação do CTE.
• Selecionando os dados do CTE.

Criando o CTE

A parte da consulta que cria o CTE é dada novamente abaixo:

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)

O CTE é definido pela cláusula WITH. Eu decidi que o nome deste CTE deveria ser grupos. Tudo o que está escrito entre parênteses após a palavra-chave AS é apenas uma declaração bastante regular SELECT.

Agora, o que isto faz? Primeiro, acrescentei o número de filas à tabela. Para fazer isso, usei a função RANK(). Esta é uma função de janela, e é por isso que é definida pela cláusula OVER(). Quero que as fileiras sejam adicionadas seqüencialmente de acordo com as datas, portanto, o resultado desta função é ordenado pela coluna date_completed.

O CTE então seleciona a coluna date_completed. Utilizei a função DATEADD (SQL Server) para deduzir o número da linha a partir do date_completed. Nesta declaração ...

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

... Eu tive que definir o intervalo que será adicionado (ou deduzido), que é o dia. Quantos dias eu quero deduzir? Os dias que são iguais ao número de linhas - é por isso que apenas copiei a função da janela RANK() que já tinha definido e adicionei o sinal negativo em frente a ela. Do que eu quero que isto seja deduzido? Do date_completed, é claro!

Esta parte da consulta dará o seguinte resultado:

Por que eu preciso disto? Ela me ajudará a calcular o comprimento da série. Olhe para a tabela. Você notou que os dias consecutivos pertencem ao mesmo grupo de datas? Por que isso acontece? Se as datas forem consecutivas e você deduzir o número da linha dela, você sempre terá a mesma data. Olhe para isto:

Deduza o número da linha (1) da data (2020-07-01) e você receberá 2020-06-30. É exatamente isso que tenho na tabela.

E depois a próxima fileira. Se você deduzir o número da linha (2) da data (2020-07-02), o resultado será 2020-06-30 novamente! Agora vamos ver o que acontece quando as datas não são consecutivas.

Para a linha 5, o resultado ainda é o mesmo, 2020-06-30. Tudo bem, mas e na fileira seguinte? Deduza a linha número 6 da data (2020-07-08), e o que você recebe? 2020-07-02. Este é agora um novo grupo de datas, o que não é surpreendente, já que 2020-07-08 não é consecutivo a 2020-07-05 em nenhum calendário!

Um pequeno truque, certo? Com relação às datas na coluna date_group, não importa realmente quais as datas que você recebe. Elas servirão apenas como valores, que serão contados na segunda parte da consulta. Conte quantas vezes cada grupo ocorre, e você terá a duração de sua série!

Seleção dos dados do CTE

A segunda parte da consulta seleciona os dados do CTE que eu defini acima:

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

Esta simples declaração SELECT conta o número de filas e mostra os resultados na nova coluna days_streak. Em seguida, seleciona a data mínima e máxima na coluna date_completed com os resultados mostrados nas colunas min_date e max_date, respectivamente. Todos estes dados serão selecionados a partir dos grupos nomeados pelo CTE.

Finalmente, os dados têm que ser agrupados pelo date_group. Por quê? Porque eu não preciso do número total de linhas; eu quero o número de linhas para cada grupo de datas.

Essa é uma maneira de calcular a duração de uma série ...

Este pequeno exemplo deve lhe dar uma idéia de como calcular o comprimento de uma série com SQL. Entretanto, não há uma maneira única de abordá-lo. E, como você já viu, não há uma função SQL simples que lhe dará o que você quer. Seus dados e o que você precisa calcular determinarão seu código SQL. Geralmente é necessário um pequeno truque; sua experiência e conhecimento em SQL o ajudará a decidir qual deles!

O que você quase certamente precisará saber ao calcular o comprimento da série são as funções da janela SQL. Se você ainda não está convencido de que deve aprender as funções de janela, leia esta entrevista com a criadora do curso - ela explica todos os benefícios das funções de janela.

Este artigo lhe mostrou apenas um vislumbre do que são séries cronológicas. Se você estiver interessado em aprender como analisar este tipo específico de dados, LearnSQL tem algumas orientações muito detalhadas.

Se este artigo o ajudou a resolver alguns problemas das séries cronológicas, informe-me na seção de comentários.