diff options
author | Yorhel <git@yorhel.nl> | 2010-01-29 20:24:42 +0100 |
---|---|---|
committer | Yorhel <git@yorhel.nl> | 2010-01-29 20:24:42 +0100 |
commit | bd67ede5ec7284f5ca0199e413cad3bb6d65f6fe (patch) | |
tree | ea47821359469a955e682944e0492fb04db3105b /util | |
parent | 0c150eca12e06b10683b729779d784cf82074e50 (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.
Diffstat (limited to 'util')
-rw-r--r-- | util/sql/func.sql | 4 |
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 |