Tirage au sort aléatoire pondéré dans une requête SQL [Résolu]

Signaler
Messages postés
1980
Date d'inscription
dimanche 20 février 2005
Statut
Membre
Dernière intervention
24 septembre 2012
-
Messages postés
1980
Date d'inscription
dimanche 20 février 2005
Statut
Membre
Dernière intervention
24 septembre 2012
-
Bonjour à tous,

je vous soumets une petite colle ! du genre qui fait cogiter, sauf si vous êtes superman.

Il s'agit de trouver une requête SQL qui ferait un tirage au sort pondéré. Je ne demande pas de script php qui fasse ça, je connais déjà bien. Ce que je veux c'est vraiment que SQL me renvoie le résultat.

Dans ma table j'ai des lignes, et chaque ligne contient dans un champ son coefficient de pondération, c'est-à-dire le pourcentage de chance qu'elle a d'être tirée.

Par exemple

# de ligne         #coefficient
Ligne 1 :          34
Ligne 2 :          33
Ligne 3 :          33

Jusqu'à présent je n'ai rien trouvé. Je me dis que la seule solution c'est de rajouter deux champs, qui me permettront d'utiliser un nombre aléatoire avec RAND tiré entre 1 et 100.
Exemple des lignes :

# de ligne         #coefficient     #debutdeplage         #findeplage
Ligne 1 :          34                   1                             34

Ligne 2 :          33                   35                           57

Ligne 3 :          33                   58                           100

Si vous avez une idée n'hésitez pas !
Merci

6 réponses

Messages postés
12303
Date d'inscription
mardi 10 février 2004
Statut
Modérateur
Dernière intervention
30 juillet 2012
36
salut

on va prendre un truc un peu moins aleatoire :

# de ligne #coefficient
Ligne 1 : 20
Ligne 2 : 20
Ligne 3 : 60

mysql> CREATE DATABASE test
-> ;
Query OK, 1 row affected (0.11 sec)

mysql> USE test
Database changedmysql> CREATE TABLE foo (id int unsigned auto_increment, ligne char(5), pourcent int, primary key(id));
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO foo(ligne, pourcent) VALUES ("l1", 20), ("l2", 20), ("l3", 60);
Query OK, 3 rows affected (0.04 sec)
Records: 3 Duplicates: 0 Warnings: 0

mysql> SELECT * FROM foo;
+----+-------+----------+
| id | ligne | pourcent |
+----+-------+----------+
| 1 | l1 | 20 |
| 2 | l2 | 20 |
| 3 | l3 | 60 |
+----+-------+----------+
3 rows in set (0.00 sec)

mysql> SELECT ligne, (SELECT SUM(pourcent) FROM foo AS b WHERE b.id <= a.id) FROM foo AS a;
+-------+---------------------------------------------------------+
| ligne | (SELECT SUM(pourcent) FROM foo AS b WHERE b.id <= a.id) |
+-------+---------------------------------------------------------+
| l1 | 20 |
| l2 | 40 |
| l3 | 100 |
+-------+---------------------------------------------------------+
3 rows in set (0.00 sec)

ensuite, en bidouillant un peu, on arrive a faire ca :

mysql> SET @random = ROUND(RAND() * 100);
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @random AS rand, (SELECT ligne FROM (SELECT id, ligne, (SELECT SUM(pourcent) FROM foo AS b WHERE b.id <= a.id) AS percent FROM foo AS a) AS sub WHERE percent > rand ORDER BY id ASC LIMIT 1) AS ligne;
+------+-------+
| rand | ligne |
+------+-------+
| 99 | l3 |
+------+-------+
1 row in set (0.00 sec)


chaque fois que j'ai tente de combiner les deux, mysql fout sa merde...
Messages postés
1980
Date d'inscription
dimanche 20 février 2005
Statut
Membre
Dernière intervention
24 septembre 2012
5
Je me rends compte que mon exemple est idiot puisque les 3 ont la même chance d'être tiré ou presque : 1/3, mais vous avez compris ce que je voulais dire
Messages postés
1980
Date d'inscription
dimanche 20 février 2005
Statut
Membre
Dernière intervention
24 septembre 2012
5
Hello et merci de t'être penché sur le problème !
C'est bizarre, mysql me met une erreur lorsque je veux faire ta deuxième requête select. Je suis en train de mettre la mise à jour de mysql, peut-être qu'il faut la dernière version pour pouvoir la faire.
En tout cas c'est très intéressant ton idée et je travaillerai sur cette base demain, là je dois partir. Je vous tiendrai au courant.
Merci encore et bonne soirée
Messages postés
12303
Date d'inscription
mardi 10 février 2004
Statut
Modérateur
Dernière intervention
30 juillet 2012
36
mysql> SELECT VERSION();
+--------------------+
| VERSION() |
+--------------------+
| 5.0.51a-3ubuntu5.1 |
+--------------------+
1 row in set (0.00 sec)
Messages postés
1980
Date d'inscription
dimanche 20 février 2005
Statut
Membre
Dernière intervention
24 septembre 2012
5
Hello,

effectivement avec la 5.0.51 ça marche mieux qu'avec la 5.0.27 ! Reste à savoir si je peux disposer de cette version sur mon hébergement qui est mutualisé pour l'instant.
Bon alors félicitations, ça marche nickel !

Pour l'histoire de combiner les deux, oui j'imagine que le problème c'est que mettre un alias au RAND ne suffit pas pour en faire une constante et qu'elle est donc recalculée à chaque sous-requête.

Merci beaucoup pour le coup de main, je n'y aurais pas pensé !
à+
Messages postés
1980
Date d'inscription
dimanche 20 février 2005
Statut
Membre
Dernière intervention
24 septembre 2012
5
Pour info à ceux qui tomberont sur ce message, on ne peut vraiment pas faire confiance à RAND de MySQL pour trouver un nombre aléatoire. Les résultats sont complètement faussés par rapport aux pondérations. Avec un random fourni par php par contre, tout marche bien.