diff options
Diffstat (limited to 'util/dump.sql')
-rw-r--r-- | util/dump.sql | 14 |
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; |