Regroupement de périodes chevauchées

Publié le par

Vous avez une table avec une date de début et de fin et vous souhaitez connaître la date mini et la date maxi des périodes qui se chevauchent.

Création de la table et des lignes

Public Function CreationTablePeriode()
   Const cInsert As String = "INSERT INTO tPeriode (DateDebut, DateFin) VALUES "
   With DoCmd
      .SetWarnings False
       
      .RunSQL "CREATE TABLE tPeriode (Id AUTOINCREMENT PRIMARY KEY, DateDebut DATE NOT NULL, DateFin DATE NOT NULL)"
       
      .RunSQL cInsert & "('2012-01-03', '2012-01-03');"
      .RunSQL cInsert & "('2011-12-20', '2012-01-04');"
      .RunSQL cInsert & "('2012-01-04', '2012-01-07');"
      .RunSQL cInsert & "('2012-01-06', '2012-01-10');"
      .RunSQL cInsert & "('2012-05-01', '2012-05-01');"
      .RunSQL cInsert & "('2012-05-02', '2012-05-03');"
      .RunSQL cInsert & "('2012-05-03', '2012-05-03');"
      .RunSQL cInsert & "('2012-05-03', '2012-05-05');"
      .RunSQL cInsert & "('2012-05-04', '2012-05-10');"
     
      .SetWarnings True
   End With
   MsgBox "Création terminée"
End Function

Résultat attendu du jeu de test
Nous remarquons que la période :

  • de la 1ère ligne [03/01/2012-03/01/2012] est chevauchée par celle de la 2ème [20/12/2011-04/01/2012]
  • de la 3ème ligne [04/01/2012-07/01/2012] chevauche celle de la 2ème [20/12/2011-04/01/2012]
  • de la 4ème ligne [06/01/2012-10/01/2012] chevauche celle de la 3ème [04/01/2012-07/01/2012]

Après regroupement, l’étendue de cette première période va du 20/12/2011 au 10/01/2012

La ligne n°5 (01/05/2012-01/05/2012) n’est chevauchée par aucune autre période et les 4 dernières lignes donnent une période du 02/05/2012 au 10/05/2012.

Le résultat attendu est :

  • 20/12/2012 – 10/01/2012
  • 01/05/2012 – 01/05/2012
  • 02/05/2012 – 10/05/2012

La requête
Il existe plusieurs méthodes pour arriver au résultat mais voici la requête la plus performante sous MS Access (et cerise sur le gâteau, visible dans l’éditeur de requête !) :

SELECT      DateDebutAS [DATE Début],
            MIN(DateFin) AS [DATE Fin]
FROM      (
            SELECT  DateDebut
            FROM    tPeriode AS T1
            WHERE   t1.DateDebut <= ALL (
                                          SELECT DateDebut
                                          FROM   tPeriode T2
                                          WHERE  T2.DateDebut = T1.DateDebut
                                        )
            GROUP BY DateDebut
          ) AS T1,
          (
            SELECT  DateFin
            FROM    tPeriode AS T1
            WHERE   t1.DateFin >= ALL (
                                        SELECT DateFin
                                        FROM   tPeriode T2
                                        WHERE  T2.DateFin >= T1.DateDebut
                                               AND
                                               T2.DateDebut <= T1.DateFin
                                      )
            GROUP BY DateFin
          ) AS T2
WHERE     t1.DateDebut <= t2.DateFin
GROUP BY  DateDebut;

Explications
La première sous-requête de la clause FROM (SELECT DateDebut…) retourne la date minimale des périodes chevauchées. En effet, la clause WHERE de cette sous-requête ne conserve que les dates de début qui sont inférieures ou égales aux périodes qui les chevauchent éventuellement.
Prenons par exemple la ligne n°1 (03/01/2012). Sa date de début est-elle <= à la date de début de la période qui la chevauche ? La réponse est non puisque la date de début de la période du 20/12/2011 au 04/01/2012 est inférieure. La date du 03/01/2012 est donc retirée et ainsi de suite pour chaque ligne de la table.
Le 'Group By DateDebut' permet, au cas où, de regrouper les lignes qui ont une même date de début.

La deuxième sous-requête de la clause FROM (SELECT DateFin …) applique le même principe mais pour la date de fin.

A ce niveau là, on a visiblement pas de lien entre la date de début et la date de fin des périodes regroupées… d’autant plus que l’on réalise une jointure croisée entre les dates pour que la requête soit visible dans l’éditeur.

Pour s’en sortir, on retire dans un premier temps les lignes incohérentes qui ont une date de fin < à la date de début (…WHERE t1.DateDebut <= t2.DateFin…)
Et la finesse finale qui consiste à dire que la date de fin de chaque regroupement de périodes est celle qui est la plus petite puisque nous avons retiré dans la clause WHERE précédente les périodes incohérentes et que les périodes restantes ne peuvent se chevaucher.
On ajoute un GROUP BY sur la date de début pour permettre l'utilisation de la fonction MIN() sur la date de fin.

Test de performance
Voici une petite fonction qui va générer 2000 lignes dans la table pour tester la performance de la requête

Public Function TablePeriodeAddLines()
    Dim odb As DAO.Database
    Dim ors As DAO.Recordset
    Dim i As Long
     
    Set odb = CurrentDb
    Set ors = odb.OpenRecordset("select * from tperiode", dbOpenDynaset)
     
    Randomize
    With ors
    For i = 1 To 2000
        .AddNew
        !datedebut = DateAdd("d", Rnd() * 1000 - 500, Date)
        !datefin = !datedebut + Int(Rnd() * 5)
        .Update
    Next i
    .Close
    End With
     
    Set ors = Nothing
    Set odb = Nothing
    MsgBox "Ajout terminée"
End Function

@+

Philippe

Une réflexion au sujet de « Regroupement de périodes chevauchées »

  1. Bonjour et merci, bien pratique!!
    Par contre j’ai du rajouter un niveau de sous requête supplémentaire.Sous SQL Server la requête sort:
    2011-12-20 2012-01-10
    2012-01-03 2012-01-10
    2012-01-04 2012-01-10
    2012-01-06 2012-01-10
    2012-05-01 2012-05-01
    2012-05-02 2012-05-10
    2012-05-03 2012-05-10
    2012-05-04 2012-05-10

    Alors j’ai rajouter

    SELECT MIN(Debut), Fin
    FROM (
    — La requête
    ) T
    GROUP BY T.Fin

Laisser un commentaire