如何创建一个mysql_query来拉动使用贝叶斯平均值排名的项目？

Question

Given a database table with the following fields:

• total_votes: The total number of votes cast on this item/row
• average_value: The average voting value (i.e. 3.6 with 5 being the max)
• total_value: The sum of all the vote values (i.e. 3 votes of 4 will result in 12)

I was simply using a SELECT to grab the rows and the ORDER BY to sort them by their average value but then I ran into the issue of a good row with 10 votes averaging 4.9 getting outranked by a brand new row with only 1 vote of 5 outranking it.

After some research, I discovered that the most popular method of sorting rated items is to use a bayesian average.

br = ( (avg_num_votes * avg_rating) + (this_num_votes * this_rating) ) / (avg_num_votes + this_num_votes)

However, I'm having difficulty creating a query that pulls these rows and sorts them using this average as I'm a novice at creating anything advanced in terms of mySQL queries.

What I'm looking for is:

$results = mysql_query("INSERT ANSWER HERE");

Where INSERT ANSWER HERE is the answer you provide me with and $results provides these items sorted in by their Bayesian Average.

For more information about the Bayesian Average, I checked out: http://www.thebroth.com/blog/118/bayesian-rating

Answer 1

A simple way would be to use two queries. Perhaps this will do the trick

Query #1

SELECT
    AVG(total_votes) AS avg_num_votes,
    AVG(average_value) AS avg_rating
FROM
    MyThings
WHERE
    total_votes > 0

This should give you the avg_num_votes for all items (that have at least one vote), and avg_rating across all items (again for those with at least one vote).

With these values in hand, construct query #2 and use ORDER BY:

Query #2

SELECT
    thing_id
    thing_name
FROM
    MyThings
ORDER BY
    (($avg_num_votes * $avg_rating) + (total_votes * average_value)) / ($avg_num_votes + total_votes)

Before submitting this query, replace $avg_num_votes and $avg_rating with the results from query #1.

Warning: Haven't tested this, and I'm not familiar with Bayesian ranking.

Hope that helps!