GSP461

概要

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

BigQuery ML を利用すると、データがすでに BigQuery 内に存在する場合に、データ アナリストが SQL の知識を生かしてすぐに ML モデルを構築できます。

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

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

演習内容

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

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

前提条件

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

• SQL for BigQuery と Cloud SQL の概要
• BigQuery: Qwik Start - コンソール

設定と要件

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

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

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

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

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

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

• ラボを完了するための時間（開始後は一時停止できません）

注: このラボでは、受講者アカウントのみを使用してください。別の Google Cloud アカウントを使用すると、そのアカウントに料金が発生する可能性があります。

ラボを開始して Google Cloud コンソールにログインする方法

1. [ラボを開始] ボタンをクリックします。ラボの料金をお支払いいただく必要がある場合は、表示されるダイアログでお支払い方法を選択してください。 左側の [ラボの詳細] ペインには、以下が表示されます。

   • [Google Cloud コンソールを開く] ボタン
   • 残り時間
   • このラボで使用する必要がある一時的な認証情報
   • このラボを行うために必要なその他の情報（ある場合）

2. [Google Cloud コンソールを開く] をクリックします（Chrome ブラウザを使用している場合は、右クリックして [シークレット ウィンドウで開く] を選択します）。

   ラボでリソースがスピンアップし、別のタブで [ログイン] ページが表示されます。

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

   注: [アカウントの選択] ダイアログが表示されたら、[別のアカウントを使用] をクリックします。

3. 必要に応じて、下のユーザー名をコピーして、[ログイン] ダイアログに貼り付けます。

   {{{user_0.username | "Username"}}}

   [ラボの詳細] ペインでもユーザー名を確認できます。

4. [次へ] をクリックします。

5. 以下のパスワードをコピーして、[ようこそ] ダイアログに貼り付けます。

   {{{user_0.password | "Password"}}}

   [ラボの詳細] ペインでもパスワードを確認できます。

6. [次へ] をクリックします。

   重要: ラボで提供された認証情報を使用する必要があります。Google Cloud アカウントの認証情報は使用しないでください。 注: このラボでご自身の Google Cloud アカウントを使用すると、追加料金が発生する場合があります。

7. その後次のように進みます。

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

その後、このタブで Google Cloud コンソールが開きます。

注: Google Cloud のプロダクトやサービスにアクセスするには、ナビゲーション メニューをクリックするか、[検索] フィールドにサービス名またはプロダクト名を入力します。

タスク 1. BigQuery コンソールを開く

1. Cloud コンソールで、ナビゲーション メニューを開いて [BigQuery] を選択します。

2. [完了] をクリックして、ベータ版 UI に移動します。プロジェクト ID が [エクスプローラ] タブに設定されているか確認します。以下のように見えるはずです。

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

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

タスク 2. NCAA March Madness

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

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

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

タスク 3. NCAA 一般公開データセットを BigQuery で見つける

1. BigQuery コンソールが開いていることを確認して、[エクスプローラ] タブで [+ 追加] ボタンをクリックしてから、[一般公開データセット] を選択します。

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

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

注: 「ncaa_basketball」を確認できない場合は、[+ 追加] > [名前を指定してプロジェクトにスターを付ける] をクリックします。プロジェクト名として「bigquery-public-data」を入力してから、[スターを付ける] をクリックします。

3. [bigquery-public-data] > [ncaa_basketball] データセットを順に展開して、テーブルを表示します。

データセットには 10 個のテーブルが表示されます。

4. 「mbb_historical_tournament_games」、[プレビュー] の順にクリックして、データのサンプル行を表示します。

5. 次に [詳細] をクリックして、テーブルのメタデータを表示します。

ページは次のようになります。

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

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

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

SELECT season, COUNT(*) as games_per_tournament FROM `bigquery-public-data.ncaa_basketball.mbb_historical_tournament_games` GROUP BY season ORDER BY season # デフォルトは昇順（低から高）

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

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

[進行状況を確認] をクリックして、目標に沿って進んでいることを確認します。 利用可能なシーズンと試合を判別するクエリを作成する

理解度チェック

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

タスク 5. ML の特徴とラベルについて理解する

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

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

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

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

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

理解度チェック

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

タスク 6. ラベル付き ML データセットを作成する

ML モデルを構築するには、高い品質のトレーニング データが多数必要です。幸いなことに、NCAA データセットは十分に堅牢なので効果的なモデルを構築できます。

1. BigQuery コンソールに戻ります。実行したクエリの結果はそのままにしておいてください。

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

理解度チェック

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

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

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

# 勝利チームの行を作成する SELECT # 特徴 season, # 例: 2015 シーズンに 2016 年 3 月のトーナメント試合が含まれる round, # sweet 16 days_from_epoch, # 試合後の経過日数 game_date, day, # 金曜日 'win' AS label, # ラベル win_seed AS seed, # ランキング 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, # ランキング 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 # 負けたチーム用の別の行を作成する SELECT # 特徴 season, round, days_from_epoch, game_date, day, 'loss' AS label, # ラベル lose_seed AS seed, # ランキング 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, # ランキング 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`

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

[進行状況を確認] をクリックして、目標に沿って進んでいることを確認します。 ラベル付き ML データセットを作成する

これで結果から利用できる特徴を把握したので、次の質問に答えてデータセットについて理解を深めましょう。

タスク 7. ML モデルを作成して、シードとチーム名を基に勝利チームを予測する

データについて探索したので、次は ML モデルをトレーニングしてみましょう。

• このセクションを理解するために、よく考えて以下の質問に答えてください。

モデルタイプを選択する

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

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

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

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

ここでの分類モデルが ML を行う際には、広く使われているロジスティック回帰と呼ばれる統計モデルを使用します。

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

注: 分類タスクを実行するにあたり、複雑さが異なる多数の ML モデルが存在します。Google でよく使用するモデルの 1 つにニューラル ネットワークを使用したディープ ラーニングがあります。

BigQuery ML を使用して ML モデルを作成する

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

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

1. [エクスプローラ] タブで、プロジェクト ID の横にある「アクションを表示」アイコンをクリックし、[データセットを作成] を選択します。

2. これにより、[データセットを作成する] ダイアログが開きます。データセット ID を bracketology に設定して、[データセットを作成] をクリックします。

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

CREATE OR REPLACE MODEL `bracketology.ncaa_model` OPTIONS ( model_type='logistic_reg') AS # 勝利チームの行を作成する SELECT # 特徴 season, 'win' AS label, # ラベル win_seed AS seed, # ランキング win_school_ncaa AS school_ncaa, lose_seed AS opponent_seed, # ランキング lose_school_ncaa AS opponent_school_ncaa FROM `bigquery-public-data.ncaa_basketball.mbb_historical_tournament_games` WHERE season <= 2017 UNION ALL # 負けたチームの行を別に作成する SELECT # 特徴 season, 'loss' AS label, # ラベル lose_seed AS seed, # ランキング lose_school_ncaa AS school_ncaa, win_seed AS opponent_seed, # ランキング win_school_ncaa AS opponent_school_ncaa FROM `bigquery-public-data.ncaa_basketball.mbb_historical_tournament_games` # ここで、データセットを WHERE 句で分割して、データのサブセットでトレーニングし、モデルがトレーニング データを記憶したり過学習したりしないように、予約済みのサブセットに対するモデルのパフォーマンスを評価およびテストできる。 # 1985 年から 2017 年までのトーナメント シーズン情報 # ここで 1985 年から 2017 年までをトレーニングして 2018 年を予測する WHERE season <= 2017

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

注: 利用可能なモデル オプションすべての一覧と設定については BigQuery ML ドキュメント ガイドをご覧ください。今回は「label」という名前のフィールドがすでにあるため、モデル オプションである input_label_cols を使用してラベル列を指定する必要はありません。

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

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

[進行状況を確認] をクリックして、目標に沿って進んでいることを確認します。 ML モデルの作成

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

• モデルの詳細が開いたら、[トレーニング オプション] セクションまで下にスクロールし、トレーニングするためにモデルが実行した実際のイテレーション回数を確認します。

ML の経験がある方は、OPTIONS ステートメントで値を定義して、これらのハイパーパラメータ（モデルの実行前に設定するオプション）をすべてカスタマイズできます。

ML を初めて使用する場合、値が未設定のオプションには BigQuery ML によりスマート デフォルト値が設定されます。

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

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

ML モデルは、既知の特徴と未知のラベルの関連付けを「学習」します。直感的に想像すると「ランキング シード」や「大学名」などの特徴は、試合が行われる曜日など他のデータ列（特徴）よりも勝敗の判断に役立ちそうです。

ML モデルではそのような直感は使わずにトレーニング プロセスを開始し、通常は各特徴の重み付けをランダム化します。

トレーニング プロセス中、モデルは各特徴に可能な限り適切な重み付けを行えるように過程を最適化します。そして、実行のたびに [トレーニング データの損失] と [評価データの損失] の値を最小化しようとします。

最終的に評価の損失の値がトレーニングの損失の値を大きく上回る場合、モデルは一般化が可能な関係を学習するのではなく、トレーニング データを過学習または記憶しています。

[トレーニング] タブをクリックして、オプションとして [表示] の [テーブル] を選択することで、モデルが何回トレーニングを行うかを確認できます。

ある特定の実行中、モデルは約 20 秒間に 3 回トレーニングのイテレーションを実行しました。実際はおそらく異なります。

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

トレーニング後に重みを調べることで、モデルに最も価値をもたらしたのはどの特徴かを確認できます。

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

SELECT category, weight FROM UNNEST(( SELECT category_weights FROM ML.WEIGHTS(MODEL `bracketology.ncaa_model`) WHERE processed_input = 'seed')) # 'school_ncaa' のような他の特徴を試す ORDER BY weight DESC

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

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

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

タスク 8. モデルのパフォーマンスを評価する

モデルのパフォーマンスを評価するには、トレーニング済みのモデルに対してシンプルな ML.EVALUATE を実行します。

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

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 曲線の下の面積です。無作為に選択した陽性のサンプルが陽性に分類される確率が、無作為に選択した陰性のサンプルが陽性に分類される確率よりも高い可能性を表します。

タスク 9. 予測を作成する

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

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

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

CREATE OR REPLACE TABLE `bracketology.predictions` AS ( SELECT * FROM ML.PREDICT(MODEL `bracketology.ncaa_model`, # 2018 年のトーナメント試合の予測（2017 年のシーズン） (SELECT * FROM `data-to-insights.ncaa.2018_tournament_results`) ) )

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

[進行状況を確認] をクリックして、目標に沿って進んでいることを確認します。 モデルのパフォーマンスを評価し、テーブルを作成する

注: 予測はテーブルに保存されるので、後から分析情報を得る際には上記のクエリを再実行しなくてもクエリできます。

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

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

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

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

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

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

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

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

タスク 11. モデルの限度

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

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

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

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

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

予測: モデルは、第 1 シードのバージニア大が第 16 シードの UMBC に 87% の信頼度で勝つと予測していますが、これは妥当なように思われます。

「第 16 シードの UMBC が第 1 シードのバージニア大を下す番狂わせ」の動画を観て、実際の結果を見てみましょう。

UMBC の Odom コーチは試合後に「信じられない、としか言えません」と語っています。「2018 年の男子バスケットボール UMBC 対バージニア大の試合」の記事で詳細を確認できます。

内容のまとめ

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

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

タスク 12. ML モデル用の効果的な特徴を使用する

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

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

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

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

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

# トレーニング データセットを作成する: # 勝利チームの行を作成する CREATE OR REPLACE TABLE `bracketology.training_new_features` AS WITH outcomes AS ( SELECT # 特徴 season, # 1994 年 'win' AS label, # ラベル win_seed AS seed, # ランキング # 今回はシードなし win_school_ncaa AS school_ncaa, lose_seed AS opponent_seed, # ランキング lose_school_ncaa AS opponent_school_ncaa FROM `bigquery-public-data.ncaa_basketball.mbb_historical_tournament_games` t WHERE season >= 2014 UNION ALL # 負けたチームの行を別に作成する SELECT # 特徴 season, # 1994 年 'loss' AS label, # ラベル lose_seed AS seed, # ランキング lose_school_ncaa AS school_ncaa, win_seed AS opponent_seed, # ランキング win_school_ncaa AS opponent_school_ncaa FROM `bigquery-public-data.ncaa_basketball.mbb_historical_tournament_games` t WHERE season >= 2014 UNION ALL # 一般公開データセットに含まれない 2018 トーナメント試合の結果を追加する: SELECT season, label, seed, school_ncaa, opponent_seed, opponent_school_ncaa FROM `data-to-insights.ncaa.2018_tournament_results` ) SELECT o.season, label, # チーム seed, school_ncaa, # 新規ペース指標（バスケットボールのポゼッション） team.pace_rank, team.poss_40min, team.pace_rating, # 新規効率指標（時系列の得点効率） team.efficiency_rank, team.pts_100poss, team.efficiency_rating, # 対戦チーム opponent_seed, opponent_school_ncaa, # 新規ペース指標（バスケットボールのポゼッション） opp.pace_rank AS opp_pace_rank, opp.poss_40min AS opp_poss_40min, opp.pace_rating AS opp_pace_rating, # 新規効率指標（時系列の得点効率） opp.efficiency_rank AS opp_efficiency_rank, opp.pts_100poss AS opp_pts_100poss, opp.efficiency_rating AS opp_efficiency_rating, # 少量の特徴量エンジニアリング（統計の差異を取得） # 新規ペース指標（バスケットボールのポゼッション） 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, # 新規効率指標（時系列の得点効率） 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 モデル用の効果的な特徴を使用する

タスク 13. 新しい特徴をプレビューする

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

次のようなテーブルが表示されます。

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

タスク 14. 選択した指標を解釈する

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

opp_efficiency_rank

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

opp_pace_rank

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

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

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

タスク 15. 新規モデルをトレーニングする

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

CREATE OR REPLACE MODEL `bracketology.ncaa_model_updated` OPTIONS ( model_type='logistic_reg') AS SELECT # 今回は大学名やシードでモデルをトレーニングしない season, label, # 自チームのペース poss_40min, pace_rank, pace_rating, # 対戦チームのペース opp_poss_40min, opp_pace_rank, opp_pace_rating, # ペースの差異 pace_rank_diff, pace_stat_diff, pace_rating_diff, # 自チームの効率性 pts_100poss, efficiency_rank, efficiency_rating, # 対戦チームの効率性 opp_pts_100poss, opp_efficiency_rank, opp_efficiency_rating, # 効率性の差異 eff_rank_diff, eff_stat_diff, eff_rating_diff FROM `bracketology.training_new_features` # 2014 - 2017 をトレーニングして 2018 を予測する WHERE season BETWEEN 2014 AND 2017 # SQL の BETWEEN は境界値を含む

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

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

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

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

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

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

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

[進行状況を確認] をクリックして、目標に沿って進んでいることを確認します。 新しいモデルをトレーニングして、評価を行う

タスク 17. モデルが学習した内容を調査する

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

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」年に関する魔法のような要因は存在していません。

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

タスク 18. 予測する

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

CREATE OR REPLACE TABLE `bracketology.ncaa_2018_predictions` AS # 参考までに他のデータ列を再度追加 SELECT * FROM ML.PREDICT(MODEL `bracketology.ncaa_model_updated`, ( SELECT * #（すでにモデルのトレーニングで使用した）すべての行を組み込む FROM `bracketology.training_new_features` WHERE season = 2018 ))

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

[進行状況を確認] をクリックして、目標に沿って進んでいることを確認します。 クエリを実行して、テーブル ncaa_2018_predictions を作成する

タスク 19. 予測分析:

正しい試合結果が判明しているので、新しいテスト データセットを使用した予測でモデルが間違えた箇所を確認できます。

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

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

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

タスク 20. 2018 年 3 月の番狂わせの原因について

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

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, # モデルの予想 n.label, # 実際の結果 ROUND(p.prob,2) AS probability, season, # 自チーム seed, school_ncaa, pace_rank, efficiency_rank, # 対戦チーム 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 # モデルが間違えた AND p.prob > .75 # 75% を超える信頼度 ORDER BY prob DESC

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

大番狂わせは前のモデルで検出されたものと同じ UMBC 対バージニア大の試合でした。2018 年が全体的にどれほど大番狂わせの多い年だったかについては、「Has This Been the “Maddest” March?」の記事で詳細をご確認ください。2019 年も大荒れが予想されるでしょうか。

タスク 21. モデル パフォーマンスの比較

単純なモデル（シードの比較）が間違えたにもかかわらず、高度なモデルでは正しかった箇所はどこでしょうか。

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

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, # モデルの予想 n.label, # 実際の結果 ROUND(p.prob,2) AS probability, season, # 自チーム seed, school_ncaa, pace_rank, efficiency_rank, # 対戦チーム 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 # モデルは正しく理解していた AND p.prob >= .55 # 55% 以上の信頼度 AND (CAST(opponent_seed AS INT64) - CAST(seed AS INT64)) > 2 # シードの違いの強度 ORDER BY (CAST(opponent_seed AS INT64) - CAST(seed AS INT64)) DESC

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

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

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

タスク 22. 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 # 自チームとは対戦できない 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 # 自チームとは対戦できない WHERE team.school_ncaa <> opp.school_ncaa ) , add_in_2018_season_stats AS ( SELECT team_seeds_all_possible_games.*, # 各チームの 2018 のレギュラー シーズンの特徴を提供する (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 ) # 予測で使用する 2019 年のデータの準備 SELECT label, 2019 AS season, # 2018～2019 年トーナメント シーズン # 自チーム seed, school_ncaa, # 新規ペース指標（バスケットボールのポゼッション） team.pace_rank, team.poss_40min, team.pace_rating, # 新規効率指標（時系列の得点効率） team.efficiency_rank, team.pts_100poss, team.efficiency_rating, # 対戦チーム opponent_seed, opponent_school_ncaa, # 新規ペース指標（バスケットボールのポゼッション） opp.pace_rank AS opp_pace_rank, opp.poss_40min AS opp_poss_40min, opp.pace_rating AS opp_pace_rating, # 新規効率指標（時系列の得点効率） opp.efficiency_rank AS opp_efficiency_rank, opp.pts_100poss AS opp_pts_100poss, opp.efficiency_rating AS opp_efficiency_rating, # 少しの特徴量エンジニアリング（統計の違いを取得） # 新規ペース指標（バスケットボールのポゼッション） 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, # 新規効率指標（時系列の得点効率） 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 # より新しいモデルを使って予測しましょう ML.PREDICT(MODEL `bracketology.ncaa_model_updated`, ( # 2019 年 3 月のトーナメントの試合で予測しましょう 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 フィルタを変更して、対戦組み合わせ表内のさまざまな対戦結果を予測してみましょう。モデルの信頼度を書き留めた上で試合をお楽しみください。

お疲れさまでした

BigQuery ML を使用して NCAA 男子バスケットボール トーナメントの勝利チームを予測しました。

次のステップと詳細情報

• バスケットボールの指標と分析の詳細については、Google Cloud NCAA トーナメントの広告と予測を裏付けるチームの追加の分析をご覧ください。
• 以下のラボをご覧ください。
  • BigQuery を使用して NCAA Data を調べる

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

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

マニュアルの最終更新日: 2025 年 1 月 23 日

ラボの最終テスト日: 2025 年 1 月 23 日

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