野球のデータを用いて選手間の「類似度」を計算する

SQL 統計学ユークリッド距離

背景

今回は統計の話に近くなってしまうのですが、選手を傾向に応じてグループ化できると面白いなーと思っていて、「野球　似ている　選手」などでググってみると、Baseball LABのコラムを見つけました。
コラム：「近い」選手とは？

類似度の計算について

現在データを集めている野球リーグでは、選手によって打席数の差がかなりあり、グラフを描いたときの二点間の距離(ユークリッド距離)の算出に打席数の偏りが反映されてしまうため、x,y軸に割合を採用することにしました。

ユークリッド距離とは

ユークリッド距離 - wikipedia
要は2点間の距離です。今回は二次元を扱うので、皆さんご存知の三平方の定理を使います。

x,y軸に採用するもの

どのような指標を採用するかによって、類似度の現れ方が異なるので、
事前にChart.jsなどでグラフを書いていくつか検討した結果です。

打者の場合

長打率(SLG)：塁打の合計数を打席数で割った指標。
打数 / 三振比率 (At Bats per Strikeout : 三振のしにくさ)

MySQLでのユークリッド距離の計算

SELECT
全体の成績を計算したものからselectして、ある選手のAB/Kと長打率の数値を渡して類似度が近い順に5つのレコードを取り出します。
WHERE
選手自身が類似度0として取れてしまうのでwhere句で除外します。
代表的な選手を類似度の候補として挙げるために条件を42打席以上としています。

select 
    name,
    player_id,
    tpa,
    not_strike_out,
    SLG,
    SQRT(POW(?-not_strike_out,2)+POW(?-SLG,2)) as distance
 from(
SELECT
    player_id,
    name,
    p.team_id as teamId,
    team_name,
  CASE
    WHEN (sum(hit)/sum(at_bats)) is null THEN 0
    WHEN (sum(hit)/sum(at_bats)) is not null THEN (sum(hit)/sum(at_bats))
  END as average,
  CASE
    WHEN ((sum(hit)+sum(twobase)*1+sum(homerun)*2)/sum(at_bats)) is null THEN 0
    WHEN ((sum(hit)+sum(twobase)*1+sum(homerun)*2)/sum(at_bats)) is not null THEN ((sum(hit)+sum(twobase)*1+sum(homerun)*2)/sum(at_bats))
  END as SLG,
  CASE
    WHEN ((sum(hit)+sum(four_ball))/sum(tpa))+((sum(hit)+sum(twobase)*1+sum(homerun)*2)/sum(at_bats)) is null THEN 0
    WHEN ((sum(hit)+sum(four_ball))/sum(tpa))+((sum(hit)+sum(twobase)*1+sum(homerun)*2)/sum(at_bats)) is not null THEN ((sum(hit)+sum(four_ball))/sum(tpa))+((sum(hit)+sum(twobase)*1+sum(homerun)*2)/sum(at_bats))
  END as OPS,
    (sum(hit)+sum(four_ball))/sum(tpa) as OBP,
    ((((sum(hit)+sum(twobase)*1+sum(homerun)*2)+0.26*sum(four_ball)-0.03*sum(strike_out)+3*sum(tpa))*(sum(hit)+sum(four_ball)+2.4*sum(tpa)))/(9*sum(tpa))-0.9*sum(tpa))*27/(sum(at_bats)-sum(hit)) as RC27,
    CASE
    WHEN sum(at_bats)/sum(strike_out) is null THEN 0
    WHEN sum(at_bats)/sum(strike_out) is not null THEN sum(at_bats)/sum(strike_out)
  END as not_strike_out,
  CASE
    WHEN sum(four_ball)/sum(strike_out) is null THEN 0
    WHEN sum(four_ball)/sum(strike_out) is not null THEN sum(four_ball)/sum(strike_out)
  END as bbk,

  CASE
    WHEN sum(at_bats)/sum(homerun) is null THEN 0
    WHEN sum(at_bats)/sum(homerun) is not null THEN sum(at_bats)/sum(homerun)
  END as avg_homerun,
  CASE
    WHEN sum(rbi)/sum(at_bats) is null THEN 0
    WHEN sum(rbi)/sum(at_bats) is not null THEN sum(rbi)/sum(at_bats)
  END as avgRbi,
  sum(tpa) as tpa,
    sum(at_bats) as at_bats,
    sum(hit) as hit,
    sum(rbi) as rbi,
    sum(four_ball) as four_ball,
    sum(strike_out) as strike_out,
    sum(twobase) as twobase,
    sum(homerun) as homerun
 FROM batting_sum b
  INNER JOIN game g on b.game_id=g.game_id
  INNER JOIN player p on b.player_id=p.id
  INNER JOIN team t on t.team_id=p.team_id
 group by player_id)p
where tpa>=42 and player_id <> ?
order by distance asc
limit 5

投手の場合

WHIP(Walks plus Hits per Inning Pitched)：与四球・被安打数合計の合計を投球回数で割ったもの。
奪三振率：奪三振率＝奪三振数× 5 ÷投球回

MySQLでのユークリッド距離の計算

select 
player_id,
name,
WHIP,
strike_avg,
inning,
SQRT(POW(?-WHIP,2)+POW(?-strike_avg,2)) as distance
 from (

SELECT
  p.player_id,
  name,
  pl.team_id as teamId,
  team_name,
  sum(runs)/sum(inning)*5 as era,
  (sum(hit)+sum(four_ball))/sum(inning) as WHIP,
  sum(four_ball)/sum(inning)*5 as BB5,
  sum(strike_out)/sum(inning)*5 as strike_avg,
  sum(inning) as inning,
  sum(pa) as pa,
  sum(hit) as hit,
  sum(homerun) as homerun,
  sum(four_ball) as four_ball,
  sum(strike_out) as strike_out,
  sum(runs) as runs,
  sum(complete) as complete,
  sum(shutout) as shutout,
  sum(win) as win,
  sum(lose) as lose,
  sum(save) as save,
  p3.run_support
 FROM
 pitching p
  inner join game g on g.game_id=p.game_id
  inner join player pl on p.player_id=pl.id
  inner join team t on t.team_id=pl.team_id
  left outer join(
    SELECT
        p.player_id as player_id,
        sum(p2.runs)/sum(p2.inning)*5 as run_support
    FROM pitching p
    inner join game g
    on p.game_id=g.game_id
    inner join pitching p2
    on p.game_id=p2.game_id and p.myteam_id!=p2.myteam_id
    group by player_id)p3
    on p3.player_id=p.PLAYER_ID
group by player_id)p
where player_id<>? and inning >= 18
order by distance asc
limit 5