diff options
author | Yorhel <git@yorhel.nl> | 2009-11-27 11:31:40 +0100 |
---|---|---|
committer | Yorhel <git@yorhel.nl> | 2009-11-27 11:31:40 +0100 |
commit | 4b6421198ab810375b6d152f0eb581a01c1017b5 (patch) | |
tree | 9bf004947aed339bf0eee58964e754ccb8176da6 | |
parent | 8448e6a074a5b5899db99567b625506fc57689c9 (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
-rw-r--r-- | ChangeLog | 3 | ||||
-rw-r--r-- | lib/VNDB/DB/Tags.pm | 10 | ||||
-rw-r--r-- | lib/VNDB/DB/VN.pm | 6 | ||||
-rw-r--r-- | lib/VNDB/Handler/Tags.pm | 2 | ||||
-rw-r--r-- | util/dump.sql | 20 | ||||
-rw-r--r-- | util/updates/update_2.10.sql | 38 |
6 files changed, 58 insertions, 21 deletions
@@ -1,3 +1,6 @@ +git - ? + - VN score on tag pages use plain averages instead of bayesian rating + 2.9 - 2009-11-16 - Fixed another bug with the calculation of tags_vn_bayesian.spoiler - Implemented proper daemonizing and error handling for Multi diff --git a/lib/VNDB/DB/Tags.pm b/lib/VNDB/DB/Tags.pm index 8cf4ee29..16b39a5b 100644 --- a/lib/VNDB/DB/Tags.pm +++ b/lib/VNDB/DB/Tags.pm @@ -200,21 +200,21 @@ sub dbTagStats { # Argument: %options->{ tag order page results maxspoil } sub dbTagVNs { my($self, %o) = @_; - $o{order} ||= 'tb.rating DESC'; + $o{order} ||= 'th.rating DESC'; $o{page} ||= 1; $o{results} ||= 10; my %where = ( 'tag = ?' => $o{tag}, defined $o{maxspoil} ? ( - 'tb.spoiler <= ?' => $o{maxspoil} ) : (), + 'th.spoiler <= ?' => $o{maxspoil} ) : (), 'v.hidden = FALSE' => 1, ); my($r, $np) = $self->dbPage(\%o, q| - SELECT tb.tag, tb.vid, tb.users, tb.rating, tb.spoiler, vr.title, vr.original, v.c_languages, v.c_released, v.c_platforms, v.c_popularity - FROM tags_vn_bayesian tb - JOIN vn v ON v.id = tb.vid + SELECT th.tag, th.vid, th.users, th.rating, th.spoiler, vr.title, vr.original, v.c_languages, v.c_released, v.c_platforms, v.c_popularity + FROM tags_vn_inherit th + JOIN vn v ON v.id = th.vid JOIN vn_rev vr ON vr.id = v.latest !W ORDER BY !s|, diff --git a/lib/VNDB/DB/VN.pm b/lib/VNDB/DB/VN.pm index 250f5267..55df48ee 100644 --- a/lib/VNDB/DB/VN.pm +++ b/lib/VNDB/DB/VN.pm @@ -33,11 +33,11 @@ sub dbVNGet { $o{platform} && @{$o{platform}} ? ( '('.join(' OR ', map "v.c_platforms ILIKE '%%$_%%'", @{$o{platform}}).')' => 1 ) : (), $o{tags_include} && @{$o{tags_include}} ? ( - 'v.id IN(SELECT vid FROM tags_vn_bayesian WHERE tag IN(!l) AND spoiler <= ? GROUP BY vid HAVING COUNT(tag) = ?)', + 'v.id IN(SELECT vid FROM tags_vn_inherit WHERE tag IN(!l) AND spoiler <= ? GROUP BY vid HAVING COUNT(tag) = ?)', [ $o{tags_include}[1], $o{tags_include}[0], $#{$o{tags_include}[1]}+1 ] ) : (), $o{tags_exclude} && @{$o{tags_exclude}} ? ( - 'v.id NOT IN(SELECT vid FROM tags_vn_bayesian WHERE tag IN(!l))' => [ $o{tags_exclude} ] ) : (), + 'v.id NOT IN(SELECT vid FROM tags_vn_inherit WHERE tag IN(!l))' => [ $o{tags_exclude} ] ) : (), # don't fetch hidden items unless we ask for an ID !$o{id} && !$o{rev} ? ( 'v.hidden = FALSE' => 0 ) : (), @@ -94,7 +94,7 @@ sub dbVNGet { '(SELECT COUNT(*)+1 FROM vn iv WHERE iv.hidden = false AND iv.c_rating > v.c_rating) AS r_ranking', ) : (), $tag_ids ? - qq|(SELECT AVG(tvb.rating) FROM tags_vn_bayesian tvb WHERE tvb.tag IN($tag_ids) AND tvb.vid = v.id AND spoiler <= $o{tags_include}[0] GROUP BY tvb.vid) AS tagscore| : (), + qq|(SELECT AVG(tvh.rating) FROM tags_vn_inherit tvh WHERE tvh.tag IN($tag_ids) AND tvh.vid = v.id AND spoiler <= $o{tags_include}[0] GROUP BY tvh.vid) AS tagscore| : (), ); my($r, $np) = $self->dbPage(\%o, q| diff --git a/lib/VNDB/Handler/Tags.pm b/lib/VNDB/Handler/Tags.pm index b28ba97a..8461fb7c 100644 --- a/lib/VNDB/Handler/Tags.pm +++ b/lib/VNDB/Handler/Tags.pm @@ -40,7 +40,7 @@ sub tagpage { my($list, $np) = $t->{meta} || $t->{state} != 2 ? ([],0) : $self->dbTagVNs( tag => $tag, - order => {score=>'tb.rating',title=>'vr.title',rel=>'v.c_released',pop=>'v.c_popularity'}->{$f->{s}}.($f->{o}eq'a'?' ASC':' DESC'), + order => {score=>'th.rating',title=>'vr.title',rel=>'v.c_released',pop=>'v.c_popularity'}->{$f->{s}}.($f->{o}eq'a'?' ASC':' DESC').($f->{s}eq'score'?', th.users DESC':''), page => $f->{p}, results => 50, maxspoil => $f->{m}, 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(); + + |