MySQL
qnoteDay 3

RANK関数はないけど、MySQLのみでランキング表示させたい

qnoteアドベントカレンダー2018の3日目です。

MySQLでランキング表示したい。

ある日、RANK関数という関数があることを知り、MySQLで実行してみたが、「そんなものはない!」と怒られた。
よくよく調べてみると、Oracle DatabaseやMicrosoft SQL Serverでは、RANK関数が使えるが、MySQLではRANK関数が使えないことを知りました。

でも、どうにかしてMySQLだけでランキング表示が出来ないか試してみました。

使用するデータ

mysql> SELECT * FROM Address;
+-----------+---------------+--------------+-----+-----+
| name      | phone_nbr     | address      | sex | age |
+-----------+---------------+--------------+-----+-----+
| 井上      | NULL          | 福島県       |   |  55 |
| 佐々木    | 080-5848-XXXX | 千葉県       |   |  19 |
| 佐藤      | 090-1922-XXXX | 三重県       |   |  25 |
| 前田      | 090-0000-XXXX | 東京都       |   |  21 |
| 小川      | 080-3333-XXXX | 東京都       |   |  30 |
| 松本      | NULL          | 千葉県       |   |  20 |
|         | 080-3333-XXXX | 福島県       |   |  32 |
|         | 090-2984-XXXX | 東京都       |   |  45 |
| 鈴木      | 090-0001-XXXX | 和歌山県     |   |  32 |
+-----------+---------------+--------------+-----+-----+
9 rows in set (0.00 sec)

欠番なしのランキング

コード

SET @rank=0, @before_line_age=0;
SELECT
 CASE WHEN @before_line_age = age THEN @rank
 ELSE @rank:=@rank+1
 END AS rank,
 name,
 @before_line_age:=age AS age
FROM Address
ORDER BY age;

結果

+------+-----------+-----+
| rank | name      | age |
+------+-----------+-----+
|    1 | 佐々木    |  19 |
|    2 | 松本      |  20 |
|    3 | 前田      |  21 |
|    4 | 佐藤      |  25 |
|    5 | 小川      |  30 |
|    6 |         |  32 |
|    6 | 鈴木      |  32 |
|    7 |         |  45 |
|    8 | 井上      |  55 |
+------+-----------+-----+
9 rows in set (0.00 sec)

何をやっているか

SET @rank=0, @before_line_age=0;

まず、ユーザー定数を定義
@rank : 行数を指定。
@before_line_age : 一つ上の行のageをここに代入。

SELECT
 ...
FROM Address
ORDER BY age;

Adressテーブルを年齢の昇順で並べる。

CASE WHEN @before_line_age = age THEN @rank
 ELSE @rank:=@rank+1
 END AS rank,

一つ上のage(@before_line_age)と該当のageが等しいか判定し、CASE句で分岐。
等しい場合は@rankをそのまま渡し、等しくない場合は@rank+1の値を渡す。

小数点を含む比較の場合は注意
小数点を含む比較の場合、=では正しく比較できない場合がある。
その場合は、ABS()関数を使用して差分が一定数以下かを判定すると良い。
例:ABS(@before_line_X – X) < 0.01

@before_line_age:=age AS age

次の行の計算で使用するために、@before_line_ageにageの値を代入しておく。

まとめ

MySQLだけでランキングを表示したい事案があるかは不明だが、やってみると面白かった。
この試みをしたことで、MySQLでも以下の事ができることを知れた。

  • ユーザー定数
  • CASE~WHEN
  • :=での代入

最後に

最後までお読みいただき、ありがとうございます。

私は未経験で11月から働き始めたばかりで分からないことだらけです。
ですので、「もっとこうした方が良い」「ここは違う」等アドバイスをいただけると大変助かります。