summaryrefslogtreecommitdiff
path: root/util/dump.sql
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/dump.sql
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/dump.sql')
-rw-r--r--util/dump.sql20
1 files changed, 8 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;