summaryrefslogtreecommitdiff
path: root/util/dump.sql
diff options
context:
space:
mode:
authorYorhel <git@yorhel.nl>2009-11-14 16:37:25 +0100
committerYorhel <git@yorhel.nl>2009-11-14 16:43:48 +0100
commited15643975e6e05ce5e8b9509901f769c86e06b4 (patch)
tree9cc857ca8217ce345d9b796217cd2858781c15ad /util/dump.sql
parentc7ff7086534cd9de7ce4043eb10c7161401c1b96 (diff)
Added bayesian rating and vote count to the VN list
It's even realtime! To my surprise this calculation isn't very heavy, or PostgreSQL is just extremely fast. The GetVN query on /v/all takes 100ms in the worst case (instead of the usual 30-60ms). Can always cache this later on.
Diffstat (limited to 'util/dump.sql')
-rw-r--r--util/dump.sql10
1 files changed, 10 insertions, 0 deletions
diff --git a/util/dump.sql b/util/dump.sql
index dfdeb021..d64fe12f 100644
--- a/util/dump.sql
+++ b/util/dump.sql
@@ -829,6 +829,16 @@ 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;