はじめに
MySQLには知っておくと便利な関数がさまざまあります。
今回は、MySQL で Version 8.0 以降で使えるようになったROW_NUMBER()関数 について取り上げてみます。
ROW_NUMBER()とは
SELCET文の結果セットに対して、連番を振ることのできる関数です。
MySQL以外の主要なRDS製品（Oracle、PostgreSQL等）では使えますが、MySQL Version 5.0 では使えませんでした。
グループ毎で、それぞれの上位順に並べたいときに便利です。
例として実際に使ってみます。
サンプル
MySQLバージョン
> mysql --version
C:\Program Files\MySQL\MySQL Server 8.0\bin\mysql.exe Ver 8.0.31 for Win64 on x86_64
データ
+--------+--------+------+------------+
| name | gender | age | department |
+--------+--------+------+------------+
| 田中 | 0 | 40 | 人事部 |
| 加藤 | 1 | 32 | 総務部 |
| 佐藤 | 0 | 23 | 経理部 |
| 渡辺 | 1 | 26 | 営業部 |
| 山田 | 0 | 23 | 開発部 |
| 伊藤 | 1 | 37 | 人事部 |
| 小林 | 0 | 24 | 総務部 |
| 高橋 | 1 | 28 | 経理部 |
| 吉田 | 0 | 35 | 営業部 |
| 鈴木 | 1 | 33 | 開発部 |
| 山本 | 0 | 39 | 人事部 |
| 中村 | 1 | 37 | 総務部 |
| 佐々木 | 0 | 44 | 経理部 |
| 松本 | 1 | 23 | 営業部 |
| 木村 | 0 | 31 | 開発部 |
+--------+--------+------+------------+
※テーブル定義（employee：テーブル名）
name：名前
gender：性別（0...男、1...女）
age：年齢
department：部署
例１）年齢が高い順に並べる
SQL
SELECT ROW_NUMBER() OVER (ORDER BY age DESC) AS row, name, gender, age, department FROM test.employee;
結果
+--------+--------+--------+------+------------+
| row_no | name | gender | age | department |
+--------+--------+--------+------+------------+
| 1 | 佐々木 | 0 | 44 | 経理部 |
| 2 | 田中 | 0 | 40 | 人事部 |
| 3 | 山本 | 0 | 39 | 人事部 |
| 4 | 伊藤 | 1 | 37 | 人事部 |
| 5 | 中村 | 1 | 37 | 総務部 |
| 6 | 吉田 | 0 | 35 | 営業部 |
| 7 | 鈴木 | 1 | 33 | 開発部 |
| 8 | 加藤 | 1 | 32 | 総務部 |
| 9 | 木村 | 0 | 31 | 開発部 |
| 10 | 高橋 | 1 | 28 | 経理部 |
| 11 | 渡辺 | 1 | 26 | 営業部 |
| 12 | 小林 | 0 | 24 | 総務部 |
| 13 | 佐藤 | 0 | 23 | 経理部 |
| 14 | 山田 | 0 | 23 | 開発部 |
| 15 | 松本 | 1 | 23 | 営業部 |
+--------+--------+--------+------+------------+
この例だと通常のORDER BYでも簡単に取り出すことができますが、次の例からROW_NUMBER()が活躍します。
例２）性別毎に年齢が高い順に並べる
SQL
SELECT ROW_NUMBER() OVER (PARTITION BY gender ORDER BY age DESC) AS row_no, name, gender, age, department FROM test.employee;
結果
+--------+--------+--------+------+------------+
| row_no | name | gender | age | department |
+--------+--------+--------+------+------------+
| 1 | 佐々木 | 0 | 44 | 経理部 |
| 2 | 田中 | 0 | 40 | 人事部 |
| 3 | 山本 | 0 | 39 | 人事部 |
| 4 | 吉田 | 0 | 35 | 営業部 |
| 5 | 木村 | 0 | 31 | 開発部 |
| 6 | 小林 | 0 | 24 | 総務部 |
| 7 | 佐藤 | 0 | 23 | 経理部 |
| 8 | 山田 | 0 | 23 | 開発部 |
| 1 | 伊藤 | 1 | 37 | 人事部 |
| 2 | 中村 | 1 | 37 | 総務部 |
| 3 | 鈴木 | 1 | 33 | 開発部 |
| 4 | 加藤 | 1 | 32 | 総務部 |
| 5 | 高橋 | 1 | 28 | 経理部 |
| 6 | 渡辺 | 1 | 26 | 営業部 |
| 7 | 松本 | 1 | 23 | 営業部 |
+--------+--------+--------+------+------------+
PARTITION BY [カラム名]で指定することでグループ毎に並べることができます。
例３）部署毎に年齢が高い順に並べる
SQL
SELECT ROW_NUMBER() OVER (PARTITION BY department ORDER BY age DESC) AS row_no, name, gender, age, department FROM test.employee;
結果
+--------+--------+--------+------+------------+
| row_no | name | gender | age | department |
+--------+--------+--------+------+------------+
| 1 | 田中 | 0 | 40 | 人事部 |
| 2 | 山本 | 0 | 39 | 人事部 |
| 3 | 伊藤 | 1 | 37 | 人事部 |
| 1 | 吉田 | 0 | 35 | 営業部 |
| 2 | 渡辺 | 1 | 26 | 営業部 |
| 3 | 松本 | 1 | 23 | 営業部 |
| 1 | 佐々木 | 0 | 44 | 経理部 |
| 2 | 高橋 | 1 | 28 | 経理部 |
| 3 | 佐藤 | 0 | 23 | 経理部 |
| 1 | 中村 | 1 | 37 | 総務部 |
| 2 | 加藤 | 1 | 32 | 総務部 |
| 3 | 小林 | 0 | 24 | 総務部 |
| 1 | 鈴木 | 1 | 33 | 開発部 |
| 2 | 木村 | 0 | 31 | 開発部 |
| 3 | 山田 | 0 | 23 | 開発部 |
+--------+--------+--------+------+------------+
応用）部署毎に一番年齢が高い人（上位1件）を取り出す
先ほどのROW_NUMBER()で並べたクエリをサブクエリとすれば、あとは条件を指定するだけで簡単に取り出せます。
SQL
SELECT t1.name, t1.gender, t1.age, t1.department FROM (SELECT ROW_NUMBER() OVER (PARTITION BY department ORDER BY age DESC) AS row_no, name, gender, age, department FROM test.employee) AS t1 WHERE t1.row_no = 1;
結果
+--------+--------+------+------------+
| name | gender | age | department |
+--------+--------+------+------------+
| 田中 | 0 | 40 | 人事部 |
| 吉田 | 0 | 35 | 営業部 |
| 佐々木 | 0 | 44 | 経理部 |
| 中村 | 1 | 37 | 総務部 |
| 鈴木 | 1 | 33 | 開発部 |
+--------+--------+------+------------+
おわりに
ROW_NUMBER()を使えば、グループ毎に上位〇件を簡単に取り出せます。
せっかくMySQLでも使えるようになったので、活用していきたいですね。
参考