sql - SQL 中的随机偏向？

Question

我的数据库中有一些条目，在我的例子中是带有评级和受欢迎程度以及其他因素的视频。在所有这些因素中，我计算了一个或更多的可能性因素，可以说是一个提升因素。

所以我基本上有 ID 和 BOOST 字段。提升的计算方式是它作为一个整数表示比较中应该命中该条目的频率的百分比。

ID  Boost
1   1
2   2
3   7

因此，如果我无限期地运行我的随机函数，我最终应该在 ID 1 上得到 X 次命中，在 ID 2 上命中是 X 次，在 ID 3 上是 7 倍。

所以每次命中都应该是随机的，但概率为 (boost/sum of boosts)。因此，此示例中 ID 3 的概率应为 0.7(因为总和为 10。为简单起见，我选择这些值)。

我想到了类似下面的查询:

SELECT id FROM table WHERE CEIL(RAND() * MAX(boost)) >= boost ORDER BY rand();

不幸的是，在考虑了表中的以下条目后，这不起作用:

ID  Boost
1   1
2   2

它将以 50/50 的几率随机选择第二个或两个元素。

所以 0.5 命中转到第二个元素并且 0.5 hit 进入(第二个和第一个)元素，它是随机选择的，所以每个 0.25。所以我们最终得到一个 0.25/0.75 的比率，但它应该是 0.33/0.66

我需要一些修改或新方法来以良好的性能执行此操作。

我还考虑过累积存储 boost 字段，所以我只是从 (0-sum()) 进行范围查询，但是如果我改变它或开发一些交换算法或其他东西......但这真的不是优雅之类的东西。

插入/更新和选择都应该很快!

你有解决这个问题的方法吗？

最好的用例可能是广告转换。 “请以给定的概率选择一个随机广告”......但是我需要它用于另一个目的，但只是为了给你最后一张图片它应该做什么。

编辑:

感谢 kens 的回答，我想到了以下方法:

1. 从 0-sum(distinct boost) 计算一个随机值

   SET @randval = (select ceil(rand() * sum(DISTINCT boost)) from test);

2. 从所有不同的提升因子中选择加起来超过随机值的提升因子

那么在第一个示例中，1 的概率为 0.1，2 的概率为 0.2，7 的概率为 0.7。

1. 现在从具有该提升因子的所有条目中随机选择一个条目

问题:因为获得一次提升的条目数总是不同的。例如，如果只有 1 个提升的条目，我会在 10 个调用中的 1 个中得到它，但是如果有 100 万个调用中有 7 个，那么它们中的每一个都几乎不会被返回......所以这行不通:(试图完善它。

我必须以某种方式包括具有此提升因子的条目数......但我不知何故坚持......

Answer 1

您需要为每行生成一个随机数并对其进行加权。

在这种情况下，RAND(CHECKSUM(NEWID())) 绕过了 RAND 的“每个查询”评估。然后简单地将它乘以 boost 和 ORDER BY 结果 DESC。 SUM..OVER 给你总的提升

DECLARE @sample TABLE (id int, boost int)

INSERT @sample VALUES (1, 1), (2, 2), (3, 7)

SELECT
    RAND(CHECKSUM(NEWID())) * boost  AS weighted,
    SUM(boost) OVER () AS boostcount,
    id
FROM
    @sample
GROUP BY
    id, boost
ORDER BY
    weighted DESC

如果您有截然不同的提升值(我想您提到过)，我也会考虑使用 LOG(以 e 为底)来平滑分布。

最后，ORDER BY NEWID() 是一种不考虑提升的随机性。为 RAND 播种很有用，但不能单独播种。

这个示例是在 SQL Server 2008 上放在一起的，顺便说一句