Posted by Stefan Rybacki on 01/08/06 15:52

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Joe wrote:
> "Stefan Rybacki" <stefan.rybacki@gmx.net> skrev i en meddelelse
> news:42apniF1hejs7U1@individual.net...
>> -----BEGIN PGP SIGNED MESSAGE-----
>> Hash: SHA1
>>
>> Joe wrote:
>>> Hi
>>> I need some help doing a easy php/sql solution to a random selection with
>>> rated stuff.
>>> I have a table like:
>>>
>>> Name Rated
>>> --------------
>>> Thompsen 500
>>> Milla 10
>>> Jensen 1
>>>
>>> Means that the name Thompsen appears 50 times as often as Milla and 500
>>> times as often Jensen.
>>>
>>> I now need some scripting that counts the total ratefactor like
>>> SELECT sum(Rated) FROM Table
>>> In this case 511
>>> And then make a random value between 1 and 511 and then print out the
>>> name,
>>> which in this case will be Thompsen 500 of 511 times (in avg)
>>>
>>> I need a quick way to find a name based on the Rated factors, any help?
>>>
>> 1. get your ratefactor (SELECT sum(rated) FROM table)
>> 2. create a random number between 1 and ratefactor in PHP
>> 3. get the random item (SELECT name FROM table WHERE
>> rated>=created_randomnumber
>> ORDER BY rated, rand() LIMIT 1)
>>
>> Regards
>> Stefan
>>
>
> I think I need some explanation for this :-)
>
> As I see it it will give equal chance of the three names if the random
> number is 1, is that correct?

no it doesn't. Lets go thru it.

1. the ratefactor is 511, ok to work with my query you just need the max rated
value so it is 500
2. you said your random number is 1
3. we're doing the following query:

SELECT name FROM table WHERE rated>=1
ORDER BY rated, rand() LIMIT 1

What we get is always Jensen. Why?
Lets remove the LIMIT 1 and lets also print out the rated column and lets see
the result:

+---------+-------+
| name | rated |
+---------+-------+
| Jensen | 1 |
| Milla | 10 |
| Thomson | 500 |
+---------+-------+

lets say we have the random number 2 the result will look like this:

+---------+-------+
| name | rated |
+---------+-------+
| Milla | 10 |
| Thomson | 500 |
+---------+-------+

lets say we have the random number 11 the result is:

+---------+-------+
| name | rated |
+---------+-------+
| Thomson | 500 |
+---------+-------+

So as you can see, for any random number between <=1 you will get Jensen, for
any random number 2<= random number <=10 you'll get Milla as result and for any
random number greater than 10 you'll get Thomson.

By the way with MySQL 4.1 and higher you can do all this in just one query!

SELECT name, rated FROM table WHERE rated>=ROUND(rand()*(SELECT max(rated) FROM
table))
ORDER BY rated, rand() LIMIT 1

>
> If that's correct it will not be 1/511 chance of the name Jensen to appear.
>
>

Not really but almost its 1/500, if you want 1/511 you have to map all your
items to an interval for example 0 to 1 with respect to your rated value.

In this example I'm mapping the values to the interval of 0 to ratefactor (in
your example 0 to 511)

And with MySQL 4.1 and higher the following query can solve this problem:

SELECT name FROM `table` t2 WHERE
(SELECT sum(t1.rated) FROM `table` t1 WHERE t1.rated<=t2.rated)>=rand()*
(SELECT sum(rated) FROM `table`) ORDER BY rated, rand() LIMIT 1

I've the following table:

+---------+-------+
| name | rated |
+---------+-------+
| Thomson | 500 |
| Milla | 10 |
| Jensen | 1 |
| Mike | 10 |
+---------+-------+

and on 521 runs I get the following distribution:

Run 1:

Thomson: 485
Milla: 20
Mike: 16
Jensen: 0

Run 2:

Thomson: 461
Milla: 27
Mike: 31
Jensen: 2

Run 3:

Thomson: 479
Milla: 22
Mike: 19
Jensen: 1

And that is pretty much what you're expecting right?

Unfortunately without subqueries you have to code it in your application.

Regards
Stefan
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.1rc2 (MingW32)

iD8DBQFDwRkuyeCLzp/JKjARAjCXAJ0bczpC0wbTJLwd43QunKnRpVZmogCeJaVU
Wfjyuc6/Ilf9YG3XPju4Qmg=
=N3TE
-----END PGP SIGNATURE-----

[Back to original message]