summaryrefslogtreecommitdiff
path: root/util
diff options
context:
space:
mode:
authorYorhel <git@yorhel.nl>2009-11-27 11:31:40 +0100
committerYorhel <git@yorhel.nl>2009-11-27 11:31:40 +0100
commit4b6421198ab810375b6d152f0eb581a01c1017b5 (patch)
tree9bf004947aed339bf0eee58964e754ccb8176da6 /util
parent8448e6a074a5b5899db99567b625506fc57689c9 (diff)
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
Diffstat (limited to 'util')
-rw-r--r--util/dump.sql20
-rw-r--r--util/updates/update_2.10.sql38
2 files changed, 46 insertions, 12 deletions
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;
diff --git a/util/updates/update_2.10.sql b/util/updates/update_2.10.sql
new file mode 100644
index 00000000..dada4889
--- /dev/null
+++ b/util/updates/update_2.10.sql
@@ -0,0 +1,38 @@
+
+-- no more bayesian rating for VN list on tag pages, just plain averages
+DROP TABLE tags_vn_bayesian;
+CREATE TABLE tags_vn_inherit (
+ tag integer NOT NULL,
+ vid integer NOT NULL,
+ users integer NOT NULL,
+ rating real NOT NULL,
+ spoiler smallint NOT NULL
+);
+
+CREATE OR REPLACE FUNCTION tag_vn_calc() RETURNS void AS $$
+BEGIN
+ -- all votes for all tags
+ CREATE OR REPLACE TEMPORARY VIEW tags_vn_all AS
+ SELECT * FROM tags_vn UNION SELECT * FROM tag_vn_childs();
+ -- grouped by (tag, vid, uid), so only one user votes on one parent tag per VN entry
+ CREATE OR REPLACE TEMPORARY VIEW tags_vn_grouped AS
+ 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_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_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_inherit WHERE tag = id);
+ RETURN;
+END;
+$$ LANGUAGE plpgsql;
+SELECT tag_vn_calc();
+
+