this is pretty much a database question

scottlucas58 · February 19th, 2009, 10:01 PM

In THB articles there is totalrating and votes. taking the average we get the beer images we need. I am trying to do a query that is a little different than just ordering by the total, votes, or avg. In my mind, if some thing has been voted on say 200 times and is a 4.6 and another items 40 times and is a 4.9, i think the 200 vote should rank higher. but if you order by total or vote count you get screwed if some thing has been voted on 300 times and has a 3.0. Hopefully that makes sense. so simple result set would look like:

Code:

description total   votes   avg
-----------------------------------
itemZ        920    200     4.6
itemA        192     40     4.8
itemQ        900    300     3.0

I have no clue.

Old Pedant · February 19th, 2009, 10:31 PM

I guess I understand what you are saying, but HOW would you rate the importance of more votes???

Most sites that show ratings just let the users decide. Something like:

Average rating 5 based on 1 vote(s).
Average rating 4.2 based on 733 votes(s).

etc.

That way the user can decide what importance he/she attaches to a much-rated item compared to a barely-rated item.

You come up with a formula, though, that factors in the number of votes, and we can surely translate that to any language you want.

Lee Dumond · February 19th, 2009, 11:03 PM

Yeah, gotta agree with Old Pedant on this one. Showing the number of votes is best.

That's how Amazon and other sites present their rankings, and it's really useful. As far as I'm concerned, a book with a 5.0 avg. that's been rated by 2 readers is meaningless to me, but it might be meaningful to someone else, who knows?

However, if a book has a 4.0 ranking from 50 or 60 readers, I personally give that more weight... but the point here is , *I* get to decide.

Plus, it's easier. ;-) Remember, K.I.S.S.

scottlucas58 · February 19th, 2009, 11:47 PM

Quote:

Originally Posted by Lee Dumond

However, if a book has a 4.0 ranking from 50 or 60 readers, I personally give that more weight..

That is exactly the logic I am trying to figure out. I'll have other sort options, but this ordering is a primary business requirement. How do you give "weight" to this logic?
It is sort of human logic as opposed to computer.

jimibt · February 20th, 2009, 06:45 AM

Quote:

Originally Posted by scottlucas58

That is exactly the logic I am trying to figure out. I'll have other sort options, but this ordering is a primary business requirement. How do you give "weight" to this logic?
It is sort of human logic as opposed to computer.

Scott [edited -found cleaner example],

You're into the domain of working with weighted averages (or in this case, Bayesian Ranking). I was going to demonstrate an example myself (cough!!), but then did a quick google and the page below kinda crystalises it pretty much in the way that i use it:

Bayesian Rating - how to implement a weighted rating system ...

to summerise:

Use this equation:
bayesianRank = ((avg_num_votes * avg_rating) + (this_num_votes * this_rating)) / (avg_num_votes + this_num_votes)

Legend:

avg_num_votes: The average number of votes of all items that have num_votes>0
avg_rating: The average rating of each item (again, of those that have num_votes>0)
this_num_votes: number of votes for this item
this_rating: the rating of this item

You can download a little excel sheet that i put together based on typical TBH ratings from here:

http://www.originaltalent.com/downlo...yesianRank.xls

Good luck

btw - another few refs i found:
Rating System Editorials : Bayesian method -- Neverwinter Nights 2 ...
How can I make rankings closer reflect votes? | Ask Metafilter

Lee Dumond · February 24th, 2009, 02:23 PM

Man, Jimi, that is awesome. I am gonna look into this more.

Very very cool, thanks.