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（PlayerRank：基于机器学习方法的足球比赛数据驱动型表现评估与球员排名）。《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 | "<用户名>"}}}

   您也可以在“实验详细信息”窗格中找到“用户名”。

4. 点击下一步。

5. 复制下面的密码，然后将其粘贴到欢迎对话框中。

   {{{user_0.password | "<密码>"}}}

   您也可以在“实验详细信息”窗格中找到“密码”。

6. 点击下一步。

   重要提示：您必须使用实验提供的凭据。请勿使用您的 Google Cloud 账号凭据。 注意：在本实验中使用您自己的 Google Cloud 账号可能会产生额外费用。

7. 继续在后续页面中点击以完成相应操作：

   • 接受条款及条件。
   • 由于这是临时账号，请勿添加账号恢复选项或双重验证。
   • 请勿注册免费试用。

片刻之后，系统会在此标签页中打开 Google Cloud 控制台。

注意：如需访问 Google Cloud 产品和服务，请点击导航菜单，或在搜索字段中输入服务或产品的名称。

任务 1. 打开 BigQuery

通过 BigQuery 控制台界面，您可以查询表（包括由 BigQuery 提供的公共数据集）。

1. 进入 Cloud 控制台后，从导航菜单中选择 BigQuery：

您会看到欢迎在 Cloud 控制台中使用 BigQuery 消息框，其中提供了指向快速入门指南和版本说明的链接。

2. 点击完成。

BigQuery 控制台即会打开。

注意：创建数据集和表的流程已在 BigQuery 足球数据注入实验中讲解过。在本实验中，重点是学习如何查询信息。

表创建完毕后，界面中显示的内容大致如下：

在本部分中，我们使用 BigQuery 界面来访问控制台。在控制台中，可以采用一种便捷的方式向数据集添加信息。BigQuery 使用表以结构化的方式表示数据。

在下一部分中，您将详细了解如何创建更复杂的查询。

任务 2. 分析嵌套的足球事件数据

在本部分中，您将运行一些查询，以便更好地控制足球事件数据。这些查询结合使用了 JOIN 与 BigQuery 的数组功能。

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 个属性，即 2 个分别表示起始位置和结束位置的 (x, y) 对。因此，可以计算 x 和 y 坐标差，然后使用足球场的平均尺寸（根据 Wikipedia，为 105 x 68 米；没有标准球场尺寸）和二维距离公式转换为估计的米数，这样就能计算出传球距离。

1. 在查询编辑器中，点击创建 SQL 查询。
2. 将以下查询添加到查询编辑器中：

WITH Passes AS ( SELECT *, /* 1801 是 tags2name 表中的已知标记，表示“准确” */ (1801 IN UNNEST(tags.id)) AS accuratePass, (CASE WHEN ARRAY_LENGTH(positions) != 2 THEN NULL ELSE /* 先使用 105x68 这一“平均”球场尺寸将基于 0-100 (x,y) 坐标的距离转换成绝对位置， 然后再用于二维距离计算 */ 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 米（埃瓦尔）。

成功传球的平均传球距离也表现出类似的离散度，但各球队的这一数据值略微更接近中值。

点击检查我的进度，核查是否完成了以下目标：

检查查询是否已运行

在本部分中，您使用 BigQuery 确定了各球队的传球次数和平均传球距离。为了做到这一点，您使用了数组处理功能来提取单个字段中的重复值，然后计算了每次传球的起点与终点之间的距离。

在下一部分中，您将学习如何取消嵌套其他坐标数据，以生成有关射门距离的信息。

任务 4. 分析射门距离

在本部分中，您将创建一个新查询来分析射门距离。

射门距离对进球概率有何影响？

要回答这个问题，需采用与上一部分类似的流程。对于射门，要用到 events 表中 positions 字段中的 (x, y) 值。

注意：根据前面的查询，足球场的大致尺寸与 x 坐标和 y 坐标距离一起用作距离公式的输入。

1. 在查询编辑器中，点击创建 SQL 查询。
2. 复制以下查询并将其粘贴到查询编辑器中：

WITH Shots AS ( SELECT *, /* 101 是 goals 表中的已知标记，表示“进球” */ (101 IN UNNEST(tags.id)) AS isGoal, /* 先使用 105x68 这一“平均”球场尺寸将基于 0-100 (x,y) 坐标的距离转换成绝对位置， 然后再用于二维距离计算 */ 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 /* 同时包括常规进攻射门和任意球射门（包括点球）*/ 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 是 goals 表中的已知标记，表示“进球” */ (101 IN UNNEST(tags.id)) AS isGoal, /* 先使用 105x68 这一“平均”球场尺寸将基于 0-100 (x,y) 坐标的距离转换成绝对位置， 然后再用于各种距离计算； LEAST 用于将射门位置限制在球场上的 (x, y) 范围内（即没有精确的 100 值）*/ 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 /* 同时包括常规进攻射门和任意球射门（包括点球）*/ eventName = 'Shot' OR (eventName = 'Free Kick' AND subEventName IN ('Free kick shot', 'Penalty')) ), ShotsWithAngle AS ( SELECT Shots.*, /* 使用余弦定理，根据射门位置计算从射门位置到球门的‘开放’角度， 已知球门宽度为 7.32 米，球门位于球场末端坐标 (105, 34) 的中间位置 */ SAFE.ACOS( SAFE_DIVIDE( ( /* 射门位置与其中一根门柱之间距离（单位：米）的平方 */ (POW(105 - shotXAbs, 2) + POW(34 + (7.32/2) - shotYAbs, 2)) + /* 射门位置与另一根门柱之间距离（单位：米）的平方 */ (POW(105 - shotXAbs, 2) + POW(34 - (7.32/2) - shotYAbs, 2)) - /* 球门宽度（单位：米）的平方 */ POW(7.32, 2) ), (2 * /* 射门位置与其中一根门柱之间的距离（单位：米）*/ SQRT(POW(105 - shotXAbs, 2) + POW(34 + 7.32/2 - shotYAbs, 2)) * /* 射门位置与另一根门柱之间的距离（单位：米）*/ SQRT(POW(105 - shotXAbs, 2) + POW(34 - 7.32/2 - shotYAbs, 2)) ) ) /* 将弧度转换为角度 */ ) * 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° 时，成功率出现了一个小高峰，因为这是点球最常见的射门角度（同样的道理，点球本身就比大多数其他射门更具优势），在这两个角度的射门中点球占了很大比例。

点击检查我的进度，核查是否完成了以下目标：

检查查询是否已运行

下一部分将测试您对本实验所学内容的掌握情况。

任务 6. 随堂测验

通过完成关于本实验所涵盖主题的简短测验，检验您对 BigQuery 的掌握情况。

恭喜！

您已经踏上了探索体育数据科学的道路！您成功使用 BigQuery 分析了平均传球距离，以及射门距离和射门角度与射门成功率之间的关系。所用的查询使用了相互关联的交叉联接以及 BigQuery 的数组与三角函数，来查看嵌套数据的内容并进行所需的距离和角度计算，以便按要求分析数据。

Google Cloud 培训和认证

…可帮助您充分利用 Google Cloud 技术。我们的课程会讲解各项技能与最佳实践，可帮助您迅速上手使用并继续学习更深入的知识。我们提供从基础到高级的全方位培训，并有点播、直播和虚拟三种方式选择，让您可以按照自己的日程安排学习时间。各项认证可以帮助您核实并证明您在 Google Cloud 技术方面的技能与专业知识。

上次更新手册的时间：2024 年 1 月 25 日

上次测试实验的时间：2024 年 1 月 25 日

版权所有 2025 Google LLC 保留所有权利。Google 和 Google 徽标是 Google LLC 的商标。其他所有公司名和产品名可能是其各自相关公司的商标。