diff options
author | Yorhel <git@yorhel.nl> | 2009-11-15 09:06:20 +0100 |
---|---|---|
committer | Yorhel <git@yorhel.nl> | 2009-11-15 09:25:29 +0100 |
commit | 0a1b1d255224f690b226fc74ad00ad2d30cf6d25 (patch) | |
tree | c18f8c288290a6624584d12e25cd9cc6100600bf | |
parent | 7791d0236a9bcd85ea3542360b0c68a8492a909e (diff) |
SQL: Fixed bug in bayesian rating calculation
avg_rating should be the average rating of all VNs, not the global average
vote.
-rw-r--r-- | lib/Multi/Maintenance.pm | 2 | ||||
-rw-r--r-- | util/updates/update_2.9.sql | 2 |
2 files changed, 2 insertions, 2 deletions
diff --git a/lib/Multi/Maintenance.pm b/lib/Multi/Maintenance.pm index 836f24ee..0ea7ef29 100644 --- a/lib/Multi/Maintenance.pm +++ b/lib/Multi/Maintenance.pm @@ -122,7 +122,7 @@ sub vnrating { $_[KERNEL]->post(pg => do => q| UPDATE vn SET c_rating = (SELECT ( - ((SELECT COUNT(vote)::real/COUNT(DISTINCT vid)::real FROM votes)*(SELECT AVG(vote)::real FROM votes) + SUM(vote)::real) / + ((SELECT COUNT(vote)::real/COUNT(DISTINCT vid)::real FROM votes)*(SELECT AVG(a)::real FROM (SELECT AVG(vote) FROM votes GROUP BY vid) AS v(a)) + SUM(vote)::real) / ((SELECT COUNT(vote)::real/COUNT(DISTINCT vid)::real FROM votes) + COUNT(uid)::real) ) FROM votes WHERE vid = id AND uid NOT IN(SELECT id FROM users WHERE ign_votes) ), diff --git a/util/updates/update_2.9.sql b/util/updates/update_2.9.sql index 78a8d88a..d11a5eed 100644 --- a/util/updates/update_2.9.sql +++ b/util/updates/update_2.9.sql @@ -48,7 +48,7 @@ ALTER TABLE vn ADD COLUMN c_rating real; ALTER TABLE vn ADD COLUMN c_votecount integer NOT NULL DEFAULT 0; UPDATE vn SET c_rating = (SELECT ( - ((SELECT COUNT(vote)::real/COUNT(DISTINCT vid)::real FROM votes)*(SELECT AVG(vote)::real FROM votes) + SUM(vote)::real) / + ((SELECT COUNT(vote)::real/COUNT(DISTINCT vid)::real FROM votes)*(SELECT AVG(a)::real FROM (SELECT AVG(vote) FROM votes GROUP BY vid) AS v(a)) + SUM(vote)::real) / ((SELECT COUNT(vote)::real/COUNT(DISTINCT vid)::real FROM votes) + COUNT(uid)::real) ) FROM votes WHERE vid = id AND uid NOT IN(SELECT id FROM users WHERE ign_votes) ), |