从MySQL中选择随机行(有可能) [英] Select random row from MySQL (with probability)

问题描述

我有一个MySQL表，该表有一个称为cur_odds的行，该行是一个百分比数，该行将被选中的概率为百分比.例如，当您执行100次查询时，如何进行查询才能以大约该频率实际选择行?

I have a MySQL table that has a row called cur_odds which is a percent number with the percent probability that that row will get selected. How do I make a query that will actually select the rows in approximately that frequency when you run through 100 queries for example?

我尝试了以下操作，但是概率为0.35的行最终在大约60-70％的时间内被选中.

I tried the following, but a row that has a probability of 0.35 ends up getting selected around 60-70% of the time.

SELECT * FROM table ORDER BY RAND()*cur_odds DESC

表中所有cur_odds的值正好相加为1.

All the values of cur_odds in the table add up to 1 exactly.

推荐答案

如果cur_odds很少更改，则可以实现以下算法:

If cur_odds is changed rarely you could implement the following algorithm:

1)创建另一列prob_sum，为此

1) Create another column prob_sum, for which

prob_sum [0]:= cur_odds [0]

prob_sum[0] := cur_odds[0]

表示1< = i< = row_count-1:

for 1 <= i <= row_count - 1:

prob_sum [i]:= prob_sum [i-1] + cur_odds [i]

prob_sum[i] := prob_sum[i - 1] + cur_odds[i]

2)生成一个从0到1的随机数.

2) Generate a random number from 0 to 1:

rnd:= rand(0,1)

rnd := rand(0,1)

3)找到prob_sum > rnd所在的第一行(如果在prob_sum上创建BTREE索引，则查询应该运行得更快):

3) Find the first row for which prob_sum > rnd (if you create a BTREE index on the prob_sum, the query should work much faster):

创建索引prob_sum_ind ON< table>(prob_sum);

CREATE INDEX prob_sum_ind ON <table> (prob_sum);

SET @rnd:= RAND();

SET @rnd := RAND();

在< table>中选择MIN(prob_sum)，prob_sum> @rnd;

SELECT MIN(prob_sum) FROM <table> WHERE prob_sum > @rnd;

这篇关于从MySQL中选择随机行(有可能)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！