|
|
Posted by Jonathan on 01/08/06 10:42
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?
>
> If that's correct it will not be 1/511 chance of the name Jensen to appear.
>
This might be a case of simple misunderstanding at first I thought you
had a a table with 500 rows containing the name Thompsen, 10 rows
containing the name Milla and one row containing the name Milla. This
makes a total of 511 rows. Then selecting based on the rownumber would
give you your statistic distribution.
But after reading your post another time I think the solution is a bit
more complex... as the table has only three rows with each name
appearing only once in combination with a number indicating the change
on the total. This is a bit harder to solve than if you would really
have 511 rows containg the data.
I think you need to use some program logic (or a stored procedure) to
determine which name to display:
1. Make a table that sums the rated number for every row above including
it self e.g.:
Name Rated_Sum
----------------------
Thompson 500
Milla 510
Jensen 511
2. Generate a random number with the range from 1 to the maximum value
(the value in the last row of the table) to determin something like the
row index in the example
3. Use logic to see if the random number is smaller or equal to the
Rated_Sum value of its own value and greater than the value in the last
row before its own value, e.g.:
The random number would be in the range of 1 up to and including 500
then you should return Thompson, but if the random number is greater
than 510 and less than or equal to 511 (only 511 would fit this
condition, assuming that you generate integer random numbers) you have
to output Jensen.
It makes it all a bit complicated... it might be easier to create a
temporary table holding the number of rows with the rated value and go
with the example of Stephan.
Good luck!
Jonathan
Navigation:
[Reply to this message]
|