Decide on a ranking method, and then put the values in the desired order and apply the method to them.
Some kinds of statistical tests require assignment of ranks.
This section describes three ranking methods and shows how each can be
implemented by using user-defined variables. The examples assume that
a table t
contains the following
scores, which are to be ranked with the values in descending
order:
mysql>SELECT score FROM t ORDER BY score DESC;
+-------+
| score |
+-------+
| 5 |
| 4 |
| 4 |
| 3 |
| 2 |
| 2 |
| 2 |
| 1 |
+-------+
One type of ranking simply assigns each value its row number within the ordered set of values. To produce such rankings, keep track of the row number and use it for the current rank:
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 | +------+-------+
That kind of ranking doesn’t take into account the possibility of ties (instances of values that are the same). A second ranking method does so by advancing the rank only when values change:
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 | +------+-------+
A third ranking method is something of a combination of the other two methods. It ranks values by row number, except when ties occur. In that case, the tied values each get a rank equal to the row number of the first of the values. To implement this method, keep track of the row number and the previous value, advancing the rank to the current row number when the value changes:
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 | +------+------+-------+
Ranks are easy to assign within a program as well. For example,
the following Ruby fragment ranks the scores in t
using the third ranking method:
dbh.execute("SELECT score FROM t ORDER BY score DESC") do |sth| rownum = 0 rank = 0 prev_score = nil puts "Row Rank Score " sth.fetch do |row| score = row[0] rownum += 1 rank = rownum if rownum == 1 || prev_score != score prev_score = score puts "#{rownum} #{rank} #{score}" end end
The third type of ranking is commonly used outside the realm of
statistical methods. Recall that in Choosing Appropriate LIMIT Values, we used a table al_winner
that contained the American League
pitchers who won 15 or more games during the 2001 baseball
season:
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 |
+----------------+------+
These pitchers can be assigned ranks using the third method as follows:
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 | Zito, Barry | 17 | | 7 | 6 | Sabathia, C.C. | 17 | | 8 | 6 | Mussina, Mike | 17 | | 9 | 6 | Mays, Joe | 17 | | 10 | 6 | Abbott, Paul | 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 | +------+------+----------------+------+
3.131.38.14