Merge branch 'master' of github.com:antzucaro/xonstatdb

diff --git a/queries/win_ratio_by_player_id.sql b/queries/win_ratio_by_player_id.sql
new file mode 100644 (file)
index 0000000..09bf360
--- /dev/null
+++ b/queries/win_ratio_by_player_id.sql
@@ -0,0 +1,21 @@
+-- win ratio per player, per game type
+SELECT game_type_cd,
+       SUM(win),
+       SUM(loss)
+FROM   (SELECT g.game_id,
+               g.game_type_cd,
+               CASE
+                 WHEN g.winner = pgs.team THEN 1
+                 WHEN pgs.rank = 1 THEN 1
+                 ELSE 0
+               END win,
+               CASE
+                 WHEN g.winner = pgs.team THEN 0
+                 WHEN pgs.rank = 1 THEN 0
+                 ELSE 1
+               END loss
+        FROM   games g,
+               player_game_stats pgs
+        WHERE  g.game_id = pgs.game_id
+               AND pgs.player_id = 6) win_loss
+GROUP  BY game_type_cd;

diff --git a/tables/player_game_stats.tab b/tables/player_game_stats.tab
index 9a9f681a5d91f3bb7239a7db0c37dc9a87ebce8c..90fd7bae9a434b36760255cb023237b9eb1ee0fb 100755 (executable)
--- a/tables/player_game_stats.tab
+++ b/tables/player_game_stats.tab
@@ -27,6 +27,8 @@ CREATE TABLE xonstat.player_game_stats
   elo_delta numeric,
   fastest_cap interval,
   avg_latency numeric,
+  teamrank integer,
+  scoreboardpos integer,
   create_dt timestamp without time zone NOT NULL DEFAULT (current_timestamp at time zone 'UTC'),
   CONSTRAINT player_game_stats_pk PRIMARY KEY (player_game_stat_id),
   CONSTRAINT player_game_stats_fk001 FOREIGN KEY (player_id)