TOP 3 aléatoire avec regroupement

Publié le par

Un internaute souhaitait extraire aléatoirement 3 Id d’une table pour chaque personne. C’est possible !

Création de la table et insertion des données
La table tTop contient une colonne Id (clef primaire) et le nom de la personne

Public Function CreationTableTop()
   Const cInsert As String = "INSERT INTO tTop (Nom) VALUES "
   Dim i As Long, Maxi As Long
   Dim aNoms As Variant
 
   With DoCmd
      .SetWarnings False
 
      .RunSQL "CREATE TABLE tTop (Id AUTOINCREMENT CONSTRAINT PrimaryKey PRIMARY KEY, Nom CHAR);"
 
      'VBA.Array impose que le tableau débute à l'indice 0 (Array de la librairie VBA)
     'Autre solution : OPTION BASE 0 en tête du module
     aNoms = VBA.Array("Albert", "Marcel", "Bernard")
      Maxi = UBound(aNoms) + 1
 
      Randomize
      For i = 1 To 50
         .RunSQL cInsert & "('" & aNoms(Int(Rnd() * Maxi)) & "');"
      Next i
      .RunSQL cInsert & "('Dominique');"
 
      .SetWarnings True
   End With
   MsgBox "Création terminée"
End Function

Difficultés
La première difficulté est d’obtenir un top 3 pour chaque Nom. Une solution consiste à filtrer les enregistrements en imposant les Id par Nom grâce à une sous-requête corrélée (WHERE T.Nom=T1.Nom) dans la clause IN :

SELECT  *
FROM    tTop AS T1
WHERE   T1.Id IN
           (SELECT TOP 3 Id
            FROM   tTop T
            WHERE  T.Nom = T1.Nom
            ORDER BY T.Id)
ORDER BY T1.Nom,
         T1.Id;

Le problème du Top 3 par Nom étant résolu, le plus compliqué reste à faire… Comment obtenir aléatoirement des Id pour chaque Nom ?
En effet, on souhaite suffisamment d’aléatoire pour que la liste des Id différe d’un appel à l’autre de la requête mais pas trop non plus pour que la sous-requête sorte la même liste des Id pour chaque T1.Id.
L’idée de base est d’utiliser la fonction RND(T.Id) dans la clause ORDER BY pour trier aléatoirement les enregistrements en sachant que T.Id est unique :

SELECT  *
FROM    tTop AS T1
WHERE   T1.Id IN
           (SELECT TOP 3 Id
            FROM   tTop T
            WHERE  T.Nom = T1.Nom
            ORDER BY Rnd(T.Id),
                     t.Id
            )
ORDER BY T1.Nom,
         T1.Id;

Comme vous pouvez le constater, on perd la notion de Top 3 par Nom car pour chaque T1.Id, la sous-requête recalcule une liste d’Id différente…

La requête SQL finale

SELECT  *
FROM    tTop AS T1
WHERE   T1.Id IN
           (SELECT TOP 3 Id
            FROM   tTop T
            WHERE  T.Nom = T1.Nom
            ORDER BY Rnd(-( T.Id + Timer())),
                     t.Id
            )
ORDER BY T1.Nom,
         T1.Id;

Explications
On s’appuie sur deux particularités d’Access :

La première est que si l’on passe un nombre négatif en argument à la fonction Rnd(), elle retourne toujours le même nombre aléatoire. Ceci permet d’avoir une liste constante d’Id pour chaque Nom.

La deuxième va nous permettre d’obtenir une liste différente d’Id entre chaque appel de la requête principale. Pour ajouter un zeste d’aléatoire on utilise la fonction Timer() en argument de la fonction Rnd(). Heureusement, Access ne met pas à jour le timer() pendant tout le temps d’exécution de la requête. La valeur du timer au démarrage de la requête est conservé jusqu’à la fin de son exécution ou de sa prochaine actualisation (dans un formulaire prendre un recordset avec snapshot pour éviter une mise à jour via modification du jeu d’enregistrements sous-jacent).

Remarques
L’ajout d’une deuxième colonne de tri (T.Id) dans la sous-requête permet de s’assurer que le top 3 retournera toujours que 3 lignes au maximum même si des ex-aequo subsistent après ORDER BY RND(-(T.Id+Timer())).

La présence de la sous-requête entraîne un temps d’exécution long de la requête qui sera donc réservée pour des tables ayant peu de lignes (~1000).
@+

Philippe

Laisser un commentaire