advanced code snippet search

Posted By

seb-oulba on 11/05/09


Tagged

rating ranking bayes


Versions (?)

Who likes this?

2 people have marked this snippet as a favorite

dantreacy
kubaitis


Bayesian ranking

 / Published in: MySQL
 

URL: http://www.thebroth.com/blog/118/bayesian-rating

Use Bayesian values to get a "more fair" ranking than average value based ranking. Example with a "games" table having some ratings in table "ratings"

Expand | Embed | Plain Text 
  1. # create a view with this query
  2. SELECT
  3. game_id,
  4. (SELECT count(game_id) FROM ratings) / (SELECT count(DISTINCT game_id) FROM ratings) AS avg_num_votes,
  5. (SELECT avg(rating) FROM ratings) AS avg_rating,
  6. count(game_id) as this_num_votes,
  7. avg(rating) as this_rating
  8. FROM
  9. ratings
  10. GROUP BY game_id
  11.  
  12. # this query outputs the ranking values for each game
  13. SELECT game_id, ((avg_num_votes * avg_rating) + (this_num_votes * this_rating)) / (avg_num_votes + this_num_votes) as real_rating FROM `bayesian_rating_values2`

Report this snippet  

You need to login to post a comment.