update player_point for new schema and requirements

1 files changed, 65 insertions, 101 deletions

diff --git a/crunch_player.sql b/crunch_player.sql
index 49570c6..8ddedb4 100644
--- a/crunch_player.sql
+++ b/crunch_player.sql
@@ -1,106 +1,70 @@
-INSERT LOW_PRIORITY INTO player_point
-SELECT
-    NULL,
-    `participant`.`player_api_id`,
+insert low_priority into `player_point`
+select
+    null,
+    p.created_at,
+    p.player_api_id,
+    s.id as series_id,
+    f.id as filter_id,
+    h.id as hero_id,
+    gm.id as game_mode_id,
+    r.id as role_id,
 
-    `series`.`id`,
-    `filter`.`id`,
-    `hero`.`id`,
-    `game_mode`.`id`,
-    `role`.`id`,
-    
-    COUNT(`participant`.`id`) AS `played`,
-    SUM(CAST(`participant`.`winner` AS INT)) AS `wins`,
-    SUM(`duration`) AS `time_spent`,
-    
-    `participant`.`created_at`,
+    -- everything added here needs to be in on DUPLICATE too!!!
+    count(p.id) as played,
+    sum(cast(p.winner as INT)) as wins,
+    sum(p_s.duration) as time_spent,
 
-    SUM(`participant_stats`.`kills`) AS `kills`,
-    SUM(`participant_stats`.`deaths`) AS `deaths`,
-    SUM(`participant_stats`.`assists`) AS `assists`,
-    SUM(`participant_stats`.`minion_kills`) AS `minion_kills`,
-    SUM(`participant_stats`.`jungle_kills`) AS `jungle_kills`,
-    SUM(`participant_stats`.`non_jungle_minion_kills`) AS `non_jungle_minion_kills`,
-    SUM(`participant_stats`.`crystal_mine_captures`) AS `crystal_mine_captures`,
-    SUM(`participant_stats`.`gold_mine_captures`) AS `gold_mine_captures`,
-    SUM(`participant_stats`.`kraken_captures`) AS `kraken_captures`,
-    SUM(`participant_stats`.`turret_captures`) AS `turret_captures`,
-    SUM(`participant_stats`.`gold`) AS `gold`,
-    SUM(`participant_stats`.`hero_level`) AS `hero_level`,
-    SUM(`participant_stats`.`kda_ratio`) AS `kda_ratio`,
-    SUM(`participant_stats`.`kill_participation`) AS `kill_participation`,
-    SUM(`participant_stats`.`cs_per_min`) AS `cs_per_min`,
-    SUM(`participant_stats`.`kills_per_min`) AS `kills_per_min`,
-    SUM(`participant_stats`.`impact_score`) AS `impact_score`,
-    SUM(`participant_stats`.`objective_score`) AS `objective_score`,
-    SUM(`participant_stats`.`damage_cp_score`) AS `damage_cp_score`,
-    SUM(`participant_stats`.`damage_wp_score`) AS `damage_wp_score`,
-    SUM(`participant_stats`.`sustain_score`) AS `sustain_score`,
-    SUM(`participant_stats`.`farm_lane_score`) AS `farm_lane_score`,
-    SUM(`participant_stats`.`kill_score`) AS `kill_score`,
-    SUM(`participant_stats`.`objective_lane_score`) AS `objective_lane_score`,
-    SUM(`participant_stats`.`farm_jungle_score`) AS `farm_jungle_score`,
-    SUM(`participant_stats`.`peel_score`) AS `peel_score`,
-    SUM(`participant_stats`.`kill_assist_score`) AS `kill_assist_score`,
-    SUM(`participant_stats`.`objective_jungle_score`) AS `objective_jungle_score`,
-    SUM(`participant_stats`.`vision_score`) AS `vision_score`,
-    SUM(`participant_stats`.`heal_score`) AS `heal_score`,
-    SUM(`participant_stats`.`assist_score`) AS `assist_score`,
-    SUM(`participant_stats`.`utility_score`) AS `utility_score`,
-    SUM(`participant_stats`.`synergy_score`) AS `synergy_score`,
-    SUM(`participant_stats`.`build_score`) AS `build_score`,
-    SUM(`participant_stats`.`offmeta_score`) AS `offmeta_score`
-FROM `participant`
-JOIN `participant_stats` ON `participant_stats`.`participant_api_id` = `participant`.`api_id`
-JOIN `series` ON `participant_stats`.`created_at` BETWEEN `series`.`start` AND `series`.`end` AND `series`.`dimension_on` = 'global'
-JOIN `filter` ON `filter`.`name` = 'all' AND `filter`.`dimension_on` = 'global'
-JOIN `hero` ON `hero`.`id` = `participant`.`hero_id` OR `hero`.`name` = 'all'
-JOIN `game_mode` ON `game_mode`.`id` = `participant`.`game_mode_id` OR `game_mode`.`name` = 'all'
-JOIN `skill_tier` ON `participant`.`skill_tier` BETWEEN `skill_tier`.`start` AND `skill_tier`.`end` OR `skill_tier`.`name` = 'all'
-JOIN `build` ON `build`.`name` = 'all'
-JOIN `role` ON `role`.`id` = `participant`.`role_id` OR `role`.`name` = 'all'
-JOIN `region` ON `region`.`name` = `participant`.`shard_id` OR `region`.`name` = 'all'
+    -- special player_point facts
+    max(p.trueskill) as trueskill,
+    sum(p.trueskill_mu) as trueskill_mu,
+    sum(p.trueskill_sigma) as trueskill_sigma,
+    0 as elo,
 
-WHERE `participant`.`api_id` IN (:participant_api_ids)
+    sum(p_s.kills) as kills,
+    sum(p_s.deaths) as deaths,
+    sum(p_s.assists) as assists,
+    sum(p_s.farm) as farm,
+    sum(p_s.minion_kills) as minion_kills,
+    sum(p_s.jungle_kills) as jungle_kills,
+    sum(p_s.non_jungle_minion_kills) as non_jungle_minion_kills,
+    sum(p_s.crystal_mine_captures) as crystal_mine_captures,
+    sum(p_s.gold_mine_captures) as gold_mine_captures,
+    sum(p_s.kraken_captures) as kraken_captures,
+    sum(p_s.turret_captures) as turret_captures,
+    sum(p_s.gold) as gold,
+    sum(p_s.impact_score) as impact_score
+from participant p
+join participant_stats p_s on (p_s.participant_api_id = p.api_id)
+-- left outer join users u on p.player_api_id = u.player_api_id  -- u.access_type is null -> unregistered
+join filter f on (f.dimension_on = 'player' and (f.name = 'all' or f.id in (select gpf.filter_id from global_point_filters gpf where gpf.match_api_id = p.match_api_id)))
+join series s on (p_s.created_at between s.start and s.end and s.dimension_on = 'player')
+join hero h on (p.hero_id = h.id or h.name = 'all')
+join role r on ((p.role_id = r.id and h.name = 'all') or r.name = 'all')  -- do not cross hero x role
+join game_mode gm on (p.game_mode_id = gm.id or gm.name = 'all')
 
-GROUP BY `series`.`id`, `filter`.`id`, `hero`.`id`, `game_mode`.`id`, `skill_tier`.`id`, `build`.`id`, `role`.`id`, `region`.`id`
-ORDER BY `participant`.`id`
+where p.api_id in (:participant_api_ids)
 
-ON DUPLICATE KEY UPDATE
-`played` = `played` + VALUES(`played`),
-`wins` = `wins` + VALUES(`wins`),
-`time_spent` = `time_spent` + VALUES(`time_spent`),
-`kills` = `kills` + VALUES(`kills`),
-`deaths` = `deaths` + VALUES(`deaths`),
-`assists` = `assists` + VALUES(`assists`),
-`minion_kills` = `minion_kills` + VALUES(`minion_kills`),
-`jungle_kills` = `jungle_kills` + VALUES(`jungle_kills`),
-`non_jungle_minion_kills` = `non_jungle_minion_kills` + VALUES(`non_jungle_minion_kills`),
-`crystal_mine_captures` = `crystal_mine_captures` + VALUES(`crystal_mine_captures`),
-`gold_mine_captures` = `gold_mine_captures` + VALUES(`gold_mine_captures`),
-`kraken_captures` = `kraken_captures` + VALUES(`kraken_captures`),
-`turret_captures` = `turret_captures` + VALUES(`turret_captures`),
-`gold` = `gold` + VALUES(`gold`),
-`kda_ratio` = `kda_ratio` + VALUES(`kda_ratio`),
-`kill_participation` = `kill_participation` + VALUES(`kill_participation`),
-`cs_per_min` = `cs_per_min` + VALUES(`cs_per_min`),
-`kills_per_min` = `kills_per_min` + VALUES(`kills_per_min`),
-`impact_score` = `impact_score` + VALUES(`impact_score`),
-`objective_score` = `objective_score` + VALUES(`objective_score`),
-`damage_cp_score` = `damage_cp_score` + VALUES(`damage_cp_score`),
-`damage_wp_score` = `damage_wp_score` + VALUES(`damage_wp_score`),
-`sustain_score` = `sustain_score` + VALUES(`sustain_score`),
-`farm_lane_score` = `farm_lane_score` + VALUES(`farm_lane_score`),
-`kill_score` = `kill_score` + VALUES(`kill_score`),
-`objective_lane_score` = `objective_lane_score` + VALUES(`objective_lane_score`),
-`farm_jungle_score` = `farm_jungle_score` + VALUES(`farm_jungle_score`),
-`peel_score` = `peel_score` + VALUES(`peel_score`),
-`kill_assist_score` = `kill_assist_score` + VALUES(`kill_assist_score`),
-`objective_jungle_score` = `objective_jungle_score` + VALUES(`objective_jungle_score`),
-`vision_score` = `vision_score` + VALUES(`vision_score`),
-`heal_score` = `heal_score` + VALUES(`heal_score`),
-`assist_score` = `assist_score` + VALUES(`assist_score`),
-`utility_score` = `utility_score` + VALUES(`utility_score`),
-`synergy_score` = `synergy_score` + VALUES(`synergy_score`),
-`build_score` = `build_score` + VALUES(`build_score`),
-`offmeta_score` = `offmeta_score` + VALUES(`offmeta_score`)
+group by p.player_api_id, s.id, f.id, h.id, gm.id, r.id
+order by p.id
+
+on duplicate key update
+played = played + values(played),
+wins = wins + values(wins),
+time_spent = time_spent + values(time_spent),
+trueskill = case when values(trueskill) > trueskill then values(trueskill) else trueskill end,
+trueskill_mu = trueskill_mu + values(trueskill_mu),
+trueskill_sigma = trueskill_sigma + values(trueskill_sigma),
+elo = elo + values(elo),
+kills = kills + values(kills),
+deaths = deaths + values(deaths),
+assists = assists + values(assists),
+farm = farm + values(farm),
+minion_kills = minion_kills + values(minion_kills),
+jungle_kills = jungle_kills + values(jungle_kills),
+non_jungle_minion_kills = non_jungle_minion_kills + values(non_jungle_minion_kills),
+crystal_mine_captures = crystal_mine_captures + values(crystal_mine_captures),
+gold_mine_captures = gold_mine_captures + values(gold_mine_captures),
+kraken_captures = kraken_captures + values(kraken_captures),
+turret_captures = turret_captures + values(turret_captures),
+gold = gold + values(gold),
+impact_score = impact_score + values(impact_score)