MySQL8.0で便利なROW_NUMBER()を使ってみた

# はじめに
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でも使えるようになったので、活用していきたいですね。

# 参考

https://mysql.sql55.com/sql/mysql-row-number.php