diff options
| author | Yorhel <git@yorhel.nl> | 2009-11-14 18:46:59 +0100 |
|---|---|---|
| committer | Yorhel <git@yorhel.nl> | 2009-11-14 18:46:59 +0100 |
| commit | 151a8338c931389c6d67389c56c2d8862e882de6 (patch) | |
| tree | 3f5c8c691f92005324c5c2cd965a4a0d04c4fcd1 /util/updates | |
| parent | 2394a77e8d503ebcf78109ced76680e46770b617 (diff) | |
SQL: Cached bayesian VN rating and vote counts
Was a good idea after all...
Diffstat (limited to 'util/updates')
| -rw-r--r-- | util/updates/update_2.9.sql | 12 |
1 files changed, 7 insertions, 5 deletions
diff --git a/util/updates/update_2.9.sql b/util/updates/update_2.9.sql index 68ea2052..0720681e 100644 --- a/util/updates/update_2.9.sql +++ b/util/updates/update_2.9.sql @@ -44,11 +44,13 @@ ALTER TABLE producers_rev ADD COLUMN l_wp varchar(150); -- bayesian rating -CREATE OR REPLACE VIEW vn_ratings AS - SELECT vid, COUNT(uid) AS votecount, ( +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) + COUNT(uid)::real) - ) AS rating - FROM votes - GROUP BY vid; + ) FROM votes WHERE vid = id + ), + c_votecount = COALESCE((SELECT count(*) FROM votes WHERE vid = id), 0); |