Plages continues (îlots)

Le problème qui semble exiger un état mémorisé de ligne en ligne, et qui se résout par un regroupement.

≈ 3 min de lectureLecture

Sur cette page

Énoncé
Jeu de données
Procédural contre ensembliste
1. Procédural
2. Ensembliste
Résultat attendu
Pourquoi l'ensembliste est préférable
Pièges
Variantes
Vérifier l'équivalence
Versions de SQL Server

Énoncé#

Pour chaque utilisateur, lister les plages de jours consécutifs pendant lesquels il s'est connecté (début, fin, nombre de jours).

Jeu de données#

CREATE TABLE dbo.Connexions
(
    ConnexionId   int IDENTITY(1,1) PRIMARY KEY,
    UtilisateurId int          NOT NULL,
    DateConnexion datetime2(0) NOT NULL
);

INSERT INTO dbo.Connexions (UtilisateurId, DateConnexion)
VALUES (1, '2026-03-01 08:00'), (1, '2026-03-02 09:30'), (1, '2026-03-03 07:45'),
       (1, '2026-03-03 18:10'),                       -- 2e connexion le même jour
       (1, '2026-03-06 10:00'), (1, '2026-03-07 11:20'),
       (2, '2026-03-01 12:00'), (2, '2026-03-05 12:00');

Procédural contre ensembliste#

Procédural#

DECLARE @R TABLE (UtilisateurId int, Debut date, Fin date);
DECLARE @U int, @J date, @UPrec int = NULL, @Debut date, @Fin date;

DECLARE c CURSOR LOCAL FAST_FORWARD FOR
    SELECT DISTINCT UtilisateurId, CAST(DateConnexion AS date) AS Jour
    FROM   dbo.Connexions
    ORDER BY UtilisateurId, Jour;
OPEN c;
FETCH NEXT FROM c INTO @U, @J;
WHILE @@FETCH_STATUS = 0
BEGIN
    IF @UPrec IS NULL OR @U <> @UPrec OR @J <> DATEADD(DAY, 1, @Fin)
    BEGIN
        IF @UPrec IS NOT NULL INSERT INTO @R VALUES (@UPrec, @Debut, @Fin);
        SET @Debut = @J;
    END;
    SET @Fin = @J;
    SET @UPrec = @U;
    FETCH NEXT FROM c INTO @U, @J;
END;
IF @UPrec IS NOT NULL INSERT INTO @R VALUES (@UPrec, @Debut, @Fin);
CLOSE c;
DEALLOCATE c;

SELECT * FROM @R ORDER BY UtilisateurId, Debut;

Ensembliste#

WITH J AS
(
    SELECT DISTINCT UtilisateurId, CAST(DateConnexion AS date) AS Jour
    FROM   dbo.Connexions
),
G AS
(
    SELECT UtilisateurId, Jour,
           DATEADD(DAY, -ROW_NUMBER() OVER (PARTITION BY UtilisateurId ORDER BY Jour), Jour) AS Groupe
    FROM   J
)
SELECT UtilisateurId,
       MIN(Jour) AS Debut,
       MAX(Jour) AS Fin,
       COUNT(*)  AS NbJours
FROM   G
GROUP BY UtilisateurId, Groupe
ORDER BY UtilisateurId, Debut;

Résultat attendu#

UtilisateurId	Début	Fin	NbJours
1	2026-03-01	2026-03-03	3
1	2026-03-06	2026-03-07	2
2	2026-03-01	2026-03-01	1
2	2026-03-05	2026-03-05	1

Pourquoi l'ensembliste est préférable#

L'astuce : pour des jours consécutifs, la différence entre la date et son rang (ROW_NUMBER) est constante à l'intérieur d'une plage et change dès qu'il y a un trou. Cette valeur identifie donc la plage.
Le problème « séquentiel » (« je me souviens de la ligne précédente ») se ramène à un regroupement : l'état n'est plus porté par des variables, il est dans les données.
La version procédurale doit gérer elle-même les transitions (début, fin, changement d'utilisateur) ; la moindre inattention y produit un bug de bord.

Pièges#

Doublons de jours : la CTE J utilise DISTINCT pour ne retenir qu'une ligne par jour et par utilisateur ; sans cela, le rang avance sans que la date n'avance et la plage est cassée.
Type de la date : travailler au grain voulu (ici le jour) avant de calculer le rang.
Tolérance aux trous : la technique stricte détecte des jours consécutifs. Pour tolérer un écart, utiliser la variante avec LAG.

Variantes#

-- Plages tolérant un jour manquant (écart ≤ 2 jours entre deux connexions)
WITH J AS (SELECT DISTINCT UtilisateurId, CAST(DateConnexion AS date) AS Jour FROM dbo.Connexions),
P AS (SELECT UtilisateurId, Jour, LAG(Jour) OVER (PARTITION BY UtilisateurId ORDER BY Jour) AS Prec FROM J),
F AS (SELECT UtilisateurId, Jour,
             CASE WHEN Prec IS NULL OR DATEDIFF(DAY, Prec, Jour) > 2 THEN 1 ELSE 0 END AS Rupture
      FROM P),
G AS (SELECT UtilisateurId, Jour,
             SUM(Rupture) OVER (PARTITION BY UtilisateurId ORDER BY Jour ROWS UNBOUNDED PRECEDING) AS Groupe
      FROM F)
SELECT UtilisateurId, MIN(Jour) AS Debut, MAX(Jour) AS Fin
FROM   G
GROUP BY UtilisateurId, Groupe;

Vérifier l'équivalence#

Un changement de méthode ne vaut que si le résultat est identique. Comparer dans les deux sens ; aucune ligne ne doit sortir.

-- À exécuter dans le même lot que la version procédurale (la variable @R doit exister)
WITH J AS (SELECT DISTINCT UtilisateurId, CAST(DateConnexion AS date) AS Jour FROM dbo.Connexions),
G AS (SELECT UtilisateurId, Jour, DATEADD(DAY, -ROW_NUMBER() OVER (PARTITION BY UtilisateurId ORDER BY Jour), Jour) AS Groupe FROM J),
E AS (SELECT UtilisateurId, MIN(Jour) AS Debut, MAX(Jour) AS Fin FROM G GROUP BY UtilisateurId, Groupe)
SELECT UtilisateurId, Debut, Fin FROM @R
EXCEPT
SELECT UtilisateurId, Debut, Fin FROM E;
-- Puis l'inverse. Aucune ligne attendue.

Versions de SQL Server#

ROW_NUMBER : SQL Server 2005 ; LAG et cadres ROWS (variante) : SQL Server 2012.