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屋！