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月から働き始めたばかりで分からないことだらけです。
ですので、「もっとこうした方が良い」「ここは違う」等アドバイスをいただけると大変助かります。