Add advanced elo checks in the merge. We want an optimistic view of the player, so...
authorAnt Zucaro <azucaro@gmail.com>
Wed, 29 Feb 2012 17:26:07 +0000 (12:26 -0500)
committerAnt Zucaro <azucaro@gmail.com>
Wed, 29 Feb 2012 17:26:07 +0000 (12:26 -0500)
functions/merge_players.sql

index 0a77026..2cb6a85 100644 (file)
@@ -19,6 +19,25 @@ begin
    get diagnostics rowcount = ROW_COUNT;
    raise notice '% game stat rows updated.', rowcount;
 
+   -- copy player_elos entries that don't currently exist for the winner
+   insert into xonstat.player_elos
+   select p_winner_player_id, game_type_cd, games, elo from xonstat.player_elos pe
+   where player_id = p_loser_player_id
+   and not exists (select 1 from xonstat.player_elos where player_id = p_winner_player_id and game_type_cd = pe.game_type_cd);
+
+   -- update the existing player_elos entries that have a higher elo on the loser
+   update xonstat.player_elos pe
+   set elo = newpe.elo
+   from
+      (select lpe.game_type_cd, lpe.elo
+      from xonstat.player_elos wpe, xonstat.player_elos lpe
+      where wpe.game_type_cd = lpe.game_type_cd
+      and wpe.player_id = 67
+      and lpe.player_id = 720
+      and lpe.elo > wpe.elo) newpe
+   where player_id = 67
+   and pe.game_type_cd = newpe.game_type_cd;
+
    -- then hashkeys (winner takes the loser's hashkey)
    update hashkeys
    set player_id = p_winner_player_id