|
|
Posted by Joe on 01/08/06 21:04
"Stefan Rybacki" <stefan.rybacki@gmx.net> skrev i en meddelelse
news:42cltdF1i3ad8U1@individual.net...
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> Stefan Rybacki wrote:
>> Joe wrote:
>> ...
>> no it doesn't. Lets go thru it.
>>
>>...
>>
>> 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
>
> I'm sorry but I found an error in this and the other 4.1 query.
>
> You have to replace rand() by an already chosen random number or by a
> before set
> user variable in mySQL otherwise you get wrong distributions (do not
> replace the
> rand() in the order by clause!!!).
>
> Example for the one above:
>
> SET @random:=rand();
> SELECT name, rated FROM table WHERE rated>=ROUND(@random*
> (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
>>
>
> The same error here:
>
> SET @random:=rand();
> SELECT name FROM `table` t2 WHERE
> (SELECT sum(t1.rated) FROM `table` t1 WHERE t1.rated<=t2.rated)>=@random*
> (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:
>>
>
> So with the table above and another four runs I got the following results:
>
> Thomson: 492
> Milla: 14
> Mike: 11
> Jensen: 4
>
> Thomson: 500
> Milla: 9
> Mike: 10
> Jensen: 2
>
> Thomson: 497
> Milla: 15
> Mike: 9
> Jensen: 0
>
> Thomson: 493
> Milla: 16
> Mike: 11
> Jensen: 1
>
> Regards
> Stefan
It's a solution like this I'm looking for, I do however get errors when
doing this, can you help me again showing the SQL query and explain it?
Navigation:
[Reply to this message]
|