Jaci's Annual FRC Datadump 2016

[Jaci]

Welcome to the inaugural release of my FRC datadumps

You can download the sql database here. It contains data about every match, stat, event, team, rank, district, award... everything. The database itself is about 13MB but it's been zipped together with some instructions and examples to bring the filesize down to about 5MB.

So let's see what we can do with it....

How about seeing all the matches your team has had over 100 points?

SELECT matches.key, events.short_name, match_scores.total_points, match_scores.alliance_color FROM matches
INNER JOIN events ON events.id == matches.event                 # Join the Event so we can see the Event Name
INNER JOIN match_scores ON matches.id == match_scores.match     # Join the Scores so we can filter based on score
INNER JOIN match_teams ON matches.id == match_teams.match       # Join the Teams so we can filter based on team
WHERE match_teams.alliance_color == match_scores.alliance_color # Make sure the scores we're checking are the same as our team's alliance
AND match_scores.total_points > 100                             # Make sure the score is over 100 points
AND match_teams.team == 5333                                    # Make sure we're checking matches with our team
ORDER BY match_scores.total_points DESC                         # Order by the match score, descending

Or every match in 2016 over 250 points?

SELECT matches.key, match_scores.total_points, match_scores.alliance_color FROM matches
INNER JOIN match_scores ON matches.id == match_scores.match   # Join the scores table, so we can see the scores
WHERE match_scores.total_points > 250                         # Filter by scores 250+
ORDER BY match_scores.total_points DESC                       # Order the scored descending

Or every match where there have been more High Goals scored in Auto than Teleop?

SELECT events.short_name, matches.key, match_scores.alliance_color, match_scores.auto_boulders_high, match_scores.teleop_boulders_high FROM match_scores
INNER JOIN matches ON matches.id == match_scores.match                  # Join the matches so we can get the match key
INNER JOIN events ON matches.event == events.id                         # Join the events so we can get the event name
WHERE match_scores.auto_boulders_high > match_scores.teleop_boulders_high   # Check the auto boulders high is larger than the teleop equivilent
ORDER BY match_scores.auto_boulders_high DESC                           # Order by auto boulders high, descending

Or something a little more advanced... every Alliance Selection where the Captain's OPR is less than the 1st Pick's OPR...

SELECT events.short_name, alliances.num, teams.id FROM alliances
INNER JOIN alliance_picks ON alliances.id == alliance_picks.alliance        # Join the alliance picks for this alliance
INNER JOIN events ON events.id == alliances.event                           # Join the event for this alliance
INNER JOIN teams ON alliance_picks.team == teams.id                         # Join the teams for the picks
INNER JOIN stats ON stats.team == teams.id                                  # Join the statistics (opr) for the teams
WHERE alliance_picks.pick == 0                                              # Select the Captain team
AND stats.event == events.id                                                # Make sure the event we're checking OPRs is the same event we're checking alliances on
AND
(SELECT stats.opr FROM alliance_picks                                       # Select the OPR stat
INNER JOIN stats ON stats.team == alliance_picks.team                       # Join the stats to the alliance picks
WHERE alliance_picks.alliance == alliances.id                               # Make sure the alliance we're checking is the same alliance the captain is from
AND alliance_picks.pick == 1)                                               # Make sure we're checking the 1st Pick. At this point, the OPR stat is selected
> stats.opr                                                                 # Check the 1st picks OPR from the previous select against the captains

All these can be seen in the examples of the ZIP file.