BigQuery足球数据中射门距离计算的ORDINAL与坐标疑问

背景 #

正在学习Google Skill Boost的BigQuery课程，通过足球数据集分析射门距离对进球概率的影响，对以下射门距离计算代码存在疑问：

SQRT(
    POW(
      (100 - positions[ORDINAL(1)].x) * 105/100,
      2) +
    POW(
      (50 - positions[ORDINAL(1)].y) * 68/100,
      2)
     ) AS shotDistance

已知代码基于勾股定理d=√((x₂-x₁)²+(y₂-y₁)²)，但有两点疑问：

1. 为何使用ORDINAL(1)？在该场景下如何作用？
2. 为何分别用100减去x值、50减去y值？

注：positions为重复字段，嵌套x、y int64类型字段，x、y取值1-100，代表事件发生位置占球场的百分比。

问题1：ORDINAL(1)的作用 #

因为positions是BigQuery中的重复字段（数组类型），一个事件可能包含多个位置记录（比如传球的起点和终点），但对于射门事件，我们只需要射门发生的位置——也就是数组的第一个元素。

BigQuery中访问数组元素有两种索引方式：

• [OFFSET(n)]：从0开始计数，OFFSET(0)对应第一个元素
• [ORDINAL(n)]：从1开始计数，ORDINAL(1)对应第一个元素

这里用ORDINAL(1)就是明确取positions数组里的第一个位置数据，也就是射门发生的坐标点。

问题2：100-x和50-y的原因 #

这是为了将射门位置坐标转换为到球门中心的直线距离：

1. 数据集的坐标规则：x取值1-100代表从球场一端到另一端的长度占比，球门位于x=100的一端；y取值1-100代表球场宽度的占比，球门中心的y坐标是50（宽度中点）。
2. 100 - positions[ORDINAL(1)].x：计算射门位置到球门所在端的水平距离占比，乘以105/100是将百分比转换为实际长度（标准球场长105米）。
3. 50 - positions[ORDINAL(1)].y：计算射门位置到球门中心的垂直距离占比，乘以68/100转换为实际宽度（标准球场宽68米）。
4. 最终通过勾股定理计算出的就是射门点到球门中心的直线距离。

完整参考代码：

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

内容的提问来源于stack exchange，提问作者ppp147