Détecter les périodes consécutives dans SQL Server

Une table d'activité stocke des observations individuelles. La question métier demande souvent combien de jours un client est resté actif sans interruption. Il faut donc définir la continuité avant de choisir une fonction de fenêtre : deux lignes voisines ne représentent pas forcément deux jours consécutifs.

Normaliser la journée observée

L'exemple compte une observation par client et date civile. Plusieurs événements le même jour comptent une seule fois. Sans cette normalisation, les dates dupliquées peuvent fausser les groupes et la longueur des séries. Les données contiennent volontairement un doublon, un jour absent et un autre client.

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;

La première CTE déduplique les couples client-date. LAG retrouve ensuite la date distincte précédente pour chaque client. Un indicateur marque le début ou un écart différent d'un jour. La somme cumulée transforme ces indicateurs en groupes ; l'agrégation finale donne début, fin et nombre de jours actifs.

Pour le client 1, les îlots attendus couvrent les 1er et 2 janvier puis les 4 et 5 janvier. Le client 2 possède son propre îlot d'une journée. COUNT(*) signifie maintenant jours parce que cette granularité a été établie au départ. Sinon il compterait les événements. Le cadre ROWS explicite rend le cumul non ambigu.

Définir le calendrier métier

Jours civils, jours ouvrés et observations consécutives sont trois définitions différentes. Vendredi puis lundi interrompt une série civile, mais peut prolonger une série ouvrée. Utilisez alors une table calendrier entretenue avec un numéro consécutif de jour ouvré, et comparez ce numéro plutôt qu'une addition d'un jour civil.

Si la source contient des horodatages, choisissez le fuseau du rapport avant d'extraire la date. Une conversion depuis UTC peut déplacer un événement sur la veille ou le lendemain local. Les changements d'heure modifient la durée écoulée : vingt-quatre heures ne sont pas toujours équivalentes à deux dates locales consécutives. Conservez l'horodatage original pour les vérifications.

Le filtre du rapport ajoute une autre limite. Une série visible au 1er février peut avoir commencé en janvier. Décidez si le rapport montre une période tronquée ou la série complète. Retrouver le véritable début nécessite davantage d'historique ou un état antérieur maintenu. Modifier seulement le libellé ne suffit pas.

Reconnaître les autres problèmes

Fusionner des intervalles chevauchants demande un calcul différent. Prenez les intervalles 1 à 10, 2 à 3 et 9 à 12. Comparer le troisième début au seul deuxième terme crée un faux trou. Il faut comparer au maximum cumulé de toutes les fins précédentes et préciser si les bornes sont incluses ou exclues.

Pour les journées, un index commençant par client et date peut aider la déduplication et l'ordre. Le plan réel dira si un tri subsiste. Sur un long historique, examinez lignes traitées et débordements mémoire. Filtrez tôt les clients concernés, tout en conservant l'historique indispensable aux séries complètes.

Testez journée unique, doublons, plusieurs clients, trou et série traversant la limite du rapport. Ajoutez une arrivée tardive : insérer la journée manquante peut fusionner deux îlots existants. Leurs identifiants calculés ne sont donc pas nécessairement des clés métier permanentes. Vérifiez aussi la politique de recalcul des rapports déjà enregistrés. La fiabilité vient du contrat calendaire et de ces tests, pas uniquement de la concision de la requête.

Références techniques: Microsoft Learn: LAG · Microsoft Learn: OVER.