|
|
Posted by Stefan Rybacki on 01/08/06 16:29
-----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
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.1rc2 (MingW32)
iD8DBQFDwSGuyeCLzp/JKjARAmscAJ4kTtibl+n1JWZAnv68gvRHFGjASACgoZ0b
bVtwCQtjQx7c4X8t/xTN9Dk=
=I6fM
-----END PGP SIGNATURE-----
Navigation:
[Reply to this message]
|