〜はじめに〜

前回、netkeiba.com様から競馬データをスクレイピングして、pythonを使った機械学習モデルに入れて予測するというのを行いました。生データなので、前処理にとてもやりがいがあり楽しかったです。
しかしここ最近は、BigQueryでデータ分析というのが流行っているみたいで、私もBigQueryで分析を行ってみました。
BigQuery MLを使ってガッツリ機械学習して、勝つ確率を上げる！というのも良いですが、今回は基礎分析をたくさん行い、それらをデータポータルでグラフ化することで可視化させることでデータに対しての知見を広げるということをやっていきます。

環境

・BigQuery
・google データポータル
この2つだけでやります。

#早速やってみる。
使うデータは過去にスクレイピングしてきたデータです。
スクレイピングは
https://qiita.com/penguinz222/items/6a30d026ede2e822e245
を参考にさせていただきました。

使うデータは2008~2018年分のデータです。

人気は関係あるの？

一番シンプルですね。関係ありそうですが、実際にどのくらい関係してるのか可視化してみてみます。
クエリは人気ごとに１着になった割合をみています。結果はこんな感じです。

SELECT
    popu,100 * COUNT(*)/(SELECT COUNT(*) FROM `django-app-325606.keiba.keiba` WHERE rank = "1") AS rank1_by_popu
FROM 
    `django-app-325606.keiba.keiba`
WHERE
    rank = "1"
GROUP BY
    popu
ORDER BY 
    rank1_by_popu desc

予想通り、人気が高ければ高いほど1着になる確率は高いです。
ここで少し考えたいのはオッズとの関係です。1番人気は2番人気より約1.78倍(32/18)勝ちやすいということは、理論上オッズは２番人気の方が1.78倍高くないと釣り合いが取れません。しかし、人間の投票によってオッズや人気は変動します。この歪みを捉えるのが勝つために必要なことです。この辺りをこれから分析していきます。
せっかくなのでデータポータルで可視化します。

グラフにした方が分かりやすいですね。

オッズは関係あるの？

ということでオッズと人気の関係をみます。
人気ごとのオッズの平均をみてみます。人気とオッズは反比例の関係にあるので、予想ではさっきのグラフの逆になりそうです。11頭以上のレースになるとレース数がやや少なめになるので、ここから10頭までのレースにします。(競馬は7頭で走ったり、13頭で走ったりレースごとに走る馬の数が違います。)

SELECT 
    popu,AVG(odds) AS avg_odds_by_popu
FROM 
    `django-app-325606.keiba.keiba`
WHERE 
    rank = "1" AND popu <= 10
GROUP BY 
    popu
ORDER BY 
    avg_odds_by_popu

人気な馬ほどオッズが低いことは分かりました。
次にこの平均オッズにさっきの勝率を勝率をかけてみます。これらで算出される数値は期待値になります。
期待値が80％であれば、かけた金額の80%が戻ってくるという意味です。
理論上はどれも同じくらいになるはずです。
ちなみに、競馬の期待値は75%らしいです。単勝は掛け方の中でも一番高いと聞いたことがありますが、単勝の期待はいくつかは分かりませんでした。平均75%だから80%くらいあるのかな。

WITH rank1_popu_table AS (
    SELECT
        popu,100 * COUNT(*)/(SELECT COUNT(*) FROM `django-app-325606.keiba.keiba` WHERE rank = "1") AS rank1_by_popu,AVG(odds) AS avg_odds
    FROM 
        `django-app-325606.keiba.keiba`
    WHERE
        rank = "1"
    GROUP BY
        popu
)

SELECT 
    rank1_popu_table.popu, rank1_by_popu * avg_odds AS popu_odds
FROM 
    rank1_popu_table
ORDER BY
    popu_odds DESC

なんと1位は5番人気。
つまり、全くの競馬素人の方が単勝賭けで回収率を高めるなら、5番人気にかけるのが良いということです。（もちろん私がスクレイピングしてきたデータでのみの話です。）
なんか5番人気あたりが、オッズも勝つ確率もちょうど良さそうという感覚はありましたが、ほんとにそうだったとは。
1番人気の期待値は意外と低いですね。やはり勝った時のオッズが勝つ確率に見合ってないということでしょうか。
と、ここまでで5番人気にかけることで競馬の期待値75%→81.8%まで上がることが分かりました。
機械学習を使わず、この数値を基礎分析だけでこれからどんどん上げていこうと思います。
さて、次に何を分析するか。正直私は競馬にそこまで詳しくはないので、順番が合ってるかは分かりませんが、次は馬ごとに得意なコースを分析します。
長距離が得意な馬、短期距離が得意な馬、芝が得意な馬、ダートが得意な馬などなど、、、この辺りも結構重要です。

コースの種類、長さは関係あるの？

ということで、馬ごとに得意なコースを分析します。

コースの種類はダート、芝、障害の3つのレースに分かれているようです。開催されたレース数はダートと芝がほぼ同じくらいですね。障害レースは少ないので、これからの分析は無視します。
馬それぞれに対して、芝とダートを走った時の平均順位を求めてみます。平均順位が高い方(上位)が得意なコースな種類ということです。
コースの長さについては大体1000~4000mと結構幅広いです。
競馬界では

・短距離(1000～1400m)
・中距離(1800～2200m)
・中長距離(2200～2800m)
・長距離(2800m以上)

と分類されるようです。
これも同様で、馬それぞれに対して上記の4つを走った時の平均順位を求めてみて、4つの中で平均順位が高いのが得意なコースの長さになります。

まず、コース別の平均順位について見てます。
反則などがありrankが"取消"とかなったやつはのぞきます。

WITH course_table AS(
SELECT
    *,CAST(rank AS INT64) as rank_int,SUBSTRING(course, 1,1) AS kind_of_course
FROM 
    `django-app-325606.keiba.keiba`
WHERE 
    rank not in ("取消","中止","失格","除外")
)

SELECT 
    horse_name,kind_of_course, count(kind_of_course) AS race_num, ROUND(AVG(rank_int),2) AS avg_rank
FROM 
    course_table
GROUP BY
    horse_name,kind_of_course

これは馬の名前、コースの種類、レース数、レースの平均順位を表しています。
例えば、1行目のドナリサはダートのコースを50回走り、その50回の平均順位が7.6位ということです。
これだとちょっと分かりにくいので、1つの馬について着目してみます。
一番上のドナリサについて見てみます。

うーん。笑
分析して初めて気づいたんですが、ダートと芝ってランダムに走らせてるんじゃないんですね。
どうやらダートを走らせる馬、芝を走らせる馬みたいに分かれているようです。
このドナリサはほとんどのレースをダートのコースで走っています。
この結果を見て、ダートが得意というのはちょっと正しくない分析ですね。
難しい。。。

さて、次は距離について見てみます。
これもドナリザについて見てみます。

WITH course_table AS(
SELECT
    *,CAST(rank AS INT64) as rank_int,SUBSTRING(course, 2,4) AS length
FROM 
    `django-app-325606.keiba.keiba`
WHERE 
    rank not in ("取消","中止","失格","除外")
)

SELECT 
    horse_name, length, ROUND(AVG(rank_int),2) AS avg_rank, COUNT(length) AS course_num
FROM 
    course_table
WHERE 
    horse_name = "ドナリサ"
GROUP BY
    horse_name,length

ドナリサは1200mと1400mが多いですね。こういうのってどうやって決めるんでしょうか。
ただこの馬は短距離が得意そうですね。1600m、1800mは2レースしか走っていませんが、16位と13位と成績が悪いです。
1000mという超短距離に限っては8レース走って、平均4.6位と結構優秀です。
距離は結構使えそうですね。　
ということで馬、レースごとに平均順位を算出し、どのくらいの距離が得意なのか分析していきます。

WITH course_table AS(
SELECT
    *,CAST(rank AS INT64) as rank_int,SUBSTRING(course, 2,4) AS length
FROM 
    `django-app-325606.keiba.keiba`
WHERE 
    rank not in ("取消","中止","失格","除外")
)

SELECT 
    horse_name, length, ROUND(AVG(rank_int),2) AS avg_rank, COUNT(length) AS course_num
FROM 
    course_table
GROUP BY
    horse_name,length
ORDER BY 
    horse_name,avg_rank

こんな感じで、馬ごとに距離と平均順位を見ていきます。
やはりレース数に結構ばらつきがありますが、、、
アイアイエンデバーは中距離型っぽいですね。2000mと2100mとかの長距離レースは順位が悪いです。
さて、2.5位は順位が良いと分かりますが、そもそも5.94位とか8.57位がいい順位なのかイマイチ分からないです。
そこで全レースの平均出頭数を見てます。

14.9頭でした。よって、競馬における平均点はこの半分の7.5位ということになります。
(距離ごとに出頭馬数が違うと思いますが、まずは気にせずざっくり平均点を出しました。)
よって、平均順位が7.5位以上のレースだけかけてみます。さっきのアイアイエンデバーだと1700mと1800mのレースだけ賭けることにします。

上記のクエリにHAVING句でavg_rank < 7.5を追加するだけなのでクエリは省略します。
結果はこんな感じです。

結構良い感じですね。
アイアイレインボーは長距離が得意そうですね。1200mでも7.29位だからそんな悪くないけど。。

#終わりに
SQLにおける基礎分析はここまでにします。これからどうするかというとpythonで機械学習モデルに入れる時、これらが良い特徴量になります。
例えば、一番最初の生データに新しく「得意なレース」という1500mみたいな値が入った列を追加します。
すると機械学習する時に良い感じに使ってくれて、精度をあげてくれます。
機械学習ではこの特徴量作成が非常に大事になってきますので、この基礎分析の過程は非常に大事です。
これからも機械学習させる前の前処理はBigQueryでやってみようかなと思います。

(冒頭にも書きましたが、ここまでの分析はリークを起こしているのでデータ分析としてはNGです。ただ、SQLの練習にもなるしスクレイピングした生データなので扱いづらく前処理の練習にもなります！)

ここまで見ていただきありがとうございました！