GSP850

概要

BigQuery を使用すると、より高度なデータ分析を実行できます。このラボでは、サッカーのイベントデータを分析して、データセットから実用的な分析情報を得ます。

このラボで使用するデータは、次のソースから取得されます。

• Pappalardo 他（2019 年）A public data set of spatio-temporal match events in soccer competitions、Nature Scientific Data 6:236、https://www.nature.com/articles/s41597-019-0247-7
• Pappalardo 他（2019 年）PlayerRank: Data-driven Performance Evaluation and Player Ranking in Soccer via a Machine Learning Approach. 　　ACM Transactions on Intelligent Systems and Technologies（TIST）10、5、記事 59（2019 年 9 月）、27 ページ。DOI: https://doi.org/10.1145/3343172

目標

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

• さまざまな BigQuery 機能を使用してサッカーのイベントデータを分析する
• BigQuery テーブルのネストされたデータを操作するクエリを作成して実行する

設定と要件

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

こちらの説明をお読みください。ラボには時間制限があり、一時停止することはできません。タイマーは、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 を開く

BigQuery コンソールには、テーブルに対してクエリを実行するためのインターフェースが用意されており、BigQuery が提供する一般公開データセットも利用できます。

1. Cloud Console のナビゲーション メニューで、[BigQuery] を選択します。

[Cloud Console の BigQuery へようこそ] メッセージ ボックスが開きます。このメッセージ ボックスには、クイックスタート ガイドとリリースノートへのリンクが表示されます。

2. [完了] をクリックします。

BigQuery コンソールが開きます。

注: データセットとテーブルを作成するプロセスについては、BigQuery サッカー データの取り込みラボで説明しています。このラボでは、情報をクエリする方法を学習することに重点を置きます。

テーブルが作成されると、以下のように表示されます。

このセクションでは、BigQuery インターフェースを使用してコンソールにアクセスしました。コンソールは、データセットに情報を追加する便利な方法を提供します。BigQuery では、テーブルを使用してデータを構造化された方法で表現します。

次のセクションでは、より複雑なクエリの作成について詳しく説明します。

タスク 2. ネストされたサッカー イベント データを分析する

このセクションでは、BigQuery の配列機能を使用して JOIN を使用するクエリを実行し、サッカーのイベントデータをより適切に制御できるようにします。

1. クエリエディタで、[SQL クエリを作成] をクリックします。
2. 次のクエリをコピーして、クエリの [エディタ] に貼り付けます。

SELECT Events.playerId, (Players.firstName || ' ' || Players.lastName) AS playerName, SUM(IF(Tags2Name.Label = 'assist', 1, 0)) AS numAssists FROM `soccer.events` Events, Events.tags Tags LEFT JOIN `soccer.tags2name` Tags2Name ON Tags.id = Tags2Name.Tag LEFT JOIN `soccer.players` Players ON Events.playerId = Players.wyId GROUP BY playerId, playerName ORDER BY numAssists DESC

アシストは events テーブルの個別のスカラー フィールドとしてマークされていないため、tags フィールドの「内部」を確認する必要があります。

これは、events テーブルと tags フィールドの間で相関クロス結合を使用し（FROM 句の「,」は暗黙的な結合を表します）、イベントごとに 1 行ではなく、タグごとに 1 行を作成することで行われます。アシストに対応するタグ ID は tags2name テーブルから取得され、そのタグの出現回数がプレーヤーごとにカウントされます。また、players テーブルはプレーヤー ID からプレーヤー名を取得します。

3. [実行] をクリックします。結果はクエリ ウィンドウの下に表示されます。

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

クエリが実行されたことを確認する

このセクションでは、BigQuery でより複雑なクエリを作成しました。BigQuery で結合を実行し、配列を活用することで、データを効果的に集計できます。

次のセクションでは、BigQuery でネストと配列を使用する方法を学びます。

タスク 3. チーム別の平均パス距離を計算する

このセクションでは、サッカーのイベントデータでネストされたフィールドと、BigQuery の配列機能および STRUCT データ型を使用して、いくつかの興味深い質問に答えるクエリを実行します。

クラブチームのパスの平均距離（全体と正確なパスの両方）はどの程度異なりますか？

この質問に答えるには、events テーブルの positions フィールドを調べます。このデータを見ると、これはイベントごとに 1 つ以上の（x、y）ペアを含む繰り返しフィールドであることがわかります。このデータを提供したサッカー業界の大手データ企業 Wyscout によると、これらはイベントに関連する起点と（該当する場合）終点の位置を表しており、攻撃チームの視点から見たフィールドの割合を 0 ～ 100 のスケールで表しています。

以下のスクリーンショットは、いくつかのイベント例について、さまざまなイベントタイプに対応する位置を示しています。

データから、パスには開始位置と終了位置を表す 2 つの属性（x、y）のペアがあることがわかります。そのため、パス距離は、x 座標と y 座標の差を計算し、サッカー場の平均的な寸法（105 x 68、Wikipedia より。標準的なフィールド サイズはありません）と2 次元距離の公式を使用して推定メートルに変換することで計算できます。

1. クエリエディタで、[SQL クエリを作成] をクリックします。
2. 次のクエリをクエリの [エディタ] に追加します。

WITH Passes AS ( SELECT *, /* 1801 is known Tag for 'accurate' from tags2name table */ (1801 IN UNNEST(tags.id)) AS accuratePass, (CASE WHEN ARRAY_LENGTH(positions) != 2 THEN NULL ELSE /* Translate 0-100 (x,y) coordinate-based distances to absolute positions using "average" field dimensions of 105x68 before combining in 2D dist calc */ SQRT( POW( (positions[ORDINAL(2)].x - positions[ORDINAL(1)].x) * 105/100, 2) + POW( (positions[ORDINAL(2)].y - positions[ORDINAL(1)].y) * 68/100, 2) ) END) AS passDistance FROM `soccer.events` WHERE eventName = 'Pass' ) SELECT Passes.teamId, Teams.name AS team, Teams.area.name AS teamArea, COUNT(Passes.Id) AS numPasses, AVG(Passes.passDistance) AS avgPassDistance, SAFE_DIVIDE( SUM(IF(Passes.accuratePass, Passes.passDistance, 0)), SUM(IF(Passes.accuratePass, 1, 0)) ) AS avgAccuratePassDistance FROM Passes LEFT JOIN `soccer.teams` Teams ON Passes.teamId = Teams.wyId WHERE Teams.type = 'club' GROUP BY teamId, team, teamArea ORDER BY avgPassDistance

最初の WITH 句のコードは、events テーブルをフィルタして合格のみを渡し、タグフィールドの「内部」を調べることで accuratePass フィールドを追加します。

パスの距離は、ORDINAL を使用して初期座標と最終座標（x, y）を抽出し、上記のコンセプトと式を適用して計算されます。最後の SELECT ステートメントは、すべてのパスと正確なパスのみの平均パス距離を含め、パスデータをチームレベルに集計します（クラブチームのみにフィルタリング）。

3. [実行] をクリックします。結果はクエリ ウィンドウの下に表示されます。

注: さまざまなチームの数千回にわたるパスの平均距離には差があり、最も短い平均は 18 メートル未満（ナポリと PSG）、最も長い平均は 23 メートル超（エイバル）です。

平均正確パス距離も同様のばらつきを示していますが、チーム全体で値が若干圧縮されています。

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

クエリが実行されたことを確認する

このセクションでは、BigQuery を使用して、チームごとのパス数とパスの平均距離を特定しました。これを実現するために、配列処理機能を使用して、単一のフィールドで繰り返される値を抽出し、各パスの開始点と終了点の間の距離を計算しました。

次のセクションでは、他の座標データをネスト解除して、ショット距離に関する情報を生成する方法を学びます。

タスク 4. ショット距離を分析する

このセクションでは、ショットの距離を分析するための新しいクエリを作成します。

シュートの距離は、得点できる可能性にどのような影響を与えますか？

この質問に答えるには、前のセクションと同様のプロセスを使用します。シュートについては、events テーブルの positions フィールドから（x、y）の値を使用します。

注: 前のクエリのとおり、サッカー場の概算の寸法が使用され、x 座標と y 座標の距離が距離の公式への入力として使用されます。

1. クエリエディタで、[SQL クエリを作成] をクリックします。
2. 次のクエリをコピーして、クエリの [エディタ] に貼り付けます。

WITH Shots AS ( SELECT *, /* 101 is known Tag for 'goals' from goals table */ (101 IN UNNEST(tags.id)) AS isGoal, /* Translate 0-100 (x,y) coordinate-based distances to absolute positions using "average" field dimensions of 105x68 before combining in 2D dist calc */ SQRT( POW( (100 - positions[ORDINAL(1)].x) * 105/100, 2) + POW( (50 - positions[ORDINAL(1)].y) * 68/100, 2) ) AS shotDistance FROM `soccer.events` WHERE /* Includes both "open play" & free kick shots (including penalties) */ eventName = 'Shot' OR (eventName = 'Free Kick' AND subEventName IN ('Free kick shot', 'Penalty')) ) SELECT ROUND(shotDistance, 0) AS ShotDistRound0, COUNT(*) AS numShots, SUM(IF(isGoal, 1, 0)) AS numGoals, AVG(IF(isGoal, 1, 0)) AS goalPct FROM Shots WHERE shotDistance <= 50 GROUP BY ShotDistRound0 ORDER BY ShotDistRound0

最初の WITH 句では、events テーブルをシュートのみにフィルタし、tags フィールドを「内部」から参照して isGoal フィールドを追加します。また、前のセクションでパスの距離を処理したのと同じ方法でシュートの距離を計算しますが、終了位置としてゴールマウスの中間点（100, 50）を使用します。

最後の SELECT ステートメントでは、シュート数、ゴール数、シュートに対するゴールの割合を、最も近いメートル単位に丸めて集計します。

3. [実行] をクリックします。結果はクエリ ウィンドウの下に表示されます。

予想どおり、近距離からのシュートはゴール率がはるかに高く、2 ～ 3 メートルでは 70% 近くの成功率ですが、8 メートルでは 25% 未満に低下し、25 メートル以上まで着実に低下しています。

結果の可視化を作成する

データを可視化すると、理解しやすくなり、傾向を把握しやすくなります。

1. [クエリ結果] セクションで [グラフ] をクリックします。
2. [グラフの種類] で [散布図] を選択します。
3. Google スプレッドシートの散布図作成機能を使用して、以下のようなグラフを作成します。

11 ～ 12 メートルでは成功率がわずかに上昇していますが、これは、その距離からのシュートの大部分をペナルティ キック（他のほとんどのシュートよりも成功率がはるかに高い）が占めているという事実で説明できるでしょう。

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

クエリが実行されたことを確認する

このセクションでは、BigQuery を使用して、シュート距離とゴール成功率のビューを確立しました。この分析から、シュートの距離に基づいてゴールが決まる可能性をより深く理解できます。

次のセクションでは、同様の分析を行って、シュートの角度がシュートの成功に与える影響を確認します。

タスク 5. ショットの角度を分析

このセクションでは、前のクエリを変更して、角度がショットに与える影響を確認します。

この場合、計算される角度は、下の図（画像提供: Ian Dragulet）のように、シュートの位置とゴールラインによって作られる角度です。

角度が大きくなるのは、目標に近く、中央にいる場合です。そのため、これは上記で実行した距離の計算とある程度相関しています。シュート角度の計算では、（x, y）データに対して BigQuery の三角関数を使用します。

1. クエリエディタで、[SQL クエリを作成] をクリックします。
2. 次のクエリをクエリの [エディタ] に追加します。

WITH Shots AS ( SELECT *, /* 101 is known Tag for 'goals' from goals table */ (101 IN UNNEST(tags.id)) AS isGoal, /* Translate 0-100 (x,y) coordinates to absolute positions using "average" field dimensions of 105x68 before using in various distance calcs; LEAST used to cap shot locations to on-field (x, y) (i.e. no exact 100s) */ LEAST(positions[ORDINAL(1)].x, 99.99999) * 105/100 AS shotXAbs, LEAST(positions[ORDINAL(1)].y, 99.99999) * 68/100 AS shotYAbs FROM `soccer.events` WHERE /* Includes both "open play" & free kick shots (including penalties) */ eventName = 'Shot' OR (eventName = 'Free Kick' AND subEventName IN ('Free kick shot', 'Penalty')) ), ShotsWithAngle AS ( SELECT Shots.*, /* Law of cosines to get 'open' angle from shot location to goal, given that goal opening is 7.32m, placed midway up at field end of (105, 34) */ SAFE.ACOS( SAFE_DIVIDE( ( /* Squared distance between shot and 1 post, in meters */ (POW(105 - shotXAbs, 2) + POW(34 + (7.32/2) - shotYAbs, 2)) + /* Squared distance between shot and other post, in meters */ (POW(105 - shotXAbs, 2) + POW(34 - (7.32/2) - shotYAbs, 2)) - /* Squared length of goal opening, in meters */ POW(7.32, 2) ), (2 * /* Distance between shot and 1 post, in meters */ SQRT(POW(105 - shotXAbs, 2) + POW(34 + 7.32/2 - shotYAbs, 2)) * /* Distance between shot and other post, in meters */ SQRT(POW(105 - shotXAbs, 2) + POW(34 - 7.32/2 - shotYAbs, 2)) ) ) /* Translate radians to degrees */ ) * 180 / ACOS(-1) AS shotAngle FROM Shots ) SELECT ROUND(shotAngle, 0) AS ShotAngleRound0, COUNT(*) AS numShots, SUM(IF(isGoal, 1, 0)) AS numGoals, AVG(IF(isGoal, 1, 0)) AS goalPct FROM ShotsWithAngle GROUP BY ShotAngleRound0 ORDER BY ShotAngleRound0

このクエリは、上記のシュート距離のクエリと似ていますが、最初の WITH 句でシュートの座標を抽出（角度計算で複数回必要になる場合に簡略化するため）し、2 番目の WITH 句で 余弦定理を使用したより詳細な三角関数計算でシュートの角度を取得しています。

最後の SELECT ステートメントは、ショット角度を最も近い度数に丸めて集計します。

3. [実行] をクリックします。結果はクエリ ウィンドウの下に表示されます。

シュート角度は一般的に、ゴール成功率と正の相関関係があるようです。20° 未満の角度では成功率が 1 桁ですが、角度が広くなるほど成功率は高くなります（60° を超えるとサンプルサイズが比較的小さくなります）。

[クエリ結果] セクションの [グラフ] をクリックし、グラフの種類として [散布図] を選択すると、以下のような全体の傾向を可視化できます。

このグラフから、シュート角度と成功率の関係は、約 100° までは比較的線形であることがわかります。また、最も広い角度はゴールに近いシュートでのみ可能であるため、この関係の一部は、上で示した距離の影響と相関しています。成功率は 35 度と 38 度でわずかに上昇しています。これは、ペナルティ キックで最も一般的なシュート角度であり（他のシュートよりもはるかに高い割合）、この範囲からのシュートの大部分を占めているためです。

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

クエリが実行されたことを確認する

次のセクションでは、このラボで学んだ内容の理解度をテストします。

タスク 6. 理解度テスト

このラボで扱ったトピックに関する短い問題に答えて、BigQuery の理解度をテストしてください。

お疲れさまでした

スポーツ データ サイエンスの理解に一歩近づきました。BigQuery を使用して、パスの平均距離と、シュートの距離と角度が成功に与える関係を分析できました。使用されたクエリでは、相関クロス結合と BigQuery の配列関数および三角関数を使用して、ネストされたデータを調べ、要求されたとおりにデータを分析するために必要な距離と角度の計算を実行しました。

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

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

マニュアルの最終更新日: 2024 年 1 月 25 日

ラボの最終テスト日: 2024 年 1 月 25 日

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