Recipe 12.7. Computing Team Standings
ProblemYou want to compute team standings from their win-loss records, including the games-behind (GB) values. SolutionDetermine which team is in first place, and then join that result to the original rows. DiscussionStandings for sports teams that compete against each other typically are ranked according to who has the best win-loss record, and the teams not in first place are assigned a "games-behind" value indicating how many games out of first place they are. This section shows how to calculate those values. The first example uses a table containing a single set of team records to illustrate the logic of the calculations. The second example uses a table containing several sets of records (that is, the records for all teams in both divisions of a league, for both halves of the season). In this case, it's necessary to use a join to perform the calculations independently for each group of teams. Consider the following table, standings1, which contains a single set of baseball team records (they represent the final standings for the Northern League in the year 1902): mysql> SELECT team, wins, losses FROM standings1 -> ORDER BY wins-losses DESC; +-------------+------+--------+ | team | wins | losses | +-------------+------+--------+ | Winnipeg | 37 | 20 | | Crookston | 31 | 25 | | Fargo | 30 | 26 | | Grand Forks | 28 | 26 | | Devils Lake | 19 | 31 | | Cavalier | 15 | 32 | +-------------+------+--------+ The rows are sorted by the win-loss differential, which is how to place teams in order from first place to last place. But displays of team standings typically include each team's winning percentage and a figure indicating how many games behind the leader all the other teams are. So let's add that information to the output. Calculating the percentage is easy. It's the ratio of wins to total games played and can be determined using this expression: wins / (wins + losses) This expression involves division by zero when a team has not played any games yet. For simplicity, I'll assume a nonzero number of games, but if you want to handle this condition, generalize the expression as follows: IF(wins=0,0,wins/(wins+losses)) This expression uses the fact that no division at all is necessary unless the team has won at least one game. Determining the games-behind value is a little trickier. It's based on the relationship of the win-loss records for two teams, calculated as the average of two values:
For example, suppose two teams A and B have the following win-loss records: +------+------+--------+ | team | wins | losses | +------+------+--------+ | A | 17 | 11 | | B | 14 | 12 | +------+------+--------+ Here, team B has to win three more games, and team A has to lose one more game for the teams to be even. The average of three and one is two, thus B is two games behind A. Mathematically, the games-behind calculation for the two teams can be expressed like this: ((winsA - winsB) + (lossesB - lossesA)) / 2 With a little rearrangement of terms, the expression becomes: ((winsA - lossesA) - (winsB - lossesB)) / 2 The second expression is equivalent to the first, but it has each factor written as a single team's win-loss differential, rather than as a comparison between teams. This makes it easier to work with, because each factor can be determined independently from a single team record. The first factor represents the first-place team's win-loss differential, so if we calculate that value first, all the other teams GB values can be determined in relation to it. The first-place team is the one with the largest win-loss differential. To find that value and save it in a variable, use this statement: mysql> SET @wl_diff = (SELECT MAX(wins-losses) FROM standings1); Then use the differential as follows to produce team standings that include winning percentage and GB values: mysql> SELECT team, wins AS W, losses AS L, -> wins/(wins+losses) AS PCT, -> (@wl_diff - (wins-losses)) / 2 AS GB -> FROM standings1 -> ORDER BY wins-losses DESC, PCT DESC; +-------------+------+------+--------+---------+ | team | W | L | PCT | GB | +-------------+------+------+--------+---------+ | Winnipeg | 37 | 20 | 0.6491 | 0.0000 | | Crookston | 31 | 25 | 0.5536 | 5.5000 | | Fargo | 30 | 26 | 0.5357 | 6.5000 | | Grand Forks | 28 | 26 | 0.5185 | 7.5000 | | Devils Lake | 19 | 31 | 0.3800 | 14.5000 | | Cavalier | 15 | 32 | 0.3191 | 17.0000 | +-------------+------+------+--------+---------+ There are a couple of minor formatting issues that can be addressed at this point. Typically, standings listings display percentages to three decimal places, and the GB value to one decimal place (with the exception that the GB value for the first-place team is displayed as -). To display n decimal places, trUNCATE( expr , n ) can be used. To display the GB value for the first-place team appropriately, put the expression that calculates the GB column within a call to IF( ) that maps 0 to a dash: mysql> SELECT team, wins AS W, losses AS L, -> TRUNCATE(wins/(wins+losses),3) AS PCT, -> IF(@wl_diff = wins-losses, -> '-',TRUNCATE((@wl_diff - (wins-losses))/2,1)) AS GB -> FROM standings1 -> ORDER BY wins-losses DESC, PCT DESC; +-------------+------+------+-------+------+ | team | W | L | PCT | GB | +-------------+------+------+-------+------+ | Winnipeg | 37 | 20 | 0.649 | - | | Crookston | 31 | 25 | 0.553 | 5.5 | | Fargo | 30 | 26 | 0.535 | 6.5 | | Grand Forks | 28 | 26 | 0.518 | 7.5 | | Devils Lake | 19 | 31 | 0.380 | 14.5 | | Cavalier | 15 | 32 | 0.319 | 17.0 | +-------------+------+------+-------+------+ These statements order the teams by win-loss differential, using winning percentage as a tie-breaker in case there are teams with the same differential value. It would be simpler just to sort by percentage, of course, but then you wouldn't always get the correct ordering. It's a curious fact that a team with a lower winning percentage can actually be higher in the standings than a team with a higher percentage. (This generally occurs early in the season, when teams may have played highly disparate numbers of games, relatively speaking.) Consider the case in which two teams, A and B, have the following rows: +------+------+--------+ | team | wins | losses | +------+------+--------+ | A | 4 | 1 | | B | 2 | 0 | +------+------+--------+ Applying the GB and percentage calculations to these team records yields the following result, in which the first-place team actually has a lower winning percentage than the second-place team: +------+------+------+-------+------+ | team | W | L | PCT | GB | +------+------+------+-------+------+ | A | 4 | 1 | 0.800 | - | | B | 2 | 0 | 1.000 | 0.5 | +------+------+------+-------+------+ The standings calculations shown thus far can be done without a join. They involve only a single set of team records, so the first-place team's win-loss differential can be stored in a variable. A more complex situation occurs when a dataset includes several sets of team records. For example, the 1997 Northern League had two divisions (Eastern and Western). In addition, separate standings were maintained for the first and second halves of the season, because season-half winners in each division played each other for the right to compete in the league championship. The following table, standings2, shows what these rows look like, ordered by season half, division, and win-loss differential: mysql> SELECT half, division, team, wins, losses FROM standings2 -> ORDER BY half, division, wins-losses DESC; +------+----------+-----------------+------+--------+ | half | division | team | wins | losses | +------+----------+-----------------+------+--------+ | 1 | Eastern | St. Paul | 24 | 18 | | 1 | Eastern | Thunder Bay | 18 | 24 | | 1 | Eastern | Duluth-Superior | 17 | 24 | | 1 | Eastern | Madison | 15 | 27 | | 1 | Western | Winnipeg | 29 | 12 | | 1 | Western | Sioux City | 28 | 14 | | 1 | Western | Fargo-Moorhead | 21 | 21 | | 1 | Western | Sioux Falls | 15 | 27 | | 2 | Eastern | Duluth-Superior | 22 | 20 | | 2 | Eastern | St. Paul | 21 | 21 | | 2 | Eastern | Madison | 19 | 23 | | 2 | Eastern | Thunder Bay | 18 | 24 | | 2 | Western | Fargo-Moorhead | 26 | 16 | | 2 | Western | Winnipeg | 24 | 18 | | 2 | Western | Sioux City | 22 | 20 | | 2 | Western | Sioux Falls | 16 | 26 | +------+----------+-----------------+------+--------+ Generating the standings for these rows requires computing the GB values separately for each of the four combinations of season half and division. Begin by calculating the win-loss differential for the first-place team in each group and saving the values into a separate firstplace table: mysql> CREATE TABLE firstplace -> SELECT half, division, MAX(wins-losses) AS wl_diff -> FROM standings2 -> GROUP BY half, division; Then join the firstplace table to the original standings, associating each team record with the proper win-loss differential to compute its GB value: mysql> SELECT wl.half, wl.division, wl.team, wl.wins AS W, wl.losses AS L, -> TRUNCATE(wl.wins/(wl.wins+wl.losses),3) AS PCT, -> IF(fp.wl_diff = wl.wins-wl.losses, -> '-',TRUNCATE((fp.wl_diff - (wl.wins-wl.losses)) / 2,1)) AS GB -> FROM standings2 AS wl INNER JOIN firstplace AS fp -> ON wl.half = fp.half AND wl.division = fp.division -> ORDER BY wl.half, wl.division, wl.wins-wl.losses DESC, PCT DESC; +------+----------+-----------------+------+------+-------+------+ | half | division | team | W | L | PCT | GB | +------+----------+-----------------+------+------+-------+------+ | 1 | Eastern | St. Paul | 24 | 18 | 0.571 | - | | 1 | Eastern | Thunder Bay | 18 | 24 | 0.428 | 6.0 | | 1 | Eastern | Duluth-Superior | 17 | 24 | 0.414 | 6.5 | | 1 | Eastern | Madison | 15 | 27 | 0.357 | 9.0 | | 1 | Western | Winnipeg | 29 | 12 | 0.707 | - | | 1 | Western | Sioux City | 28 | 14 | 0.666 | 1.5 | | 1 | Western | Fargo-Moorhead | 21 | 21 | 0.500 | 8.5 | | 1 | Western | Sioux Falls | 15 | 27 | 0.357 | 14.5 | | 2 | Eastern | Duluth-Superior | 22 | 20 | 0.523 | - | | 2 | Eastern | St. Paul | 21 | 21 | 0.500 | 1.0 | | 2 | Eastern | Madison | 19 | 23 | 0.452 | 3.0 | | 2 | Eastern | Thunder Bay | 18 | 24 | 0.428 | 4.0 | | 2 | Western | Fargo-Moorhead | 26 | 16 | 0.619 | - | | 2 | Western | Winnipeg | 24 | 18 | 0.571 | 2.0 | | 2 | Western | Sioux City | 22 | 20 | 0.523 | 4.0 | | 2 | Western | Sioux Falls | 16 | 26 | 0.380 | 10.0 | +------+----------+-----------------+------+------+-------+------+ That output is somewhat difficult to read, however. To make it easier to understand, you'd likely execute the statement from within a program and reformat its results to display each set of team records separately. Here's some Perl code that does that by beginning a new output group each time it encounters a new group of standings. The code assumes that the join statement has just been executed and that its results are available through the statement handle $sth: my ($cur_half, $cur_div) = ("", ""); while (my ($half, $div, $team, $wins, $losses, $pct, $gb) = $sth->fetchrow_array ()) { if ($cur_half ne $half || $cur_div ne $div) # new group of standings? { # print standings header and remember new half/division values print "\n$div Division, season half $half\n"; printf "%-20s %3s %3s %5s %s\n", "Team", "W", "L", "PCT", "GB"; $cur_half = $half; $cur_div = $div; } printf "%-20s %3d %3d %5s %s\n", $team, $wins, $losses, $pct, $gb; } The reformatted output looks like this: Eastern Division, season half 1 Team W L PCT GB St. Paul 24 18 0.571 - Thunder Bay 18 24 0.428 6.0 Duluth-Superior 17 24 0.414 6.5 Madison 15 27 0.357 9.0 Western Division, season half 1 Team W L PCT GB Winnipeg 29 12 0.707 - Sioux City 28 14 0.666 1.5 Fargo-Moorhead 21 21 0.500 8.5 Sioux Falls 15 27 0.357 14.5 Eastern Division, season half 2 Team W L PCT GB Duluth-Superior 22 20 0.523 - St. Paul 21 21 0.500 1.0 Madison 19 23 0.452 3.0 Thunder Bay 18 24 0.428 4.0 Western Division, season half 2 Team W L PCT GB Fargo-Moorhead 26 16 0.619 - Winnipeg 24 18 0.571 2.0 Sioux City 22 20 0.523 4.0 Sioux Falls 16 26 0.380 10.0 This code comes from the script calc_standings.pl in the joins directory of the recipes distribution. That directory also contains a PHP script, calc_standings.php, that produces output in the form of HTML tables, which you might prefer for generating standings in a web environment. |
