MySQL:选择随机条目,但对某些条目加权 [英] MySQL: Select Random Entry, but Weight Towards Certain Entries
问题描述
我有一个 MySQL 表,里面有一堆条目,还有一个名为乘数"的列.此列的默认(也是最常见的)值为 0,但它可以是任何数字.
I've got a MySQL table with a bunch of entries in it, and a column called "Multiplier." The default (and most common) value for this column is 0, but it could be any number.
我需要做的是从该表中随机选择一个条目.但是,根据乘数"列中的数字对行进行加权.值为 0 意味着它根本没有加权.值为 1 意味着它的权重是原来的两倍,就好像条目在表中出现了两次一样.值为 2 意味着它的权重是其权重的三倍,就像该条目在表中的三倍一样.
What I need to do is select a single entry from that table at random. However, the rows are weighted according to the number in the "Multiplier" column. A value of 0 means that it's not weighted at all. A value of 1 means that it's weighted twice as much, as if the entry were in the table twice. A value of 2 means that it's weighted three times as much, as if the entry were in the table three times.
我正在尝试修改我的开发人员已经给我的东西,如果设置没有多大意义,那么抱歉.我可能会更改它,但希望尽可能多地保留现有的表设置.
I'm trying to modify what my developers have already given me, so sorry if the setup doesn't make a whole lot of sense. I could probably change it but want to keep as much of the existing table setup as possible.
我一直试图弄清楚如何使用 SELECT 和 RAND() 来做到这一点,但不知道如何进行加权.可能吗?
I've been trying to figure out how to do this with SELECT and RAND(), but don't know how to do the weighting. Is it possible?
推荐答案
这个人问了同样的问题.他和弗兰克说的一样,但权重不正确,在评论中有人建议使用 ORDER BY -LOG(1.0 - RAND())/Multiplier
,这在我的测试中给出了很好的非常完美的结果.
This guy asks the same question. He says the same as Frank, but the weightings don't come out right and in the comments someone suggests using ORDER BY -LOG(1.0 - RAND()) / Multiplier
, which in my testing gave pretty much perfect results.
(如果有任何数学家想解释为什么这是正确的,请赐教!但它有效.)
(If any mathematicians out there want to explain why this is correct, please enlighten me! But it works.)
缺点是您无法将权重设置为 0 以暂时禁用某个选项,因为您最终会被零除.但是你总是可以用 WHERE Multiplier > 过滤掉它.0
.
The disadvantage would be that you couldn't set the weighting to 0 to temporarily disable an option, as you would end up dividing by zero. But you could always filter it out with a WHERE Multiplier > 0
.
这篇关于MySQL:选择随机条目,但对某些条目加权的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!