scripts/refresh_player_agg_stats_mv.sql

   1 -- This transaction and corresponding anonymous block will update the
   2 -- player_agg_stats_mv table. Change the values of "since" and "until" below to
   3 -- control the window of players you want to update.
   4 begin;
   5
   6 do $$
   7 declare
   8     since timestamp without time zone := now() at time zone 'UTC' - interval '4 weeks';
   9     until timestamp without time zone := now() at time zone 'UTC';
  10 begin
  11     -- clear the slots for the upcoming records
  12     delete
  13     from player_agg_stats_mv
  14     where player_id in (
  15         select distinct player_id
  16         from player_game_stats
  17         where player_id > 2
  18         and create_dt between since and until
  19     );
  20
  21     insert into player_agg_stats_mv
  22     select
  23        p.player_id,
  24        agg_stats.game_type_cd game_type_cd,
  25        p.nick,
  26        p.stripped_nick,
  27        max(agg_stats.create_dt) last_seen,
  28        coalesce(sum(win) + sum(loss), 0) games,
  29        coalesce(sum(win), 0) wins,
  30        coalesce(sum(loss), 0) losses,
  31        coalesce(sum(kills), 0) kills,
  32        coalesce(sum(deaths), 0) deaths,
  33        coalesce(sum(suicides), 0) suicides,
  34        coalesce(sum(captures), 0) captures,
  35        coalesce(sum(pickups), 0) pickups,
  36        coalesce(sum(drops), 0) drops,
  37        coalesce(sum(carrier_frags), 0) carrier_frags,
  38        coalesce(round(sum(alivetime)/60), 0) alivetime
  39     from
  40        (select
  41           pgs.player_id,
  42           g.game_id,
  43           g.game_type_cd,
  44           g.create_dt,
  45           case
  46              when g.winner = pgs.team then 1
  47              when pgs.scoreboardpos = 1 then 1
  48              else 0
  49           end win,
  50           case
  51              when g.winner = pgs.team then 0
  52              when pgs.scoreboardpos = 1 then 0
  53              else 1
  54           end loss,
  55           pgs.kills,
  56           pgs.deaths,
  57           pgs.suicides,
  58           pgs.captures,
  59           pgs.pickups,
  60           pgs.drops,
  61           pgs.carrier_frags,
  62           extract(epoch from pgs.alivetime) alivetime
  63        from
  64           games g,
  65           player_game_stats pgs
  66        where
  67           g.game_id = pgs.game_id
  68           and pgs.player_id > 2
  69           and pgs.player_id in (
  70              select distinct player_id
  71              from player_game_stats
  72              where player_id > 2
  73              and create_dt between since and until
  74           )
  75        ) agg_stats
  76     join
  77        players p
  78           on p.player_id = agg_stats.player_id
  79        group by
  80           p.player_id,
  81           p.nick,
  82           p.stripped_nick,
  83           agg_stats.game_type_cd
  84     ;
  85
  86 exception when others then
  87     raise notice 'something went wrong';
  88 end$$;
  89
  90 commit;