Skip to content

Instantly share code, notes, and snippets.

@fetus-hina
Created June 11, 2019 18:48
Show Gist options
  • Save fetus-hina/093a071a4c400f7e815bc9fabd6e175b to your computer and use it in GitHub Desktop.
Save fetus-hina/093a071a4c400f7e815bc9fabd6e175b to your computer and use it in GitHub Desktop.
野良 X ブキ集計
-- 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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment