CODEWITHSUNDEEP
mysqlselectsql-order-byrating
DenisCGN
DenisCGN

Reputation: 407

MySQL Rating With Weight

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?

Upvotes: 2

Views: 591

Answers (1)

Eugen Rieck
Eugen Rieck

Reputation: 65264

A first step is to calculate avg_num_votes and avg_rating:

SELECT
  SUM(totalVotes)/COUNT(*) AS avg_num_votes,
  SUM(avgVote)/COUNT(*) AS avg_rating
FROM voting;

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 it avg_summand

SELECT
  voting.*, -- or whatever fields you need
  ($avg_summand+totalVotes*avgVote)/($avg_num_votes+totalVotes) AS bayesian
FROM voting
ORDER BY  bayesian DESC
LIMIT 1;

Edit

You could run this as a join:

SELECT
  voting.*, -- or whatever fields you need
  (avg_num_votes*avg_rating+totalVotes*avgVote)/(avg_num_votes+totalVotes) AS bayesian
FROM voting,
(
  SELECT
    SUM(totalVotes)/COUNT(*) AS avg_num_votes,
    SUM(avgVote)/COUNT(*) AS avg_rating
  FROM voting AS iv
) AS avg
ORDER BY  bayesian DESC
LIMIT 1;

But this will calculate sum and average on every single query - call it a performance bomb.

Upvotes: 1

Related Questions