野良 X ブキ集計

query.sql

-- https://twitter.com/fetus_hina/status/1138514672494080000

SELECT
  MAX(c2.key) AS weapon_key,
  MAX(c2.name) AS weapon_name_en,
  COUNT(*) AS players_count,
  SUM(CASE WHEN battle2.is_win = battle_player2.is_my_team THEN 1 ELSE 0 END) AS win_count,
  (100.0 * SUM(CASE WHEN battle2.is_win = battle_player2.is_my_team THEN 1 ELSE 0 END)::float / COUNT(*)::float)::decimal(5,2) AS win_pct,
  AVG(battle_player2.kill)::decimal(4,2) AS avg_kill,
  AVG(battle_player2.death)::decimal(4,2) AS avg_death,
  (STDDEV_SAMP(battle_player2.kill) / SQRT(COUNT(*)))::decimal(4,2) AS stderr_kill,
  (STDDEV_SAMP(battle_player2.death) / SQRT(COUNT(*)))::decimal(4,2) AS stderr_death
FROM battle2
INNER JOIN rule2 ON battle2.rule_id = rule2.id
INNER JOIN battle_player2 ON battle2.id = battle_player2.battle_id
INNER JOIN weapon2 AS w2 ON battle_player2.weapon_id = w2.id
INNER JOIN weapon2 AS c2 ON w2.canonical_id = c2.id
WHERE TRUE
AND battle2.lobby_id = 1     -- solo queue
AND battle2.mode_id = 3      -- gachi match
AND rule2.key = 'area'
AND battle2.rank_id = 12     -- udemae X
AND battle2.is_win IS NOT NULL
AND battle2.period >= 216576 -- 2019-06-01T00:00:00+00:00-
AND battle2.version_id = 38  -- current version
AND battle2.is_automated = TRUE
AND battle2.use_for_entire = TRUE
AND battle2.end_at - battle2.start_at >= '30 seconds'::interval
AND battle_player2.is_me = FALSE
AND battle_player2.kill IS NOT NULL
AND battle_player2.death IS NOT NULL
GROUP BY c2.id
HAVING COUNT(*) >= 40
ORDER BY win_pct DESC