summaryrefslogtreecommitdiff
path: root/util/dump.sql
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/dump.sql
parent2394a77e8d503ebcf78109ced76680e46770b617 (diff)
SQL: Cached bayesian VN rating and vote counts
Was a good idea after all...
Diffstat (limited to 'util/dump.sql')
-rw-r--r--util/dump.sql14
1 files changed, 3 insertions, 11 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;