Kalith

New effort to create tank specific values for wn8 rating

102 posts in this topic

1 hour ago, Gryphon_ said:

I'm not able to read SQL so wanted to check a few things that are key to the expected values calculation. First you apply the 1000 battles per account filter, then the 50 battles per tank filter. Then using the last set of expected values, you calculate the rSTATs and then the tank WN8 for every account's tanks, then the weighted average rSTATs and WN8 for each account, based on the tanks they have left in the dataset.

Then - most important - you find the median tank by WN8 for every account, and filter out all the tanks in every account below their median. THIS CUTS THE DATASET IN HALF but leaves you with data on tanks that each account has played well. The 'worst half' of every accounts' tanks are GONE and not used. If you dont do this step - its not WN8. Looking at your script, I don't know if you are doing this or not.

That's exactly what I'm doing. The script above is only for creating initial expected values for the new tanks. I've attached full script with whole calculation.

But my results is not so good, may be that's because of the different samples - as I know, you have used data from vbAddict, whereas our sample includes all players who played last 3 months. May be we have many weak players and they spoil statistics.

wn8.sql

/*
    NOTES:
        - players with at least 1 battle during last 3 months
        - players with 1000+ battles
        - tanks with 50+ battles
    wn8.data with source data have such schema:
      player_id bigint - player id
      tank_id int - tank id
      partkey date - player's last update date
      battles int - number of battles on the tank
      wins int - wins
      dmg int - damage dealt
      frg int - frags
      spo int - spotted
      def int - defence points
*/

-- calculate initial expected values for new tanks
CREATE OR REPLACE FUNCTION wn8.fill_missed_expected_values()
RETURNS VOID
LANGUAGE plpgsql
AS $$
DECLARE
    i int := 0;
    l_total int;
    l_tank_ids int[];
    l_tank_id int;
    l_cnt int;
    l_ofs_lo int;
    l_ofs_hi int;
    l_battles bigint;
    l_tag text;
    e_dmg float8;
    e_frg float8;
    e_spo float8;
    e_def float8;
    e_win float8;
BEGIN
    RAISE LOG '[WN8] === calculating initial wn8.exp for new tanks ===';

    l_tank_ids := ARRAY(SELECT tank_id FROM xvm.vehicles_list ORDER BY tank_id);
    l_total := array_length(l_tank_ids, 1);
    RAISE LOG '[WN8] % tanks in the vehicles list', l_total;

    i := 0;
    FOREACH l_tank_id IN ARRAY l_tank_ids LOOP
        i := i + 1;
        IF EXISTS(SELECT 1 FROM wn8.exp WHERE tank_id = l_tank_id LIMIT 1) THEN
            CONTINUE;
        END IF;

        -- new tank - select initial expected values as avg between median and top 1000
        SELECT
            COUNT(tank_id),
            SUM(battles)
        FROM wn8.data
        WHERE tank_id = l_tank_id
        INTO l_cnt, l_battles;
        l_ofs_lo := l_cnt / 100; -- 1%
        l_ofs_hi := l_cnt / 10000; -- 0.01%
        l_tag := tag FROM xvm.vehicles_list WHERE tank_id = l_tank_id;
        RAISE LOG '[WN8] tank_id=% cnt=% offset=%..% battles=% %', l_tank_id, l_cnt, l_ofs_lo, l_ofs_hi, l_battles, l_tag;

        IF l_cnt < 10000 THEN
            RAISE LOG '        skip: low players count';
            CONTINUE;
        END IF;

        WITH
            xx AS (
                SELECT
                    (dmg::float8 / battles) AS a_dmg,
                    (frg::float8 / battles) AS a_frg,
                    (spo::float8 / battles) AS a_spo,
                    (def::float8 / battles) AS a_def,
                    (100::float8 * wins / battles) AS a_win
                FROM wn8.data WHERE tank_id=l_tank_id),
            ld AS (SELECT a_dmg as v FROM xx ORDER BY a_dmg DESC LIMIT 1 OFFSET l_ofs_lo),
            lf AS (SELECT a_frg as v FROM xx ORDER BY a_frg DESC LIMIT 1 OFFSET l_ofs_lo),
            ls AS (SELECT a_spo as v FROM xx ORDER BY a_spo DESC LIMIT 1 OFFSET l_ofs_lo),
            le AS (SELECT a_def as v FROM xx ORDER BY a_def DESC LIMIT 1 OFFSET l_ofs_lo),
            lw AS (SELECT a_win as v FROM xx ORDER BY a_win DESC LIMIT 1 OFFSET l_ofs_lo),
            hd AS (SELECT a_dmg as v FROM xx ORDER BY a_dmg DESC LIMIT 1 OFFSET l_ofs_hi),
            hf AS (SELECT a_frg as v FROM xx ORDER BY a_frg DESC LIMIT 1 OFFSET l_ofs_hi),
            hs AS (SELECT a_spo as v FROM xx ORDER BY a_spo DESC LIMIT 1 OFFSET l_ofs_hi),
            he AS (SELECT a_def as v FROM xx ORDER BY a_def DESC LIMIT 1 OFFSET l_ofs_hi),
            hw AS (SELECT a_win as v FROM xx ORDER BY a_win DESC LIMIT 1 OFFSET l_ofs_hi),
            mv AS (
                SELECT
                    median(a_dmg) as m_dmg,
                    median(a_frg) as m_frg,
                    median(a_spo) as m_spo,
                    median(a_def) as m_def,
                    median(a_win) as m_win
                FROM xx, ld, lf, ls, le, lw, hd, hf, hs, he, hw
                WHERE xx.a_dmg >= ld.v AND xx.a_dmg <= hd.v
                   OR xx.a_frg >= lf.v AND xx.a_frg <= hf.v
                   OR xx.a_spo >= ls.v AND xx.a_spo <= hs.v
                   OR xx.a_def >= le.v AND xx.a_def <= he.v
                   OR xx.a_win >= lw.v AND xx.a_win <= hw.v)
        SELECT m_dmg, m_frg, m_spo, m_def, m_win FROM mv
        INTO e_dmg, e_frg, e_spo, e_def, e_win;

        RAISE LOG '[WN8] %/% e_dmg=% e_frg=% e_spo=% e_def=% e_win=% tank_id=% %',
            i, l_total, e_dmg::numeric(9,2), e_frg::numeric(9,2), e_spo::numeric(9,2), e_def::numeric(9,2), e_win::numeric(9,2), l_tank_id, l_tag;
        INSERT INTO wn8.exp(partkey, tank_id, battles, dmg, frg, spo, def, win)
        VALUES ('epoch'::date, l_tank_id, l_battles, e_dmg, e_frg, e_spo, e_def, e_win);
    END LOOP;
END;
$$;

CREATE OR REPLACE FUNCTION wn8.calculate_expected_values_1()
RETURNS VOID
LANGUAGE plpgsql
AS $$
DECLARE
    i int := 0;
    l_total int;
    l_tank_ids int[];
    l_tank_id int;
    l_tag text;
    e_dmg float8;
    e_frg float8;
    e_spo float8;
    e_def float8;
    e_win float8;
BEGIN
    RAISE LOG '[WN8] === calculating wn8.exp (STEP 1/3) ===';

    -- create temp tables
    CREATE TEMP TABLE tmp_exp (LIKE wn8.tmp_exp) WITH (OIDS=FALSE) TABLESPACE pg_default;
    CREATE TEMP TABLE tmp_exp_median (player_id bigint, wn8_median real) WITH (OIDS=FALSE) TABLESPACE ssd;

    -- loop for each tank
    l_tank_ids := ARRAY(SELECT tank_id FROM wn8.exp GROUP BY tank_id ORDER BY tank_id);
    l_total := array_length(l_tank_ids, 1);
    RAISE LOG '[WN8] processing % tanks', l_total;

    i := 0;
    FOREACH l_tank_id IN ARRAY l_tank_ids LOOP
        l_tag := tag FROM xvm.vehicles_list WHERE tank_id = l_tank_id;
        i := i + 1;

        -- select last expected values for current tank
        SELECT dmg, frg, spo, def, win FROM wn8.exp WHERE tank_id = l_tank_id ORDER BY partkey DESC
        INTO e_dmg, e_frg, e_spo, e_def, e_win;
        RAISE LOG '[WN8] [1] %/% e_dmg=% e_frg=% e_spo=% e_def=% e_win=% tank_id=% %',
            i, l_total, e_dmg::numeric(9,2), e_frg::numeric(9,2), e_spo::numeric(9,2), e_def::numeric(9,2), e_win::numeric(9,2), l_tank_id, l_tag;

        INSERT INTO tmp_exp (
            player_id, tank_id, battles, r_dmg, r_frg, r_spo, r_def, r_win, r_dmg_c, r_frg_c, r_spo_c, r_def_c, r_win_c, wn8
        )
        WITH
            src AS (SELECT * FROM wn8.data WHERE tank_id = l_tank_id),
            -- calc actuals
            actuals AS (
                SELECT
                    player_id,
                    tank_id,
                    battles,
                    (dmg::float8 / battles) AS a_dmg,
                    (frg::float8 / battles) AS a_frg,
                    (spo::float8 / battles) AS a_spo,
                    (def::float8 / battles) AS a_def,
                    (100::float8 * wins / battles) AS a_win
                FROM src),
            -- calculate the user rSTATS
            rstats AS (
                SELECT
                    *,
                    (a_dmg / e_dmg) as r_dmg,
                    (a_frg / e_frg) as r_frg,
                    (a_spo / e_spo) as r_spo,
                    (a_def / e_def) as r_def,
                    (a_win / e_win) as r_win
                FROM actuals),
            -- calculate the user rSTATc's
            rstatc_1 AS (
                SELECT
                    *,
                    GREATEST(0, (r_dmg - 0.22) / (1 - 0.22)) as r_dmg_c
                FROM rstats),
            rstatc AS (
                SELECT
                    *,
                    GREATEST(0, LEAST(r_dmg_c + 0.2, ((r_frg - 0.12) / (1 - 0.12)))) as r_frg_c,
                    GREATEST(0, LEAST(r_dmg_c + 0.1, ((r_spo - 0.38) / (1 - 0.38)))) as r_spo_c,
                    GREATEST(0, LEAST(r_dmg_c + 0.1, ((r_def - 0.10) / (1 - 0.10)))) as r_def_c,
                    GREATEST(0, (r_win - 0.71) / (1 - 0.71)) as r_win_c
                FROM rstatc_1),
            -- calculate the user WN8 per tank 
            wn8 AS (
                SELECT
                    *,
                    980 * r_dmg_c + 210 * r_dmg_c * r_frg_c + 155 * r_frg_c * r_spo_c + 75 * r_frg_c * r_def_c + 145 * LEAST(1.8, r_win_c) as wn8
                FROM rstatc)
        SELECT
            player_id, tank_id, battles, r_dmg, r_frg, r_spo, r_def, r_win, r_dmg_c, r_frg_c, r_spo_c, r_def_c, r_win_c, wn8
        FROM wn8;
    END LOOP;

    -- filter out all tanks where WN8 is below median WN8 for every users' tanks

    RAISE LOG '[WN8] calculate tmp_exp_median';
    INSERT INTO tmp_exp_median (player_id, wn8_median)
    SELECT
        player_id,
        median(wn8)
    FROM tmp_exp
    GROUP BY player_id
    ORDER BY player_id;

    RAISE LOG '[WN8] ANALYZE tmp_exp_median';
    ANALYZE tmp_exp_median;

    RAISE LOG '[WN8] delete below median';
    TRUNCATE TABLE wn8.tmp_exp;
    DROP INDEX wn8.tmp_exp_tank_id_idx;
    INSERT INTO wn8.tmp_exp
    SELECT a.*
    FROM tmp_exp a
    JOIN tmp_exp_median b USING (player_id)
    WHERE a.wn8 >= b.wn8_median
    ORDER BY a.tank_id, a.player_id;

    DROP TABLE tmp_exp;

    RAISE LOG '[WN8] create index on wn8.tmp_exp';
    CREATE INDEX tmp_exp_tank_id_idx ON wn8.tmp_exp(tank_id) TABLESPACE ssd;

    RAISE LOG '[WN8] ANALYZE wn8.tmp_exp';
    ANALYZE wn8.tmp_exp;
END;
$$;

CREATE OR REPLACE FUNCTION wn8.calculate_expected_values_2()
RETURNS VOID
LANGUAGE plpgsql
AS $$
BEGIN
    RAISE LOG '[WN8] === calculating wn8.exp (STEP 2/3) ===';

    -- calculate the user account WN8, rSTATs, and rSTATSc

    RAISE LOG '[WN8] create wn8.tmp_user_account_stats';

    TRUNCATE TABLE wn8.tmp_user_account_stats;

    ALTER TABLE wn8.tmp_user_account_stats DROP CONSTRAINT tmp_user_account_stats_pkey;

    INSERT INTO wn8.tmp_user_account_stats (player_id, battles, wn8, r_dmg, r_frg, r_spo, r_def, r_win, r_dmg_c, r_frg_c, r_spo_c, r_def_c, r_win_c)
    SELECT
        player_id,
        battles,
        wn8_p / battles,
        r_dmg_p / battles,
        r_frg_p / battles,
        r_spo_p / battles,
        r_def_p / battles,
        r_win_p / battles,
        r_dmg_cp / battles,
        r_frg_cp / battles,
        r_spo_cp / battles,
        r_def_cp / battles,
        r_win_cp / battles
    FROM (
        SELECT
            player_id,
            sum(battles) as battles,
            sum(wn8 * battles) as wn8_p,
            sum(r_dmg * battles) as r_dmg_p,
            sum(r_frg * battles) as r_frg_p,
            sum(r_spo * battles) as r_spo_p,
            sum(r_def * battles) as r_def_p,
            sum(r_win * battles) as r_win_p,
            sum(r_dmg_c * battles) as r_dmg_cp,
            sum(r_frg_c * battles) as r_frg_cp,
            sum(r_spo_c * battles) as r_spo_cp,
            sum(r_def_c * battles) as r_def_cp,
            sum(r_win_c * battles) as r_win_cp
        FROM wn8.tmp_exp
        GROUP BY player_id
        ORDER BY player_id
    ) a;

    RAISE LOG '[WN8] create index on wn8.tmp_user_account_stats';
    ALTER TABLE wn8.tmp_user_account_stats ADD CONSTRAINT tmp_user_account_stats_pkey PRIMARY KEY (player_id) USING INDEX TABLESPACE ssd;

    RAISE LOG '[WN8] ANALYZE wn8.tmp_user_account_stats';
    ANALYZE wn8.tmp_user_account_stats;
END;
$$;

CREATE OR REPLACE FUNCTION wn8.calculate_expected_values_3()
RETURNS VOID
LANGUAGE plpgsql
AS $$
DECLARE
    l_partkey date;
    i int := 0;
    l_total int;
    l_tank_ids int[];
    l_tank_id int;
    l_tag text;
    l_battles bigint;
    e_dmg float8;
    e_frg float8;
    e_spo float8;
    e_def float8;
    e_win float8;
    n_dmg float8;
    n_frg float8;
    n_spo float8;
    n_def float8;
    n_win float8;
BEGIN
    RAISE LOG '[WN8] === calculating wn8.exp (STEP 3/3) ===';

    l_partkey = MAX(partkey) FROM wn8.data;

    l_tank_ids := ARRAY(SELECT tank_id FROM wn8.exp GROUP BY tank_id ORDER BY tank_id);
    l_total := array_length(l_tank_ids, 1);
    RAISE LOG '[WN8] processing % tanks', l_total;

    RAISE LOG '[WN8] calculate new expected values';
    i := 0;
    FOREACH l_tank_id IN ARRAY l_tank_ids LOOP
        l_tag := tag FROM xvm.vehicles_list WHERE tank_id = l_tank_id;
        i := i + 1;

        -- select last expected values for current tank
        SELECT dmg, frg, spo, def, win FROM wn8.exp WHERE tank_id = l_tank_id ORDER BY partkey DESC
        INTO e_dmg, e_frg, e_spo, e_def, e_win;

        RAISE LOG '[WN8] [2] %/% e_dmg=% e_frg=% e_spo=% e_def=% e_win=% tank_id=% %',
            i, l_total, e_dmg::numeric(9,2), e_frg::numeric(9,2), e_spo::numeric(9,2), e_def::numeric(9,2), e_win::numeric(9,2), l_tank_id, l_tag;

        SELECT
            sum(a.battles),
            (regr_intercept(a.r_dmg, b.r_dmg) + regr_slope(a.r_dmg, b.r_dmg)) * e_dmg,
            (regr_intercept(a.r_frg, b.r_frg) + regr_slope(a.r_frg, b.r_frg)) * e_frg,
            (regr_intercept(a.r_spo, b.r_spo) + regr_slope(a.r_spo, b.r_spo)) * e_spo,
            (regr_intercept(a.r_def, b.r_def) + regr_slope(a.r_def, b.r_def)) * e_def,
            (regr_intercept(a.r_win, b.r_win) + regr_slope(a.r_win, b.r_win)) * e_win
        FROM wn8.tmp_exp a
        JOIN wn8.tmp_user_account_stats b USING(player_id)
        WHERE a.tank_id = l_tank_id
        INTO l_battles, n_dmg, n_frg, n_spo, n_def, n_win;

        IF l_battles IS NULL THEN
            RAISE LOG '          skip: no players';
            CONTINUE;
        END IF;

        IF n_dmg IS NULL THEN
            RAISE LOG '          skip: dmg IS NULL';
            CONTINUE;
        END IF;

        IF n_frg IS NULL THEN
            RAISE LOG '          skip: frg IS NULL';
            CONTINUE;
        END IF;

        IF n_spo IS NULL THEN
            RAISE LOG '          skip: spo IS NULL';
            CONTINUE;
        END IF;

        IF n_def IS NULL THEN
            RAISE LOG '          skip: def IS NULL';
            CONTINUE;
        END IF;

        IF n_win IS NULL THEN
            RAISE LOG '          skip: win IS NULL';
            CONTINUE;
        END IF;

        DELETE FROM wn8.exp WHERE partkey = l_partkey AND tank_id = l_tank_id;

        INSERT INTO wn8.exp (partkey, tank_id, battles, dmg, frg, spo, def, win)
        VALUES (l_partkey, l_tank_id, l_battles, n_dmg, n_frg, n_spo, n_def, n_win);

        RAISE LOG '          %/% n_dmg=% n_frg=% n_spo=% n_def=% n_win=%',
            i, l_total, n_dmg::numeric(9,2), n_frg::numeric(9,2), n_spo::numeric(9,2), n_def::numeric(9,2), n_win::numeric(9,2);
        RAISE LOG '          %/% d_dmg=% d_frg=% d_spo=% n_def=% d_win=%',
            i, l_total, (n_dmg - e_dmg)::numeric(9,2), (n_frg - e_frg)::numeric(9,2), (n_spo - e_spo)::numeric(9,2),
            (n_def - e_def)::numeric(9,2), (n_win - e_win)::numeric(9,2);
    END LOOP;

    TRUNCATE TABLE wn8.tmp_exp;
    TRUNCATE TABLE wn8.tmp_user_account_stats;

    ANALYZE wn8.exp;
END;
$$;

Share this post


Link to post
Share on other sites
2 hours ago, sirmax said:

That's exactly what I'm doing. The script above is only for creating initial expected values for the new tanks. I've attached full script with whole calculation.

But my results is not so good, may be that's because of the different samples - as I know, you have used data from vbAddict, whereas our sample includes all players who played last 3 months. May be we have many weak players and they spoil statistics.

 

Thanks, as far as I can tell your full script does it right - good work!

Yes, I used vbaddict data, and at most it was 23,000 accounts, which usually resulted in about 3M rows of data, which filtered down to 1.5M rows.

When you get a chance, try running the user_WN8 vs user_rWINc plot on your filtered dataset but with the v30/31 average expected values instead of your per tank values. Is there a significant difference in the Adjusted R Squared? That will show us all the benefit of individual tank expected values vs averaged expected values, using your massive dataset. Your results should have a better Adjusted R Squared as they are current values, and 'per tank', whereas the v30/31 are 6 months old and 'per type/tier'. I doubt you'll find time to do that anytime soon but it's the best way to confirm that you have improved WN8 from where we left it to gracefully fade away :)

Share this post


Link to post
Share on other sites

Ok, I runned 3 iterations with v30 average expected values, you can see results here: https://yadi.sk/d/GSC6dx153NGzBh/plots/user_account_data

2017-09-29 is the first iteration (using v30 as initial expected values), 2017-09-30 - the second (using expected values from the first iteration), and 2017-10-01 - the third.

Don't take into consideration the dates, they are only for keeping the sequence. I used the same players data for all iterations.

By the way, what expected behavior of the formula if we'll run it in the loop? Is result should be better or worst?

Share this post


Link to post
Share on other sites
4 hours ago, sirmax said:

Ok, I runned 3 iterations with v30 average expected values, you can see results here: https://yadi.sk/d/GSC6dx153NGzBh/plots/user_account_data

2017-09-29 is the first iteration (using v30 as initial expected values), 2017-09-30 - the second (using expected values from the first iteration), and 2017-10-01 - the third.

Don't take into consideration the dates, they are only for keeping the sequence. I used the same players data for all iterations.

By the way, what expected behavior of the formula if we'll run it in the loop? Is result should be better or worst?

Thanks for running that - really useful - you get the same Adjusted R Squared  as I did on the vbaddict dataset (0.88). So with the averages as the input, and the per tank as the output, you get the same level of correlation between WN8 and adjusted winrate. That is one of the reasons we went to averages :)

It will be really hard getting to 0.89 or even 0.90. Here are some thoughts:

  • Looping is bad. When I ran my own expected values script 30 times around (years ago) I noticed that some values diverge not converge. This is because some of the rSTATS are weak correlations to rWIN (rSPOT and rDEF are the worst). It's best to run the values no more than once a month (I did it once every 3 months) to ensure the data has changed a fair bit between runs.
  • To avoid divergence, I recommend you use the v30/31 averages as the baseline for every run. So you would be starting with a known good set of values, and trying to add fidelity by taking them down to the per tank level. That would be a neat way to reconcile the two philosophies on WN8 expected values (averages vs per tank)
  • I looked at your expected values you originally posted. I see a problem with the expected winrates. I dont know why, but your expected winrates are higher than they have been historically. In v29, hardly any tanks had expected winrates above 60%, and there were quite a few that had winrates below 50%. Notice how in your files dated 20th and 30th they have moved up significantly in the histograms below, with almost no tanks having winrates below 50 and many above 60.
  • EDIT: I just found the new expected values you posted - the winrates in those (especially the first set) - look much better
  • I think you need to check some other values against v29 to see if there are unexpected changes in general- there shouldnt be if the method is the same. I cant see any problems with your script, but I'm not a SQL guy so there might be a subtle error in there somewhere. Table merges are the usual suspect...
 

v29.jpeg

20.jpeg

30.jpeg

[\spoiler]

Share this post


Link to post
Share on other sites

I made some experiments.

1. I tried to use v29 table and filled new tanks from v31. The results are here: https://yadi.sk/d/GSC6dx153NGzBh/experiment-v29%2Bv31

2. I tried to increase total account battles filter from 1000+ to 10000+ (to increase number of experienced players in the sample) and use v29+v31 and v31 only initial expected values. The results are here: https://yadi.sk/d/GSC6dx153NGzBh/plots/user_account_data

Share this post


Link to post
Share on other sites
48 minutes ago, sirmax said:

I made some experiments.

1. I tried to use v29 table and filled new tanks from v31. The results are here: https://yadi.sk/d/GSC6dx153NGzBh/experiment-v29%2Bv31

2. I tried to increase total account battles filter from 1000+ to 10000+ (to increase number of experienced players in the sample) and use v29+v31 and v31 only initial expected values. The results are here: https://yadi.sk/d/GSC6dx153NGzBh/plots/user_account_data

Looking good - I see 0.91 for the update using v31 as the input and the account filter set to 10,000, and 0.92 for same when input is v29 + v31 for missing tanks

Clearly, a 10,000 battle filter on the accounts in the dataset is a big step forward. 

However, I like the 0.91 result a little better than the 0.92 for two reasons:

  1. The red line goes much closer to 1565 WN8 than on the other graph, which shows that overall, its working as intended (!)
  2. v31 can be used as the input for every update, as it will not age as fast as v29

Share this post


Link to post
Share on other sites

Currently I'm using v31 for new tanks, updated script:

CREATE OR REPLACE FUNCTION wn8.fill_missed_expected_values()
RETURNS VOID
LANGUAGE plpgsql
AS $$
DECLARE
    l_tank_id int;
BEGIN
    FOREACH l_tank_id IN ARRAY ARRAY(SELECT tank_id FROM xvm.vehicles_list ORDER BY tank_id) LOOP
        IF NOT EXISTS(SELECT 1 FROM wn8.exp WHERE tank_id = l_tank_id LIMIT 1) THEN
            -- new tank - select initial expected values from wn8.exp_def
            INSERT INTO wn8.exp(partkey, tank_id, battles, dmg, frg, spo, def, win)
            SELECT 'epoch'::date, l_tank_id, 0, a.dmg, a.frg, a.spo, a.def, a.win
            FROM wn8.exp_def a
            JOIN xvm.vehicles_list b USING(type, tier)
            WHERE b.tank_id = l_tank_id;
        END IF;
    END LOOP;
END;
$$;

xvm.vehicles_list contains all vehicles updated daily from WG API

wn8.exp_def is v31 table

wn8.exp - expected values used for calculation

This allows to tune initial expected values for each tank individually if necessary.

How else we can improve the result? May be using plots for the tanks (https://yadi.sk/d/GSC6dx153NGzBh/plots/10k-v31) or selecting more suitable battle filter on the account (10k was just empirical assumption).

Share this post


Link to post
Share on other sites

I found that all my plots was built using initial expected values, not updated. Now I'm drawing both - initial values with suffix "def" and updated values with suffix "new".

New values looks much better:

fKqInKw3d7bLFOeFnMGnhCsJoo7Q6lqVRu1PXDcSrzjD8K11CeHWajMyvgqxGDrVTQPoYKhH-wi7585ZDRcBYblioTOBsaFZMLuIj5qozp2r8npumZHI4midPdWhecNq?uid=62728604&filename=wn8-vs-win-10k-v31.new.png&disposition=inline&hash=&limit=0&content_type=image%2Fpng&tknv=v2&size=1280x649  

Share this post


Link to post
Share on other sites

@Gryphon_, is new expected values in your script are calculated correctly?

    rDAMAGEmodel <- lm(rDAMAGE ~ user_rDAMAGE, data=sample)
    rDAMAGEcorrection <- rDAMAGEmodel$coef[[1]] + rDAMAGEmodel$coef[[2]]
    eDAMAGE_new <- round(rDAMAGEcorrection * expectedValues$eDAMAGE[expectedValues$compDescr == i], 2)

May be it should be like this?

    rDAMAGEmodel <- lm(rDAMAGE ~ user_rDAMAGE, data=sample)
    eDAMAGE_new <- round(rDAMAGEmodel$coef[[1]] + rDAMAGEmodel$coef[[2]] * expectedValues$eDAMAGE[expectedValues$compDescr == i], 2)

because linear function is `a + b * x` but not `(a + b) * x`

Share this post


Link to post
Share on other sites

@sirmax

No, its correct. In the plot, the line of best fit should pass through (1.0,1.0). The correction that must be applied is the same as the value of the rSTAT coordinate when user-rSTAT = 1.0. 

Example (graph below). Line crosses user-rDAMAGE = 1.0 at a value of rDAMAGE = 0.9855. So the expected value for rDAMAGE needs to be multiplied by 0.9855 to correct that. That is 'rDAMAGEcorrection'

We derive rDAMAGEcorrection from the R linear model coefficients:

coeff[[1]] = -0.06541298 (= intercept)
coeff[[2]] = 1.050946 (= slope)

rDAMAGEcorrection = coeff[[1]] + coeff[[2]] = 0.985533

..and that is the factor that has to be applied to the expected value.

t34.jpeg

Share this post


Link to post
Share on other sites

Why correction is built on

rDAMAGEmodel <- lm(rDAMAGE ~ user_rDAMAGE, data=sample)

but plots in your script are drawn using 

model <- lm(rDAMAGEc ~ user_rDAMAGEc, data=sample)

Share this post


Link to post
Share on other sites

The script to calculate expected values uses rSTATs in the model - not rSTATc

Reason - rSTATc values might include 'corrections' from other rSTATS per the WN8 method. Thats fine for overall WN8 but not for EV calcs.

I dont know what plot script you are referring to, but the ones I have are written to accept any of many different models to make sure they all look right when updates are done. 

Share this post


Link to post
Share on other sites
6 minutes ago, sirmax said:

I was talking about plotter script from this post.

That script has a wide range of plots you can run. it should have an rSTAT vs user_rSTAT option too, but doesnt. I changed those scripts all the time, depending on what i wanted to plot. 

Use rSTAT vs user_rSTAT for tank expected values.

For overall check, user_WN8 vs user_rWINc is fine as the c cuts out outliers.

Share this post


Link to post
Share on other sites

Looks like LT and SPG are bad balanced in v31, because they have big corrections. Here is the table ordered by damage correction:

 tank_id |              tag              |    type    | corr_dmg | corr_frg | corr_spo | corr_def | corr_win
---------+-------------------------------+------------+----------+----------+----------+----------+----------
    3617 | A16_M7_Priest                 | SPG        |    1.338 |    0.772 |    0.814 |    1.098 |    0.892
   63505 | G117_Toldi_III                | lightTank  |    0.775 |    0.653 |    0.631 |    1.001 |    1.018
    4129 | A18_M41                       | SPG        |    1.283 |    0.883 |    0.783 |    0.925 |    0.950
    4865 | R14_SU-5                      | SPG        |    1.282 |    0.930 |    0.812 |    0.931 |    0.932
   52497 | G33_H39_captured              | lightTank  |    1.277 |    1.328 |    1.017 |    1.813 |    0.951
    7681 | R66_SU-26                     | SPG        |    1.260 |    1.043 |    0.859 |    1.117 |    0.969
   61441 | R118_T28_F30                  | mediumTank |    1.250 |    1.158 |    0.786 |    0.982 |    1.014
   18465 | A108_T18_HMC                  | SPG        |    0.801 |    0.919 |    0.822 |    1.213 |    1.016
    3409 | GB27_Sexton                   | SPG        |    0.803 |    0.979 |    0.898 |    1.113 |    1.006
   62529 | F19_Lorraine40t               | mediumTank |    1.242 |    1.169 |    0.777 |    0.763 |    1.025
   55073 | A93_T7_Combat_Car             | lightTank  |    0.830 |    0.937 |    0.978 |    0.804 |    0.995
    2689 | S09_L_120_TD                  | AT-SPG     |    0.836 |    0.765 |    0.486 |    0.818 |    1.029
    2881 | F14_AMX40                     | lightTank  |    0.838 |    0.871 |    0.439 |    1.073 |    1.012
   54865 | GB76_Mk_VIC                   | lightTank  |    0.839 |    0.952 |    0.955 |    0.796 |    0.980
   10833 | GB26_Birch_Gun                | SPG        |    0.841 |    0.943 |    0.771 |    1.200 |    1.000
   56833 | R99_T44_122                   | mediumTank |    1.189 |    1.223 |    1.044 |    0.903 |    1.018
   54033 | G32_PzV_PzIV_ausf_Alfa        | mediumTank |    1.181 |    1.098 |    1.251 |    0.802 |    1.006
   52241 | G35_B-1bis_captured           | heavyTank  |    1.175 |    1.262 |    1.397 |    1.227 |    1.032
   19457 | R131_Tank_Gavalov             | lightTank  |    1.171 |    1.539 |    1.019 |    1.219 |    1.043
   47361 | R146_STG_Tday                 | mediumTank |    1.168 |    0.986 |    0.732 |    0.610 |    1.024
   48641 | R134_Object_252K              | heavyTank  |    1.167 |    1.057 |    0.930 |    0.680 |    1.039
   11857 | GB77_FV304                    | SPG        |    0.858 |    0.964 |    1.594 |    1.338 |    1.011
   51841 | S15_L_60                      | lightTank  |    0.858 |    0.829 |    0.826 |    0.650 |    0.997
    5953 | F13_AMX38                     | lightTank  |    0.862 |    0.913 |    0.531 |    1.253 |    1.025
   53505 | R56_T-127                     | lightTank  |    1.159 |    1.234 |    0.937 |    1.254 |    0.968
   11345 | GB29_Crusader_5inch           | SPG        |    0.863 |    0.955 |    1.084 |    1.463 |    1.009
   47617 | R146_STG                      | mediumTank |    1.158 |    0.981 |    0.740 |    0.634 |    1.024
   12817 | G53_PzI                       | lightTank  |    0.864 |    0.918 |    1.264 |    0.843 |    1.007
   52737 | R67_M3_LL                     | lightTank  |    0.864 |    0.884 |    0.936 |    1.146 |    0.950
   18961 | G113_SP_I_C                   | lightTank  |    1.157 |    1.069 |    0.913 |    1.001 |    1.014
   54609 | GB78_Sexton_I                 | SPG        |    0.867 |    1.024 |    0.885 |    1.092 |    1.011
   49665 | R134_Object_252U              | heavyTank  |    1.153 |    1.054 |    0.929 |    0.672 |    1.048
     609 | J01_NC27                      | lightTank  |    0.868 |    0.916 |    0.770 |    1.194 |    0.989
     769 | R03_BT-7                      | lightTank  |    0.869 |    0.813 |    1.060 |    0.935 |    0.971
   53537 | A74_T1_E6                     | lightTank  |    0.869 |    0.837 |    0.917 |    0.797 |    1.006
   15121 | G93_GW_Mk_VIe                 | SPG        |    0.870 |    0.816 |    0.900 |    0.872 |    0.980

 

Share this post


Link to post
Share on other sites

Most of those are low tier, rare, bad/unpopular, or new. I think your 10k battle filter might have found a lot more data on some of them than we ever had. (Birch Gun, AMX40, Sexton....)

Anyway, I dont think most players will care about those changes. The review of any new set of values always focuses on tiers 10, 9. and 8, and how much individual overall WN8 changes. 

Share this post


Link to post
Share on other sites

I made plots for all tanks: https://yadi.sk/d/GSC6dx153NGzBh/experiments

v31-1.def - original v31 table

v31-1.new - after calculation using v31-1.def as initial expected values

v31-2.def - some tuning of the most different values of v31-1.def

v31-2.new - after calculation using v31-2.def as initial expected values

Which one is better?

Share this post


Link to post
Share on other sites
6 hours ago, sirmax said:

I made plots for all tanks: https://yadi.sk/d/GSC6dx153NGzBh/experiments

v31-1.def - original v31 table

v31-1.new - after calculation using v31-1.def as initial expected values

v31-2.def - some tuning of the most different values of v31-1.def

v31-2.new - after calculation using v31-2.def as initial expected values

Which one is better?

Great job, and some very nice data.

I think there are two criteria:

  1. For an account-level rating (which is what WN8 is) the Adjusted R Squared of the u_wn8 vs u_winc. 
    • v31-1.def = 0.915 <- baseline
    • v31-1.new= 0.919 <- best
    • v31-2.def = 0.919 <- equal best
    • v31-2.new= 0.918 <- a step back
  2. Ease of calculation / sustainability. We stopped doing per-tank values because its a lot of work and we found the averages were almost as good as the per tank values. So the extra work wasn't justified as we couldn't automate it all. So how easy is it for you to generate the values automatically? My guess:
    • v31-1.def = zero work <- baseline 
    • v31-1.new= one automated run <-  best 
    • v31-2.def = one automated run plus manual inspection and tuning (?) <- distant second if manual inspection and tweaking needed
    • v31-2.new= one automated run plus manual inspection and tuning, followed by another automated run <- distant third

On balance, you get all the gains and least work by going with v31.1.new - run the data against the averages to create per tank values. Run that once a month and you'll keep WN8 running as good as it can be. I suggest creating new averages from your battle weighted values about twice a year, so you have a steadily improving baseline, and you are set.

Share this post


Link to post
Share on other sites

I also added v29-1.def (baseline) and v29-1.new (one automated run) to comparison.

I can confirm your guesses and I agree with you that v31-1.new is the best choice, because it gives good result and don't require manual tuning. At least one of the controversial pair - Maus and 50B - looks much better, this was one of the point why we disagree with the v31 table.

One automated run takes about 1 hour, and I can run it daily. I think it's better than montly updates because changes will be applies more smoothly. We can use daily based version numeration and draw the plots with changes on timeline.

There only question is how to update baseline table. I also need to automate it.

Share this post


Link to post
Share on other sites

Okay, sounds like you have a process - good job.

To update the averages there are two ways:

  • Simple: For each type+tier, use your latest set of expected values (with battle counts) and do a weighted average for each expected value. That will work for all types and tiers that exist now (the v31 table has estimated values for things that don't exist yet, like tier 1 heavies, so write the script so that they just go through unchanged)
  • Harder: apply the v31 values to the dataset, run the script as normal, then when you get to the calculation of corrections, generate the linear models using every type+tier pair vs the rSTAT instead of the tankid vs the rSTAT. That will give you a correction to the averaged value same as normal. (I have never done this as we had no plans to update the values. There may be unforeseen problems with this method)

Share this post


Link to post
Share on other sites

Update:

I had some thoughts on how to update the averages the 'hard' way. I figured it out and the following R script works. 

#apply filters as needed
userTankStats <- dataMaster[dataMaster$battles > 50,]
userTankStats$damage_dealt <- as.double(userTankStats$damage_dealt)
userTankStats <- userTankStats[,c("userid", "compDescr","title", 
                                  "type", "tier", "countryid", "battles",
                                  "victories","damage_dealt","frags",
                                  "spotted","defence_points")]
userTankStats$userid <- as.factor(userTankStats$userid)
any(is.na(userTankStats))

# number of battles in dataset
sum(userTankStats$battles)

#calc actuals
userTankStats$aFRAG <- userTankStats$frags/userTankStats$battles
userTankStats$aDAMAGE <- userTankStats$damage_dealt/userTankStats$battles
userTankStats$aSPOT <- userTankStats$spotted/userTankStats$battles
userTankStats$aDEF <- userTankStats$defence_points/userTankStats$battles
userTankStats$aWIN <- 100*userTankStats$victories/userTankStats$battles
any(is.na(userTankStats))


#load average expected values from wnefficiency.net - currently version 31
wnefficiencyURL <- "http://www.wnefficiency.net/exp/expected_tank_values_31.csv"
expectedValues <- read.csv(wnefficiencyURL)
names(expectedValues) <- c("type","tier", "eFRAG", "eDAMAGE","eSPOT", "eDEF", "eWIN")

head(expectedValues)
any(is.na(expectedValues))

# add the expected values data to the user tanks data
require(dplyr)
userTankStats <- inner_join(x=userTankStats, y=expectedValues, by = c("type","tier") )

# fix chars that upset file naming
userTankStats$title <- chartr("*/", "_-", userTankStats$title)
any(is.na(userTankStats))

# calculate the user rSTATS
userTankStats$rFRAG <- userTankStats$aFRAG/userTankStats$eFRAG
userTankStats$rDAMAGE <- userTankStats$aDAMAGE/userTankStats$eDAMAGE
userTankStats$rSPOT <- userTankStats$aSPOT/userTankStats$eSPOT
userTankStats$rDEF <- userTankStats$aDEF/userTankStats$eDEF
userTankStats$rWIN <- userTankStats$aWIN/userTankStats$eWIN
userTankStats$rFRAGproduct <- userTankStats$rFRAG * userTankStats$battles
userTankStats$rDAMAGEproduct <- userTankStats$rDAMAGE * userTankStats$battles
userTankStats$rSPOTproduct <- userTankStats$rSPOT * userTankStats$battles
userTankStats$rDEFproduct <- userTankStats$rDEF * userTankStats$battles
userTankStats$rWINproduct <- userTankStats$rWIN * userTankStats$battles
any(is.na(userTankStats))

# calculate the user rSTATc's
userTankStats$rWINc <- pmax(0,(userTankStats$rWIN - 0.71)/(1 - 0.71))
userTankStats$rDAMAGEc <- pmax(0,(userTankStats$rDAMAGE - 0.22)/(1 - 0.22))
userTankStats$rFRAGc <- pmax(0,pmin(userTankStats$rDAMAGEc + 0.2,((userTankStats$rFRAG - 0.12)/(1 - 0.12))))
userTankStats$rSPOTc <- pmax(0,pmin(userTankStats$rDAMAGEc + 0.1,((userTankStats$rSPOT - 0.38)/(1 - 0.38))))
userTankStats$rDEFc <- pmax(0,pmin(userTankStats$rDAMAGEc + 0.1,((userTankStats$rDEF - 0.10)/(1 - 0.10))))
userTankStats$rWINcproduct <- userTankStats$rWINc * userTankStats$battles
userTankStats$rDAMAGEcproduct <- userTankStats$rDAMAGEc * userTankStats$battles
userTankStats$rFRAGcproduct <- userTankStats$rFRAGc * userTankStats$battles
userTankStats$rSPOTcproduct <- userTankStats$rSPOTc * userTankStats$battles
userTankStats$rDEFcproduct <- userTankStats$rDEFc * userTankStats$battles
any(is.na(userTankStats))

# calculate the user WN8 per tank 
userTankStats$WN8 <- with(userTankStats, 980*rDAMAGEc + 210*rDAMAGEc*rFRAGc + 155*rFRAGc*rSPOTc + 75*rDEFc*rFRAGc + 145*pmin(1.8,rWINc))
userTankStats$WN8product <- userTankStats$battles * userTankStats$WN8
any(is.na(userTankStats))

# filter out all tanks where WN8 is below median WN8 for every users' tanks
require(dplyr)
median.userTankStatsWN8 <- summarize(group_by(userTankStats,userid), median_WN8 = median(WN8, na.rm=TRUE))
userTankStatsFiltered <- inner_join(x=userTankStats, y=median.userTankStatsWN8, by = "userid")
userTankStatsFiltered <- userTankStatsFiltered[userTankStatsFiltered$WN8 >= userTankStatsFiltered$median_WN8,]
nrow(userTankStatsFiltered)
any(is.na(userTankStatsFiltered))
rm(median.userTankStatsWN8)

#calculate the user account WN8, rSTATs, and rSTATSc
require(dplyr)
userAccountStats <- summarize(group_by(userTankStatsFiltered, userid), 
                              WN8product = sum(WN8product),
                              rWINproduct = sum(rWINproduct), 
                              rDAMAGEproduct = sum(rDAMAGEproduct),
                              rFRAGproduct = sum(rFRAGproduct), 
                              rSPOTproduct = sum(rSPOTproduct), 
                              rDEFproduct = sum(rDEFproduct),
                              rWINcproduct = sum(rWINcproduct), 
                              rDAMAGEcproduct = sum(rDAMAGEcproduct),
                              rFRAGcproduct = sum(rFRAGcproduct), 
                              rSPOTcproduct = sum(rSPOTcproduct), 
                              rDEFcproduct = sum(rDEFcproduct),
                              battles = sum(battles))

userAccountStats$user_WN8 <- userAccountStats$WN8product / userAccountStats$battles
userAccountStats$user_rWIN <- userAccountStats$rWINproduct / userAccountStats$battles
userAccountStats$user_rDAMAGE <- userAccountStats$rDAMAGEproduct / userAccountStats$battles
userAccountStats$user_rFRAG <- userAccountStats$rFRAGproduct / userAccountStats$battles
userAccountStats$user_rSPOT <- userAccountStats$rSPOTproduct / userAccountStats$battles
userAccountStats$user_rDEF <- userAccountStats$rDEFproduct / userAccountStats$battles
userAccountStats$user_rWINc <- userAccountStats$rWINcproduct / userAccountStats$battles
userAccountStats$user_rDAMAGEc <- userAccountStats$rDAMAGEcproduct / userAccountStats$battles
userAccountStats$user_rFRAGc <- userAccountStats$rFRAGcproduct / userAccountStats$battles
userAccountStats$user_rSPOTc <- userAccountStats$rSPOTcproduct / userAccountStats$battles
userAccountStats$user_rDEFc <- userAccountStats$rDEFcproduct / userAccountStats$battles

userAccountStats <- userAccountStats[,c("userid",  "user_WN8", "user_rWIN", "user_rDAMAGE", "user_rFRAG", "user_rSPOT", "user_rDEF", "user_rWINc", "user_rDAMAGEc", "user_rFRAGc", "user_rSPOTc", "user_rDEFc")]
any(is.na(userAccountStats))

#merge back
require(dplyr)
userTankStatsFiltered <- inner_join(x=userTankStatsFiltered, y=userAccountStats, by = c("userid"))
any(is.na(userTankStatsFiltered))

# create table of compDescr and title as index for the loop
require(dplyr)
listOfTanks <- summarize(group_by(userTankStatsFiltered, tier, type ), users = n() )
any(is.na(listOfTanks))

# loop to do linear regression for each rSTAT vs user account rSTAT, derive corrected average expected values
newExpectedValues <- expectedValues 
for (i in 1:10) {
    for (j in 1:5) {0
        sample <- subset(x = userTankStatsFiltered, tier == i & type == j)
        if (nrow(sample) == 0){
            next
        }
        rDAMAGEmodel <- lm(rDAMAGE ~ user_rDAMAGE, data = sample)
        rDAMAGEcorrection <- rDAMAGEmodel$coef[[1]] + rDAMAGEmodel$coef[[2]]
        eDAMAGE_new <- round(rDAMAGEcorrection * expectedValues$eDAMAGE[which(expectedValues$tier == i & expectedValues$type == j)], 2)
        newExpectedValues$eDAMAGE[which(newExpectedValues$tier == i & newExpectedValues$type == j)] <- eDAMAGE_new 
        rFRAGmodel <- lm(rFRAG ~ user_rFRAG, data = sample)
        rFRAGcorrection <- rFRAGmodel$coef[[1]] + rFRAGmodel$coef[[2]]
        eFRAG_new <- round(rFRAGcorrection * expectedValues$eFRAG[which(expectedValues$tier == i & expectedValues$type == j)], 2)
        newExpectedValues$eFRAG[which(newExpectedValues$tier == i & newExpectedValues$type == j)] <- eFRAG_new
        rSPOTmodel <- lm(rSPOT ~ user_rSPOT, data = sample)
        rSPOTcorrection <- rSPOTmodel$coef[[1]] + rSPOTmodel$coef[[2]]
        eSPOT_new <- round(rSPOTcorrection * expectedValues$eSPOT[which(expectedValues$tier == i & expectedValues$type == j)], 2)
        newExpectedValues$eSPOT[which(newExpectedValues$tier == i & newExpectedValues$type == j)] <- eSPOT_new
        rDEFmodel <- lm(rDEF ~ user_rDEF, data = sample)
        rDEFcorrection <- rDEFmodel$coef[[1]] + rDEFmodel$coef[[2]]
        eDEF_new <- round(rDEFcorrection * expectedValues$eDEF[which(expectedValues$tier == i & expectedValues$type == j)], 2)
        newExpectedValues$eDEF[which(newExpectedValues$tier == i & newExpectedValues$type == j)] <- eDEF_new
        rWINmodel <- lm(rWIN ~ user_rWIN, data = sample)
        rWINcorrection <- rWINmodel$coef[[1]] + rWINmodel$coef[[2]]
        eWIN_new <- round(rWINcorrection * expectedValues$eWIN[which(expectedValues$tier == i & expectedValues$type == j)], 2)
        newExpectedValues$eWIN[which(newExpectedValues$tier == i & newExpectedValues$type == j)] <- eWIN_new
    }
}


any(is.na(newExpectedValues))
names(newExpectedValues) <- c("type","tier", "frag", "dmg","spot", "def", "win")


#export new values
date <- as.Date(Sys.Date(), "%m/%d/%Y" )
expected_value_filename <- paste("~/R/WN8 Averaged/averaged_expected_values_",date,".csv")
write.csv(x=newExpectedValues,file=expected_value_filename ,row.names = FALSE)

 

Share this post


Link to post
Share on other sites

How about to increase number of battles on tanks?

50 battles is too small to correctly evaluate winrate or defense points. I understand that you had a small database, so you had to make such a restriction. But if we have full database, does it make sense to increase number of battles on tanks? ~200 battles for example

Share this post


Link to post
Share on other sites
6 hours ago, seriych said:

How about to increase number of battles on tanks?

50 battles is too small to correctly evaluate winrate or defense points. I understand that you had a small database, so you had to make such a restriction. But if we have full database, does it make sense to increase number of battles on tanks? ~200 battles for example

50 was what the original WN8 team came up with. I think while WN9 was being researched we found that raising it didn't really make any difference. The big change that sirmax is making is to raise the account threshold to 10,000 battles (was 1000). THAT makes a big difference, if you have a huge dataset. 

Share this post


Link to post
Share on other sites

BTW, raising the account threshold to 10,000 battles was seriych's idea. 

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

  • Recently Browsing   0 members

    No registered users viewing this page.