GSP850

總覽

BigQuery 可用於執行較複雜的資料分析。在本實驗室，您將分析足球賽事資料，從資料集取得實用洞察資訊。

本實驗室使用以下資料來源：

• Pappalardo et al., (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 et al. (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, Article 59 (September 2019), 27 pages. DOI: https://doi.org/10.1145/3343172

目標

本實驗室的學習內容包括：

• 使用各種 BigQuery 功能分析足球賽事資料
• 撰寫及執行查詢，處理 BigQuery 資料表中的巢狀資料

設定和需求

瞭解以下事項後，再點選「Start Lab」按鈕

請詳閱以下操作說明。實驗室活動會計時，且中途無法暫停。點選「Start Lab」後就會開始計時，顯示可使用 Google Cloud 資源的時間。

您將在真正的雲端環境完成實作實驗室活動，而不是模擬或示範環境。為此，我們會提供新的暫時憑證，供您在實驗室活動期間登入及存取 Google Cloud。

為了順利完成這個實驗室，請先確認：

• 可以使用標準的網際網路瀏覽器 (Chrome 瀏覽器為佳)。

注意事項：請使用無痕模式 (建議選項) 或私密瀏覽視窗執行此實驗室，這可以防止個人帳戶和學員帳戶之間的衝突，避免個人帳戶產生額外費用。

• 是時候完成實驗室活動了！別忘了，活動一旦開始將無法暫停。

注意事項：務必使用實驗室專用的學員帳戶。如果使用其他 Google Cloud 帳戶，可能會產生額外費用。

如何開始研究室及登入 Google Cloud 控制台

1. 點選「Start Lab」按鈕。如果實驗室會產生費用，畫面上會出現選擇付款方式的對話方塊。左側的「Lab Details」窗格會顯示下列項目：

   • 「Open Google Cloud console」按鈕
   • 剩餘時間
   • 必須在這個研究室中使用的臨時憑證
   • 完成這個實驗室所需的其他資訊 (如有)

2. 點選「Open Google Cloud console」；如果使用 Chrome 瀏覽器，也能按一下滑鼠右鍵，選取「在無痕視窗中開啟連結」。

   接著，實驗室會啟動相關資源，並開啟另一個分頁，顯示「登入」頁面。

   提示：您可以在不同的視窗中並排開啟分頁。

   注意：如果頁面中顯示「選擇帳戶」對話方塊，請點選「使用其他帳戶」。

3. 如有必要，請將下方的 Username 貼到「登入」對話方塊。

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

   您也可以在「Lab Details」窗格找到 Username。

4. 點選「下一步」。

5. 複製下方的 Password，並貼到「歡迎使用」對話方塊。

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

   您也可以在「Lab Details」窗格找到 Password。

6. 點選「下一步」。

   重要事項：請務必使用實驗室提供的憑證，而非自己的 Google Cloud 帳戶憑證。 注意：如果使用自己的 Google Cloud 帳戶來進行這個實驗室，可能會產生額外費用。

7. 按過後續的所有頁面：

   • 接受條款及細則。
   • 由於這是臨時帳戶，請勿新增救援選項或雙重驗證機制。
   • 請勿申請免費試用。

Google Cloud 控制台稍後會在這個分頁開啟。

注意：如要使用 Google Cloud 產品和服務，請點選「導覽選單」，或在「搜尋」欄位輸入服務或產品名稱。

工作 1：開啟 BigQuery

BigQuery 控制台包含資料表查詢介面，可用於查詢 BigQuery 提供的公開資料集。

1. 在 Cloud 控制台的「導覽選單」，選取「BigQuery」：

接著，畫面會顯示「歡迎使用 Cloud 控制台中的 BigQuery」訊息方塊，列出快速入門導覽課程指南的連結和版本資訊。

2. 點選「完成」。

BigQuery 控制台會隨即開啟。

注意：如需建立資料集和資料表的流程，請參閱 BigQuery Soccer Data Ingestion 實驗室。本實驗室的重點是學習如何查詢資訊。

建立資料表後，會出現類似以下內容的畫面：

在這個部分，我們使用 BigQuery 介面存取控制台。您可以透過控制台輕鬆將資訊新增至資料集。BigQuery 以表格呈現資料，因此條理分明。

在下一部分，您將進一步瞭解如何建立更複雜的查詢。

工作 2：分析足球賽事巢狀資料

在這個部分，您使用 BigQuery 的陣列功能與 JOIN 執行一些查詢，進一步控管足球賽事資料。

1. 在查詢編輯器，點選「SQL query」。
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 欄位的「內容」。

做法是使用 correlated cross join，在 events 資料表和 tags 欄位之間建立關聯 (FROM 子句中的「,」代表隱含的 join)，為每個事件的每個標記建立 1 個資料列 (而不是為每個事件建立 1 個資料列)。助攻對應的標記 ID 可在 tags2name 資料表找到，該標記的出現次數會依球員計算，而 players 資料表則會根據 ID 取得球員名稱。

3. 按一下「執行」。查詢結果會顯示在查詢視窗下方。

點選「Check my progress」，確認目標已達成

確認查詢已執行

在這個部分，我們用 BigQuery 建立了更複雜的查詢。以 BigQuery 執行 join 作業並運用陣列，可高效彙整資料。

在下一部分，您將瞭解如何在 BigQuery 使用巢狀結構和陣列。

工作 3：計算各隊的平均傳球距離

在這個部分，您將執行一些查詢，使用足球賽事資料的巢狀欄位、BigQuery 的陣列功能和 STRUCT 資料類型，回答一些有趣的問題。

俱樂部球隊的平均傳球距離 (整體和準確傳球) 有多大差異？

如要回答這個問題，請細查 events 資料表中的 positions 欄位。您會發現這是重複欄位，每個事件包含至少 1 個 (x, y) 配對。根據提供這項資料的足球產業資料龍頭 Wyscout，這些數字代表事件的起點和終點 (如有)，以 0 到 100 的比例表示，代表進攻球隊視角下的球場百分比。

下方螢幕截圖顯示幾個範例事件中，不同事件類型對應的位置。

從資料可看出，傳球有 2 個屬性 (x, y) 配對，分別代表傳球的起點和終點。因此，傳球距離可透過計算 x 和 y 座標的差異表示，然後使用足球場的平均面積 (根據 Wikipedia 為長 105 公尺、寬 68 公尺；球場沒有標準面積) 和二維距離公式換算為預估公尺數。

1. 在查詢編輯器，點選「SQL query」。
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 資料表，只傳遞傳球資料，並查詢 tags 欄位的「內容」來新增 accuratePass 欄位。

傳球距離的計算方式是使用 ORDINAL 擷取起點與終點 (x, y) 座標，並套用上述概念和公式。最後的 SELECT 陳述式會彙整傳球資料，並以球隊層級呈現 (只篩選俱樂部球隊)，包括所有傳球的平均距離，以及準確傳球的平均距離。

3. 按一下「執行」。查詢結果會顯示在查詢視窗下方。

注意：在數千次傳球中，各隊的平均傳球距離略有不同，從最短不到 18 公尺 (拿坡里和巴黎聖日耳曼)，到最長超過 23 公尺 (埃瓦爾)。

平均準確傳球距離的分布情況也類似，但各隊的數值略為集中。

點選「Check my progress」，確認目標已達成

確認查詢已執行

在這個部分，我們使用 BigQuery 得出各隊的傳球次數和平均傳球距離。為完成這項工作，您需要使用陣列功能，從單一欄位擷取重複值，然後計算每次傳球起點和終點之間的距離。

在下一部分，您將瞭解如何展開其他座標巢狀資料，以生成投籃距離資訊。

工作 4：分析射門距離

在這個部分，您將建立新查詢來分析射門距離。

射門距離對進球機率有何影響？

如要回答這個問題，請使用與上一部分類似的程序。請使用 events 資料表 positions 欄位的 (x, y) 值表示射門距離。

注意：如同先前的查詢，距離公式會代入足球場的概略面積，以及表示射門距離的 x 座標和 y 座標。

1. 在查詢編輯器，點選「SQL query」。
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 公尺的成功率略有提升，但這可能是因為這個距離的射門中，有很大一部分是罰球 (因形式不同，罰球本就比較容易射門成功)。

點選「Check my progress」，確認目標已達成

確認查詢已執行

在這個部分，我們使用 BigQuery 建立檢視表，比較射門距離與進球率。從這項分析，我們更瞭解射門距離與進球率的關係。

在下一部分，您將進行類似的分析，瞭解射門角度對進球率的影響。

工作 5：分析射門角度

在這個部分，請修改先前的查詢，查看射門角度對進球與否的影響。

這裡計算的角度是射門位置和球門線之間的角度，如下圖所示 (圖片來源：Ian Dragulet)。

越靠近球門與中心，角度就越大，因此這與先前的距離算法有些關聯。計算射門角度時，會使用 BigQuery 的三角函數函式處理 (x, y) 資料。

1. 在查詢編輯器，點選「SQL query」。
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 子句，用於擷取射門座標 (角度計算中需多次使用座標)，而第二個 WITH 子句中則用更詳細的三角函數算法，以餘弦定理取得射門角度。

最後的 SELECT 陳述式會將射門角度四捨五入至最接近的整數，並彙整各項結果。

3. 按一下「執行」。查詢結果會顯示在查詢視窗下方。

射門角度與進球率大致呈正相關，角度小於 20° 時，進球率只有個位數，角度較大時，進球率則高出許多 (超過 60° 時，樣本數相對較少)。

點按「查詢結果」部分的「圖表」，然後選取「散布圖」這個類型，即可用圖表呈現完整趨勢，如下所示。

圖表顯示，射門角度與成功率的關係在 100° 左右之前大致呈線性。複習一下：只有在靠近球門時才會出現最廣的角度，因此這與先前所說的距離效應有關。在 35° 和 38° 時，成功率略有上升，因為這些是罰球最常見的射門角度 (重點複習：罰球的成功率又高出許多)，占該範圍內射門的很大一部分。

點選「Check my progress」，確認目標已達成

確認查詢已執行

在下一部分，請測試您對本實驗室所學內容的理解程度。

工作 6：趣味測驗

完成本實驗室介紹的主題後，請回答簡短測驗，檢驗您對 BigQuery 的瞭解程度。

恭喜！

您已開始瞭解運動資料科學，並成功使用 BigQuery 分析平均傳球距離、射門距離、射門角度與進球的關係。查詢作業使用相關 cross join 和 BigQuery 的陣列與三角函數函式，查詢巢狀資料並計算距離和角度，以按照要求分析資料。

Google Cloud 教育訓練與認證

協助您瞭解如何充分運用 Google Cloud 的技術。我們的課程會介紹專業技能和最佳做法，讓您可以快速掌握要領並持續進修。我們提供從基本到進階等級的訓練課程，並有隨選、線上和虛擬課程等選項，方便您抽空參加。認證可協助您驗證及證明自己在 Google Cloud 技術方面的技能和專業知識。

使用手冊上次更新日期：2024 年 1 月 25 日

實驗室上次測試日期：2024 年 1 月 25 日

Copyright 2025 Google LLC 保留所有權利。Google 和 Google 標誌是 Google LLC 的商標，其他公司和產品名稱則有可能是其關聯公司的商標。