From b912190bdef96756d550eb849d8508256b394f65 Mon Sep 17 00:00:00 2001 From: Ant Zucaro Date: Wed, 29 Feb 2012 12:26:07 -0500 Subject: [PATCH] Add advanced elo checks in the merge. We want an optimistic view of the player, so we will go with the best possible elo. --- functions/merge_players.sql | 19 +++++++++++++++++++ 1 file changed, 19 insertions(+) diff --git a/functions/merge_players.sql b/functions/merge_players.sql index 0a77026..2cb6a85 100644 --- a/functions/merge_players.sql +++ b/functions/merge_players.sql @@ -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 -- 2.39.2