根据概率机会选择随机值 [英] select random value based on probability chance

问题描述

如何根据分配给每一行的概率概率从数据库中选择随机行.
示例:

How do I select a random row from the database based on the probability chance 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 do I select random make name and its value based on the probability it has to be chosen.

rand()和 ORDER BY 的组合是否有效?如果是这样，最好的方法是什么?

Would a combination of rand() and ORDER BY work? If so what is the best way to do this?

推荐答案

您可以通过使用 rand()然后使用累积和来做到这一点.假设它们的总和为100％:

You can do this by using rand() and then using a cumulative sum. Assuming they add 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;

注意:

• rand()在子查询中被调用一次以初始化变量.不需要多次调用 rand().
• 极有可能随机数将恰好位于两个值之间的边界上. limit 1 任意选择1.
• 当 cumep>停止子查询时，可以提高效率.@r .
• 这些值不必按任何特定顺序排列.
• 可以修改此值以处理总和不等于1的机会，但这将是另一个问题.

• rand() is called once in a subquery to initialize a variable. Multiple calls to rand() are not desirable.
• There is a remote chance that the random number will be exactly on the boundary between two values. The limit 1 arbitrarily chooses 1.
• This could be made more efficient by stopping the subquery when cumep > @r.
• The values do not have to be in any particular order.
• This can be modified to handle chances where the sum is not equal to 1, but that would be another question.

这篇关于根据概率机会选择随机值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！