Choose a random value based on probability of probability

Question

How to select a random row from the database based on the probability probability assigned to each row.
Example:

Make        Chance  Value
ALFA ROMEO  0.0024  20000
AUDI        0.0338  35000
BMW         0.0376  40000
CHEVROLET   0.0087  15000
CITROEN     0.016   15000
........

How to choose an arbitrary name and its value based on the probability that it should choose.

Will the combination rand()and work ORDER BY? If so, what is the best way to do this?

Answer 1

You can do this using rand()and then using the total amount. Assuming they are up to 100%:

select t.*
from (select t.*, (@cumep := @cumep + chance) as cumep
      from t cross join
           (select @cumep := 0, @r := rand()) params
     ) t
where @r between cumep - chance and cumep
limit 1;

Notes:

• rand() , . rand() .
• , . limit 1 1.
• , , cumep > @r.
• - .
• , 1, .