Tirage au sort aléatoire pondéré dans une requête SQLRésolu

Question

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

coucou747 · Accepted Answer

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...

Evangun · Answer

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

Evangun · Answer

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

coucou747 · Answer

mysql> SELECT VERSION();
+--------------------+
| VERSION()          |
+--------------------+
| 5.0.51a-3ubuntu5.1 |
+--------------------+
1 row in set (0.00 sec)

Evangun · Answer

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é !
à+

Evangun · Answer

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.

Tirage au sort aléatoire pondéré dans une requête SQL

6 réponses

Votre réponse

Discussions similaires