Assigning Ranks

13.10.1 Problem

You want to assign ranks to a set of values.

13.10.2 Solution

Decide on a ranking method, then put the values in the desired order and apply the method to them.

13.10.3 Discussion

Some kinds of statistical tests require assignment of ranks. I'll describe three ranking methods and show how each can be implemented using SQL 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 PHP fragment ranks the scores in t using the third ranking method:

$result_id = mysql_query ("SELECT score FROM t ORDER BY score DESC", $conn_id)
 or die ("Cannot select scores
");
$rownum = 0;
$rank = 0;
unset ($prev_score);
print ("Row	Rank	Score
");
while (list ($score) = mysql_fetch_row ($result_id))
{
 ++$rownum;
 if ($rownum == 1 || $prev_score != $score)
 $rank = $rownum;
 print ("$rownum	$rank	$score
");
 $prev_score = $score;
}
mysql_free_result ($result_id);

The third type of ranking is commonly used outside the realm of statistical methods. Recall that in Recipe 3.19, we used a table al_winner that contains the top 15 winning pitchers in the American League for 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 |
+----------------+------+

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

Using the mysql Client Program

Writing MySQL-Based Programs

Record Selection Techniques

Working with Strings

Working with Dates and Times

Sorting Query Results

Generating Summaries

Modifying Tables with ALTER TABLE

Obtaining and Using Metadata

Importing and Exporting Data

Generating and Using Sequences

Using Multiple Tables

Statistical Techniques

Handling Duplicates

Performing Transactions

Introduction to MySQL on the Web

Incorporating Query Resultsinto Web Pages

Processing Web Input with MySQL

Using MySQL-Based Web Session Management

Appendix A. Obtaining MySQL Software

Appendix B. JSP and Tomcat Primer

Appendix C. References



MySQL Cookbook
MySQL Cookbook
ISBN: 059652708X
EAN: 2147483647
Year: 2005
Pages: 412
Authors: Paul DuBois

Similar book on Amazon

Flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net