Extraire les séries de valeurs

Publié le par

Un forumeur souhaitait connaître les séries de valeurs pour chaque famille et chaque donnee.
Une requête permet de répondre à la question…

Création de la table et insertion des données

Public Function CreationFamille()
   Const cTable As String = "tFamilles"
   With DoCmd
      .SetWarnings False
 
      .RunSQL "CREATE TABLE " & cTable & _
            " (Famille LONG, Donnee VARCHAR(15), Valeur INTEGER," & _
            " CONSTRAINT PrimaryKey PRIMARY KEY (Famille, Donnee, Valeur));"
 
      .SetWarnings True
   End With
 
   InsertionFamilles
End Function
 
Public Sub InsertionFamilles()
   Const cNombreFamille As Integer = 1
   Const cTable As String = "tFamilles"
   Const cInsert As String = "INSERT INTO " & cTable & " VALUES "
   Dim i As Integer
 
   With DoCmd
      .SetWarnings False
 
      i = Nz(DMax("famille", cTable)) + 1
      For i = i To i + cNombreFamille - 1
         .RunSQL cInsert & "(" & i & ", 'div', 1302);", False
         .RunSQL cInsert & "(" & i & ", 'div', 1303);", False
         .RunSQL cInsert & "(" & i & ", 'div', 1308);", False
         .RunSQL cInsert & "(" & i & ", 'dov', 1290);", False
         .RunSQL cInsert & "(" & i & ", 'dov', 1292);", False
         .RunSQL cInsert & "(" & i & ", 'dov', 1293);", False
         .RunSQL cInsert & "(" & i & ", 'dov', 1294);", False
         .RunSQL cInsert & "(" & i & ", 'dov', 1295);", False
         .RunSQL cInsert & "(" & i & ", 'dov', 1296);", False
         .RunSQL cInsert & "(" & i & ", 'dov', 1297);", False
      Next i
 
      .SetWarnings True
   End With
   MsgBox "Création terminée"
End Sub

Résultat attendu

famille  donnee  mini  maxi
1        div     1302   1303
1        div     1308   1308
1        dov     1290   1290
1        dov     1292   1297

Le groupe composé de la famille 1 et de la donnee ‘div’ possède 2 séries de valeurs dont la première est composée de 2 nombres qui se suivent (1302 à 1303) et la seconde n’est composée que d’un seul nombre (1308).

La requête SQL

SELECT  T.famille,
        T.donnee,
        MIN(T.mini) AS mini,
        T.maxi AS maxi
 
FROM    (SELECT     t1.famille,
                    t1.donnee,
                    t1.valeur AS mini,
                    MAX(t2.valeur) AS maxi
         FROM       tFamilles AS T1
         INNER JOIN tFamilles AS T2
                    ON (t1.valeur <= t2.valeur)
                       AND
                       (t1.famille = t2.famille)
                       AND
                       (t1.donnee = t2.donnee)
         WHERE      (SELECT COUNT(*)
                     FROM   tfamilles T
                     WHERE  t.famille = t1.famille
                            AND
                            t.donnee = t1.donnee
                            AND
                            t.valeur BETWEEN t1.valeur AND t2.valeur
                     ) = (t2.valeur - T1.valeur + 1 )
         GROUP BY   t1.famille,
                    t1.donnee,
                    t1.valeur
        ) AS T
GROUP BY T.famille,
         T.donnee,
         T.maxi
ORDER BY T.famille,
         T.donnee,
         MIN(T.mini);

Explications
On part d’une auto jointure interne sur la table (…FROM tFamilles T1 INNER JOIN tFamilles T2…) qui nous permettra de confronter chaque valeur de tFamilles aux autres (un peu comme deux boucles For/Next imbriquées).
Les contraintes de jointure (…ON t1.valeur<=t2.valeur AND t1.famille=t2.famille AND t1.donnee=t2.donnee…) limitent les valeurs de T2 testées pour chaque valeur de T1 puisque l’on souhaite connaître les séries par famille et donnee. De plus, on impose que les valeurs de T1 soient inférieures ou égales à celles de T2 pour faciliter la suite…

La clause WHERE contient une sous-requête (…SELECT COUNT(*)…) qui compte le nombre de lignes de la table tFamille entre T1.Valeur et T2.Valeur pour la famille et la donnee courante (…t.famille=t1.famille and t.donnee=t1.donnee…). Le nombre de lignes issues de cette sous-requête est comparé au nombre de lignes attendues entre T1.Valeur et T2.Valeur (Exemple : 1302 – 1300 + 1 = 3 valeurs [1300,1301,1302]) pour que la série entre ces deux valeurs soit complète.
Si les deux nombres sont égaux, la série est complète et on la conserve.

A ce niveau là, on se retrouve avec toutes les séries intermédiaires entre le minimum et la maximum de chaque série complète entre t1.Valeur et T2.Valeur.
Exemple : Pour la série complète 1300 à 1302 on obtient aussi les séries intermédiaires 1300 à 1300, 1300 à 1301, 1300 à 1302, 1301 à 1301, 1301 à 1302 et 1302 à 1302.

Il reste donc à trouver le minimum et la maximum de chaque série.
Pour ce faire, on regroupe les données par famille, donnee et T1.Valeur (la valeur basse de chaque série) puis par la fonction d’agrégation MAX(T2.Valeur) on retient la valeur maximale des séries qui commencent par T1.valeur. Dans notre exemple, on réduit donc les séries à : 1300 à 1302, 1301 à 1302 et 1302 à 1302.

Pour déterminer la valeur minimale des séries, on procède sur le même principe en regroupant par famille, donnee et maxi (valeur haute de chaque série que l’on vient de déterminer). La fonction d’agrégation MIN(T1.Valeur) donnera le minimum de chaque série, soit 1300 dans notre exemple.

Performance
Les 3 colonnes appartiennent à la clef primaire ce qui permet à Access d’utiliser un index qui optimise la requête.

Si vous souhaitez augmenter le nombre de lignes de la table, il suffit de modifier la valeur de la constante ‘cNombreFamille’ (100 par exemple) de la fonction InsertionFamilles() et de la relancer. Eviter d’insérer trop de lignes à la fois car RunSQL n’est pas très rapide…

@+

Philippe

Laisser un commentaire