I have a banner rotator based on similar principle (implemented in PHP by using arrays).
Suppose you have N data records (R1 ... Rn) - then you have N weights (W1 ... Wn).
You can calculate the sum of all weights = Ws.
So, you want to get a probability for record N to be chosen equal to:
p = Wn/Ws
We can build a new series of these records like these:
R1(V1,W1), R2(V2,W2) ... RN(Vn,Wn)
where Vi = Vi-1+Wi (similar to Fibonacci series)
Having this in mind a possible abstract query would look like this:
select first Ri where Vi > randomNumber * Ws
or its equivalent with Wi
select first Ri where SUM(W1..Wi) > randomNumber * Ws
the randomNumber *should* be constant while MySQL engine executes the query ( @Weirdan - that's why your query gives unexpected results)
Tested (
http://ipclassify.relef.net/1.php and refresh several times

)
Full code:
http://ipclassify.relef.net/1.phps
Code: Select all
$rand = rand(0,1000)/1000;
$query = "
select
id, weight
from
t
where
(select sum(t2.weight) from t as t2 where t.id >= t2.id) > (select sum(weight) from t)*$rand
limit
1
";
Code: Select all
id: 1 (10) count: 1005
id: 2 (20) count: 2018
id: 3 (30) count: 3046
id: 4 (40) count: 3921
PS: It's best case when we have
$rand = rand(0,sum_of_all_weights)/sum_of_all_weights;
instead of
$rand = rand(0,1000)/1000;
It's about precision.