Hi:

Anyone who has run a Links SQL site for long has probably seen this... only long-standing sites get a fair shake at rating high. Well, one of my users just sent me a different formula than the simple average approach that Alex has in Links... and I thought it would be worth posting here for others to share in... well, that, and I want some help writting the code to impliment it!

It is called the Bayesian Estimate, and the formula goes like this:

weighted rank (WR) = (v ÷ (v+m)) × R + (m ÷ (v+m)) × C

where:

R = average for the link
v = number of votes for the link
m = minimum votes required to be listed in the top ranking
C = the mean vote across the whole report

Basically, pretty easy. Except I am not sure how to get the "C" component, the mean vote. I know I can do:

my $rows = $db->count ( { Votes } );

to get the total number of votes, but I am not sure how to get the total sum of all the votes... call that $sum.

$sum / $rows would equal C, right?...

So:

$rec->{Rating} = ($rec->{Votes} / ($rec->{Votes} / 10)) * $rec->{Rating} + (10 / ($rec->{Votes} + 10)) x ($sum / $rows );

right? So how do I get $sum?

Thanks!


dave

Big Cartoon DataBase
Big Comic Book DataBase

I found a good write-up on this here:

http://www.wowwebdesigns.com/formula.php

I have decided the best way to attack this is to create a new table, and add the rating and incriment the vote with each rating... then call the current number each time a rating is changed. I thought this would be less cpu-intesnsive than counting and summing every time...
dave

Big Cartoon DataBase
Big Comic Book DataBase

ok, I got it running... but not perfect. It seems to be rounding to a whole number- anyone know how to fix that?

OK, here is what I did. I made a table called vote_count, with three fields. The first is a primary key, and contains "1" the second I call number, and the third I call total. If you run the sql command:

Select Rating from Links where Vote > 0

it will get you the number of returned rows (that becomes the value of field "number") then I cut and pasted those outputed values into Excel, totalled them, and that became the value of "total."

Then, to rate.pm, I added...

(Added lines with a "+")

my $rate_db = $DB->table ('Rating');
+ my $vote_db = $DB->table ('vote_count'); # open vote_count table
+ my $rec1 = $vote_db->get (1); #get the record
+ my $rows2 = $rec1->{number}; # Pop a value in
+ my $sum = $rec1->{total}; # pop another value in

and

$rec->{Rating} = $rec->{Rating} / $rec->{Votes};
+ $rec->{Rating} = (($rec->{Votes} / ($rec->{Votes} + 10)) * $rec->{Rating})+ ((10 / ($rec->{Votes} + 10)) x ($sum / $rows2) );
#the new formula for making the rating value weighted...
+ $rec1->{number} = $rec1->{number} + 1;
# Incriment the counter
+ $rec1->{total} = $rec1->{total} + $rating;
#Add the rating to the running total
+ $vote_db->update ( { number => $rec1->{number}, total => $rec1->{total} }, { id => 1} );
# Update table vote_count



There you go, pretty easy! Now, can someone tell me why I am stuck with integers, and how I can get results to two decinmal places?

Thanks!




dave

Big Cartoon DataBase
Big Comic Book DataBase

OK, it got ugly, but I got it working. For some reason, the formula above does not compile the way it should.... in the right order. I had to recode to make it do a step at a time... so it is ugly.

Here is the code that seems to work OK!

my $rate_db = $DB->table ('Rating');
+ my $vote_db = $DB->table ('vote_count');
+ my $rec1 = $vote_db->get (1);
+ my $rows2 = $rec1->{number};
+ my $sum = $rec1->{total};
+ my $c = ($sum/$rows2);

and:

$rec->{Rating} = ($rec->{Rating} * $rec->{Votes}) + $rating;
$rec->{Votes} = $rec->{Votes} + 1;
+ my $Votes = $rec->{Votes};
+ my $Votes10 = $rec->{Votes} + 10;
$rec->{Rating} = $rec->{Rating} / $rec->{Votes};
+ my $test1 = (($Votes / $Votes10) * $rec->{Rating});
+ my $test2 = 10 / $Votes10;
+ $test2 = $test2 * $c;
+ $rec->{Rating} = $test1 + $test2;
+ $rec1->{number} = $rec1->{number} + 1;
+ $rec1->{total} = $rec1->{total} + $rating;
+ $vote_db->update ( { number => $rec1->{number}, total => $rec1->{total} }, { id => 1} );
$db->update ( { Votes => $rec->{Votes}, Rating => $rec->{Rating} }, { ID => $rec->{ID} });

Seems to work pretty good for me, but comments are welcome. And if you can (or want to!) clean that mess up... have at it!
dave

Big Cartoon DataBase
Big Comic Book DataBase