Вычисление рейтинга команд

Задача
Вы хотите вычислить рейтинг команд по записям об их победах и поражениях, включая значения «отставания в играх» (GB – games-behind).

Решение
Определите, какая команда занимает первое место, затем объедините результат с исходными записями.

Обсуждение
Рейтинги спортивных команд, соревнующихся друг с другом, обычно определяются по соотношению победы-поражения, при этом командам, занявшим не первое место, присваивается значение «отставания в играх», показывающее, на сколько матчей они отстают от первого места. В разделе будет рассказано о том, как вычислять такие значения. В первом примере для пояснения логики вычислений используется таблица, содержащая всего один набор записей о командах. Затем будет рассмотрена вторая таблица, включающая несколько наборов записей; в этом случае нужно будет использовать соединение, чтобы выполнить независимые вычисления для каждой группы команд.

Рассмотрим таблицу standings1, которая содержит один набор записей о бейсбольных командах (они представляют итоговые результаты Северной Лиги за 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 |
+--------------+------+--------+

Записи упорядочены по разности побед-поражений; именно так формируются списки команд от первого к последнему месту.


Но рейтинг команды обычно включает в себя процент побед каждой команды и число, показывающее, на сколько игр лидер опередил всех остальных. Давайте добавим эту информацию в наш вывод. Вычислить процент просто – это отношение количества побед к общему количеству игр, которое вычисляется так:

wins / (wins + losses)

Если вы хотите выполнить расчеты для команды, которая еще не приступила к играм, то выражение будет вычислено как NULL из-за деления на ноль.

Для простоты я буду предполагать, что количество игр отлично от нуля, но если вы хотите обрабатывать это условие, заменяя NULL нулем, то выражение можно обобщить так:

IFNULL(wins / (wins + losses),0)
или:
wins / IF(wins=0,1,wins + losses)

Определить значение «отставания в играх» несколько сложнее. Оно зависит от отношения побед-поражений двух команд и вычисляется как среднее арифметическое двух значений:

• Количество игр, которое занимающая второе место команда должна выиграть, чтобы иметь то же число побед, что и команда-лидер.

• Количество игр, которое лидер должен проиграть, чтобы иметь то же число поражений, что и команда, занимающая второе место.

Предположим, например, что для двух команд A и B имеются такие записи о победах-поражениях:

+------+------+--------+
| team | wins | losses |
+------+------+--------+
| A | 17 | 11 |
| B | 14 | 12 |
+------+------+--------+

Команда B должна выиграть еще три игры, а команда A – проиграть одну, для того чтобы команды сравнялись.


Среднее арифметическое трех и одного равно двум, следовательно, B на две игры позади A. Математически можно выразить «отставание в играх» для вычисления так:

((winsA - winsB) + (lossesB - lossesA)) / 2

Перегруппируем выражение и получим:

((winsA - lossesA) - (winsB - lossesB)) / 2

Второе выражение эквивалентно первому, но каждая его составляющая представлена разностью побед и поражений одной команды, а не сравнением результатов двух команд. Со вторым выражением легче работать, поскольку каждый элемент не зависит от другого и определяется записями только одной команды. Первый элемент соответствует разности побед и поражений команды-лидера, так что если начать с вычисления этого значения, все остальные «отставания в играх» можно будет определить по отношению к нему.

У команды, занимающей первое место, самая большая разница количеств побед и поражений. Выполним запрос для нахождения этого значения и сохранения его в переменной:

mysql> SELECT @wl_diff := MAX(wins-losses) FROM standings1;

+------------------------------------+
| @wl_diff := MAX(wins-losses) |
+------------------------------------+
| 17 |
+------------------------------------+

Теперь используем эту разность для вывода рейтинга команд, включающего процент выигрышей и значения «отставания в играх»:

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.65 | 0 |
| Crookston | 31 | 25 | 0.55 | 5.5 |
| Fargo | 30 | 26 | 0.54 | 6.5 |
| Grand Forks | 28 | 26 | 0.52 | 7.5 |
| Devils Lake | 19 | 31 | 0.38 | 14.5 |
| Cavalier | 15 | 32 | 0.32 | 17 |
+---------------+----+---+------+------+

Обратимся теперь к некоторым тонкостям форматирования.


Процентные отношения в рейтингах обычно представляются тремя разрядами, а значение «отставания в играх» для лидера выводится как знак -, а не 0. Чтобы выводить три десятичных разряда, используем TRUNCATE(выражение,3). Чтобы соответствующим образом выводить значение «отставания в играх» для первой команды, поместим выражение, вычисляющее столбец отставаний, внутрь вызова IF(), который преобразует 0 в тире:

mysql> SELECT team, wins AS W, losses AS L,
-> TRUNCATE(wins/(wins+losses),3) AS PCT,
-> IF((@wl_diff - (wins-losses)) = 0,'-',(@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.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 |
+--------------+----+----+-------+------+

Эти запросы упорядочивают команды по разности побед-поражений, используя процент побед для сортировки команд с одинаковым значением разности. Конечно, было бы проще упорядочивать значения по процентному отношению, но результат не всегда был бы корректным. Как это ни удивительно, но команда с более низким процентом побед может на самом деле занимать более высокое положение в турнирной таблице, чем команда с более высоким процентом побед. (Обычно так бывает в начале сезона, когда у команд может сильно отличаться количество сыгранных матчей.) Рассмотрим случай с двумя командами A и B, имеющими такие результаты:

+------+------+--------+
| team | wins | losses |
+------+------+--------+
| A | 4 | 1 |
| B | 2 | 0 |
+------+------+--------+

Вычисление для этих команд процента побед и «отставания в играх» приводит к следующим результатам, из которых видно, что команда, занимающая первое место, имеет более низкий процент побед, чем вторая команда:

+------+---+--+-------+-----+
| team | W | L | PCT | GB |
+------+--+---+-------+-----+
| A | 4 | 1 | 0.800 | - |
| B | 2 | 0 | 1.000 | 0.5 |
+------+--+---+-------+-----+

Рассмотренные вычисления рейтингов можно выполнить без использования соединения. Они проводятся всего для одной группы команд, так что разность количества побед и поражений лидера может храниться в переменной.

Если же множество данных охватывает несколько групп команд, ситуация усложняется. Например, в 1997 году Северная Лига включала два дивизиона: Восточный и Западный. Кроме того, отдельные рейтинги велись для первой и второй половины сезона, так как победители первой половины сезона в каждом дивизионе играли друг с другом за право выступать в Лиге чемпионов. Таблица standings2 приводит такие записи, упорядоченные по половине сезона, дивизиону и разности побед-поражений:

mysql> SELECT half, div, team, wins, losses FROM standings2
-> ORDER BY half, div, wins-losses DESC;

+-----+----------+-------------------+-----+--------+
| half | div | 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 |
+-----+-----------+--------------------+---+--------+

Для формирования рейтингов необходимо вычислить значения «отставания в играх» отдельно для каждой из четырех комбинаций половины сезона с дивизионом. Начнем с вычисления разности побед-поражений для команды-лидера каждой группы и сохранения значений в специальной таблице firstplace:

mysql> CREATE TABLE firstplace
-> SELECT half, div, MAX(wins-losses) AS wl_diff
-> FROM standings2
-> GROUP BY half, div;

Затем объединим таблицу firstplace с исходной, сопоставляя записи каждой команды соответствующую разность побед-поражений для получения ее значения «отставания в играх»:

mysql> SELECT wl.half, wl.div, 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)) = 0,
-> '-', (fp.wl_diff - (wl.wins-wl.losses)) / 2) AS GB
-> FROM standings2 AS wl, firstplace AS fp
-> WHERE wl.half = fp.half AND wl.div = fp.div
-> ORDER BY wl.half, wl.div, wl.wins-wl.losses DESC, PCT DESC;

+-----+----------+-------------------+----+---+--------+---------+
| half | div | team | W | L | PCT | GB |
+-----+----------+-------------------+----+---+--------+---------+
| 1 | Eastern | St. Paul | 24 | 18 | 0.571 | - |
| 1 | Eastern | Thunder Bay | 18 | 24 | 0.428 | 6.00 |
| 1 | Eastern | Duluth-Superior | 17 | 24 | 0.414 | 6.50 |
| 1 | Eastern | Madison | 15 | 27 | 0.357 | 9.00 |
| 1 | Western | Winnipeg | 29 | 12 | 0.707 | - |
| 1 | Western | Sioux City | 28 | 14 | 0.666 | 1.50 |
| 1 | Western | Fargo-Moorhead | 21 | 21 | 0.500 | 8.50 |
| 1 | Western | Sioux Falls | 15 | 27 | 0.357 | 14.50 |
| 2 | Eastern | Duluth-Superior | 22 | 20 | 0.523 | - |
| 2 | Eastern | St. Paul | 21 | 21 | 0.500 | 1.00 |
| 2 | Eastern | Madison | 19 | 23 | 0.452 | 3.00 |
| 2 | Eastern | Thunder Bay | 18 | 24 | 0.428 | 4.00 |
| 2 | Western | Fargo-Moorhead | 26 | 16 | 0.619 | - |
| 2 | Western | Winnipeg | 24 | 18 | 0.571 | 2.00 |
| 2 | Western | Sioux City | 22 | 20 | 0.523 | 4.00 |
| 2 | Western | Sioux Falls | 16 | 26 | 0.380 | 10.00 |
+-----+----------+---------------------+---+----+-------+--------+

Надо сказать, что такой вывод трудно воспринять. Чтобы сделать его понятнее, вероятно, стоит выполнить запрос в программе и переформатировать его результаты так, чтобы записи каждой группы выводились отдельно.

Приведем код на Perl, который начинает новую группу вывода каждый раз, когда встречается новая группа рейтингов. Предполагается, что соединение было выполнено только что, и его результаты доступны через дескриптор предложения $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) # новая группа рейтингов?
{
# вывести заголовок и запомнить новые значения половины и дивизиона
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;
}

Преобразованный вывод выглядит так:

Eastern Division, season half 1
Team W L PCT GB
St. Paul 24 18 0.57 -
Thunder Bay 18 24 0.43 6.00
Duluth-Superior 17 24 0.41 6.50
Madison 15 27 0.36 9.00
Western Division, season half 1
Team W L PCT GB
Winnipeg 29 12 0.71 -
Sioux City 28 14 0.67 1.50
Fargo-Moorhead 21 21 0.50 8.50
Sioux Falls 15 27 0.36 14.50Eastern Division, season half 2
Team W L PCT GB
Duluth-Superior 22 20 0.52 -
St. Paul 21 21 0.50 1.00
Madison 19 23 0.45 3.00
Thunder Bay 18 24 0.43 4.00
Western Division, season half 2
Team W L PCT GB
Fargo-Moorhead 26 16 0.62 -
Winnipeg 24 18 0.57 2.00
Sioux City 22 20 0.52 4.00
Sioux Falls 16 26 0.38 10.00

Приведенный выше код, формирующий полнотекстовый вывод, взят из сценария calc_standings.pl каталога joins дистрибутива recipes. Этот же каталог содержит сценарий на PHP, calc_standings.php, который реализует другой подход, генерируя вывод в виде HTML-таблиц, что может быть удобнее для формирования рейтингов в веб-среде.

Оцените статью: (0 голосов)
0 5 0

Статьи из раздела MySQL на эту тему:
Вставка записей в таблицу, включающую значения из другой
Вывод списков для записей «главная-подчиненная» и итогов
Вычисление разности между последовательными строками
Выявление и удаление несвязанных записей
Заполнение пустых мест в списке с помощью соединения