Edited at
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月から働き始めたばかりで分からないことだらけです。

ですので、「もっとこうした方が良い」「ここは違う」等アドバイスをいただけると大変助かります。