Picking random row with certain chance (weight) in MySQL

Question

I searched this for a while but results are just confusing my head because I am quite new on MySQL.

I have a table with these 4 columns: AUTO_INCREMENT ID, NAME, TYPE, CHANCE so rows look like this:

1, NOTHING, NO, 35
1, VERSICOLOR, TREE, 35
3, DIVERSIPES, TREE, 35
4, AMAZONICA, TREE, 35
5, EMILIA, GROUND, 25
6, BOEHMI, GROUND, 25
7, SMITHI, GROUND, 25
8, METALLICA, SKY, 5
9, REGALIS, SKY, 5

Note: Those are simple examples, there will be x100 like them. What I need to do is picking one row from this table with chances as shown in CHANCE column.

Meaning; I need to pick one row from 9 of them and results can be "VERSICOLOR, DIVERSIPES, AMAZONICA or NOTHING with 35% chance" or "EMILIA, BOEHMI or SMITHI with 25% chance" or "METALLICA or REGALIS with %5 chance". So this query will probably give me the result of "VERSICOLOR, DIVERSIPES, AMAZONICA or NOTHING" because it has 35% chance or maybe I am gonna be lucky and I will get the "METALLICA or REGALIS" :)

Basicly there are 3 group of types, GROUND, TREE and SKY. What I want to do is getting only one result from all of these. GROUND, TREE or SKY type item with certain chances but to be certain, I dont want one for each group, I want only one result, it can be item of GROUND, TREE or SKY type. I hope that I did explain myself. Regards.

score 0 · Answer 1 · answered Mar 20 '14 at 16:39

0

There is probably a more elegant solution and one that doesn't assume your percentages add to 100 - but this may work:

Example http://sqlfiddle.com/#!2/ec699/1

SELECT *
FROM (
  SELECT id, name, type, chance
    , @value + 1 AS lowval
    , @value := @value + chance AS hival
  FROM tbl
  JOIN (SELECT @value := 0) AS foo) AS bar
JOIN (SELECT FLOOR(1 + RAND()*99) AS guesser) AS bar2
  ON guesser  BETWEEN lowval and hival;

answered Mar 20 '14 at 16:39

AgRizzo

5,261
1
13
28

I tried to be more specific. I tried your suggestion but it does not work when you multiply the options. Please check the question again, I made some corrections. – user3442854 Mar 20 '14 at 18:07

score 0 · Answer 2 · edited May 23 '17 at 10:31

This problem comes up outside SQL. I posted a general solution here, generate random numbers within a range with different probabilities.

With the first set of numbers you used (70%, 25%, 5%) fill three urns with 100 balls each. Urn 0 all balls are red. Urn 1 75 of the balls are green, 25 balls are red. Finally, urn 2 will have 15 blue balls and 85 red balls. Now pick a random urn, each having probability 1/3, and pick a random ball. Using this scheme the probability of getting a red ball is 0.75, the probability of a green ball is 0.25 and the probability of a blue ball is 0.05.

Picking random row with certain chance (weight) in MySQL

2 Answers2