Page 3 of 3

Posted: Fri Dec 14, 2007 5:20 pm
by VladSun
Look at http://ipclassify.relef.net/1.php and http://ipclassify.relef.net/1.phps - modified with the new queries and table structure.

And

Code: Select all

$v_rand = rand(1,10);
is wrong - should be

Code: Select all

$v_rand = rand(0,10);

Posted: Fri Dec 14, 2007 8:20 pm
by paladaxar
It really doesnt make a difference. If you use rand(0,10) then in the query you need to use >. If you use (1,10) then you can use >=. Really things would be off by a factor of 10% at most either way. Actually, now that I think about it, if you start at 0, then you should go from 0 to n-1. So it would be (0,9).

Still, I tried all of these combinations just to make sure I wasnt overlooking something. All of the combinations still produce the wrong results for the "broken" table. Does this repeat on anyone else's machines? It really could just be my server. Although I have no idea what I would do to fix it if it is just my problem.

Posted: Sat Dec 15, 2007 5:35 pm
by VladSun
I think 0..N distribution means 0...100% probabuilty distribution ... You have a particular case (1 === 10%) when this query will work but it won't work in all other cases.

I can't understand why it doesn't work in your case - I've tested it with exctly the same table values and it works ...

Posted: Sat Dec 15, 2007 8:27 pm
by paladaxar
Did you try it with the same code that I had (making the change that we talked about, going from 0 to 10)? I just tried my code and the "broken" table on a completely different server with a different database and different domain name...and it doesn't work on that server either.

Posted: Tue Dec 18, 2007 11:45 pm
by paladaxar
GOT IT!!! Um...this is kind of embarrassing...but I was using varchars for all of my columns. So, when the total weight got to 10, the whole thing fell apart because it was looking at the number as a string instead of an int.

So, thank you everyone for your help...problem solved.

THANKS ESPECIALLY to VladSun who came up with the final query that did the job.

I think this thread is a great thread and will be referenced frequently. For weeks I googled "mysql weighted random selection" and came up with nothing useful, so this thread may have broken some new ground :).

Posted: Thu Dec 20, 2007 3:09 am
by VladSun
I'm glad you got it :)
In fact, I think I wouldn't be able to help you with this "varchar" mistake ;)

Indeed, Weirdan is the first one who posted the right solution with a little mistake :) His logic was absolutely the same as the one I use in my banner rotator. It's code:

Code: Select all

<?
 
$aBann = Array();
$aBann[] = 6;
$aBann[] = 12;
$aBann[] = 18;
$aBann[] = 24;
$aBann[] = 30;
$aBann[] = 36;
$aBann[] = 42;
$aBann[] = 48;
$aBann[] = 75;
 
$rand = rand(0, end($aBann)-1); 
 
for ($i=0; $i<count($aBann); $i++)
{
    if ($aBann[$i] > $rand)
    {
        // Image names start from header-1.jpg 
        echo "<img src='images/banners/header-".($i+1).".jpg' alt='Banner' width='778' height='125' />";
        break;
    }
}
 
?>
You can see that I calculate the "V" field at development time :)

Posted: Fri Dec 21, 2007 8:49 pm
by patrickwonders
And, if you don't want to calculate the sum of all of them and then take the weights, then you can do this:

Code: Select all

SELECT * FROM mytable ORDER BY POW( RAND(), weight ) ASC LIMIT 1;
Of course, that only works if the RAND() is only calculated once per row despite how many times the sort may need that row for comparison.... which I believe it is.

Edit: bleh, actually, it doesn't work so well for more than two items in the table either... feh. It seems to work pretty well if the random number is recalculated at every comparison rather than once per row.

Posted: Sat Dec 22, 2007 8:56 am
by Weirdan
patrickwonders wrote:

Code: Select all

SELECT * FROM mytable ORDER BY POW( RAND(), weight ) ASC LIMIT 1;
It doesn't work good enough:

Code: Select all

mysql> select sum(id=1)/count(*) as 'weight=0.5', sum(id=2) / count(*) as 'weight=0.3', sum(id=3) / count(*) as 'weight=0.2', count(*) as 'total runs' from t2;
+------------+------------+------------+------------+
| weight=0.5 | weight=0.3 | weight=0.2 | total runs |
+------------+------------+------------+------------+
|     0.5327 |     0.3645 |     0.1028 |        107 |
+------------+------------+------------+------------+
1 row in set (0.00 sec)