Do a weighted average instead of picking the max.
[xonotic/xonstatdb.git] / functions / merge_players.sql
1 create or replace function merge_players(p_winner_player_id players.player_id%TYPE, p_loser_player_id players.player_id%TYPE) RETURNS void as
2 $$
3 declare
4    rowcount integer;
5    weighted_elo record;
6 begin
7    -- start with weapon stats
8    update player_weapon_stats
9    set player_id = p_winner_player_id
10    where player_id = p_loser_player_id;
11
12    get diagnostics rowcount = ROW_COUNT;
13    raise notice '% weapon stat rows updated.', rowcount;
14
15    -- then game stats
16    update player_game_stats
17    set player_id = p_winner_player_id
18    where player_id = p_loser_player_id;
19
20    get diagnostics rowcount = ROW_COUNT;
21    raise notice '% game stat rows updated.', rowcount;
22
23    -- copy player_elos entries that don't currently exist for the winner
24    insert into xonstat.player_elos
25    select p_winner_player_id, game_type_cd, games, elo from xonstat.player_elos pe
26    where player_id = p_loser_player_id
27    and not exists (select 1 from xonstat.player_elos where player_id = p_winner_player_id and game_type_cd = pe.game_type_cd);
28
29    -- take the weighted average of the elos and sum the games to get the new total
30    FOR weighted_elo IN (select game_type_cd, elo_sum/game_count aggregate_elo, game_count
31       from (
32          select game_type_cd, sum(games*elo) elo_sum, sum(games) game_count 
33          from player_elos 
34          where player_id in (p_winner_player_id, p_loser_player_id) 
35          group by game_type_cd
36       ) es)
37    LOOP
38       update xonstat.player_elos
39       set elo = weighted_elo.aggregate_elo, games = weighted_elo.game_count
40       where player_id = p_winner_player_id
41       and game_type_cd = weighted_elo.game_type_cd;
42
43       raise notice 'New % Elo is %.', weighted_elo.game_type_cd, weighted_elo.aggregate_elo;
44    END LOOP;
45
46    -- update the existing player_elos entries that have a higher elo on the loser
47    -- update xonstat.player_elos pe
48    -- set elo = newpe.elo
49    -- from
50       -- (select lpe.game_type_cd, lpe.elo
51       -- from xonstat.player_elos wpe, xonstat.player_elos lpe
52       -- where wpe.game_type_cd = lpe.game_type_cd
53       -- and wpe.player_id = 67
54       -- and lpe.player_id = 720
55       -- and lpe.elo > wpe.elo) newpe
56    -- where player_id = 67
57    -- and pe.game_type_cd = newpe.game_type_cd;
58
59    -- then hashkeys (winner takes the loser's hashkey)
60    update hashkeys
61    set player_id = p_winner_player_id
62    where player_id = p_loser_player_id;
63
64    get diagnostics rowcount = ROW_COUNT;
65    raise notice '% hashkeys copied.', rowcount;
66
67    -- and finally deactivate the old player record
68    update players set active_ind = false where player_id = p_loser_player_id;
69 end;
70 $$
71 language plpgsql;