CODEWITHSUNDEEP
phpmysqlratingrating-system
mohsenJsh
mohsenJsh

Reputation: 2108

mysql query order by two column with weight for each of them

I have table Foo(id, name, rateAvg, rateNum). rateAvg is between 1 to 5 and rateNum is number of rates by users.

I query table with mysql and order them by most rated Foos like this:

SELECT * FROM Fooo ORDER BY rateAVG DESC, rateNum DESC

but that is not fair enough, for example one row has rateAvg of 4.8 with 1000 rates and the other with rateAvg of 5 and 10 rates and by my query item two come first.

Edit: by comment of @kingkero I found out that each of rateAvg and rateNum should have some weight for ordering, how can apply that in my query

Upvotes: 0

Views: 380

Answers (1)

alexander.polomodov
alexander.polomodov

Reputation: 5524

You can try to apply a bayesian average, but you should pre calculate this rating and store in one of your fields.

b(r) = [ W(a) * a + W(r) * r ] / (W(a) + W(r)]

r = average rating for an item
W(r) = weight of that rating, which is the number of ratings
a = average rating for your collection
W(a) = weight of that average, which is an arbitrary number,
       but should be higher if you generally expect to have more ratings
       for your items; 100 is used here, for a database which expects
       many ratings per item
b(r) = new bayesian rating

For example in your case: 

a = (4.8 * 1000 + 5 * 10) / 1010 = 4.8019
r1 = 4.8
r2 = 5
W(a) = 100 // arbitrary average weight
W(r1) = 1000 // weight of first raiting
W(r2) = 10 // weight of second rating

b(r1) = (100 * 4.8019 + 1000 * 4.8) / (100 + 1000) = 4.8001
b(r2) = (100 * 4.8019 + 10 * 5) / (100 + 10) = 4.8199

So you can see that these values are close to each other and you can try to change average weight to setup this bayesian average rating for your case.

Upvotes: 3

Related Questions