我正在创建一个广告系统,它会更频繁地显示出价最高的广告.

这是我正在使用的表结构的示例,但已简化…

+----+----------+------------------------+----------------------+-----+
| id | name     | image                  | destination          | bid |
+----+----------+------------------------+----------------------+-----+
| 1  | abc, co  | htt.../blah            | htt...djkd.com/      | 3   |
+----+----------+------------------------+----------------------+-----+
| 2  | facebook | htt.../blah            | htt...djkd.com/      | 200 |
+----+----------+------------------------+----------------------+-----+
| 3  | google   | htt.../blah            | htt...djkd.com/      | 78  |
+----+----------+------------------------+----------------------+-----+

现在,现在我要从数据库中选择值,然后将它们插入到数组中并随机选择一个值,类似于以下内容：

$ads_array = [];
$ads = Ad::where("active", "=", 1)->orderBy("price", "DESC");

if ($ads->count() > 0) {
    $current = 0;
    foreach ($ads->get() as $ad) {
        for ($i = 0; $i <= ($ad->price == 0 ? 1 : $ad->price); $i++) {
            $ads_array[$current] = $ad->id;
            $current++;
        }
    }

    $random = rand(0,$current-1);
    $ad = Ad::where("id", "=", $ads_array[$random])->first();

    ...
}

因此,实际上,这是在将广告ID插入数组1 * $bid次.遗憾的是(由于明显的原因)这是非常低效的,但这是我想到的最好方法.

是否有更好的方法从数据库中挑选随机广告；同时仍使出价更高的人有更高的出现率？

解决方法:

看起来这可以解决问题(但所有功劳都归功于这个guy in the comments)

SELECT ads.*
FROM ads
ORDER BY -log(1.0 - rand()) / ads.bid
LIMIT 1

一个测试这个的脚本：

<?php
$pdo = new PDO('mysql:host=localhost;dbname=test;', 'test', 'test');


$times = array();

// repeat a lot to have real values
for ($i = 0; $i < 10000; $i++) {
    $stmt = $pdo->query('SELECT ads.* FROM ads ORDER BY -log(1.0 - rand()) / bid LIMIT 1');
    $bid = $stmt->fetch()['bid'];
    if (isset($times[$bid])) {
        $times[$bid] += 1;
    } else {
        $times[$bid] = 1;
    }
}

// echoes the number of times one bid is represented
var_dump($times);

该测试得出的数字相当不错：

// key is the bid, value is the number of times this bid is represented
array (size=3)
  200 => int 7106
  78 => int 2772
  3 => int 122

关于数学解释的进一步参考

Many important univariate distributions can be sampled by inversion using simple closed form expressions. Some of the most useful ones are listed here.

Example 4.1 (Exponential distribution). The standard exponential distribution has density f(x) = e−x on x > 0. If X has this distribution, then E(X) = 1, and we write X ∼ Exp(1). The cumulative distribution function is F(x) = P(X