Задача
Вы хотите присвоить ранги набору значений.
Решение
Выберите метод ранжирования, расположите элементы в нужном порядке и примените к ним выбранный метод.
Обсуждение
Для некоторых типов статистических проверок требуется присваивание рангов. Я опишу три метода ранжирования и покажу, как можно реализовать каждый из них при помощи переменных SQL. В примерах предполагается, что таблица t содержит следующие результаты, которые должны быть ранжированы по убыванию значений:
mysql> SELECT score FROM t ORDER BY score DESC;
+-------+
| score |
+-------+
| 5 |
| 4 |
| 4 |
| 3 |
| 2 |
| 2 |
| 2 |
| 1 |
+-------+
Один из способов ранжирования заключается просто в присвоении каждому значению номера соответствующей строки упорядоченного множества значений. Для получения такой классификации будем отслеживать номер строки и использовать его для текущего ранга:
mysql> SET @rownum := 0;
mysql> SELECT @rownum := @rownum + 1 AS rank, score
-> FROM t ORDER BY score DESC;
+-----+-------+
| rank | score |
+-----+-------+
| 1 | 5 |
| 2 | 4 |
| 3 | 4 |
| 4 | 3 |
| 5 | 2 |
| 6 | 2 |
| 7 | 2 |
| 8 | 1 |
+-----+-------+
Такая классификация не принимает в расчет возможность «ничейного счета» – вхождения одинаковых значений. Второй метод учитывает такую возможность, увеличивая ранг только при изменении значения:
mysql> SET @rank = 0, @prev_val = NULL;
mysql> SELECT @rank := IF(@prev_val=score,@rank,@rank+1) AS rank,
-> @prev_val := score AS score
-> FROM t ORDER BY score DESC;
+-----+-------+
| rank | score |
+-----+-------+
| 1 | 5 |
| 2 | 4 |
| 2 | 4 |
| 3 | 3 |
| 4 | 2 |
| 4 | 2 |
| 4 | 2 |
| 5 | 1 |
+-----+-------+
Третий метод ранжирования – это комбинация первых двух. Ранги присваиваются по номеру строки, за исключением случаев с повторениями. Все одинаковые значения получают одинаковый ранг, равный номеру строки первого из значений. Для реализации этого метода будем отслеживать номер строки и предыдущее значение, увеличивая ранг до текущего номера строки при изменении значения:
mysql> SET @rownum = 0, @rank = 0, @prev_val = NULL;
mysql> SELECT @rownum := @rownum + 1 AS row,
-> @rank := IF(@prev_val!=score,@rownum,@rank) AS rank,
-> @prev_val := score AS score
-> FROM t ORDER BY score DESC;
+-----+------+-------+
| row | rank | score |
+-----+------+-------+
| 1 | 1 | 5 |
| 2 | 2 | 4 |
| 3 | 2 | 4 |
| 4 | 4 | 3 |
| 5 | 5 | 2 |
| 6 | 5 | 2 |
| 7 | 5 | 2 |
| 8 | 8 | 1 |
+-----+------+-------+
Ранги так же легко присваивать и в программах. Например, следующий фрагмент кода PHP ранжирует результаты из таблицы t, используя третий метод:
$result_id = mysql_query ("SELECT score FROM t ORDER BY score DESC", $conn_id)
or die ("Cannot select scores\n");
$rownum = 0;
$rank = 0;
unset ($prev_score);
print ("Row\tRank\tScore\n");
while (list ($score) = mysql_fetch_row ($result_id))
{
++$rownum;
if ($rownum == 1 || $prev_score != $score)
$rank = $rownum;
print ("$rownum\t$rank\t$score\n");
$prev_score = $score;
}
mysql_free_result ($result_id);
Третий способ ранжирования широко распространен вне области статистических расчетов. Вспомните, как в рецепте 3.18 мы использовали таблицу al_winner, содержащую информацию о 15 лучших подающих 2001 года из Американской Лиги :
mysql> SELECT name, wins FROM al_winner ORDER BY wins DESC, name;
+-----------------+------+
| name | wins |
+-----------------+------+
| Mulder, Mark | 21 |
| Clemens, Roger | 20 |
| Moyer, Jamie | 20 |
| Garcia, Freddy | 18 |
| Hudson, Tim | 18 |
| Abbott, Paul | 17 |
| Mays, Joe | 17 |
| Mussina, Mike | 17 |
| Sabathia, C.C. | 17 |
| Zito, Barry | 17 |
| Buehrle, Mark | 16 |
| Milton, Eric | 15 |
| Pettitte, Andy | 15 |
| Radke, Brad | 15 |
| Sele, Aaron | 15 |
+----------------+------+
С помощью третьего метода этим игрокам можно присвоить ранги следующим образом:
mysql> SET @rownum = 0, @rank = 0, @prev_val = NULL;
mysql> SELECT @rownum := @rownum + 1 AS row,
-> @rank := IF(@prev_val!=wins,@rownum,@rank) AS rank,
-> name,
-> @prev_val := wins AS wins
-> FROM al_winner ORDER BY wins DESC;
+-----+------+-----------------+------+
| row | rank | name | wins |
+-----+------+-----------------+------+
| 1 | 1 | Mulder, Mark | 21 |
| 2 | 2 | Clemens, Roger | 20 |
| 3 | 2 | Moyer, Jamie | 20 |
| 4 | 4 | Garcia, Freddy | 18 |
| 5 | 4 | Hudson, Tim | 18 |
| 6 | 6 | Abbott, Paul | 17 |
| 7 | 6 | Mays, Joe | 17 |
| 8 | 6 | Mussina, Mike | 17 |
| 9 | 6 | Sabathia, C.C. | 17 |
| 10 | 6 | Zito, Barry | 17 |
| 11 | 11 | Buehrle, Mark | 16 |
| 12 | 12 | Milton, Eric | 15 |
| 13 | 12 | Pettitte, Andy | 15 |
| 14 | 12 | Radke, Brad | 15 |
| 15 | 12 | Sele, Aaron | 15 |
+----+------+-----------------+------+