ウィンドウ関数(分析関数)をまとめてみた

# はじめに
他のまとめ記事もたくさんありますが、勉強も兼ねて改めてウィンドウ関数(分析関数)についてまとめてみました。
今回は `Mysql8.x` 系を対象として、記事を作成しています。

# ウィンドウ関数とは？
ウィンドウ関数についての説明は、下記の方の記事がとてもわかりやすかったです。
[ウィンドウ関数（分析関数）をわかりやすく説明してみた
](https://qiita.com/tlokweng/items/fc13dc30cc1aa28231c5)
また、ウィンドウ関数を以前よりサポートしている[PostgreSQLの公式ドキュメント](https://www.postgresql.jp/document/11/html/tutorial-window.html)上には、下記のような文言が書いてありました。

```
ウィンドウ関数は現在の行に何らかとも関係するテーブル行の集合に渡って計算を行います。
ウィンドウ関数は問い合わせ結果による現在行だけでなく、それ以上の行にアクセスすることができます。
```

文言だけ見ても若干イメージしづらいですね。
ということで、実際に試していきたいと思います。

# その前に
ウィンドウ関数を理解するには、 `ウィンドウフレーム` という概念を知っておくとよさそうです。

# ウィンドウフレームとは？
PostgreSQLの公式ドキュメントには、下記の説明文が記述してあります。

```
ウィンドウ関数に関連した別の重要な概念があります。 
それぞれの行に対して、そのウィンドウフレームと呼ばれる、そのパーティション内の行の集合が存在します。 
ウィンドウ関数の中には、パーティション全体ではなく、ウィンドウフレームの行のみに対して作用するものもあります。
```

若干わかりづらいですが、ウィンドウ関数はウィンドウフレームと呼ばれる部分を定義して、
そのフレーム内の行を対象として、関数が実行されます。
フレームに関して詳しく知りたい方は、下記の方の記事が非常に参考になりましたので、そちらを参考にして頂けると良いかと思います。
(当記事では詳しく触れません。）
https://masahikosawada.github.io/2018/07/07/Window-Frame/

# 今回使用するテーブルのスキーマ
下記のテーブルを使用します。

```SQL
CREATE TABLE `emp_salary` (
  `dep_name` varchar(255) NOT NULL, -- 部署名
  `emp_no` int(11) NOT NULL, -- 従業員ID
  `salary` int(11) NOT NULL  -- 給与
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_general_ci
;
```

また、サンプルとして下記データを登録しておきます。

```SQL
INSERT INTO emp_salary 
VALUE ('営業',1,15000000)
     ,('営業',2,6800000)
     ,('営業',3,9200000)
     ,('営業',4,8500000)
     ,('営業',5,5400000)
     ,('営業',6,9200000)     
     ,('営業',7,8500000)
     ,('システム開発',8,8200000)
     ,('システム開発',9,9800000)
     ,('システム開発',10,5400000)
     ,('システム開発',11,3900000)
     ,('システム開発',12,4600000)
     ,('システム開発',13,5400000)
     ,('システム開発',14,8200000)
     ,('マーケティング',15,5600000)
     ,('マーケティング',16,6200000)
     ,('マーケティング',17,3700000)
     ,('マーケティング',18,4400000)
     ,('マーケティング',19,6200000)
     ,('マーケティング',20,4200000)
     ,('マーケティング',21,4200000)
;
```

# ウィンドウ関数の構文について
ウィンドウ関数は下記のような構文で記述します。
下記の例(よく使われてる例)では、従業員の部署名、給与と、所属部署に対しての平均給与を算出しています。

```sql
SELECT 
    dep_name -- 部署名
  , emp_no   -- 従業員ID
  , salary  -- 給与
  , avg(salary) OVER (PARTITION BY dep_name) -- ここがウインドウ関数。部署名毎に区間(ウィンドウフレーム)を区切り、給与の平均を算出している。
FROM 
  emp_salary;
```

# 今回試す関数
下記関数を試していきたいと思います。

|関数名　　　　　　　　　|説明　　　　　　　　　　　　　|
|:-------------------|:------------------------|
|DENSE_RANK　|order by で指定した順にソートした際の順位を返します。<br />同率があった場合順位は同じになり、その次は順位を飛ばしません。|
|FIRST_VALUE　|ソート項目でソートした結果から最初の値を返します。|
|LAG　|ソート項目でソートして現在行からN行前の値を返します。先行する行が存在しない場合はNULLを返します。|
|NTH_VALUE　|ソート項目でソートした結果からN番目の値を返します。|
|NTILE　|ソート項目でソートした結果を指定数でグループ分けします。|
|RANK　|DENSE_RANK（）関数と同様ですが、同率があった場合順位は同じになり、その次は順位を飛ばします。|
|ROW_NUMBER　|行番号を返します。行番号は、ソート項目でソートした結果に対して設定されます。|


## DENSE_RANK
order by で指定した順にソートした際の順位を返します。
同率があった場合順位は同じになり、その次は順位を飛ばしません。

### 実行するクエリ

```sql
SELECT 
    dep_name
  , emp_no
  , salary
  , DENSE_RANK() OVER (PARTITION BY dep_name order by salary) AS d_rank
FROM 
  emp_salary
;
```

### 結果

`dep_name`を区切りとして、 `salary` の昇順で順位付けした結果を返しています。

```sql
+-----------------------+--------+----------+--------+
| dep_name              | emp_no | salary   | d_rank |
+-----------------------+--------+----------+--------+
| システム開発            |     11 |  3900000 |      1 |
| システム開発            |     12 |  4600000 |      2 |
| システム開発            |     10 |  5400000 |      3 |
| システム開発            |     13 |  5400000 |      3 |
| システム開発            |      8 |  8200000 |      4 |
| システム開発            |     14 |  8200000 |      4 |
| システム開発            |      9 |  9800000 |      5 |
| マーケティング           |     17 |  3700000 |      1 |
| マーケティング           |     20 |  4200000 |      2 |
| マーケティング           |     21 |  4200000 |      2 |
| マーケティング           |     18 |  4400000 |      3 |
| マーケティング           |     15 |  5600000 |      4 |
| マーケティング           |     16 |  6200000 |      5 |
| マーケティング           |     19 |  6200000 |      5 |
| 営業                  |      5 |  5400000 |      1 |
| 営業                  |      2 |  6800000 |      2 |
| 営業                  |      4 |  8500000 |      3 |
| 営業                  |      7 |  8500000 |      3 |
| 営業                  |      3 |  9200000 |      4 |
| 営業                  |      6 |  9200000 |      4 |
| 営業                  |      1 | 15000000 |      5 |
+-----------------------+--------+----------+--------+
```

## FIRST_VALUE
ソート項目でソートした結果から最初の値を返します。

### 実行するクエリ

```sql
SELECT 
    dep_name
  , emp_no
  , salary
  , FIRST_VALUE(salary) OVER (PARTITION BY dep_name order by salary) AS flrst_val
FROM 
  emp_salary
;
```

### 結果

`dep_name`を区切りとして、 `salary` の昇順で並べた中の、最初の値を返しています。

```sql
+-----------------------+--------+----------+-----------+
| dep_name              | emp_no | salary   | flrst_val |
+-----------------------+--------+----------+-----------+
| システム開発            |     11 |  3900000 |   3900000 |
| システム開発            |     12 |  4600000 |   3900000 |
| システム開発            |     10 |  5400000 |   3900000 |
| システム開発            |     13 |  5400000 |   3900000 |
| システム開発            |      8 |  8200000 |   3900000 |
| システム開発            |     14 |  8200000 |   3900000 |
| システム開発            |      9 |  9800000 |   3900000 |
| マーケティング           |     17 |  3700000 |   3700000 |
| マーケティング           |     20 |  4200000 |   3700000 |
| マーケティング           |     21 |  4200000 |   3700000 |
| マーケティング           |     18 |  4400000 |   3700000 |
| マーケティング           |     15 |  5600000 |   3700000 |
| マーケティング           |     16 |  6200000 |   3700000 |
| マーケティング           |     19 |  6200000 |   3700000 |
| 営業                  |      5 |  5400000 |   5400000 |
| 営業                  |      2 |  6800000 |   5400000 |
| 営業                  |      4 |  8500000 |   5400000 |
| 営業                  |      7 |  8500000 |   5400000 |
| 営業                  |      3 |  9200000 |   5400000 |
| 営業                  |      6 |  9200000 |   5400000 |
| 営業                  |      1 | 15000000 |   5400000 |
+-----------------------+--------+----------+-----------+
```

## LAG
ソート項目でソートして現在行からN行前の値を返します。先行する行が存在しない場合はNULLを返します。

### 実行するクエリ

```sql
SELECT 
    dep_name
  , emp_no
  , salary
  , LAG(salary, 2, 'none') OVER (PARTITION BY dep_name order by salary) AS lag_val
FROM 
  emp_salary
;
```

### 結果

`dep_name`を区切りとして、 `salary` の昇順で並べた中で、2つ前の値を取得しています。
存在しない場合は、noneを返しています。

```sql
+-----------------------+--------+----------+---------+
| dep_name              | emp_no | salary   | lag_val |
+-----------------------+--------+----------+---------+
| システム開発            |     11 |  3900000 | none    |
| システム開発            |     12 |  4600000 | none    |
| システム開発            |     10 |  5400000 | 3900000 |
| システム開発            |     13 |  5400000 | 4600000 |
| システム開発            |      8 |  8200000 | 5400000 |
| システム開発            |     14 |  8200000 | 5400000 |
| システム開発            |      9 |  9800000 | 8200000 |
| マーケティング           |     17 |  3700000 | none    |
| マーケティング           |     20 |  4200000 | none    |
| マーケティング           |     21 |  4200000 | 3700000 |
| マーケティング           |     18 |  4400000 | 4200000 |
| マーケティング           |     15 |  5600000 | 4200000 |
| マーケティング           |     16 |  6200000 | 4400000 |
| マーケティング           |     19 |  6200000 | 5600000 |
| 営業                  |      5 |  5400000 | none    |
| 営業                  |      2 |  6800000 | none    |
| 営業                  |      4 |  8500000 | 5400000 |
| 営業                  |      7 |  8500000 | 6800000 |
| 営業                  |      3 |  9200000 | 8500000 |
| 営業                  |      6 |  9200000 | 8500000 |
| 営業                  |      1 | 15000000 | 9200000 |
+-----------------------+--------+----------+---------+
```

## LEAD
ソート項目でソートして現在行からN行後の値を返します。後続の行が存在しない場合はNULLを返します。

### 実行するクエリ

```sql
SELECT 
    dep_name
  , emp_no
  , salary
  , LEAD(salary, 2, 'none') OVER (PARTITION BY dep_name order by salary) AS lead_val
FROM 
  emp_salary
;
```

### 結果

`dep_name`を区切りとして、 `salary` の昇順で並べた中で、2つ後の値を取得しています。
存在しない場合は、noneを返しています。

```sql
+-----------------------+--------+----------+----------+
| dep_name              | emp_no | salary   | lead_val |
+-----------------------+--------+----------+----------+
| システム開発            |     11 |  3900000 | 5400000  |
| システム開発            |     12 |  4600000 | 5400000  |
| システム開発            |     10 |  5400000 | 8200000  |
| システム開発            |     13 |  5400000 | 8200000  |
| システム開発            |      8 |  8200000 | 9800000  |
| システム開発            |     14 |  8200000 | none     |
| システム開発            |      9 |  9800000 | none     |
| マーケティング           |     17 |  3700000 | 4200000  |
| マーケティング           |     20 |  4200000 | 4400000  |
| マーケティング           |     21 |  4200000 | 5600000  |
| マーケティング           |     18 |  4400000 | 6200000  |
| マーケティング           |     15 |  5600000 | 6200000  |
| マーケティング           |     16 |  6200000 | none     |
| マーケティング           |     19 |  6200000 | none     |
| 営業                  |      5 |  5400000 | 8500000  |
| 営業                  |      2 |  6800000 | 8500000  |
| 営業                  |      4 |  8500000 | 9200000  |
| 営業                  |      7 |  8500000 | 9200000  |
| 営業                  |      3 |  9200000 | 15000000 |
| 営業                  |      6 |  9200000 | none     |
| 営業                  |      1 | 15000000 | none     |
+-----------------------+--------+----------+----------+
```

## NTH_VALUE
ソート項目でソートした結果からN番目の値を返します。

### 実行するクエリ

```sql
SELECT 
    dep_name
  , emp_no
  , salary
  , NTH_VALUE(salary, 2) OVER (PARTITION BY dep_name order by salary) AS nth_val
FROM 
  emp_salary
;
```

### 結果

`dep_name`を区切りとして、 `salary` の昇順で並べた中で、2つ目の値を取得しています。

```sql
+-----------------------+--------+----------+---------+
| dep_name              | emp_no | salary   | nth_val |
+-----------------------+--------+----------+---------+
| システム開発            |     11 |  3900000 |    NULL |
| システム開発            |     12 |  4600000 | 4600000 |
| システム開発            |     10 |  5400000 | 4600000 |
| システム開発            |     13 |  5400000 | 4600000 |
| システム開発            |      8 |  8200000 | 4600000 |
| システム開発            |     14 |  8200000 | 4600000 |
| システム開発            |      9 |  9800000 | 4600000 |
| マーケティング           |     17 |  3700000 |    NULL |
| マーケティング           |     20 |  4200000 | 4200000 |
| マーケティング           |     21 |  4200000 | 4200000 |
| マーケティング           |     18 |  4400000 | 4200000 |
| マーケティング           |     15 |  5600000 | 4200000 |
| マーケティング           |     16 |  6200000 | 4200000 |
| マーケティング           |     19 |  6200000 | 4200000 |
| 営業                  |      5 |  5400000 |    NULL |
| 営業                  |      2 |  6800000 | 6800000 |
| 営業                  |      4 |  8500000 | 6800000 |
| 営業                  |      7 |  8500000 | 6800000 |
| 営業                  |      3 |  9200000 | 6800000 |
| 営業                  |      6 |  9200000 | 6800000 |
| 営業                  |      1 | 15000000 | 6800000 |
+-----------------------+--------+----------+---------+
```

## NTILE
ソート項目でソートした結果を指定数でグループ分けします。

### 実行するクエリ

```sql
SELECT 
    dep_name
  , emp_no
  , salary
  , NTILE(4) OVER(ORDER BY salary) AS ntile_val
FROM 
  emp_salary
;
```

### 結果

`salary` の昇順で並べた中で、4つのグルーピングを行っています。

```sql
+-----------------------+--------+----------+-----------+
| dep_name              | emp_no | salary   | ntile_val |
+-----------------------+--------+----------+-----------+
| マーケティング           |     17 |  3700000 |         1 |
| システム開発            |     11 |  3900000 |         1 |
| マーケティング           |     20 |  4200000 |         1 |
| マーケティング           |     21 |  4200000 |         1 |
| マーケティング           |     18 |  4400000 |         1 |
| システム開発            |     12 |  4600000 |         1 |
| 営業                  |      5 |  5400000 |         2 |
| システム開発            |     10 |  5400000 |         2 |
| システム開発            |     13 |  5400000 |         2 |
| マーケティング           |     15 |  5600000 |         2 |
| マーケティング           |     16 |  6200000 |         2 |
| マーケティング           |     19 |  6200000 |         3 |
| 営業                  |      2 |  6800000 |         3 |
| システム開発            |      8 |  8200000 |         3 |
| システム開発            |     14 |  8200000 |         3 |
| 営業                  |      4 |  8500000 |         3 |
| 営業                  |      7 |  8500000 |         4 |
| 営業                  |      3 |  9200000 |         4 |
| 営業                  |      6 |  9200000 |         4 |
| システム開発            |      9 |  9800000 |         4 |
| 営業                  |      1 | 15000000 |         4 |
+-----------------------+--------+----------+-----------+
```

## RANK　
DENSE_RANK（）関数と同様ですが、同率があった場合順位は同じになり、その次は順位を飛ばします。

### 実行するクエリ

```sql
SELECT 
    dep_name
  , emp_no
  , salary
  , RANK() OVER (PARTITION BY dep_name order by salary) AS rank_val
FROM 
  emp_salary
;
```

### 結果

`dep_name`を区切りとして、 `salary` の昇順で並べたものを、順位付けします。
`salary` が同じ場合は、同一順位になります。

```sql
+-----------------------+--------+----------+----------+
| dep_name              | emp_no | salary   | rank_val |
+-----------------------+--------+----------+----------+
| システム開発            |     11 |  3900000 |        1 |
| システム開発            |     12 |  4600000 |        2 |
| システム開発            |     10 |  5400000 |        3 |
| システム開発            |     13 |  5400000 |        3 |
| システム開発            |      8 |  8200000 |        5 |
| システム開発            |     14 |  8200000 |        5 |
| システム開発            |      9 |  9800000 |        7 |
| マーケティング           |     17 |  3700000 |        1 |
| マーケティング           |     20 |  4200000 |        2 |
| マーケティング           |     21 |  4200000 |        2 |
| マーケティング           |     18 |  4400000 |        4 |
| マーケティング           |     15 |  5600000 |        5 |
| マーケティング           |     16 |  6200000 |        6 |
| マーケティング           |     19 |  6200000 |        6 |
| 営業                  |      5 |  5400000 |        1 |
| 営業                  |      2 |  6800000 |        2 |
| 営業                  |      4 |  8500000 |        3 |
| 営業                  |      7 |  8500000 |        3 |
| 営業                  |      3 |  9200000 |        5 |
| 営業                  |      6 |  9200000 |        5 |
| 営業                  |      1 | 15000000 |        7 |
+-----------------------+--------+----------+----------+
```

## ROW_NUMBER　
行番号を返します。行番号は、ソート項目でソートした結果に対して設定されます。

### 実行するクエリ

```sql
SELECT 
    dep_name
  , emp_no
  , salary
  , ROW_NUMBER() OVER (PARTITION BY dep_name order by salary) AS rownum_val
FROM 
  emp_salary
;
```

### 結果

`dep_name`を区切りとして、 `salary` の昇順で並べたものの、行番号を返しています。

```sql
+-----------------------+--------+----------+------------+
| dep_name              | emp_no | salary   | rownum_val |
+-----------------------+--------+----------+------------+
| システム開発            |     11 |  3900000 |          1 |
| システム開発            |     12 |  4600000 |          2 |
| システム開発            |     10 |  5400000 |          3 |
| システム開発            |     13 |  5400000 |          4 |
| システム開発            |      8 |  8200000 |          5 |
| システム開発            |     14 |  8200000 |          6 |
| システム開発            |      9 |  9800000 |          7 |
| マーケティング           |     17 |  3700000 |          1 |
| マーケティング           |     20 |  4200000 |          2 |
| マーケティング           |     21 |  4200000 |          3 |
| マーケティング           |     18 |  4400000 |          4 |
| マーケティング           |     15 |  5600000 |          5 |
| マーケティング           |     16 |  6200000 |          6 |
| マーケティング           |     19 |  6200000 |          7 |
| 営業                  |      5 |  5400000 |          1 |
| 営業                  |      2 |  6800000 |          2 |
| 営業                  |      4 |  8500000 |          3 |
| 営業                  |      7 |  8500000 |          4 |
| 営業                  |      3 |  9200000 |          5 |
| 営業                  |      6 |  9200000 |          6 |
| 営業                  |      1 | 15000000 |          7 |
+-----------------------+--------+----------+------------+
```

# 最後に
いかがでしたでしょうか？
自身として使ったことないものもいくつかあり、良い勉強になりました。
分析・集計時にも非常に便利ですし、過去にレコメンドエンジンを作っていたときにも、非常にお世話になっていました。
使いこなせれば非常に有益な関数だと思いますので、是非ご参考にしてください。

お付き合い頂きありがとうございました！

# 共に働くWebエンジニアを募集しています!
不動産SHOPナカジツでは自社サービスを作っていく仲間を募集しています。
詳しくは[Wantedly](https://www.wantedly.com/projects/134741)からお問い合わせください。
お待ちしております！