summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
authorYorhel <git@yorhel.nl>2009-11-15 09:06:20 +0100
committerYorhel <git@yorhel.nl>2009-11-15 09:25:29 +0100
commit0a1b1d255224f690b226fc74ad00ad2d30cf6d25 (patch)
treec18f8c288290a6624584d12e25cd9cc6100600bf
parent7791d0236a9bcd85ea3542360b0c68a8492a909e (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.pm2
-rw-r--r--util/updates/update_2.9.sql2
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)
),