MySQL / 13. Статистические методы

Присваивание рангов

Задача
Вы хотите присвоить ранги набору значений.

Решение
Выберите метод ранжирования, расположите элементы в нужном порядке и примените к ним выбранный метод.

Обсуждение
Для некоторых типов статистических проверок требуется присваивание рангов. Я опишу три метода ранжирования и покажу, как можно реализовать каждый из них при помощи переменных 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    |
+----+------+-----------------+------+


Вернуться в раздел: MySQL / 13. Статистические методы