[MySQL]window関数を紐解くexisting_window_name

# はじめに

Cloud SQLでもMySQL8がサポートされ、window関数が使えるようになりました。
window関数の基礎的な概念をまとめようと思います。

公式ドキュメントはこちらです。https://dev.mysql.com/doc/refman/8.0/en/window-functions.html

# 環境

MySQL: ^8.0.0

# まずはふわっとwindow関数を使ってみる

window関数の定義を公式ドキュメントから引用すると、以下のような定義だそうです。

> ウィンドウ関数は、一連のクエリ行に対して集計のような操作を実行します。ただし、集計操作はクエリ行を1つの結果行にグループ化しますが、ウィンドウ関数はクエリ行ごとに結果を生成します。

集計のような操作を行うそうですが、定義だけではわかりにくいので実際にwindow関数を使ってみましょう。

使うデータは以下の通りです。

従業員のID,名前,部署,役職、給与を保存しているテーブルです。

| employee_id | employee_name      | department | position | salary   | 
| ----------- | ------------------ | ---------- | -------- | -------- | 
| 1           | Nakajima Atsushi   | human reso | Manager  | 8000000  | 
| 2           | Nakamura Mai       | human reso | Manager  | 65000000 | 
| 3           | Yamamoto Youko     | human reso | Director | 6000000  | 
| 4           | Hukuda Kyoko       | human reso | Director | 5000000  | 
| 5           | Kobayashi Takahiro | human reso | Director | 45000000 | 
| 6           | Satou Osamu        | human reso | Member   | 4300000  | 
| 7           | Suzuki Haruka      | human reso | Member   | 4000000  | 
| 8           | Matsuda Kana       | human reso | Member   | 4100000  | 
| 9           | Kimura Sayuri      | human reso | Member   | 4400000  | 
| 10          | Endo Syota         | Legal      | Manager  | 9000000  | 
| 11          | Suzuki Yuka        | Legal      | Director | 7000000  | 
| 12          | Tanaka Maya        | Legal      | Director | 7600000  | 
| 13          | Hayasi Asuka       | Legal      | Member   | 5500000  | 
| 14          | Takahasi Noboru    | Legal      | Member   | 5400000  | 
| 15          | Tezuka Kei         | Legal      | Member   | 5300000  | 

各従業員は以下のようになっています

- 各従業員はhuman reso, Legalのいずれかの部署に所属する。
- 各従業員はManager, Director, Memberのいずれかの役職に付いている

window関数を使って見ます。

```sql
SELECT
    employee_id,
    employee_name,
    department,
    position,
    salary,
    AVG(salary) OVER(PARTITION BY department) AS salary_average
FROM employee;

| employee_id | employee_name      | department | position | salary   | salary_average | 
| ----------- | ------------------ | ---------- | -------- | -------- | -------------- | 
| 1           | Nakajima Atsushi   | human reso | Manager  | 8000000  | 16200000       | 
| 2           | Nakamura Mai       | human reso | Manager  | 65000000 | 16200000       | 
| 3           | Yamamoto Youko     | human reso | Director | 6000000  | 16200000       | 
| 4           | Hukuda Kyoko       | human reso | Director | 5000000  | 16200000       | 
| 5           | Kobayashi Takahiro | human reso | Director | 45000000 | 16200000       | 
| 6           | Satou Osamu        | human reso | Member   | 4300000  | 16200000       | 
| 7           | Suzuki Haruka      | human reso | Member   | 4000000  | 16200000       | 
| 8           | Matsuda Kana       | human reso | Member   | 4100000  | 16200000       | 
| 9           | Kimura Sayuri      | human reso | Member   | 4400000  | 16200000       | 
| 10          | Endo Syota         | Legal      | Manager  | 9000000  | 6633333.333    | 
| 11          | Suzuki Yuka        | Legal      | Director | 7000000  | 6633333.333    | 
| 12          | Tanaka Maya        | Legal      | Director | 7600000  | 6633333.333    | 
| 13          | Hayasi Asuka       | Legal      | Member   | 5500000  | 6633333.333    | 
| 14          | Takahasi Noboru    | Legal      | Member   | 5400000  | 6633333.333    | 
| 15          | Tezuka Kei         | Legal      | Member   | 5300000  | 6633333.333    | 

```

window関数をsalary_averageで使っていますが、各行にまるで部署でGROUP BYしAVG関数を使ったような値が入っています。

このように`集合関数をGROUP BYをせずに各行に結果を返す`処理が冒頭で引用した `ウィンドウ関数は、一連のクエリ行に対して集計のような操作を実行します。ただし、集計操作はクエリ行を1つの結果行にグループ化しますが、ウィンドウ関数はクエリ行ごとに結果を生成します`というものの実態です。

同様の結果は以下のようなGROUP BY句を使ったSQLで取得することもできます。
window関数を使った方が簡単にかけますね。

```sql
SELECT
    employee_id,
    employee_name,
    t.department,
    position,
    salary,
    salary_average
FROM employee
JOIN (
    SELECT
        department,
        AVG(salary) AS salary_average
    FROM
        employee
    GROUP BY department
    ) AS t
ON employee.department = t.department
```

なんとなく、window関数は集合関数を各行に返すものと理解していただけたと思いますが、まだまだふわっとしていると思いますので、詳しく見ていきましょう。

# window関数にはOVER句が必要

window関数のシンタックスを全て書くと以下のようになります。

```
window_function:
　　 [ existing_window_name ] [ over_clause ]

over_clause:
    {OVER (window_spec) | OVER window_name}

window_spec:
    [window_name] [partition_clause] [order_clause] [frame_clause]

partition_clause:
    PARTITION BY expr [, expr] ...

order_clause:
    ORDER BY expr [ASC|DESC] [, expr [ASC|DESC]] ...

frame_clause:
    frame_units frame_extent

frame_units:
    {ROWS | RANGE}

frame_extent:
    {frame_start | frame_between}

frame_between:
    BETWEEN frame_start AND frame_end

frame_start, frame_end: {
    CURRENT ROW
  | UNBOUNDED PRECEDING
  | UNBOUNDED FOLLOWING
  | expr PRECEDING
  | expr FOLLOWING
}
```

呪文のようですね。。。重要そうなところを見ていきましょう。

window_functionがwindow関数のシンタックスです。

```
window_function:
　　 [ existing_window_name ] [ over_clause ]
```

window関数はexisting_window_nameとover_clauseの二つの構文があれば成り立ちます。

- existing_window_name: 実行される関数です。最初のSQLではAVGを使いました。


```
over_clause:
    {OVER (window_spec) | OVER window_name}
```

- over_clause: window関数を構成する重要な構文の一つ。書き方は `OVER ()`と`名前付きウィンドウ`をという2つの書き方がある。ここでは主に `OVER ()`で書く方法について触れる。


```
window_spec:
    [window_name] [partition_clause] [order_clause] [frame_clause]
```

- window_spec: `OVER ()`の`()`の中で使われる。フレームを定義する。

ここまでの内容をまとめると、window関数は以下のような構文で成り立つことがわかります。

```
window関数() OVER (フレーム定義)
```


実際、一番最初に使ったSQLを例に出すと

```sql


    AVG(salary) OVER(PARTITION BY department) AS salary_average
        ^                              ^
   window関数             　　　　　フレーム定義
```
　
のようになっています。

さて、謎の言葉`フレーム`が出てきました。
次はフレームについて触れていきます。

# window関数の勘所,フレームの感覚を掴む

window関数にはフレームという概念があり、そのフレームに対してwindow関数が適用されます。

感覚を掴むために最初に使ったSQLのフレーム定義が実際にどのような動きをしているのか見ていきます。window関数部分のSQLは以下です。

```sql

    AVG(salary) OVER(PARTITION BY department) AS salary_average
```

`PARTITION BY department`は以下のようなフレームごとにデータを分割します。

```
| employee_id | employee_name      | department | position | salary   | 
| ----------- | ------------------ | ---------- | -------- | -------- | ---------
| 1           | Nakajima Atsushi   | human reso | Manager  | 8000000  | 
| 2           | Nakamura Mai       | human reso | Manager  | 65000000 | 
| 3           | Yamamoto Youko     | human reso | Director | 6000000  | 
| 4           | Hukuda Kyoko       | human reso | Director | 5000000  | 
| 5           | Kobayashi Takahiro | human reso | Director | 45000000 | フレーム①
| 6           | Satou Osamu        | human reso | Member   | 4300000  | 
| 7           | Suzuki Haruka      | human reso | Member   | 4000000  | 
| 8           | Matsuda Kana       | human reso | Member   | 4100000  | 
| 9           | Kimura Sayuri      | human reso | Member   | 4400000  |
| ----------- | ------------------ | ---------- | -------- | -------- | ---------
| 10          | Endo Syota         | Legal      | Manager  | 9000000  | 
| 11          | Suzuki Yuka        | Legal      | Director | 7000000  | 
| 12          | Tanaka Maya        | Legal      | Director | 7600000  |  フレーム②
| 13          | Hayasi Asuka       | Legal      | Member   | 5500000  | 
| 14          | Takahasi Noboru    | Legal      | Member   | 5400000  | 
| 15          | Tezuka Kei         | Legal      | Member   | 5300000  | 
```

そしてフレーム毎に`AVG(salary)`が実行され、それぞれの行に値が返されます。

```

| employee_id | employee_name      | department | position | salary   | salary_average | 
| ----------- | ------------------ | ---------- | -------- | -------- | -------------- | 
| 1           | Nakajima Atsushi   | human reso | Manager  | 8000000  | 16200000       | 
| 2           | Nakamura Mai       | human reso | Manager  | 65000000 | 16200000       | 
| 3           | Yamamoto Youko     | human reso | Director | 6000000  | 16200000       | 
| 4           | Hukuda Kyoko       | human reso | Director | 5000000  | 16200000       | 
| 5           | Kobayashi Takahiro | human reso | Director | 45000000 | 16200000       | 
| 6           | Satou Osamu        | human reso | Member   | 4300000  | 16200000       | 
| 7           | Suzuki Haruka      | human reso | Member   | 4000000  | 16200000       | 
| 8           | Matsuda Kana       | human reso | Member   | 4100000  | 16200000       | 
| 9           | Kimura Sayuri      | human reso | Member   | 4400000  | 16200000       | 
| 10          | Endo Syota         | Legal      | Manager  | 9000000  | 6633333.333    | 
| 11          | Suzuki Yuka        | Legal      | Director | 7000000  | 6633333.333    | 
| 12          | Tanaka Maya        | Legal      | Director | 7600000  | 6633333.333    | 
| 13          | Hayasi Asuka       | Legal      | Member   | 5500000  | 6633333.333    | 
| 14          | Takahasi Noboru    | Legal      | Member   | 5400000  | 6633333.333    | 
| 15          | Tezuka Kei         | Legal      | Member   | 5300000  | 6633333.333    | 
```

このようにwindow関数はOVER句の中のフレーム定義によって、行を拡張しフレームとしてデータを扱います。そしてフレームに関数を適用されることで、結果`AVG(salary)　FROM employee　GROUP GY　department`を使ったかのような値が`salary_average`カラムに入ります。

フレームについてなんとなくイメージできましたでしょうか？

重要なのは `行を拡張しフレームとしてデータを扱う`ということです。

window関数は `フレームの切り方`と`フレームに適用させる関数`の2つを自由に操作できるようになれば怖くありません。

## フレームの分割過程

実はフレームは、2つの過程を経て、分割されます。

その過程は以下の通りです。

1. パーティション分割
2. フレーム分割


フレーム定義のシンタックスを見てみると、

```
window_spec:
    [window_name] [partition_clause] [order_clause] [frame_clause]
```

- window_name: こちらは名前付きウィンドウで使われます。今回は`OVER (フレーム定義)`のような構文でフレーム定義をするので、window_nameは使いません。

 `partition_clause` ,`order_clause`, `frame_clause`の3つの構文で構成されることがわかります。

フレームの分割過程と上記3つの構文を紐づけると

- パーティション分割
    - partition_clause
        - PARTITION BY句を使う
    - order_clause
        - ORDER BY句を使う
- フレーム分割
    - frame_clause
        - ROWS BETWEENなどを使う。
        - フレーム定義の最後に使う

のようになります。

3つの構文を全てを使ったSQLを実行して、その処理過程を見て、見ましょう。
実行するSQLは下記です。

```sql
SELECT
    employee_id,
    employee_name,
    department,
    position,
    salary,
    SUM(salary) OVER( PARTITION BY department ORDER BY salary ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS salary_sum
FROM employee;
```

window関数部分はこちらです。

```
SUM(salary) OVER( PARTITION BY department ORDER BY salary ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS salary_sum
                       ^                      ^              ^
               partition_clause        order_clause    frame_clause
```

元のデータはこちらです

```
| employee_id | employee_name      | department | position | salary   | 
| ----------- | ------------------ | ---------- | -------- | -------- | 
| 1           | Nakajima Atsushi   | human reso | Manager  | 8000000  | 
| 2           | Nakamura Mai       | human reso | Manager  | 65000000 | 
| 3           | Yamamoto Youko     | human reso | Director | 6000000  | 
| 4           | Hukuda Kyoko       | human reso | Director | 5000000  | 
| 5           | Kobayashi Takahiro | human reso | Director | 45000000 | 
| 6           | Satou Osamu        | human reso | Member   | 4300000  | 
| 7           | Suzuki Haruka      | human reso | Member   | 4000000  | 
| 8           | Matsuda Kana       | human reso | Member   | 4100000  | 
| 9           | Kimura Sayuri      | human reso | Member   | 4400000  |
| 10          | Endo Syota         | Legal      | Manager  | 9000000  | 
| 11          | Suzuki Yuka        | Legal      | Director | 7000000  | 
| 12          | Tanaka Maya        | Legal      | Director | 7600000  |  
| 13          | Hayasi Asuka       | Legal      | Member   | 5500000  | 
| 14          | Takahasi Noboru    | Legal      | Member   | 5400000  | 
| 15          | Tezuka Kei         | Legal      | Member   | 5300000  | 
```

#### まずpartition_clauseとorder_clauseによって`パーティション分割`されます。

- partition_clauseは`PARTITION BY department`で表されています
- order_clauseは` ORDER BY salary`で表されています



```
| employee_id | employee_name      | department | position | salary   | 
| ----------- | ------------------ | ---------- | -------- | -------- | ----------
| 7           | Suzuki Haruka      | human reso | Member   | 4000000  | 
| 8           | Matsuda Kana       | human reso | Member   | 4100000  | 
| 6           | Satou Osamu        | human reso | Member   | 4300000  | 
| 9           | Kimura Sayuri      | human reso | Member   | 4400000  | 
| 4           | Hukuda Kyoko       | human reso | Director | 5000000  | パーティション①
| 3           | Yamamoto Youko     | human reso | Director | 6000000  | 
| 1           | Nakajima Atsushi   | human reso | Manager  | 8000000  | 
| 5           | Kobayashi Takahiro | human reso | Director | 45000000 | 
| 2           | Nakamura Mai       | human reso | Manager  | 65000000 | 
| ----------- | ------------------ | ---------- | -------- | -------- | ----------
| 15          | Tezuka Kei         | Legal      | Member   | 5300000  | 
| 14          | Takahasi Noboru    | Legal      | Member   | 5400000  | 
| 13          | Hayasi Asuka       | Legal      | Member   | 5500000  | パーティション②
| 11          | Suzuki Yuka        | Legal      | Director | 7000000  | 
| 12          | Tanaka Maya        | Legal      | Director | 7600000  | 
| 10          | Endo Syota         | Legal      | Manager  | 9000000  | 
```
1. employeeテーブルのデータが`PARTITION BY句`で指定されたdepartmentと、`ORDER BY句`で指定されたsalaryを使って`ORDER BY department, salary`のようにソートされます。

2. `PARTITION BY句`で指定されたdepartment列の値でパーティションが分割されます。

#### 続いて、frame_clauseによってフレーム分割されます

- frame_clauseは`ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW`で表されています。

簡略化のために左側のカラムを省略しています。

```
~ | department | position | salary   | 
~ | ---------- | -------- | -------- | --------------------------------------　パーティション①の開始点
~ | human reso | Member   | 4000000  | フレーム1       |          |         |    
~ | ---------- | -------- | -------- | -------       |          |         |
~ | human reso | Member   | 4100000  | 　　　　　　フレーム2        |         |
~ | ---------- | -------- | -------- | -------------------      |         |
~ | human reso | Member   | 4300000  |                       フレーム3      |
~ | ---------- | -------- | -------- | -------------------------------    |
~ | human reso | Member   | 4400000  |                                    |
　　　　　.           .          .                                          |
　　　　　.           .          .                                          |
　　　　　.           .          .                                          |
~ | human reso | Manager  | 65000000 |                                 フレーム9
~ | ---------- | -------- | -------- | -------------------------------------- パーティション①の終了点&パーティション②の開始点
~ | Legal      | Member   | 5300000  | フレーム10       |          |         |
~ | ---------- | -------- | -------- | -------        |          |         |
~ | Legal      | Member   | 5400000  | 　　　　　　フレーム11        |         |
~ | ---------- | -------- | -------- | -------------------       |         |
~ | Legal      | Member   | 5500000  |                        フレーム12     |
~ | ---------- | -------- | -------- | ----------------------------------  |
~ | Legal      | Director | 7000000  |                                     |
        .            .          .                                          |
        .            .          .                                          |
        .            .          .                                          |
~ | Legal      | Manager  | 9000000  |                                 フレーム15
~ | ---------- | -------- | -------- | -------------------------------------- パーティション②の終了点
```

以下の評価基準でフレームを分割します

 - `ROWS`なので行を評価します
 - `BETWEEN UNBOUNDED PRECEDING`なのでパーティションの開始行からフレームを始めます
 - `AND CURRENT ROW`なので評価されている行でフレームを終了します

#### 最後にフレームごとに関数を適用します。

- 関数は`SUM(salary)`です

```
| employee_id | employee_name      | department | position | salary   | salary_sum | 
| ----------- | ------------------ | ---------- | -------- | -------- | ---------- | 
| 7           | Suzuki Haruka      | human reso | Member   | 4000000  | 4000000    | 
| 8           | Matsuda Kana       | human reso | Member   | 4100000  | 8100000    | 
| 6           | Satou Osamu        | human reso | Member   | 4300000  | 12400000   | 
| 9           | Kimura Sayuri      | human reso | Member   | 4400000  | 16800000   | 
| 4           | Hukuda Kyoko       | human reso | Director | 5000000  | 21800000   | 
| 3           | Yamamoto Youko     | human reso | Director | 6000000  | 27800000   | 
| 1           | Nakajima Atsushi   | human reso | Manager  | 8000000  | 35800000   | 
| 5           | Kobayashi Takahiro | human reso | Director | 45000000 | 80800000   | 
| 2           | Nakamura Mai       | human reso | Manager  | 65000000 | 145800000  | 
| 15          | Tezuka Kei         | Legal      | Member   | 5300000  | 5300000    | 
| 14          | Takahasi Noboru    | Legal      | Member   | 5400000  | 10700000   | 
| 13          | Hayasi Asuka       | Legal      | Member   | 5500000  | 16200000   | 
| 11          | Suzuki Yuka        | Legal      | Director | 7000000  | 23200000   | 
| 12          | Tanaka Maya        | Legal      | Director | 7600000  | 30800000   | 
| 10          | Endo Syota         | Legal      | Manager  | 9000000  | 39800000   | 
```

結果salary_sumが演算され各行に返されます。

# まとめ

フレームとフレームに適用する関数という2つの概念がwindow関数の根幹をなす概念です。