Sort user reviews by helpfulness (like amazon.com) - I just need help with mySQL sort

andrewm

Hello,

I need some help with the mySQL SORT statement for listing reviews by how helpful they were rated by users.

I have two columns in the review table: helpfulrate and totalrate. helpfulrate contains the number of people that rated the review helpful and totalrate contains the total number of people that rated the review. I use the two columns to get "XX out of XX people found this review helpful".

Right now I am using the following text for sorting the reviews by which is most helpful:
"... ORDER by IFNULL(helpfulrate/totalrate,0) DESC,totalrate DESC"

The above code orders by the highest percentage first (of people who rated the review helpful divided by the total number of people that rated the review), then orders by the total number of people that rated the review.

So 50 out of 50 people would be higher than 1 out of 1 or 2 out of 2.

The problem is, why should a review that is only 1 out of 1 be ranked higher than a review that is 250 out of 251. I know that it shouldn't.

Amazon.com has some way for sorting well, but I just can't figure out a good way to do it.

Can anyone help at all?

I don't expect a full out answer (although it would be nice 🙂). It would be great if someone could point me in the right direction.

Thanks for your time,
Andrew

corsc

This is more of a maths question that anything. You need to decide how you are going to 'weight' the results.

There are two basic ways of doing this:
1) Mulitply all percentages by a fixed factor (relative to the total number of ratings or the total number of ratings).

Eg. %Approve x (No. of Ratings) = Effective Rank

2) Develop your own variation of the above system with sliding scales based on ranges of ratings.
Eg.

No. Ratings Equation
<25 %Approve

25 && <50 %Approve 2
50 && <100 %Approve 4
100 %Approve * 8

(Please note all numbers are made up for display only)

I hope you find this useful.

andrewm

First I want to thank you for your help! It is greatly appreciated.

Originally posted by corsc

1) Mulitply all percentages by a fixed factor (relative to the total number of ratings or the total number of ratings).

Eg. %Approve x (No. of Ratings) = Effective Rank

First I tried (HELPFUL / TOTAL) * TOTAL (hey, it was late 🙂), but then I quickly relized that it just gets the helpful number.

Then I tried (HELPFUL / TOTAL) * HELPFUL and go the following test results:
0 / 1 = 0
1 / 2 = .5
5 / 50 = .5
1 / 1 = 1
2 / 2 = 2
50 / 50 = 50
45 / 50 = 40.5
150 / 151 = 149
1000 / 1100 = 909

They are decent, but not as good as I would like.

Can you think of any other way to use a "fixed factor" with the helpful %?

Originally posted by corsc

2) Develop your own variation of the above system with sliding scales based on ranges of ratings.
Eg.

No. Ratings Equation
<25 %Approve

25 && <50 %Approve 2
50 && <100 %Approve 4
100 %Approve * 8

I would rather not use a the above method, or anything similar to it, unless it is the only option. Although it is an interesting idea, I don't like having the specified ranges.

I wonder if amazon.com will give out their way for calculating it...hey I can dream, right? 😃

Thanks again corsc. You really got me to start thinking 🙂

andrewm

After finding an example of what I would like my helpful sorting to be like here:
http://electronics.bizrate.com/marketplace/product_info/reviews__cat_id--11140000,prod_id--6487970,name--Canon%20ZR-45MC%20%28%20ZR-45MC%20%29.html

I tested it with the above sorting equation and got the following results:
(Note: The numbers are listed in the order they were at BizRate, but the end numbers represent what order they should be in, if I used my equation.)
66 / 67 = 65.01
26 / 27 = 25.03
31 / 34 = 28.26
15 / 18 = 12.5
13 / 16 = 10.56
15 / 19 = 11.84
5 / 6 = 4.16
2 / 2 = 2
4 / 6 = 2.66
1 / 3 = .33
8 / 43 = 1.48
2 / 29 = .13

As you can see, the numbers are off from BizRate in certain situations. I still can't seem to think of a way to compensate for this offset.

corsc

Andrewm,

I think I may have cracked it for you. I am not sure if this will give the exact results you want but it definately imitates the site you mentioned.
Please bare with me it is pretty raw math.

-corsc

a) Help / Total = %Help

b) FINALRank = %Help / ( 2 ^ ( %Rank / Total ) )

So in SQL .....

ORDER BY ( ( HELP / TOTAL ) / ( POWER( 2, ( ( HELP / TOTAL ) / TOTAL ) ) )

andrewm

Thanks!

I'll give it a try tomorrow.