TimescaleDB is packaged as a PostgreSQL extension. As such, TimescaleDB is PostgreSQL with super-powers. You can do anything in TimescaleDB that you can in PostgreSQL, including joining tables and combining data for further analysis.
The Mile-High advantage
In professional football, it's a well-understood phenomenon that teams that visit Denver's Mile-High stadium are at a disadvantage because unlike the home team (the Denver Broncos), they are not accustomed to playing in high altitude.
Earlier we ingested stadium data. Now we can run a query to see the performance of players when they are playing at Mile High Stadium.
Like many of the queries in our analysis section, for this example you use the
relational nature of this data. You join the
tables to compare the average acceleration and yards run of individual players
when they are performing in stadiums outside of Denver versus when they are
playing within Denver. The columns
the acceleration and yard statistics while in Denver.
WITH stat_vals AS ( -- This table collects the summed yard and avg acceleration data of a player during one game SELECT a.player_id, displayname, SUM(yards) AS yards, AVG(acc) AS acc, team, gameid FROM player_yards_by_game a LEFT JOIN player p ON a.player_id = p.player_id GROUP BY a.player_id, displayname, gameid, team ), team_data AS ( -- This table gets us the team information so that we can filter on teams SELECT a.player_id, acc, yards, a.gameid, CASE WHEN a.team = 'away' THEN g.visitor_team WHEN a.team = 'home' THEN g.home_team ELSE NULL END AS team_name, g.home_team FROM stat_vals a LEFT JOIN game g ON a.gameid = g.game_id ), avg_stats AS ( -- This table takes the avg acceleration and yards run for players when they are not in denver -- and then when they are in denver SELECT p.player_id, p.displayname, AVG(acc) FILTER (WHERE team_name != 'DEN' AND home_team !='DEN') AS avg_acc, AVG(acc) FILTER (WHERE team_name != 'DEN' AND home_team = 'DEN') AS avg_acc_den, AVG(yards) FILTER (WHERE team_name != 'DEN' AND home_team !='DEN') AS avg_yards, AVG(yards) FILTER (WHERE team_name != 'DEN' AND home_team = 'DEN') AS avg_yards_den, COUNT(gameid) FILTER (WHERE team_name != 'DEN' AND home_team !='DEN') AS games, COUNT(gameid) FILTER (WHERE team_name != 'DEN' AND home_team ='DEN') AS games_den FROM team_data t LEFT JOIN player p ON t.player_id = p.player_id GROUP BY p.player_id, p.displayname ) SELECT * FROM avg_stats WHERE avg_acc IS NOT NULL AND avg_acc_den IS NOT NULL ORDER BY avg_acc DESC, avg_acc_den DESC
You should see this:
You can see that generally, it appears many players may have worse acceleration and average number of yards run per game while playing in Denver. However, it is good to note that you only have one sample point showing Denver averages which effects statistical significance.
Grass vs. turf, the eternal (football) question
Players often say they "feel" faster on artificial turf. How much faster are they in reality?
Using this query you join the
tables, to extract the average acceleration that a player has while using turf
verses grass. The column
avg_acc_turf represents the players average
acceleration while using artificial turf, and
avg_acc_grass represents their
average acceleration while on grass.
WITH acceleration AS ( -- This table gets the average acceleration for a player over each game SELECT a.player_id, avg(acc) AS acc, a.gameid, a.team FROM player_yards_by_game a GROUP BY a.player_id, a.gameid, a.team ), team_data AS ( -- This table gets us the surface information so that we can filter on turf type SELECT a.player_id, acc, g.game_id, si."location", si.surface FROM acceleration a LEFT JOIN game g ON a.gameid = g.game_id LEFT JOIN stadium_info si on g.home_team = si.team_abbreviation ), avg_stats AS ( -- This table takes the avg acceleration and yards run for players when they are not in denver -- and then when they are in denver SELECT p.player_id, p.displayname, AVG(acc) FILTER (WHERE surface LIKE '%Turf%') AS avg_acc_turf, AVG(acc) FILTER (WHERE surface NOT LIKE '%Turf%') AS avg_acc_grass FROM team_data t LEFT JOIN player p ON t.player_id = p.player_id GROUP BY p.player_id, p.displayname ) SELECT * FROM avg_stats WHERE avg_acc_turf IS NOT NULL AND avg_acc_grass IS NOT NULL AND player_id IS NOT NULL ORDER BY avg_acc_turf DESC, avg_acc_grass DESC
You should see this:
For many players, it appears that they are indeed faster on artificial turf. This 'feeling' of increased speed may in fact be grounded in reality.
We're going to overtime!
Sometimes it's helpful to visualize time-series data in order to fully understand how a system is performing. The NFL dataset includes play-by-play data. It is possible to visualize this information so that you can see how a play unfolds.
Found an issue on this page?Report an issue!