プレイヤー単位でどのような分布になってるかを見ていこうと思います。
先に、viewを作っておきます。
SQL
select members.player as player,
COUNT(matches.winner_key = teams.key or null) as win,
COUNT(matches.loser_key = teams.key or null) as lose,
COUNT(*) as total,
AVG(ratings.rating) as rating
from members
inner join teams on members.tournament_key = teams.tournament_key and members.team_key = teams.key
inner join tournaments on teams.tournament_key = tournaments.key
left join matches on teams.tournament_key = matches.tournament_key and teams.key IN (matches.winner_key, matches.loser_key)
left join ratings on members.player = ratings.name and ratings.latest = 1
group by player
大会参加数
大会参加回数 / 人数
大会に参加した回数がN回以上のプレイヤー数です。大会に一度だけ参加したプレイヤーが半数以上です。
SQL
select a.tournament as "参加回数", sum(b.count) as "プレイヤー数" from
(select tournament, COUNT(*) as count from players group by tournament) a
inner join (select tournament, COUNT(*) as count from players group by tournament) b
on a.tournament <= b.tournament
group by a.tournament;
大会参加回数 / 勝率
大会参加数別に、勝率を集計しました。こちらは、上の図と違い、N回以上ではなく、N回出場したプレイヤーの勝率です。大会出場数が多いほうが平均して勝率が高く、18回以上のプレイヤー(30人程度)は、75%前後あります。
SQL
select a.tournament as "参加回数", floor(avg(a.win) / avg(a.total) * 100) as "勝率" from
(select tournament, SUM(win) as win, SUM(total) as total from players group by tournament) a
inner join (select tournament, COUNT(*) as count from players group by tournament) b
on a.tournament <= b.tournament
group by a.tournament;
勝敗
勝利数 / 人数
プレイヤーの勝利数別人数です。およそ1/3のプレイヤーは0勝で、2勝以上出来ているのはプレイヤーは1/2以下です。
SQL
select a.win as "勝利数", sum(b.count) as "プレイヤー数", floor(sum(b.count) * 100 / 13236) as "割合" from
(select win, COUNT(*) as count from players group by win) a
inner join (select win, COUNT(*) as count from players group by win) b
on a.win <= b.win
group by a.win;
プレイヤー勝敗数プロット
各プレイヤーの勝敗をプロットしてます。XとYでスケールが違う + 左下に要素が寄りすぎてわかりにくいですが、左下以外では勝利数が敗北数を上回るプロットになってるのが解ると思います。
これはゲームの性質と言うよりは、主にシングルエリミネーション、ダブルエリミネーションで大会が行われていたのが理由になります。シングルエリミネーションでは1敗、ダブルエリミネーションでは2敗した段階で大会終了となるため、敗北数は伸びにくく、勝利数が、一部の強いプレイヤーに集まりやすくなります。
SQL
select win || '勝-' || lose || '敗(' || count(*) || ')', win, lose, count(*) from players
group by win, lose;
勝利数別敗北数内訳
上の図では勝利数/敗北数が少ない範囲での内訳が分かりづらいため、10勝以下の敗北数内訳を見てみます。
SQL
select win,
sum(case when lose = 0 then 1 else 0 end) as l0,
sum(case when lose = 1 then 1 else 0 end) as l1,
sum(case when lose = 2 then 1 else 0 end) as l2,
sum(case when lose = 3 then 1 else 0 end) as l3,
sum(case when lose = 4 then 1 else 0 end) as l4,
sum(case when lose = 5 then 1 else 0 end) as l5,
sum(case when lose = 6 then 1 else 0 end) as l6,
sum(case when lose = 7 then 1 else 0 end) as l7,
sum(case when lose = 8 then 1 else 0 end) as l8,
sum(case when lose = 9 then 1 else 0 end) as l9,
sum(case when lose > 9 then 1 else 0 end) as l10over
from players
where win <= 10
group by win;
プレイヤーグラフ
大会実績の高いプレイヤー200人位を、過去にチームを組んだプレイヤーとのネットワークグラフにしてます。
一部、通常と異なるチームを組むことが参加要件となっている大会(ユナイトAST、ポケモン甲子園)を取り除いたデータを元にしています。
独立した五角形となっているプレイヤーは完全に固定されたチームですが、多くのプレイヤーが複数のチームで参加していたようです。
SQL
select a.player, b.player, count(*) from players a
join members am on a.player = am.player
join teams t on am.tournament_key = t.tournament_key and am.team_key = t.key
join members bm on bm.tournament_key = t.tournament_key and bm.team_key = t.key
join players b on b.player = bm.player
where a.rating > 1650 and b.rating >= 1650 and a.player <> b.player
group by a.player, b.player;
select player, floor(rating / 45) - 35
from players where rating >= 1650;
select a.player, b.player, count(*) from players a
join members am on a.player = am.player
join teams t on am.tournament_key = t.tournament_key and am.team_key = t.key
join members bm on bm.tournament_key = t.tournament_key and bm.team_key = t.key
join players b on b.player = bm.player
where a.rating > 1650 and b.rating >= 1650 and a.player <> b.player and t.key not in ('koushien_01', 'uast')
group by a.player, b.player;
