使用 Postgres 破解 NFL 数据（也许会赢得你的幻想选秀）

了解如何使用 NFL 提供的时间序列数据来发现对许多球员表现指标的宝贵见解 - 以及如何应用相同的方法来提高您的梦幻联盟球队、您对比赛的了解或您的观看体验 - 全部使用 PostgreSQL 、标准 SQL 和免费提供的扩展。时间序列数据无处不在，令我们惊讶的是，包括职业体育界。在 Timescale，我们一直在寻找有趣的方式来展示时间序列数据的扩展范围。股票、加密货币、物联网和基础设施指标数据是相对常见且被广泛理解的时间序列数据场景。在任何一天前往 Twitter，搜索 #timeseries 或 #TimescaleDB，您一定会发现有关使用 Prometheus 等工具进行高频交易或大规模可观察性数据的问题。你可以想象当我们遇到 NFL 大数据碗时我们的兴奋，这是一年一度的比赛，鼓励数据科学界使用历史球员位置和比赛数据来创建机器学习模型。 NFL 真的可以访问每场常规赛 NFL 比赛的 18+ 百万行详细比赛数据吗？作为背景，美国国家橄榄球联盟 (NFL) 是美国橄榄球职业体育联盟，NFL 赛季紧随其后的是数以千万计的人，最终以一年一度的超级碗（吸引了 1 亿 + 全球观众，无论是为了比赛）或用于广告）。每场 NFL 比赛都是一系列“比赛”，其中两支球队试图得分并阻止另一支球队得分。每场比赛大约有 200 场比赛，常规赛期间每周最多有 15 场比赛。大量的数据，但没有什么是不可管理的。因此，乍一看，足球比赛指标可能不会立即跳出任何特别的东西。

所有 NFL 球员都配备了 RFID 芯片，用于跟踪球员的位置、速度和各种其他指标，团队使用这些指标来识别趋势、降低风险并持续优化。 NFL 开始跟踪和存储场上每位球员、每次比赛、每场比赛的数据。因此，我们现在可以非常详细地分析一场比赛是如何展开的，不同球员在每次比赛中加速的速度有多快，以及比赛的结果。逐场比赛指标的传统观点是“距离和距离”以及比赛的结果（获得的码数，是否有得分等）。使用 NFL 的数据集，我们能够在整个比赛中以 100 毫秒的间隔挖掘大约 100 个数据点，以查看速度、距离、参与的球员等等。这不是普通的数据。这是时间序列数据。时间序列数据是在时间间隔内收集的一系列数据点，使我们能够跟踪随时间的变化。在 NFL 数据集的情况下，我们有时间序列数据来表示比赛如何变化，包括球员在场上的位置、球的位置、球员在场上的相对加速度等多得多。时间序列数据很快就会出现，有时每秒生成数百万个数据点（阅读更多关于时间序列数据的信息）。由于信息量和信息量巨大，时间序列数据的查询和分析已经很复杂，这就是我们构建 TimescaleDB 的原因，这是一个多节点、PB 级、完全免费的时间序列关系数据库。我们不能错过使用 TimescaleDB 查看 NFL 数据集的机会，探索我们可以更深入地观察球员表现的方法，希望能提供有关下赛季整体球员表现的见解。继续阅读以了解有关 NFL 数据集以及如何开始使用它的更多信息，以及一些示例查询以快速启动您的分析。它们可以帮助您从游戏中获得更多乐趣。如果您想开始使用 NFL 数据，您可以启动一个完全托管的 TimescaleDB 服务：创建一个帐户以免费试用 30 天。本文后面的说明将带您了解如何摄取数据并开始使用它进行分析。

如果您不熟悉时间序列数据，或者只是有一些关于数据集的问题，请加入我们的公共 Slack 社区，在那里您可以找到 Timescale 团队成员和数以千计的时间序列爱好者，我们很乐意为您提供帮助。在过去的几年里，NFL 和 Kaggle 在 NFL 大数据碗上进行了合作。目标是使用历史数据来回答预定类型的问题，通常会生成一个机器学习模型，可以帮助预测常规赛期间某些比赛的结果。尽管 2020/2021 比赛已经结束，但他们提供的上一赛季的样本数据集仍然可供下载和分析。 2020/2021赛季的比赛侧重于传球防守效率；因此，数据集中只有进攻和防守“组织者”的跟踪数据可用。不包括进攻或防守边锋数据。 （您可以阅读有关去年获奖者的更多信息。）出于本博客文章和随附教程的目的，我们将使用 NFL 提供的示例数据。这些数据来自 2018 NFL 赛季，并以 CSV 文件的形式提供，包括比赛特定数据和参与传球“进攻”部分的每个球员的每周跟踪数据。下一季的比赛参与者将可以访问新的每周游戏数据。这些数据本质上也是非常相关的，这意味着 SQL 是开始收集价值的绝佳媒介——不需要 Jupyter notebook、其他数据科学特定语言（如 Python 或 R）或其他工具集。如果您想跟随 - 或重新创建！ - 我们在下面经历的查询，按照我们的教程设置表，摄取数据，并开始在 TimescaleDB 中分析数据。对于那些不熟悉 TimescaleDB 的人来说，它是建立在 PostgreSQL 之上的，所以你会发现我们所有的查询都是标准的 SQL。如果您了解 SQL，您就会知道如何在此处完成所有操作。 （我们提供的一些更高级的查询示例需要我们新的、高级的超函数，它预装在任何 Timescale Forge 实例中。）我们在随附的教程中提供了将数据集摄取到 TimescaleDB 所需的步骤，所以我们赢了不要在这里讨论。

比赛：关于常规赛每场比赛的所有相关数据，包括日期、球队、时间和地点球员：关于每个球员的信息，包括他们效力的球队和他们最初的大学比赛：关于每次传球的丰富数据游戏。有用的字段包括击球、对发生的比赛的描述、混战线和总进攻码数等细节。周 [1-17]：对于本赛季的每一周，NFL 都会提供一个新的 CSV 文件，其中包含每个球员的跟踪数据，每次比赛（此数据的传球次数）。有趣的字段包括每次比赛中每隔几百毫秒的 X/Y 位置数据（相对于足球场）、球员加速度以及所采取路线的“类型”。 （在我们的教程中，这些数据被导入到跟踪表中，总共有近 2000 万行时间序列数据。）除了 NFL 数据集，我们还提供了一些来自 Wikipedia 的额外数据，其中包括每个项目的比赛得分和体育场条件。游戏，您可以将其作为教程的一部分加载。对于其他时间序列数据库，可能很难将您的时间序列数据与您手头可能拥有的任何其他数据结合起来（请参阅我们的 TimescaleDB 与 InfluxDB 比较以供参考）。因为 TimescaleDB 是具有时间序列超能力的 PostgreSQL，它支持 JOINS，因此您想要添加任何额外的关系数据以进行更深入的分析，只需一个 SQL 查询即可。在我们的例子中，我们能够将 NFL 的逐场比赛数据与每个体育场的天气数据结合起来。准备好数据后，NFL 组织者的世界就在您的指尖，让我们开始吧！

年复一年，在 StackOverflow 调查中，我们看到 SQL 被列为最受开发人员欢迎的语言之一。然而，有时我们可能会认为，从关系数据中获得洞察力的唯一方法是使用强大的数据分析工具和语言进行查询、创建数据框并使用专门的回归算法，然后才能做任何富有成效的事情。通常感觉 SQL 仅用于在应用程序中获取和存储数据，我们需要将分析的“繁重工作”留给更成熟的工具。不是这样！ SQL 可以使用其中最好的来处理数据！让我们看第一个简单的例子。对于第一个示例，我们将查询跟踪表（所有 17 周比赛的球员移动数据）并加入比赛表以确定每场比赛每个球员位置的码数。结果可让您快速了解每场比赛中不同位置跑了多少码。您可以稍后使用它来比较特定球员，以了解他们与总码数或多或少的比较情况。 WITH total_position_yards AS ( SELECT sum(dis) position_yards, POSITION, gameid FROM tracking t GROUP BY POSITION, gameid)SELECT avg(position_yards), position, game_dateFROM game gINNER JOIN total_position_yards tpy ON g.game_id = tpy.gameidWHERE POSITION IN (' ','RB','WR','TE')GROUP BY game_date, POSITION;随着赛季的进行和球员受伤（或被交易），了解哪些球员有更多的比赛经验会很有帮助，而不是那些在赛季大部分时间都坐在场边的球员。拥有更多上场时间的球员通常能够为比赛的结果做出贡献。

此查询查找任何进攻中的所有球员，并计算他们参与的总传球次数，按总传球次数降序排列。 WITH snap_events AS (--创建一个过滤播放事件的表格以仅显示快速播放--并显示玩家团队信息 SELECT DISTINCT player_id, t.event, t.gameid, t.playid, CASE WHEN t.team = '离开' THEN g.visitor_team WHEN t.team = 'home' THEN g.home_team ELSE NULL END AS team_name FROM tracking t LEFT JOIN game g ON t.gameid = g.game_id WHERE t.event IN ('snap_direct','ball_snap '))-- 计算这些事件并过滤结果以仅在玩家处于进攻状态时显示数据--在进攻时SELECT a.player_id, pl.display_name, COUNT(a.event) AS play_count, a.team_nameFROM snap_events aLEFT JOIN play p ON a.gameid = p.gameid AND a.playid = p.playidLEFT JOIN player pl ON a.player_id = pl.player_idWHERE a.team_name = p.possessionteamGROUP BY a.player_id, pl.display_name, a.team_name ORDER BY play_count DESC;如果您熟悉美式足球，您可能知道球员会根据比赛条件在比赛内外被替换。在某些情况下，更强壮、更大的球员可能会玩，而在其他情况下，速度更快、更敏捷的球员可能会玩。然而，四分卫是场上最“重要”的球员，而且往往比其他人打得更多。但是，通过省略四分卫，我们可以更深入地了解所有其他位置的球员。 WITH snap_events AS (--创建一个过滤播放事件的表格以仅显示快速播放--并显示玩家团队信息 SELECT DISTINCT player_id, t.event, t.gameid, t.playid, CASE WHEN t.team = '离开' THEN g.visitor_team WHEN t.team = 'home' THEN g.home_team ELSE NULL END AS team_name FROM tracking t LEFT JOIN game g ON t.gameid = g.game_id WHERE t.event IN ('snap_direct','ball_snap '))-- 计算这些事件并过滤结果以仅显示球员在进攻时的数据--在进攻时SELECT a.player_id, pl.display_name, COUNT(a.event) AS play_count, a.team_name, pl."position" FROM snap_events aLEFT JOIN play p ON a.gameid = p.gameid AND a.playid = p.playidLEFT JOIN player pl ON a.player_id = pl.player_idWHERE a.team_name = p.possessionteam AND pl."position" != 'QB 'GROUP BY a.player_id, pl.display_name, a.team_name, pl."position" ORDER BY play_count DESC;所以，现在我们可以看到一个赛季中进攻最多的非四分卫：我们可以通过从跟踪表中提取特定数据并在其上分层查询以进行关联来开始更深入一些。一项可能对您的分析有所帮助的信息是了解哪些四分卫在传球过程中最常被解雇。在足球中，“解雇”对进攻来说是一种消极的表现，经常被解雇的四分卫在整体上的表现往往较差。

一旦你了解了这些球员，你就可以扩展你的分析，看看他们是否因特定类型的比赛（散弹枪阵型）被解雇更多，或者是否在比赛的特定季度（可能是第四节，因为进攻线更累，或者球队在比赛后期往往落后，必须更频繁地传球）。像这样的查询可以快速向您展示更有可能被解雇的四分卫，尤其是当他们打一支防守强队时。首先，我们想根据每个四分卫在常规赛期间参与的传球总数找到他们的擒杀百分比。为此，我们通过在公共表表达式上分层来处理跟踪数据，以便每个查询都可以建立在先前的结果之上。首先，我们为每个四分卫 (qb_plays) 选择所有比赛的不同列表。我们执行 SELECT DISTINCT... 的原因是因为跟踪表为每个玩家、每个游戏保存了多个条目。对于每个四分卫，每次比赛我们只需要一行。有了这个结果，我们就可以计算每个四分卫的总上场次数 (total_qb_plays)，每个四分卫上场的总比赛次数 (qb_games)，最后是四分卫的传球次数是导致擒杀的一部分（麻袋）。有了这些数据，我们最终可以查询所有值，进行百分比计算，并按总袋数对其进行排序。 WITH qb_plays AS ( SELECT DISTINCT ON (POSITION, playid, gameid) POSITION, playid, player_id, gameid FROM tracking t WHERE POSITION = 'QB'),total_qb_plays AS (SELECT count(*) play_count, player_id FROM qb_plays GROUP BY player_id), qb_games AS ( SELECT count(DISTINCT gameid) game_count, player_id FROM qb_plays GROUP BY player_id),sacks AS ( SELECT count(*) sack_count, player_id FROM play p INNER JOIN qb_plays ON p.gameid = qb_plays.gameid AND p.plays = qb_play .playid WHERE p.passresult = 'S' GROUP BY player_id)SELECT play_count, game_count, sack_count, (sack_count/play_count::float)*100 sack_percentage, display_name FROM total_qb_plays tqpINNER JOIN qb_games qgFT ON tqp_ids_erg. s ON s.player_id = qg.player_idINNER JOIN player ON tqp.player_id = player.player_idORDER BY sack_count DESC NULLS 最后；当然，有一些四分卫似乎总是有办法避免被解雇。

到目前为止，我们展示的查询很有趣，有助于为整个赛季的不同球员提供洞察力——但如果你仔细观察，它们都是常规的 SQL 语句。然而，检查一个赛季的 NFL 跟踪数据与典型的时间序列数据不同。我们想要执行的大多数查询都需要以某种方式检查所有 2000 万行。这是为时间序列分析构建的工具，即使数据不是典型的时间序列数据，也可以显着提高您检查数据的能力并同时节省资金。我们注意到我们经常需要构建从跟踪表开始的查询，按特定玩家、位置和游戏过滤数据。部分原因是游戏表没有列出参与特定游戏的所有玩家。因此，我们需要交叉引用跟踪表以识别参与任何给定比赛的球员。我们演示的第一个示例查询——“每场比赛每个位置的平均码数”——就是一个很好的例子。查询首先按位置对每场比赛的所有码数求和。这意味着在进行任何其他分析之前，必须读取并聚合跟踪中的每一行。扫描这 2000 万行是非常无聊、重复且缓慢的工作——尤其是与我们想要进行的分析相比！在我们的小型测试实例上，“平均码数”查询大约需要 8 秒才能运行。我们可以增加实例的大小（这将花费我们更多的钱），或者我们可以更聪明地查询数据（这将花费我们更多的时间）。

相反，我们可以使用连续聚合来预先聚合我们一遍又一遍查询的数据，这减少了 TimescaleDB 每次运行查询时需要做的工作量。 （连续聚合就像 PostgreSQL 物化视图。有关更多信息，请查看我们的连续聚合文档。） CREATE MATERIALIZED VIEW player_yards_by_game_WITH (timescaledb.continuous) ASSELECT player_id, position, gameid, time_bucket(INTERVAL '1 day', "time") AS bucket, SUM(dis) AS yardsFROM 跟踪 tGROUP BY player_id, position, gameid, bucket;运行此查询并创建连续聚合后，我们可以稍微修改第一个查询，将其用作我们的基础表。 WITH total_position_yards AS ( SELECT sum(yards) position_yards, POSITION, gameid FROM player_yards_by_game t GROUP BY POSITION, gameid)SELECT avg(position_yards), position, game_dateFROM game gINNER JOIN total_position_yards tpy ON g.game_id = tpy.gameid = tpy.gameidW ','RB','WR','TE')GROUP BY game_date, POSITIONORDER BY game_date, position;我们得到相同的结果，但现在查询运行时间为 100 毫秒 - 快 800 倍！最后，我们对数据挖掘得越多，就越发现我们需要（或想要）专门针对时间序列数据分析调整的函数来回答我们想问的问题类型。正是针对这种分析，我们构建了 TimescaleDB 超函数，TimescaleDB 中的一系列 SQL 函数，可以更轻松地以更少的代码行操作和分析 PostgreSQL 中的时间序列数据。

NFL 数据集是一个很好的百分位数用例。能够快速找到比某个队列表现更好或更差的球员真的很强大。例如，我们将使用我们之前创建的相同连续聚合（总码数、每场比赛、每名球员）来查找每场比赛按位置移动的总码数中位数。 WITH sum_yards AS (--将位置添加到表中以允许稍后对其进行分组 SELECT a.player_id, display_name, SUM(yards) AS yards, p.position, gameid FROM player_yards_by_game a LEFT JOIN player p ON a.player_id = p .player_id GROUP BY a.player_id, display_name, p.position, gameid)--求每个位置类型的均值和中位数SELECT position, mean(percentile_agg(yards)) AS mean_yards, approx_percentile(0.5, percentile_agg(yards)) AS medium_yardsFROM sum_yardsWHERE POSITION IS NOT nullGROUP BY positionORDER BY mean_yards DESC;最后，我们可以在这个百分位查询的基础上找到在每个位置上跑动超过该位置所有其他球员 95% 的球员。对于某些位置，例如外接手或自由安全，这可以帮助我们找到能够在整场比赛中始终如一地跑动场地的“异常”球员——并进行比赛！ WITH sum_yards AS (--向表中添加位置以允许稍后按它分组 SELECT a.player_id, display_name, SUM(yards) AS yards, p.position FROM player_yards_by_game a LEFT JOIN player p ON a.player_id = p.player_id GROUP BY a.player_id, display_name, p.position),position_percentile AS ( SELECT POSITION, approx_percentile(0.95, percentile_agg(yar ......