summaryrefslogtreecommitdiff
path: root/util
diff options
context:
space:
mode:
authorYorhel <git@yorhel.nl>2009-11-14 18:46:59 +0100
committerYorhel <git@yorhel.nl>2009-11-14 18:46:59 +0100
commit151a8338c931389c6d67389c56c2d8862e882de6 (patch)
tree3f5c8c691f92005324c5c2cd965a4a0d04c4fcd1 /util
parent2394a77e8d503ebcf78109ced76680e46770b617 (diff)
SQL: Cached bayesian VN rating and vote counts
Was a good idea after all...
Diffstat (limited to 'util')
-rw-r--r--util/dump.sql14
-rw-r--r--util/updates/update_2.9.sql12
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);