勉強したことのメモ

Webエンジニア / プログラマが勉強したことのメモ。

MySQLのRANK及びDENSE_RANK関数を用いてランキングを実装する方法

  MySQL データベース

ランキング機能を実装する際、上位〇位までのデータをMySQLから持ってきて順位付けはPHP側で行っていた。ただ、MySQL8からはRANK及びDENSE_RANKという関数が実装され、こちらを用いることでMySQLのみで対応できるみたい。以下に対応方法をメモ。

 

リファレンス

RANK

https://dev.mysql.com/doc/refman/8.0/ja/window-function-descriptions.html#function_rank

DENSE_RANK

https://dev.mysql.com/doc/refman/8.0/ja/window-function-descriptions.html#function_dense-rank

 

テーブル情報

テーブル構造

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
mysql> SHOW COLUMNS FROM `test_table`;
+-------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+----------------+
| id | int | NO | PRI | NULL | auto_increment |
| name | varchar(255) | NO | | NULL | |
| score | int | NO | | NULL | |
+-------+--------------+------+-----+---------+----------------+
mysql> SHOW COLUMNS FROM `test_table`; +-------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+--------------+------+-----+---------+----------------+ | id | int | NO | PRI | NULL | auto_increment | | name | varchar(255) | NO | | NULL | | | score | int | NO | | NULL | | +-------+--------------+------+-----+---------+----------------+
mysql> SHOW COLUMNS FROM `test_table`;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int          | NO   | PRI | NULL    | auto_increment |
| name  | varchar(255) | NO   |     | NULL    |                |
| score | int          | NO   |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+

データ内容

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
mysql> SELECT * FROM `test_table`;
+----+-----------+-------+
| id | name | score |
+----+-----------+-------+
| 1 | 鈴木 | 50 |
| 2 | 山田 | 60 |
| 3 | 佐々木 | 70 |
| 4 | 佐藤 | 40 |
| 5 | 渡辺 | 60 |
| 6 | 高橋 | 80 |
+----+-----------+-------+
mysql> SELECT * FROM `test_table`; +----+-----------+-------+ | id | name | score | +----+-----------+-------+ | 1 | 鈴木 | 50 | | 2 | 山田 | 60 | | 3 | 佐々木 | 70 | | 4 | 佐藤 | 40 | | 5 | 渡辺 | 60 | | 6 | 高橋 | 80 | +----+-----------+-------+
mysql> SELECT * FROM `test_table`;
+----+-----------+-------+
| id | name      | score |
+----+-----------+-------+
|  1 | 鈴木      |    50 |
|  2 | 山田      |    60 |
|  3 | 佐々木    |    70 |
|  4 | 佐藤      |    40 |
|  5 | 渡辺      |    60 |
|  6 | 高橋      |    80 |
+----+-----------+-------+

 

実装方法

同率順位があった場合、次の順位は＋2とする場合(RANK)

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
SELECT *, RANK() OVER (ORDER BY `score`) AS `rank`
FROM `test_table`;
+----+-----------+-------+------+
| id | name | score | rank |
+----+-----------+-------+------+
| 4 | 佐藤 | 40 | 1 |
| 1 | 鈴木 | 50 | 2 |
| 2 | 山田 | 60 | 3 |
| 5 | 渡辺 | 60 | 3 |
| 3 | 佐々木 | 70 | 5 |
| 6 | 高橋 | 80 | 6 |
+----+-----------+-------+------+
SELECT *, RANK() OVER (ORDER BY `score`) AS `rank` FROM `test_table`; +----+-----------+-------+------+ | id | name | score | rank | +----+-----------+-------+------+ | 4 | 佐藤 | 40 | 1 | | 1 | 鈴木 | 50 | 2 | | 2 | 山田 | 60 | 3 | | 5 | 渡辺 | 60 | 3 | | 3 | 佐々木 | 70 | 5 | | 6 | 高橋 | 80 | 6 | +----+-----------+-------+------+
SELECT *, RANK() OVER (ORDER BY `score`) AS `rank` 
FROM `test_table`;

+----+-----------+-------+------+
| id | name      | score | rank |
+----+-----------+-------+------+
|  4 | 佐藤      |    40 |    1 |
|  1 | 鈴木      |    50 |    2 |
|  2 | 山田      |    60 |    3 |
|  5 | 渡辺      |    60 |    3 |
|  3 | 佐々木    |    70 |    5 |
|  6 | 高橋      |    80 |    6 |
+----+-----------+-------+------+

同率順位があっても次の順位は+1とする場合(DENSE_RANK)

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
SELECT *, DENSE_RANK() OVER (ORDER BY `score`) AS `rank`
FROM `test_table`;
+----+-----------+-------+------+
| id | name | score | rank |
+----+-----------+-------+------+
| 4 | 佐藤 | 40 | 1 |
| 1 | 鈴木 | 50 | 2 |
| 2 | 山田 | 60 | 3 |
| 5 | 渡辺 | 60 | 3 |
| 3 | 佐々木 | 70 | 4 |
| 6 | 高橋 | 80 | 5 |
+----+-----------+-------+------+
SELECT *, DENSE_RANK() OVER (ORDER BY `score`) AS `rank` FROM `test_table`; +----+-----------+-------+------+ | id | name | score | rank | +----+-----------+-------+------+ | 4 | 佐藤 | 40 | 1 | | 1 | 鈴木 | 50 | 2 | | 2 | 山田 | 60 | 3 | | 5 | 渡辺 | 60 | 3 | | 3 | 佐々木 | 70 | 4 | | 6 | 高橋 | 80 | 5 | +----+-----------+-------+------+
SELECT *, DENSE_RANK() OVER (ORDER BY `score`) AS `rank` 
FROM `test_table`;

+----+-----------+-------+------+
| id | name      | score | rank |
+----+-----------+-------+------+
|  4 | 佐藤      |    40 |    1 |
|  1 | 鈴木      |    50 |    2 |
|  2 | 山田      |    60 |    3 |
|  5 | 渡辺      |    60 |    3 |
|  3 | 佐々木    |    70 |    4 |
|  6 | 高橋      |    80 |    5 |
+----+-----------+-------+------+

 - MySQL データベース

  関連記事

MySQLにて直近のクエリで発行されたAUTO_INCREMENTの値を取得する方法

やりたかったことは「MySQLで新規登録(INSERT)なら、登録時のAUTO_ ...

MySQLでIPアドレスを数値として入れると変な値になる場合の対処方法

MySQLでIPアドレスをINET_ATON関数を用いてINT型に変換し格納する ...

MySQLで全国の市区町村のデータを入手し、抽出する方法

都道府県と市区町村が連携したセレクトメニューを作成したかった。具体的には大阪府を ...

mysqlで正規化できない時はシリアライズ使う

アンケートか何かに機能追加している際、配列を カンマ区切りで結合して文字列でDB ...

MySQLでtext型カラムに入っている数値をint型としてソートする

MySQLでtext型として指定されているカラムがあり、その中には文字列であった ...

S