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 | |
parent | 2394a77e8d503ebcf78109ced76680e46770b617 (diff) |
SQL: Cached bayesian VN rating and vote counts
Was a good idea after all...
Diffstat (limited to 'util')
-rw-r--r-- | util/dump.sql | 14 | ||||
-rw-r--r-- | util/updates/update_2.9.sql | 12 |
2 files changed, 10 insertions, 16 deletions
diff --git a/util/dump.sql b/util/dump.sql index d64fe12f..43562da3 100644 --- a/util/dump.sql +++ b/util/dump.sql @@ -291,7 +291,9 @@ CREATE TABLE vn ( c_released integer NOT NULL DEFAULT 0, c_languages varchar(32) NOT NULL DEFAULT '', c_platforms varchar(32) NOT NULL DEFAULT '', - c_popularity real NOT NULL DEFAULT 0 + c_popularity real NOT NULL DEFAULT 0, + c_rating real, + c_votecount integer NOT NULL DEFAULT 0 ); -- vn_anime @@ -829,16 +831,6 @@ CREATE TRIGGER insert_notify AFTER INSERT ON tags FOR EACH STATEMENT EXECUTE PRO --------------------------------- --- bayesian rating view -CREATE OR REPLACE VIEW vn_ratings AS - SELECT vid, COUNT(uid) AS votecount, ( - ((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; - - -- Sequences used for ID generation of items not in the DB CREATE SEQUENCE covers_seq; 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); |