| markmcgary |
13-05-2016 12:59 |
Re: Upset Percentage
Quote:
Originally Posted by Ether
(Post 1586284)
The "alliance_picks" table has the alliance seeding.
|
I found the seeding value in alliances.num.
This SQL may not be the most efficient, but it works:
Code:
select distinct events.name as Event, match_levels.name as Round, matches.set_number as Number, matches.match_number as Match,
red_alliance.num as red_seed, red_teams.team as red_captain, red_scores.total_points as red_points,
blue_alliance.num as blue_seed, blue_teams.team as blue_captain, blue_scores.total_points as blue_points from matches
inner join events on matches.event = events.id
inner join match_levels on matches.match_level = match_levels.id
inner join match_teams as red_teams on matches.id = red_teams.match
inner join match_teams as blue_teams on matches.id = blue_teams.match
inner join alliance_picks as red_alliance_picks on red_teams.team = red_alliance_picks.team
inner join alliance_picks as blue_alliance_picks on blue_teams.team = blue_alliance_picks.team
inner join alliances as red_alliance on events.id = red_alliance.event and red_alliance_picks.alliance = red_alliance.id
inner join alliances as blue_alliance on events.id = blue_alliance.event and blue_alliance_picks.alliance = blue_alliance.id
inner join match_scores as red_scores on matches.id = red_scores.match
inner join match_scores as blue_scores on matches.id = blue_scores.match
where match_level > 1
and red_alliance_picks.pick = 0
and blue_alliance_picks.pick = 0
and red_teams.alliance_color = "red"
and blue_teams.alliance_color = "blue"
and red_scores.alliance_color = "red"
and blue_scores.alliance_color = "blue"
and ((red_seed > blue_seed and (red_points > blue_points or (red_points = blue_points and red_scores.foul_points > blue_scores.foul_points)))
or (blue_seed > red_seed and blue_points > red_points or (blue_points = red_points and blue_scores.foul_points > red_scores.foul_points)))
and events.is_official = 1
order by events.name, match_levels.id, set_number, match_number
Results are:
712 of 2273 playoff matches were upsets. That's 31.3%
643 times Blue was the underdog and upset Red.
69 times Red was the underdog and upset Blue.
Whew.... I hope that's it. Are there any other quirks of the system that I have missed?
(Now, I have to get back to work!) |
