从MySQL中随机选择行（带概率）

Question

我有一个MySQL表，其中有一行叫做cur_odds，它是一个百分数，表示该行被选中的概率。我该如何查询以实际频率选择行，例如运行100个查询时？我尝试了以下方法，但概率为0.35的行最终被选择的次数约为60-70％。

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

表格中所有cur_odds的值加起来恰好为1。

- James Simpson

---

ORDER BY RAND() 在大数据集中可能非常缓慢，因为它的时间复杂度是 O(n log(n))。你的表将有多少行？ - Mark Byers

Answer 1

根据您上面的SQL语句，cur_odds中的任何数字都不是每行被选中的概率，而只是一个任意权重（相对于所有其他行的“权重”），最好解释为相对于排序表顶部的浮动趋势。每行的实际值是无意义的（例如，您可以有4行的值为0.35、0.5、0.75和0.99，或者您可以有值为35、50、75和99的行，结果将是相同的）。 更新：以下是查询的情况。您有一行的cur_odds值为0.35。为了说明，我假设其他9行都具有相同的值（0.072）。同样为了说明，假设RAND()返回从0.0到1.0的值（实际上可能是这样）。

每次运行此SELECT语句时，通过将其cur_odds值乘以从0.0到1.0的RAND()值，为每行分配一个排序值。这意味着具有0.35的行将具有介于0.0和0.35之间的排序值。

其他每一行（值为0.072）将具有介于0.0和0.072之间的排序值。这意味着您的一行将有大约80%的几率具有大于0.072的排序值，这意味着任何其他行都不可能被排序得更高。这就是为什么具有cur_odds值为0.35的行比您预期的更经常地出现在第一位。

我错误地将cur_odds值描述为相对变化权重。它实际上作为最大相对权重，这将涉及一些复杂的数学来确定实际的相关概率。

我不确定您需要的内容是否可以通过直接使用T-SQL完成。我已经多次实现了加权概率选择器（讽刺的是，今天早上我甚至要问一个关于最佳方法的问题），但始终是在代码中完成的。

- MusiGenesis

---

实际上，我有10行数据，在cur_odds中的10个值都恰好等于1。 - James Simpson

尝试将所有值乘以10（使它们总共恰好为10.0），您会发现得到相同的排序结果。或者您可以将它们全部除以3，或者乘以100等。 - MusiGenesis

Answer 2

如果cur_odds很少更改，您可以实现以下算法：

1）创建另一列prob_sum，其内容为：

prob_sum[0] := cur_odds[0]

for 1 <= i <= row_count - 1:

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

2）生成一个介于0和1之间的随机数：

rnd := rand(0,1)

3）找到第一行满足prob_sum > rnd的条件（如果在prob_sum上创建了BTREE索引，则查询速度应该会更快）：

CREATE INDEX prob_sum_ind ON <table> (prob_sum);

SET @rnd := RAND();

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

- Vitalii Fedorenko