Detectar períodos consecutivos no SQL Server

Uma tabela de atividade guarda observações individuais. A pergunta de negócio costuma pedir quantos dias um cliente ficou ativo sem interrupção. Antes de escolher uma função de janela, é necessário definir continuidade: linhas vizinhas não representam necessariamente dias consecutivos.

Normalizar a observação diária

O exemplo conta uma observação por cliente e data. Vários eventos no mesmo dia contam uma vez. Sem normalização, datas duplicadas podem distorcer grupos e tamanho das sequências. A entrada contém propositalmente uma duplicata, um dia ausente e outro cliente.

CREATE TABLE #Activity(CustomerId int, ActivityDate date);
INSERT #Activity VALUES
(1,'20230101'),(1,'20230102'),(1,'20230102'),
(1,'20230104'),(1,'20230105'),(2,'20230102');
;WITH days AS
(SELECT DISTINCT CustomerId, ActivityDate FROM #Activity),
previous AS
(SELECT *, LAG(ActivityDate) OVER
 (PARTITION BY CustomerId ORDER BY ActivityDate) AS PrevDate FROM days),
flags AS
(SELECT *, CASE WHEN DATEDIFF(day, PrevDate, ActivityDate)=1
 THEN 0 ELSE 1 END AS NewIsland FROM previous),
groups AS
(SELECT *, SUM(NewIsland) OVER
 (PARTITION BY CustomerId ORDER BY ActivityDate
  ROWS UNBOUNDED PRECEDING) AS IslandId FROM flags)
SELECT CustomerId, MIN(ActivityDate) AS StartDate,
 MAX(ActivityDate) AS EndDate, COUNT(*) AS ActiveDays
FROM groups GROUP BY CustomerId, IslandId
ORDER BY CustomerId, StartDate;

A primeira CTE elimina pares repetidos. LAG encontra a data distinta anterior dentro de cada cliente. Um indicador marca a primeira linha ou uma diferença diferente de um dia. A soma acumulada transforma indicadores em grupos, e a agregação calcula início, fim e dias ativos.

Para o cliente 1, o resultado deve conter janeiro 1 a janeiro 2 e janeiro 4 a janeiro 5. O cliente 2 possui uma ilha independente de um dia. COUNT(*) agora representa dias porque essa granularidade foi estabelecida inicialmente; caso contrário contaria eventos. O frame ROWS explícito deixa clara a acumulação.

Especificar o calendário de negócio

Dias corridos, dias úteis e observações consecutivas são definições diferentes. Sexta e segunda interrompem uma sequência de dias corridos, mas podem continuar uma sequência útil. Use então uma tabela calendário mantida com número consecutivo de dia útil e compare esse número em vez de adicionar um dia corrido.

Se a fonte contém timestamps, escolha o fuso do relatório antes de extrair datas. Converter UTC pode levar um evento para o dia local anterior ou seguinte. Mudanças de horário alteram horas decorridas; vinte e quatro horas nem sempre equivalem a datas locais consecutivas. Preserve o timestamp original para auditoria.

O filtro temporal cria outra fronteira. Uma sequência visível em primeiro de fevereiro pode ter começado em janeiro. Decida entre apresentar um período cortado ou a sequência completa. Descobrir o começo real exige histórico adicional ou estado anterior mantido, não apenas uma mudança no rótulo.

Diferenciar intervalos sobrepostos

Unir intervalos exige outra comparação. Considere 1 a 10, 2 a 3 e 9 a 12. Comparar o terceiro início somente com o término imediatamente anterior inventa uma lacuna. Use o máximo acumulado de todos os términos anteriores e defina se as extremidades são inclusivas ou exclusivas.

Para dias de atividade, um índice começando por cliente e data pode ajudar a ordenar e deduplicar. O plano real determina se ainda haverá sort. Em históricos grandes, examine linhas processadas e spills. Filtre cedo os clientes necessários, sem perder o histórico exigido para sequências completas.

Teste dia único, duplicatas, vários clientes, lacuna e sequência atravessando o início do relatório. Acrescente eventos atrasados: inserir o dia ausente pode unir duas ilhas existentes. Identificadores calculados não se tornam automaticamente chaves permanentes de negócio. Confira também se relatórios já armazenados devem ser recalculados ou preservar a fotografia original. A correção depende dessas regras e testes, não apenas da elegância do SQL.

Referências técnicas: Microsoft Learn: LAG · Microsoft Learn: OVER.