Added VN popularity ranking

The following query should be run periodically to update the rankings:
  SELECT update_vnpopularity();

I'll fix Multi::Maintenance to do this automatically.

1 files changed, 14 insertions, 1 deletions

diff --git a/util/dump.sql b/util/dump.sql
index 6d7f1f26..afbbc622 100644
--- a/util/dump.sql
+++ b/util/dump.sql
@@ -199,7 +199,8 @@ CREATE TABLE vn (
   rgraph integer,
   c_released integer NOT NULL DEFAULT 0,
   c_languages varchar(32) NOT NULL DEFAULT '',
-  c_platforms varchar(32) NOT NULL DEFAULT ''
+  c_platforms varchar(32) NOT NULL DEFAULT '',
+  c_popularity real NOT NULL DEFAULT 0
 );
 
 -- vn_anime
@@ -413,6 +414,18 @@ END;
 $$ LANGUAGE plpgsql;
 
 
+-- recalculate vn.c_popularity
+CREATE OR REPLACE FUNCTION update_vnpopularity() RETURNS void AS $$
+BEGIN
+  CREATE OR REPLACE TEMP VIEW tmp_pop1 (uid, vid, rank) AS
+    SELECT v.uid, v.vid, sqrt(count(*))::real FROM votes v JOIN votes v2 ON v.uid = v2.uid AND v2.vote < v.vote GROUP BY v.vid, v.uid;
+  CREATE OR REPLACE TEMP VIEW tmp_pop2 (vid, win) AS
+    SELECT vid, sum(rank) FROM tmp_pop1 GROUP BY vid;
+  UPDATE vn SET c_popularity = COALESCE((SELECT win/(SELECT MAX(win) FROM tmp_pop2) FROM tmp_pop2 WHERE vid = id), 0);
+  RETURN;
+END;
+$$ LANGUAGE plpgsql;
+