|
|
Posted by Stefan Rybacki on 01/10/06 16:03
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
J.O. Aho wrote:
> Stefan Rybacki wrote:
>> J.O. Aho wrote:
>>>> Joe wrote:
>>>>
>>>> ...
>>>>
>>>> ...
>>>>
>>>> -- Next line is first in the procedure
>>>> DROP PROCEDURE IF EXISTS randrow;
>>>> delimiter //
>>>> CREATE PROCEDURE randrow(OUT result CHAR(20))
>>>> BEGIN
>>>> DECLARE valueadd INT DEFAULT 0;
>>>> DECLARE rowsno INT DEFAULT 0;
>>>> DECLARE temp INT DEFAULT 0;
>>>> DECLARE curs1 CURSOR FOR SELECT Rated_Sum,Name FROM RateTable;
>>>> SELECT @randvalue:=CEIL(RAND(NOW())*SUM(Rated_Sum)) AS RandNo FROM RateTable;
>>>> OPEN curs1;
>>>> REPEAT
>>>> FETCH curs1 INTO temp,result;
>>>> SET valueadd := valueadd + temp;
>>>> UNTIL (@randvalue <= valueadd) END REPEAT;
>>>> CLOSE curs1;
>>>> END
>>>> //
>>>> delimiter ;
>>>> -- previous line was the last line
>>>>
>> What if there are multiple entries with the same rated_sum value? As I
>> understand right, you're just giving back the first found entry.
>
> There aren't any less chance if the entry with the same value would be in the
> end of the list or on the top of the list, all entries with the same Rated_Sum
> will have the same chance to be picked.
That is true.
>
> SUM(Rated_Sum) gives the total of all Rated_Sum in the whole table and you
> then generate a random number between 1 and the SUM(Rated_Sum), which makes
> that if you have two entires with Rated_Sum=50, they have exactly the same
> chance to be picked, regardless where in the list they do appear.
>
Yes.
> It's easy to try this out, just pick a 6 sided dice and start rolling and you
> will see that it's as easy to get '1' as it's to get '6'.
I know.
>
> The loop through the table is there to determine who is the "owner" of the
> value, it's kind of doing as the modified table in Jonathan's post:
>
> Name Rated_Sum
> ----------------------
> Thompson 500
> Milla 510
> Jensen 511
>
But, what I meant is does your procedure choose different entires with the same
rated_sum?
Maybe I got your procedure wrong, but let me go thru it:
Say we have the following table:
+---------+-------+
| name | rated |
+---------+-------+
| Thomson | 500 |
| Milla | 10 |
| Jensen | 1 |
| Mike | 10 |
+---------+-------+
where rated is your rated_sum value
DROP PROCEDURE IF EXISTS randrow;
delimiter //
CREATE PROCEDURE randrow(OUT result CHAR(20))
BEGIN
DECLARE valueadd INT DEFAULT 0;
DECLARE rowsno INT DEFAULT 0;
DECLARE temp INT DEFAULT 0;
DECLARE curs1 CURSOR FOR SELECT Rated_Sum,Name FROM RateTable;
SELECT @randvalue:=CEIL(RAND(NOW())*SUM(Rated_Sum)) AS RandNo FROM RateTable;
say @randvalue=510
OPEN curs1;
REPEAT
FETCH curs1 INTO temp,result;
SET valueadd := valueadd + temp;
UNTIL (@randvalue <= valueadd) END REPEAT;
ok, 1. loop
temp=500, name=Thomson
valueadd=0+500;
510 <= 500 -> false
2. loop
temp=10, name=Milla
valueadd=500+10
510 <= 510 -> true
Return Milla
CLOSE curs1;
END
I'm also not sure when the result is set? Never mind. How do I ever get Jensen
or Mike with your procedure? I guess what you have to do is to add an ORDER BY
rated_sum DESC, rand() to the cursors select to get right results. If not I
would appreciate an explanation.
Regards
Stefan
>
>
>
> //Aho
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.1rc2 (MingW32)
iD8DBQFDw77KyeCLzp/JKjARAjWDAJ45/pr0Ifo14ns5qImjORHxXX1GTgCglTYw
jxxCG+i+GZg+E+YgM5HClgc=
=acOg
-----END PGP SIGNATURE-----
Navigation:
[Reply to this message]
|