如何在MySQL表中添加“权重”，并根据这些权重选择随机值？

Question

我想创建一个表格，每一行都包含某种权重。然后我想随机选择值，概率等于（该行的权重）/（所有行的权重）。例如，在1000个元素中有5个行的权重为1、2、3、4、5，那么我将大约得到1/15*1000=67次第一行，以此类推。

表格需要手动填充。然后我将从中取一个随机值。但是我希望在填充阶段有更改概率的能力。

- Fluffy

---

你是想创建一个表格，其中的数据是基于权重和出现次数进行伪随机生成的吗？ - Zyris Development Team

另一种思考方式是，如果一个项目有2颗星，而另一个项目有3颗星，那么我们应该在2/5的时间内选择第一个而不是第二个。 - Josh Lee

请查看我的回答：https://dev59.com/rHE95IYBdhLWcg3wMa91#41577458 - Ali

Answer 1

我在Quod Libet中发现了这个不错的小算法。你可能可以将其翻译为一些过程化SQL。

function WeightedShuffle(list of items with weights):
  max_score ← the sum of every item’s weight
  choice ← random number in the range [0, max_score)
  current ← 0
  for each item (i, weight) in items:  
    current ← current + weight  
    if current ≥ choice or i is the last item:  
      return item i

- Josh Lee

Answer 2

问题被称为蓄水池抽样（https://en.wikipedia.org/wiki/Reservoir_sampling）。

A-Res算法在SQL中易于实现：

SELECT *
FROM table
ORDER BY pow(rand(), 1 / weight) DESC
LIMIT 10;

- Péter Pallos

Answer 3

我不是概率论专家，但假设你有一个名为WEIGHT的列，那么怎么样？

select FIELD_1, ... FIELD_N, (rand() * WEIGHT) as SCORE
  from YOURTABLE
 order by SCORE
 limit 0, 10

这将给您10条记录，但您当然可以更改限制子句。

- Tom Bartel

---

Downvote。很容易看出，它无法生成权重（1,2,2,2,2,2）的正确结果（特别是如果发生rand()=1的情况，选择权重为1的行的机会只有1/32，这本来就不太可能）。 - tc.

Answer 4

最简单（或者可能是最好/最安全的）方法是将这些行添加到表中，次数与您想要的权重相同 - 比如我想让“Tree”比“Dog”出现的频率高两倍 - 我会将其插入表中两次，将“Dog”插入一次，然后随机选择一个元素。

如果行是复杂/大型的，则最好创建一个单独的表（weighted_Elements或其他名称），其中您只需插入外键以指向真实的行，并根据权重要求插入多次。

- RnR

---

这往往也是最有效的方法，不是吗？ ;) （至少考虑到当前的选项）尤其是在选择比插入/重新加权更频繁时（这可能是一个典型的情况）。 它只是一个查询，而不是每次都要线性循环遍历所有元素，并且当试图以Y秒为单位获取带权重X的随机元素时，它将有助于避免复杂问题... :) - RnR

实际上，如果你想要获取X个随机元素，你只需要编写一个聪明的比较函数进行排序（尽管我不知道在SQL中这有多容易）。http://code.google.com/p/quodlibet/source/browse/quodlibet/quodlibet/browsers/search.py#117 - Josh Lee

但是在这个问题上，使用SQL更自然、更高效。 - Josh Lee

Answer 5

如果我理解你的问题正确的话，最好的情况是按照正常流程设置表格，然后添加两个 INT 类型的列。

• 列1：重量 - 该列将保存您的重量值，范围从-X到+X，其中X是您想要作为重量的最高值（例如：X=100，-100到100）。此值被填充以赋予行一个实际的权值，并增加或减少其出现的概率。

• 列2：计数 - 该列将保存此行已出现的次数，仅在您想使用公平权重时才需要此列。公平权重可防止一行总是出现。（例如，如果您有一行权重为100，另一行权重为2，则权重为100的行将始终显示，此列将允许使权重2更具“价值”，因为您获得更多的权重100结果）。每次抽取一行结果时应将此列递增1，但您可以稍后使逻辑更加先进，以添加权重等。

• 逻辑：- 现在非常简单，您的查询只需像通常一样请求所有行，然后进行额外的选择（您可以在此更改逻辑，使其符合您的要求），提取权重并减去计数，然后按该列排序。

最终结果应该是一个表格，在该表格中，您的权重将出现更频繁，直到某个点系统会均匀分布自己（留出第二列），除非您偏移查询的基础（例如：LIMIT [RANDOM NUMBER]，[NUMBER OF ROWS TO RETURN]），否则您将拥有始终返回相同加权顺序的系统。

- Zyris Development Team

Answer 6

我来寻找同样问题的答案 - 我决定提供以下解决方案：

id      weight
1       5
2       1

SELECT * FROM table ORDER BY RAND()/weight

这不是精确的 - 但它使用了随机数，所以我不会期望完全一致。我运行了70次才得到10次数字2。我本来期望是1/6，但实际上是1/7。我认为这已经相当接近了。如果要真正确定它是否有效，我需要运行几千次脚本。

- macdabby