diff options
author | Yorhel <git@yorhel.nl> | 2009-11-14 16:37:25 +0100 |
---|---|---|
committer | Yorhel <git@yorhel.nl> | 2009-11-14 16:43:48 +0100 |
commit | ed15643975e6e05ce5e8b9509901f769c86e06b4 (patch) | |
tree | 9cc857ca8217ce345d9b796217cd2858781c15ad /util/updates | |
parent | c7ff7086534cd9de7ce4043eb10c7161401c1b96 (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/updates')
-rw-r--r-- | util/updates/update_2.9.sql | 10 |
1 files changed, 10 insertions, 0 deletions
diff --git a/util/updates/update_2.9.sql b/util/updates/update_2.9.sql index 9bb00979..68ea2052 100644 --- a/util/updates/update_2.9.sql +++ b/util/updates/update_2.9.sql @@ -42,3 +42,13 @@ UPDATE releases_rev SET minage = NULL WHERE minage < 0; -- wikipedia link for producers 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, ( + ((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; + |