summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
authorYorhel <git@yorhel.nl>2010-01-29 20:24:42 +0100
committerYorhel <git@yorhel.nl>2010-01-29 20:24:42 +0100
commitbd67ede5ec7284f5ca0199e413cad3bb6d65f6fe (patch)
treeea47821359469a955e682944e0492fb04db3105b
parent0c150eca12e06b10683b729779d784cf82074e50 (diff)
SQL: Another try at fixing the tag spoiler calculation
62cb41c3b8780bffe5a8ea58a6a7b5053d9e1059 and 4895ea63323b94f0b128d6874be997a24d3a3b0d were bad jokes, really... let's hope this permanently fixes the problem.
-rw-r--r--util/sql/func.sql4
1 files changed, 2 insertions, 2 deletions
diff --git a/util/sql/func.sql b/util/sql/func.sql
index ff38f02d..ed054207 100644
--- a/util/sql/func.sql
+++ b/util/sql/func.sql
@@ -85,10 +85,10 @@ BEGIN
)
-- grouped by (tag, vid)
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
+ (CASE WHEN AVG(spoiler) > 1.3 THEN 2 WHEN AVG(spoiler) > 0.7 THEN 1 ELSE 0 END)::smallint AS spoiler
FROM (
-- grouped by (tag, vid, uid), so only one user votes on one parent tag per VN entry (also removing meta tags)
- SELECT tag, vid, uid, MAX(vote)::real, COALESCE(AVG(spoiler), 0)::real
+ SELECT tag, vid, uid, MAX(vote)::real, AVG(spoiler)::real
FROM tags_vn_all
WHERE NOT meta
GROUP BY tag, vid, uid