GSP461

概要

このラボでは、NCAA 男子バスケットボールのトーナメントの勝者を BigQuery、機械学習（ML）、NCAA 男子バスケットボールのデータセットを使用して予測します。

ここでは、SQL を利用して予測と分類のための ML モデルを作成できる BigQuery 機械学習（BQML）を使用します。

演習内容

このラボでは、次の方法について学びます。

• BigQuery を使用して NCAA の一般公開データセットにアクセスする。
• NCAA データセットを探索して、利用可能なデータのスキーマと範囲について把握する。
• 既存のデータを準備して、特徴とラベルに変換する。
• データセットをトレーニング サブセットと評価サブセットに分ける。
• BQML を使用して NCAA トーナメント データセットに基づいたモデルを構築する。
• 新たに作成したモデルを使用して、対戦組み合わせ表の NCAA トーナメントの勝者を予測する。

前提事項

これは基礎レベルのラボです。このラボは、SQL とそのキーワードに関して一定の経験がある方を対象としています。また、BigQuery に関する知識もあると望ましいでしょう。この分野に関して必要な知識を得るためには、このラボの受講前に以下の少なくとも 1 つを受講することをおすすめします。

• SQL for BigQuery と Cloud SQL の概要
• BigQuery: Qwik Start - Console

準備ができたら下にスクロールし、使用するサービスやラボ環境の適切な設定方法を確認します。

BigQuery

BigQuery は、Google が低料金で提供する NoOps のフルマネージド分析データベースです。BigQuery を使用すると、インフラストラクチャを管理したりデータベース管理者を配置したりすることなく、テラバイト単位の大規模なデータをクエリできます。また、BigQuery では SQL が採用されており、従量課金制モデルでご利用いただけます。このような特徴を活かし、お客様は有用な情報を得るためのデータ分析に専念できます。

最近、NCAA バスケットボールの試合、チーム、選手のデータセットが新たに利用できるようになりました。試合データには、2009 年以降のプレイバイプレイとボックススコア、1996 年以降の最終得点が含まれます。一部のチームについては、1894～1895 年シーズン以降の勝敗に関するデータも追加されています。

機械学習

Google Cloud にはデータ アナリストとデータ サイエンティスト向けの幅広い機械学習オプションが用意されています。よく使われているオプションは次のとおりです。

• 機械学習 API: Cloud Vision など一般的な ML タスク向けのトレーニング済み API を使用できます。
• AutoML: カスタムの ML モデルを構築します。コーディングは必要はありません。
• BigQuery ML: BigQuery に読み込まれたデータで、SQL の知識を活かして ML モデルを迅速に構築します。
• AI Platform: 独自のカスタム ML モデルを構築し、Google のインフラストラクチャを使用した本番環境に配置します。

このラボでは、BigQuery ML を使用して、NCAA バスケットボール トーナメントで 2 チーム間の「勝者」と「敗者」のプロトタイプを作成し、トレーニングして評価と予測を行います。

設定と要件

Qwiklabs の設定

[ラボを開始] ボタンをクリックする前に

こちらの手順をお読みください。ラボの時間は記録されており、一時停止することはできません。[ラボを開始] をクリックするとスタートするタイマーは、Google Cloud のリソースを利用できる時間を示しています。

このハンズオンラボでは、シミュレーションやデモ環境ではなく、実際のクラウド環境を使ってご自身でラボのアクティビティを行うことができます。そのため、ラボの受講中に Google Cloud にログインおよびアクセスするための、新しい一時的な認証情報が提供されます。

このラボを完了するためには、下記が必要です。

• 標準的なインターネット ブラウザ（Chrome を推奨）

注: このラボの実行には、シークレット モードまたはシークレット ブラウジング ウィンドウを使用してください。これにより、個人アカウントと受講者アカウント間の競合を防ぎ、個人アカウントに追加料金が発生することを防ぎます。

• ラボを完了するために十分な時間を確保してください。ラボをいったん開始すると一時停止することはできません。

注: すでに個人の Google Cloud アカウントやプロジェクトをお持ちの場合でも、このラボでは使用しないでください。アカウントへの追加料金が発生する可能性があります。

ラボを開始して Console にログインする方法

1. [ラボを開始] ボタンをクリックします。ラボの料金をお支払いいただく必要がある場合は、表示されるポップアップでお支払い方法を選択してください。 左側の [Connection Details] パネルに、このラボで使用する一時的な認証情報が表示されます。

   

2. ユーザー名をコピーし、[Google Console を開く] をクリックします。 ラボでリソースが起動し、別のタブで [アカウントの選択] ページが表示されます。

   ヒント: タブをそれぞれ別のウィンドウで開き、並べて表示しておきましょう。

3. [アカウントの選択] ページで [別のアカウントを使用] をクリックします。

   

4. [ログイン] ページが開きます。[Connection Details] パネルでコピーしたユーザー名を貼り付けます。パスワードもコピーして貼り付けます。

   重要: 認証情報は [Connection Details] パネルに表示されたものを使用してください。ご自身の Qwiklabs 認証情報は使用しないでください。請求が発生する事態を避けるため、GCP アカウントをお持ちの場合でもそのアカウントはラボで使用しないでください。

5. 以降のページでは次の点にご注意ください。

   • 利用規約に同意してください。
   • 復元オプションや 2 要素認証プロセスは設定しないでください（一時的なアカウントであるため）。
   • 無料トライアルには登録しないでください。

その後このタブで GCP Console が開きます。

BigQuery コンソールを開く

Google Cloud Console で、ナビゲーション メニューを開いて [BigQuery] を選択します。

[完了] をクリックして、beta UI に移動します。Qwiklabs GCP プロジェクト ID が、以下のように左側のリソース メニューに表示されます。

プロジェクトの横にあるドロップダウン矢印をクリックしても、データベースやテーブルは表示されません。これは、まだプロジェクトを追加していないためです。

BigQuery では非常に多くの一般公開データセットを利用できます。ここでは NCAA のデータセットについて学習し、データセットを BigQuery プロジェクトに追加する方法について確認します。

NCAA March Madness

全米大学体育協会（NCAA）では、全米大学バスケットボール トーナメントを毎年 2 回（男子と女子）開催しています。3 月の NCAA 男子トーナメントでは、68 チームがシングル エリミネーション形式で競い合い、最終的に 1 チームが March Madness の優勝校となります。

NCAA が提供している一般公開データセットには、シーズンと最終トーナメントでの男女のバスケットボールの試合と選手についての統計情報が含まれています。試合データには、2009 年以降のプレイバイプレイとボックススコア、1996 年以降の最終得点が含まれます。一部のチームについては、1894～1895 年シーズン以降の勝敗に関するデータも追加されています。

このデータセットとその成果について詳しく知るには、ライブ分析情報を予測する Google Cloud マーケティング広告キャンペーンをご覧ください。今年のトーナメントの最新情報は G.co/marchmadness から入手してください。

NCAA 一般公開データセットを BigQuery で検索する

BigQuery コンソールが開いていることを確認して、左側のメニューの [リソース] タブで [ + データを追加] をクリックし、[一般公開データセットを調べる] を選択します。

検索バーに「NCAA Basketball」と入力し、Enter キーを押します。結果が 1 つポップアップされるので、選択して [データセットを表示] をクリックします。

新しい BigQuery のタブが開きデータセットが読み込まれます。このタブでそのまま作業を続けることも、他のタブで BigQuery コンソールを更新して一般公開データセットを表示することもできます。

「ncaa_basketball」データセットの横にある矢印をクリックして、テーブルを表示します。

データセットには 10 のテーブルが表示されます。「mbb_historical_tournament_games」、[プレビュー] の順にクリックして、データのサンプル行を表示します。次に [詳細] をクリックして、テーブルのメタデータを表示します。ページは次のようになります。

利用可能なシーズンと試合を判別するクエリを作成する

ここで mbb_historical_tournament_games テーブルで探索できるシーズン数と試合数を判別するため、簡単な SQL クエリを作成します。

テーブルの詳細セクションの上にあるクエリエディタで、次のコードをコピーしてフィールドに貼り付けます。

SELECT
  season,
  COUNT(*) as games_per_tournament
  FROM
 `bigquery-public-data.ncaa_basketball.mbb_historical_tournament_games`
 GROUP BY season
 ORDER BY season # default is Ascending (low to high)

[実行] をクリックします。間もなく次のような出力が返されます。

出力をスクロールして、シーズンの数とシーズンごとの試合数をメモしておきます。この情報を使用して次の質問に答えます。戻された行数は、右下（ページ分け矢印の近く）でも確認できます。

[進行状況を確認] をクリックして、目標に沿って進行していることを確認します。

利用可能なシーズンと試合を判別するクエリを作成する

理解度を確認する

以下の問題に取り組み、ここまで学習した内容について理解を深めましょう。正解を目指して頑張ってください。

機械学習の特徴とラベルについて理解する

このラボの最終目標は、NCAA 男子バスケットボールの特定の試合の勝者を、過去の試合の知識を利用して予測することです。機械学習では、結果（トーナメント試合の勝敗）を判定するのに役立つデータの各列を「特徴」と呼びます。

予測しようとしているデータ列は「ラベル」と呼びます。機械学習モデルは、特徴間の関連性を「学習」してラベルの結果を予測します。

過去のデータセットの特徴の例を以下に示します。

• シーズン
• チーム名
• 対戦チーム名
• チームのシード（ランキング）
• 対戦チームのシード

今後の試合で予測しようとしているラベルは、チームが勝つか負けるかという試合結果です。

理解度を確認する

以下の問題に取り組み、ここまで学習した内容について理解を深めましょう。正解を目指して頑張ってください。

ラベル付き機械学習データセットを作成する

機械学習モデルを構築するには、高い品質のトレーニング データが多数必要です。幸いなことに、NCAA データセットは十分に堅牢なので効果的なモデルを構築できます。BigQuery コンソールに戻ります。実行したクエリの結果はそのままにしておいてください。

左側のメニューで [mbb_historical_tournament_games] テーブル名をクリックして開きます。テーブルが読み込まれたら、[プレビュー] をクリックします。ページは次のようになります。

理解度を確認する

以下の問題に取り組み、ここまで学習した内容について理解を深めましょう。正解を目指して頑張ってください。

データセットを調べると、1 つの行に「win_market」と「lose_market」の両方の列があることがわかります。行ごとに「winner」または「loser」のラベルを付けられるように、1 試合分の記録をチーム別に分ける必要があります。

クエリエディタに以下のクエリをコピーして貼り付け、[実行] をクリックします。

# create a row for the winning team
SELECT
  # features
  season, # ex: 2015 season has March 2016 tournament games
  round, # sweet 16
  days_from_epoch, # how old is the game
  game_date,
  day, # Friday

  'win' AS label, # our label

  win_seed AS seed, # ranking
  win_market AS market,
  win_name AS name,
  win_alias AS alias,
  win_school_ncaa AS school_ncaa,
  # win_pts AS points,

  lose_seed AS opponent_seed, # ranking
  lose_market AS opponent_market,
  lose_name AS opponent_name,
  lose_alias AS opponent_alias,
  lose_school_ncaa AS opponent_school_ncaa
  # lose_pts AS opponent_points

FROM `bigquery-public-data.ncaa_basketball.mbb_historical_tournament_games`

UNION ALL

# create a separate row for the losing team
SELECT
# features
  season,
  round,
  days_from_epoch,
  game_date,
  day,

  'loss' AS label, # our label

  lose_seed AS seed, # ranking
  lose_market AS market,
  lose_name AS name,
  lose_alias AS alias,
  lose_school_ncaa AS school_ncaa,
  # lose_pts AS points,

  win_seed AS opponent_seed, # ranking
  win_market AS opponent_market,
  win_name AS opponent_name,
  win_alias AS opponent_alias,
  win_school_ncaa AS opponent_school_ncaa
  # win_pts AS opponent_points

FROM
`bigquery-public-data.ncaa_basketball.mbb_historical_tournament_games`

次の出力が表示されます。

[進行状況を確認] をクリックして、目標に沿って進行していることを確認します。

ラベル付き機械学習データセットを作成する

これで結果から利用できる特徴について把握したので、次の質問に答えてデータセットを理解できているかどうか確認してください。

パート 1: シードとチーム名に基づいて勝者を予測する機械学習モデルを作成する

データについて探索したので、次は機械学習モデルをトレーニングしてみましょう。このセクションの内容について理解するために、よく考えて以下の質問に答えてください。

モデルタイプを選択する

今回取り上げる問題に対しては分類モデルを構築します。勝ち負けの 2 つのクラスがあるため、バイナリ分類モデルとも呼ばれます。チームは試合に勝つか負けるかのどちらかです。

ラボの終了後、予測モデルを使用してチームの合計ポイント数を予測することもできますが、ここでは特に取り上げません。

予測または分類のどちらを行うかを確認する簡単な方法は、予測しているデータのラベル（列）のタイプを調べることです。

• 数値列（販売数や試合のポイントなど）の場合、予測を行います。
• 文字列値の場合は分類を行います（この行は一方のクラスまたは他方のクラスのいずれかになります）
• 3 つ以上のクラス（勝ち、負け、引き分けなど）がある場合は、マルチクラス分類を行います。

ここでの分類モデルが機械学習を行う際には、広く使われているロジスティック回帰と呼ばれる統計モデルを使用します。個別のラベル値（今回の場合は「win」または「loss」）ごとにその確率を生成するモデルが必要ですが、ロジスティック回帰はそのために最適なモデルです。幸いなことに、ML モデルがモデル トレーニング中にすべての計算と最適化を行います。これはコンピュータが得意とする分野です。

BigQuery ML を使用して機械学習モデルを作成する

BigQuery では、SQL ステートメントの CREATE MODEL を記述していくつかのオプションを指定するだけで、分類モデルを作成できます。

ただし、モデルを作成する前にその保管場所をプロジェクト内に確保する必要があります。

左側のメニューのリソースリストから、Qwiklabs プロジェクトを選択します。

次に、右側にある [データセットを作成] をクリックします。メニューが開くのでデータセット ID を「bracketology」に設定し、[データセットを作成] をクリックします。

次にクエリエディタで次のコマンドを実行します。

CREATE OR REPLACE MODEL
  `bracketology.ncaa_model`
OPTIONS
  ( model_type='logistic_reg') AS

# create a row for the winning team
SELECT
  # features
  season,

  'win' AS label, # our label

  win_seed AS seed, # ranking
  win_school_ncaa AS school_ncaa,

  lose_seed AS opponent_seed, # ranking
  lose_school_ncaa AS opponent_school_ncaa

FROM `bigquery-public-data.ncaa_basketball.mbb_historical_tournament_games`
WHERE season <= 2017

UNION ALL

# create a separate row for the losing team
SELECT
# features
  season,

  'loss' AS label, # our label
  lose_seed AS seed, # ranking
  lose_school_ncaa AS school_ncaa,

  win_seed AS opponent_seed, # ranking
  win_school_ncaa AS opponent_school_ncaa

FROM
`bigquery-public-data.ncaa_basketball.mbb_historical_tournament_games`

# now we split our dataset with a WHERE clause so we can train on a subset of data and then evaluate and test the model's performance against a reserved subset so the model doesn't memorize or overfit to the training data.

# tournament season information from 1985 - 2017
# here we'll train on 1985 - 2017 and predict for 2018
WHERE season <= 2017

このコードでは、わずか数行の SQL でモデルを作成していることがわかります。最も重要なオプションの 1 つはモデルタイプであり、分類タスクのモデルタイプとして logistic_reg を選択します。

モデルのトレーニングには 3～5 分かかります。ジョブが終了すると次の出力が表示されます。

コンソールの右側で、[モデルに移動] ボタンをクリックします。

[進行状況を確認] をクリックして、目標に沿って進行していることを確認します。

機械学習モデルを作成する

モデル トレーニングの詳細を表示する

モデルの詳細が開いたら、[トレーニング オプション] セクションまでスクロールし、トレーニングするためにモデルが実行した実際の反復回数を確認します。機械学習の経験がある方は、OPTIONS ステートメントで値を定義して、これらのハイパーパラメータ（モデルの実行前に設定するオプション）をすべてカスタマイズすることができます。機械学習を初めて使用する場合、値が未設定のオプションには BigQuery ML によりスマート デフォルト値が設定されます。

詳しくは、BigQuery ML モデル オプション リストをご覧ください。

モデル トレーニング統計を表示する

機械学習モデルは、既知の特徴と未知のラベルの関連付けを「学習」します。直感的に想像すると「ランキング シード」や「大学名」などの特徴は、試合が行われる曜日など他のデータ列（特徴）よりも勝敗の判断に役立ちそうです。機械学習モデルではそのような直感は使わずにトレーニング プロセスを開始し、通常は各特徴の重み付けをランダム化します。

トレーニング プロセス中、モデルは各特徴に適切な重み付けを行えるように過程を最適化します。そして、実行のたびに [トレーニング データの損失] と [評価データの損失] の値を最小化しようとします。最終的に評価の損失の値がトレーニングの損失の値を大きく上回る場合、モデルは一般化が可能な関係を学習するのではなく、トレーニング データを過学習または記憶しています。

モデルツールを実行するトレーニングの数を表示するには [モデルの統計情報] タブや [トレーニング] タブをクリックします。ある特定の実行中、モデルは約 20 秒間に 3 回トレーニングの反復を実行しました。実際はおそらく異なります。

モデルが特徴について学習した内容を表示する

トレーニング後に重みを調べることで、モデルに最も価値をもたらしたのはどの特徴かを確認できます。クエリエディタで次のコマンドを実行します。

SELECT
  category,
  weight
FROM
  UNNEST((
    SELECT
      category_weights
    FROM
      ML.WEIGHTS(MODEL `bracketology.ncaa_model`)
    WHERE
      processed_input = 'seed')) # try other features like 'school_ncaa'
      ORDER BY weight DESC

出力は次のようになります。

ご覧のように、チームのシードの値が非常に低い場合（1、2、3）または非常に高い場合（14、15、16）、モデルは勝敗の決定にかなりの重み（最大値は 1.0）を付与します。シードが低いほどチームが大会で活躍することが予想されるので、これは直感的に理解できます。

これが機械学習の素晴らしい点です。SQL でハードコードされた IF THEN ステートメントを山ほど作成せずにすみます。つまり（IF）シードが 1 の場合（THEN）チームに 80% 以上の勝率を付与するようにモデルに指示する必要はありません。機械学習にはハードコードされたルールとロジックは必要ありません。これらの関係は自動的に学習されます。詳しくは、BQML の WEIGHTS 構文の資料をご覧ください。

モデル パフォーマンスを評価する

モデル パフォーマンスを評価するには、トレーニングされたモデルに対してシンプルな ML.EVALUATE を実行します。クエリエディタで次のコマンドを実行します。

SELECT
  *
FROM
  ML.EVALUATE(MODEL   `bracketology.ncaa_model`)

次のような出力が返されます。

精度はおよそ 69% になります。コイントスより若干ましですが、改善の余地はあります。

注: 分類モデルの場合、出力で注意すべきメトリックはモデルの精度だけではありません。 ロジスティック回帰を実行したので、以下のすべての指標に対してモデル パフォーマンスを評価できます（1.0 に近づくほど精度が向上） Precision : 分類モデルの指標。陽性のクラスの予測でモデルが正しかった確率を表します。Recall : 「全陽性のラベルの中でモデルが正しく識別したラベルの数は？」という質問に回答する分類モデルの指標。 Accuracy : 分類モデルの予測のうち、正解の割合。 f1_score : モデルの精度を表す尺度。f1 値は適合率と再現率の調和平均を表します。最も精度の高い f1 値は 1 で、最も精度の低い値は 0 です。 log_loss : ロジスティック回帰で使用される損失関数。モデルの予測が正しいラベルからどのくらい離れているかを表します。roc_auc : ROC 曲線の下の面積。これは、無作為に選択した陽性のサンプルが陽性に分類される確率が、無作為に選択した陰性のサンプルが陽性に分類される確率よりも高い可能性を表します。

予測を作成する

2017 年のシーズンまでの過去のデータ（手持ちのすべてのデータ）でモデルをトレーニングしたので、次に 2018 年のシーズンの予測を行ってみましょう。データ サイエンス チームにより、元のデータセットが入っていたのとは別のテーブルに、2018 年のトーナメント結果が提供されています。

トレーニング済みのモデルで ML.PREDICT を呼び出し、予測するデータセットを渡すだけで予測を作成できます。

クエリエディタで次のコマンドを実行します。

CREATE OR REPLACE TABLE `bracketology.predictions` AS (

SELECT * FROM ML.PREDICT(MODEL `bracketology.ncaa_model`,

# predicting for 2018 tournament games (2017 season)
(SELECT * FROM `data-to-insights.ncaa.2018_tournament_results`)
)
)

すぐに次のような出力が返されます。

[進行状況を確認] をクリックして、目標に沿って進行していることを確認します。

モデルのパフォーマンスを評価し、テーブルを作成する

元のデータセットに加えて、新たに次の 3 列が追加されています。

• 予測ラベル
• 予測ラベル オプション
• 予測ラベルの確率

2018 年の March Madness のトーナメント結果は判明しているので、モデルがどのように予測したかを見てみましょう（ヒント: 今年の March Madness トーナメントを予測する場合は、2019 年のシードとチーム名を含むデータセットを渡すだけです。当然ながら、これらの試合はまだ行われていないためにラベルの列は空になりますが、これから自分で予測できます）。

2018 年の NCAA トーナメントでのモデルの正解数を確認する

クエリエディタで次のコマンドを実行します。

SELECT * FROM `bracketology.predictions`
WHERE predicted_label <> label

次のような出力が返されます。

134 の予測（3 月の 67 のトーナメント試合）のうち、モデルは 38 回間違えたので、2018 年のトーナメント試合結果全体の精度は 70% です。

モデルの限度

March Madness トーナメントでは僅差での勝利や番狂わせなど、モデルの予測が難しい他の要因や特徴が数多くあります。

モデルと照合しながら 2017 年度トーナメントの最大の番狂わせを見つけてみましょう。モデルが 80% 以上の信頼度で予測したもので間違った箇所を確認します。

クエリエディタで次のコマンドを実行します。

SELECT
  model.label AS predicted_label,
  model.prob AS confidence,

  predictions.label AS correct_label,

  game_date,
  round,

  seed,
  school_ncaa,
  points,

  opponent_seed,
  opponent_school_ncaa,
  opponent_points

FROM `bracketology.predictions` AS predictions,
UNNEST(predicted_label_probs) AS model

WHERE model.prob > .8 AND predicted_label <> predictions.label

出力は次のようになります。

こちらの動画で実際の結果を確認してみましょう。

UMBC の Odom コーチは試合後に「信じられない、としか言えません」と語っています。

内容のまとめ

• 試合結果を予測する機械学習モデルを作成しました。
• パフォーマンスを評価したところ、主な特徴の「シード」と「チーム名」を使用して 69% の精度を達成したことがわかりました。
• 2018 年のトーナメント結果を予測しました。
• 結果を分析して詳細な情報を得ました。

次の課題は、特徴にシードとチーム名を「使わずに」より優れたモデルを構築することです。

パート 2: ML モデルで効果的な特徴を使用する

このラボの後半では、新たに提供される詳細な特徴を使用して、2 つ目の ML モデルを構築します。

BigQuery ML を使用した ML モデルの構築方法について理解できたところで、今度はデータ サイエンス チームによって提供されている新しいプレイバイプレイ データセットを見ていきましょう。このデータセットでは、モデルによる学習用に新しいチーム指標が作成されています。以下が含まれます。

• 過去のプレイバイプレイの分析に基づく、時系列の得点効率。
• 時系列のバスケットボールのポゼッション。

これらの効果的な特徴によって新規 ML データを作成する

クエリエディタで次のコマンドを実行します。

# create training dataset:
# create a row for the winning team
CREATE OR REPLACE TABLE `bracketology.training_new_features` AS
WITH outcomes AS (
SELECT
  # features
  season, # 1994

  'win' AS label, # our label

  win_seed AS seed, # ranking # this time without seed even
  win_school_ncaa AS school_ncaa,

  lose_seed AS opponent_seed, # ranking
  lose_school_ncaa AS opponent_school_ncaa

FROM `bigquery-public-data.ncaa_basketball.mbb_historical_tournament_games` t
WHERE season >= 2014

UNION ALL

# create a separate row for the losing team
SELECT
# features
  season, # 1994

  'loss' AS label, # our label

  lose_seed AS seed, # ranking
  lose_school_ncaa AS school_ncaa,

  win_seed AS opponent_seed, # ranking
  win_school_ncaa AS opponent_school_ncaa

FROM
`bigquery-public-data.ncaa_basketball.mbb_historical_tournament_games` t
WHERE season >= 2014

UNION ALL

# add in 2018 tournament game results not part of the public dataset:
SELECT
  season,
  label,
  seed,
  school_ncaa,
  opponent_seed,
  opponent_school_ncaa
FROM
  `data-to-insights.ncaa.2018_tournament_results`

)

SELECT
o.season,
label,

# our team
  seed,
  school_ncaa,
  # new pace metrics (basketball possession)
  team.pace_rank,
  team.poss_40min,
  team.pace_rating,
  # new efficiency metrics (scoring over time)
  team.efficiency_rank,
  team.pts_100poss,
  team.efficiency_rating,

# opposing team
  opponent_seed,
  opponent_school_ncaa,
  # new pace metrics (basketball possession)
  opp.pace_rank AS opp_pace_rank,
  opp.poss_40min AS opp_poss_40min,
  opp.pace_rating AS opp_pace_rating,
  # new efficiency metrics (scoring over time)
  opp.efficiency_rank AS opp_efficiency_rank,
  opp.pts_100poss AS opp_pts_100poss,
  opp.efficiency_rating AS opp_efficiency_rating,

# a little feature engineering (take the difference in stats)

  # new pace metrics (basketball possession)
  opp.pace_rank - team.pace_rank AS pace_rank_diff,
  opp.poss_40min - team.poss_40min AS pace_stat_diff,
  opp.pace_rating - team.pace_rating AS pace_rating_diff,
  # new efficiency metrics (scoring over time)
  opp.efficiency_rank - team.efficiency_rank AS eff_rank_diff,
  opp.pts_100poss - team.pts_100poss AS eff_stat_diff,
  opp.efficiency_rating - team.efficiency_rating AS eff_rating_diff

FROM outcomes AS o
LEFT JOIN `data-to-insights.ncaa.feature_engineering` AS team
ON o.school_ncaa = team.team AND o.season = team.season
LEFT JOIN `data-to-insights.ncaa.feature_engineering` AS opp
ON o.opponent_school_ncaa = opp.team AND o.season = opp.season

すぐに次のような出力が返されます。

[進行状況を確認] をクリックして、目標に沿って進行していることを確認します。

ML モデルで効果的な特徴を使用する

新しい特徴をプレビューする

コンソールの右側の [テーブルに移動] ボタンをクリックします。次に [プレビュー] タブをクリックすると、

テーブルは次のようになります。

出力がこのスクリーンショットと同じでなくても問題ありません。

選択した指標を解釈する

ここでは、予測を行う際に役立つ重要なラベルについて学習します。

opp_efficiency_rank

対戦相手の効率ランク: 時系列の得点効率に関する、すべてのチーム中の相手チームのランク（100 ポゼッションあたりのポイント数）。 値が小さいほど良い。

opp_pace_rank

対戦相手のペースランク: バスケットボールのポゼッションに関する、すべてのチームの中の相手チームのランク（40 分間のポゼッション数）。値が小さいほど良い。

チームがどれだけ効率的に得点できたか、およびどの程度ボールを保持できたかについて有益な特徴が用意できたので、2 つ目のモデルをトレーニングしてみましょう。

モデルが「過去の優れたチームを記憶」しないようにするため、追加手段としてチームの名前とシードをこのモデルから除外して指標にのみ焦点を合わせます。

新規モデルをトレーニングする

クエリエディタで次のコマンドを実行します。

CREATE OR REPLACE MODEL
  `bracketology.ncaa_model_updated`
OPTIONS
  ( model_type='logistic_reg') AS

SELECT
  # this time, dont train the model on school name or seed
  season,
  label,

  # our pace
  poss_40min,
  pace_rank,
  pace_rating,

  # opponent pace
  opp_poss_40min,
  opp_pace_rank,
  opp_pace_rating,

  # difference in pace
  pace_rank_diff,
  pace_stat_diff,
  pace_rating_diff,


  # our efficiency
  pts_100poss,
  efficiency_rank,
  efficiency_rating,

  # opponent efficiency
  opp_pts_100poss,
  opp_efficiency_rank,
  opp_efficiency_rating,

  # difference in efficiency
  eff_rank_diff,
  eff_stat_diff,
  eff_rating_diff

FROM `bracketology.training_new_features`

# here we'll train on 2014 - 2017 and predict on 2018
WHERE season BETWEEN 2014 AND 2017 # between in SQL is inclusive of end points

すぐに次のような出力が表示されます。

新規モデルのパフォーマンスを評価する

モデルのパフォーマンスを評価するために、クエリエディタで次のコマンドを実行します。

SELECT
  *
FROM
  ML.EVALUATE(MODEL     `bracketology.ncaa_model_updated`)

出力は次のようになります。

効果がありました。別の特徴を使用して新しいモデルをトレーニングした結果、精度は約 75%となり、元のモデルから 5% 上がりました。

これは機械学習で得られる最大の教訓の 1 つです。質の高い特徴のデータセットは、モデルの精度を大きく向上させます。

[進行状況を確認] をクリックして、目標に沿って進行していることを確認します。

新しいモデルをトレーニングして評価する

モデルが学習した内容を調査する

モデルが勝敗の結果を出すのに最も重く見た特徴は何でしょうか。クエリエディタで次のコマンドを実行して確認します。

SELECT
  *
FROM
  ML.WEIGHTS(MODEL     `bracketology.ncaa_model_updated`)
ORDER BY ABS(weight) DESC

出力は次のようになります。

重みの絶対値を順序付けに採用しているため、（勝敗に）最も影響の大きいものが最初に示されます。

結果をご覧になるとわかるように、上位 3 つは「pace_stat_diff」、「eff_stat_diff」、および「eff_rating_diff」です。もう少し詳しく見ていきましょう。

pace_stat_diff

（ポゼッション数 / 40 分）の実際の統計情報のチーム間の違い。モデルによると、これは試合結果を左右する最大の要因です。

eff_stat_diff

（ネットポイント数 / 100 ポゼッション）の実際の統計情報のチーム間の違い。

eff_rating_diff

得点効率の正規化された評価のチーム間の違い。

このモデルが予測で重みを置かなかったものは、シーズンです。シーズンは上記出力の順付けされた重み付けで最下位でした。つまり、シーズンの違い（2013、2014、2015）が試合結果の予測にそれほど役に立たないことが示されています。どのチームでも「2014」年に関する魔法のような要因は存在していません。

興味深い分析情報は、モデルがチームのペース（ボールをどの程度制御しているか）を、チームの得点効率より重視したことです。

予測する

クエリエディタで次のコマンドを実行します。

CREATE OR REPLACE TABLE `bracketology.ncaa_2018_predictions` AS

# let's add back our other data columns for context
SELECT
  *
FROM
  ML.PREDICT(MODEL     `bracketology.ncaa_model_updated`, (

SELECT
* # include all columns now (the model has already been trained)
FROM `bracketology.training_new_features`

WHERE season = 2018

))

出力は次のようになります。

[進行状況を確認] をクリックして、目標に沿って進行していることを確認します。

クエリを実行してテーブル ncaa_2018_predictions を作成する

予測分析:

正しい試合結果は判明しているため、新しいテスト データセットを使用した予測のモデルが間違えた箇所を確認できます。クエリエディタで次のコマンドを実行します。

SELECT * FROM `bracketology.ncaa_2018_predictions`
WHERE predicted_label <> label

クエリから返されたレコード数からわかるように、モデルの 2018 年の精度は 64% で、トーナメントの全試合結果のうち 48 の試合結果（24 試合分）が誤りです。2018 年は大荒れの年だったようです。何が起きたのか見てみましょう。

2018 年 3 月の混乱の原因について

クエリエディタで次のコマンドを実行します。

SELECT
CONCAT(school_ncaa, " was predicted to ",IF(predicted_label="loss","lose","win")," ",CAST(ROUND(p.prob,2)*100 AS STRING), "% but ", IF(n.label="loss","lost","won")) AS narrative,
predicted_label, # what the model thought
n.label, # what actually happened
ROUND(p.prob,2) AS probability,
season,

# us
seed,
school_ncaa,
pace_rank,
efficiency_rank,

# them
opponent_seed,
opponent_school_ncaa,
opp_pace_rank,
opp_efficiency_rank

FROM `bracketology.ncaa_2018_predictions` AS n,
UNNEST(predicted_label_probs) AS p
WHERE
  predicted_label <> n.label # model got it wrong
  AND p.prob > .75  # by more than 75% confidence
ORDER BY prob DESC

結果は次のようになります。

大番狂わせは前のモデルで検出されたものと同じ UMBC 対バージニアの試合でした。2018 年は全体的に大番狂わせが多い年でした。2019 年も大荒れが予想されるでしょうか。

モデル パフォーマンスの比較

単純なモデル（シードの比較）が間違えたにも関わらず、高度なモデルは正しかった箇所はどこでしょうか。クエリエディタで次のコマンドを実行します。

SELECT
CONCAT(opponent_school_ncaa, " (", opponent_seed, ") was ",CAST(ROUND(ROUND(p.prob,2)*100,2) AS STRING),"% predicted to upset ", school_ncaa, " (", seed, ") and did!") AS narrative,
predicted_label, # what the model thought
n.label, # what actually happened
ROUND(p.prob,2) AS probability,
season,

# us
seed,
school_ncaa,
pace_rank,
efficiency_rank,

# them
opponent_seed,
opponent_school_ncaa,
opp_pace_rank,
opp_efficiency_rank,

(CAST(opponent_seed AS INT64) - CAST(seed AS INT64)) AS seed_diff

FROM `bracketology.ncaa_2018_predictions` AS n,
UNNEST(predicted_label_probs) AS p
WHERE
  predicted_label = 'loss'
  AND predicted_label = n.label # model got it right
  AND p.prob >= .55  # by 55%+ confidence
  AND (CAST(opponent_seed AS INT64) - CAST(seed AS INT64)) > 2 # seed difference magnitude
ORDER BY (CAST(opponent_seed AS INT64) - CAST(seed AS INT64)) DESC

結果は次のようになります。

モデルはザビエル大（01）に対するフロリダ州立大（09）の番狂わせを予測して、それが当たっていました。

この番狂わせは、ペースやシュート効率といった新しい効果的な特徴に基づき、（シード ランキングが間違えたにも関わらず）新しいモデルによって正しく予測されていました。YouTube で試合ハイライトをご覧ください。

2019 年 March Madness トーナメントを予想する

2019 年 3 月のチームとシードのランキングは判明しているため、将来の試合結果を予測してみましょう。

2019 年のデータを確認する

次のクエリを実行して上位シードを確認します。

SELECT * FROM `data-to-insights.ncaa.2019_tournament_seeds` WHERE seed = 1

結果は次のようになります。

可能性があるすべての試合のマトリックスを作成する

トーナメントの進行次第で、どのチームの組み合わせで対戦になるかは不明なので、単純にすべてのチームが対戦するようにします。

SQL で 1 つのチームがテーブル内の他のすべてのチームと対戦するように設定するには、CROSS JOIN を使用すると便利です。

以下のクエリを実行して、トーナメントで実施される可能性があるすべてのチームの試合を取得します。

SELECT
  NULL AS label,
  team.school_ncaa AS team_school_ncaa,
  team.seed AS team_seed,
  opp.school_ncaa AS opp_school_ncaa,
  opp.seed AS opp_seed
FROM `data-to-insights.ncaa.2019_tournament_seeds` AS team
CROSS JOIN `data-to-insights.ncaa.2019_tournament_seeds` AS opp
# teams cannot play against themselves :)
WHERE team.school_ncaa <> opp.school_ncaa

2018 年のチーム統計（ペース、効率）に追加する

CREATE OR REPLACE TABLE `bracketology.ncaa_2019_tournament` AS

WITH team_seeds_all_possible_games AS (
  SELECT
    NULL AS label,
    team.school_ncaa AS school_ncaa,
    team.seed AS seed,
    opp.school_ncaa AS opponent_school_ncaa,
    opp.seed AS opponent_seed
  FROM `data-to-insights.ncaa.2019_tournament_seeds` AS team
  CROSS JOIN `data-to-insights.ncaa.2019_tournament_seeds` AS opp
  # teams cannot play against themselves :)
  WHERE team.school_ncaa <> opp.school_ncaa
)

, add_in_2018_season_stats AS (
SELECT
  team_seeds_all_possible_games.*,
  # bring in features from the 2018 regular season for each team
  (SELECT AS STRUCT * FROM `data-to-insights.ncaa.feature_engineering` WHERE school_ncaa = team AND season = 2018) AS team,
  (SELECT AS STRUCT * FROM `data-to-insights.ncaa.feature_engineering` WHERE opponent_school_ncaa = team AND season = 2018) AS opp

FROM team_seeds_all_possible_games
)

# Preparing 2019 data for prediction
SELECT

  label,

  2019 AS season, # 2018-2019 tournament season

# our team
  seed,
  school_ncaa,
  # new pace metrics (basketball possession)
  team.pace_rank,
  team.poss_40min,
  team.pace_rating,
  # new efficiency metrics (scoring over time)
  team.efficiency_rank,
  team.pts_100poss,
  team.efficiency_rating,

# opposing team
  opponent_seed,
  opponent_school_ncaa,
  # new pace metrics (basketball possession)
  opp.pace_rank AS opp_pace_rank,
  opp.poss_40min AS opp_poss_40min,
  opp.pace_rating AS opp_pace_rating,
  # new efficiency metrics (scoring over time)
  opp.efficiency_rank AS opp_efficiency_rank,
  opp.pts_100poss AS opp_pts_100poss,
  opp.efficiency_rating AS opp_efficiency_rating,

# a little feature engineering (take the difference in stats)

  # new pace metrics (basketball possession)
  opp.pace_rank - team.pace_rank AS pace_rank_diff,
  opp.poss_40min - team.poss_40min AS pace_stat_diff,
  opp.pace_rating - team.pace_rating AS pace_rating_diff,
  # new efficiency metrics (scoring over time)
  opp.efficiency_rank - team.efficiency_rank AS eff_rank_diff,
  opp.pts_100poss - team.pts_100poss AS eff_stat_diff,
  opp.efficiency_rating - team.efficiency_rating AS eff_rating_diff

FROM add_in_2018_season_stats

予測を行う

CREATE OR REPLACE TABLE `bracketology.ncaa_2019_tournament_predictions` AS

SELECT
  *
FROM
  # let's predicted using the newer model
  ML.PREDICT(MODEL     `bracketology.ncaa_model_updated`, (

# let's predict on March 2019 tournament games:
SELECT * FROM `bracketology.ncaa_2019_tournament`
))

[進行状況を確認] をクリックして、目標に沿って進行していることを確認します。

クエリを実行して、テーブル ncaa_2019_tournament および ncaa_2019_tournament_predictions を作成する

予測を取得する

SELECT
  p.label AS prediction,
  ROUND(p.prob,3) AS confidence,
  school_ncaa,
  seed,
  opponent_school_ncaa,
  opponent_seed
FROM `bracketology.ncaa_2019_tournament_predictions`,
UNNEST(predicted_label_probs) AS p
WHERE p.prob >= .5
AND school_ncaa = 'Duke'
ORDER BY seed, opponent_seed

ここでは、対デューク大の可能性があるすべての試合を確認するためにモデルの結果をフィルタしました。スクロールしてデューク大対ノースダコタ州立大の試合を見つけます。

分析情報: デューク大（1）は、2019 年 3 月 22 日にノースダコタ州立大（16）に 88.5% の確率で勝利します。

上記の school_ncaa フィルタを変更して、対戦組み合わせ表内のさまざまな対戦結果を予測してみましょう。モデルの信頼度を書き留めた上で試合をお楽しみください。

お疲れさまでした

このように、機械学習を使用して NCAA 男子バスケットボール トーナメントの勝利チームを予測しました。

クエストを完了する

このセルフペース ラボは、Qwiklabs の「NCAA® March Madness®: Google Cloud を使用した予想」と「BigQuery を使用した機械学習」クエストの一部です。クエストとは学習パスを構成する一連のラボのことで、完了するとその成果が認められてバッジが贈られます。バッジは公開して、オンライン レジュメやソーシャル メディア アカウントにリンクできます。このラボの修了後、次のクエストに登録すれば、すぐにクレジットを受け取ることができます。受講可能なその他の Qwiklabs のクエスト（https://google.qwiklabs.com/catalog?locale=ja）もご覧ください。

次のラボを受講する

このラボで、ビッグデータの探索と処理、BigQuery 内での機械学習モデルの迅速な作成を十分にお試しいただけたでしょうか。続いて以下のラボの受講をお試しください。

• BigQuery を使用して NCAA Data を調べる

• SQL for BigQuery と Cloud SQL の概要

次のステップと詳細情報

• バスケットボールの指標と分析の詳細については、Google Cloud NCAA トーナメント広告と予測を裏付けるチームの追加の分析をご覧ください。
• ML で実施できることを確認するには、Compute Engine で機械学習を使用して、商品のレコメンデーションを作成するをご覧ください。
• データ サイエンス クエスト

Google Cloud トレーニングと認定資格

Google Cloud トレーニングと認定資格を通して、Google Cloud 技術を最大限に活用できるようになります。必要な技術スキルとベスト プラクティスについて取り扱うクラスでは、学習を継続的に進めることができます。トレーニングは基礎レベルから上級レベルまであり、オンデマンド、ライブ、バーチャル参加など、多忙なスケジュールにも対応できるオプションが用意されています。認定資格を取得することで、Google Cloud テクノロジーに関するスキルと知識を証明できます。

マニュアルの最終更新日: 2020 年 10 月 9 日

ラボの最終テスト日: 2020 年 10 月 9 日

Copyright 2024 Google LLC All rights reserved. Google および Google のロゴは Google LLC の商標です。その他すべての企業名および商品名はそれぞれ各社の商標または登録商標です。