I want to create a rating with weight depending on number of votes. So, 1 voting with 5 can't be better than 4 votings with 4.
I found this math form:
bayesian = ( (avg_num_votes * avg_rating) + (this_num_votes * this_rating) ) / (avg_num_votes + this_num_votes)
How can I make a MySQL SELECT to get the ID of the best rating image.
I got a table for IMAGE, and a table for VOTING
VOTING:
id
imageID
totalVotes
avgVote
I think I got to do this with SELECT in SELECT, but how?
A first step is to calculate
avg_num_votes
andavg_rating
:If you can live with a small error, it might be good enough to calculate that once in a while.
Now using your formula and the values above, you can run the weighing query. As a small optimization I precalculate
avg_num_votes * avg_rating
and call itavg_summand
Edit
You could run this as a join:
But this will calculate sum and average on every single query - call it a performance bomb.