download201401
download201401
采纳率0%
2011-06-20 03:41

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

已采纳

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

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享
  • 邀请回答

1条回答

  • dongxianghui3709 dongxianghui3709 10年前

    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!

    点赞 评论 复制链接分享
提交

为你推荐