On Wed, 28 Jan 2004, Deven Phillips wrote:
> Hello,
>
> There is a web site associated with the web-radio. Users of the web site
> can rate songs which are contained in the database. The rating system
> works such that users can rate songs from +3 to -2. Now, what I would
> like to accomplish is to create a query that allows me to randomly
> select a song from the database to add to the queue while taking into
> account the ratings. For example:
>
> There are 2400 songs listed
> One song has been rated three times as follows:
> +3
> +1
> -1
> I would like that song to have a 3/2400 chance of being selected for the
> queue.
You can easily come into a situation where this isn't feasible.
I assume the 3 in 3/2400 is the sum of the votes.
Imagine you have 2 songs and 10 users.
5 users vote 3 for one of the songs and 5 users vote 2 for the same song.
Then you'd have 25/2 chance of having that song selected.
> Secondly, I need the database to store who voted for waht so that users
> cannot continually vote +3 over and over for their favorite song.
That is easily accomplished, just use a table like:
CREATE TABLE votes (
userid INT UNSIGNED NOT NULL,
songid INT NOT NULL,
vote TINYINT NOT NULL,
PRIMARY KEY (userid, songid)
)
Then when your web application inserts or updates a vote you can use
REPLACE INTO votes (userid, songid, vote) VALUES(1, 1, 2);
That will take care of the problem of someone voting for their favorite
song more than once.
One suggestion (doesn't have the percentages that you specify above, but
that is easily implementable) could be to use the votes table above and
the songs table:
CREATE TABLE songs (
songid INT UNSIGNED NOT NULL AUTO_INCREMENT,
artistid INT UNSIGNED NOT NULL,
songname char(75) NOT NULL,
PRIMARY KEY (songid)
)
Then you could use a query like:
SELECT songname, RAND() * (AVG(votes.vote) + 10) AS rnd
FROM songs
JOIN votes ON votes.songid = songs.songid
GROUP BY songs.songid
ORDER BY rnd DESC
LIMIT 1
This will generate random numbers between 0 and 8-13 depending on what the
average vote for the song is, you can change the + 10 to any number to
weigh them differently (although, if you use 0-2 votes with low averages
might never be played since their values will always be negative).
Might give you some new ideas if nothing else
cheers,
Tobias
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
