From 4b6421198ab810375b6d152f0eb581a01c1017b5 Mon Sep 17 00:00:00 2001
From: Yorhel <git@yorhel.nl>
Date: Fri, 27 Nov 2009 11:31:40 +0100
Subject: Changed VN score on tags pages to display plain averages

For three reasons:
- Speed
  tag_vn_calc() is now more than 10 times faster (granted, it could
  have been a lot faster even with the bayesian rating, but whatever)
- Consistency with the tag scores displayed on the VN pages (which are
  raw averages as well)
- It didn't always make sense
---
 util/dump.sql | 20 ++++++++------------
 1 file changed, 8 insertions(+), 12 deletions(-)

(limited to 'util/dump.sql')

diff --git a/util/dump.sql b/util/dump.sql
index e0af79e8..a1a04aef 100644
--- a/util/dump.sql
+++ b/util/dump.sql
@@ -222,8 +222,8 @@ CREATE TABLE tags_vn (
   PRIMARY KEY(tag, vid, uid)
 );
 
--- tags_vn_bayesian
-CREATE TABLE tags_vn_bayesian (
+-- tags_vn_inherit
+CREATE TABLE tags_vn_inherit (
   tag integer NOT NULL,
   vid integer NOT NULL,
   users integer NOT NULL,
@@ -570,7 +570,7 @@ END;
 $$ LANGUAGE plpgsql;
 
 
--- recalculate tags_vn_bayesian
+-- recalculate tags_vn_inherit
 CREATE OR REPLACE FUNCTION tag_vn_calc() RETURNS void AS $$
 BEGIN
   -- all votes for all tags
@@ -581,21 +581,17 @@ BEGIN
     SELECT tag, vid, uid, MAX(vote)::real AS vote, COALESCE(AVG(spoiler), 0)::real AS spoiler
     FROM tags_vn_all GROUP BY tag, vid, uid;
   -- grouped by (tag, vid) and serialized into a table
-  DROP INDEX IF EXISTS tags_vn_bayesian_tag;
-  TRUNCATE tags_vn_bayesian;
-  INSERT INTO tags_vn_bayesian
+  DROP INDEX IF EXISTS tags_vn_inherit_tag;
+  TRUNCATE tags_vn_inherit;
+  INSERT INTO tags_vn_inherit
       SELECT tag, vid, COUNT(uid) AS users, AVG(vote)::real AS rating,
           (CASE WHEN AVG(spoiler) < 0.7 THEN 0 WHEN AVG(spoiler) > 1.3 THEN 2 ELSE 1 END)::smallint AS spoiler
         FROM tags_vn_grouped
     GROUP BY tag, vid
       HAVING AVG(vote) > 0;
-  CREATE INDEX tags_vn_bayesian_tag ON tags_vn_bayesian (tag);
-  -- now perform the bayesian ranking calculation
-  UPDATE tags_vn_bayesian tvs SET rating =
-      ((SELECT AVG(users)::real * AVG(rating)::real FROM tags_vn_bayesian WHERE tag = tvs.tag) + users*rating)
-    / ((SELECT AVG(users)::real FROM tags_vn_bayesian WHERE tag = tvs.tag) + users)::real;
+  CREATE INDEX tags_vn_inherit_tag ON tags_vn_inherit (tag);
   -- and update the VN count in the tags table as well
-  UPDATE tags SET c_vns = (SELECT COUNT(*) FROM tags_vn_bayesian WHERE tag = id);
+  UPDATE tags SET c_vns = (SELECT COUNT(*) FROM tags_vn_inherit WHERE tag = id);
   RETURN;
 END;
 $$ LANGUAGE plpgsql;
-- 
cgit v1.2.3