All geek questions in one place

MySQL chooses random with probability

Let's say I have a database table with an integer field - name it "flavor", and the values ​​for this field can only be from 1 to 10.

Is there a way to select one random row from the database with a 20% chance that it will taste six, a 30% chance that it will be two, and a 50% chance that it will be one?

Apologies for the late reply - many thanks for the help. Eugene's answer seems to best cover what I need; I know the dangers of ORDER BY rand (), but the application I am writing will not work on a large data source or should support many concurrent users. So I will go with him and take a performance hit.

+6
source share
2 answers
SELECT IF(@rnd<0.5,1,IF(@rnd<0.8,2,6)) AS rndflavour FROM (SELECT @rnd:=rand()) AS rndinit;

Gives you a scent with the requested probabilities.

 SELECT * FROM tablename ORDER BY rand() LIMIT 1

gives you one random string. Now we will split it:

 SELECT tablename.* FROM tablename INNER JOIN ( SELECT IF(@rnd<0.5,1,IF(@rnd<0.8,2,6)) AS rndflavour FROM (SELECT @rnd:=rand()) AS rndinit ) AS rndview ON rndview.rndflavour=tablename.flavour ORDER BY rand() LIMIT 1
+4
source

I propose to split the task

1 write some logic that assigns a value to a variable: your_flavour with the corresponding capabilities

2

 select * from YOUR_TABLE where FLAVOUR = :your_flavour order by rand() limit 1
0
source

Source: https://habr.com/ru/post/905547/

More articles:


All Articles