[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関数を使って見ます。

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関数を使った方が簡単にかけますね。

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を例に出すと

    AVG(salary) OVER(PARTITION BY department) AS salary_average
        ^                              ^
   window関数             　　　　　フレーム定義

　
のようになっています。

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

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

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

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

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関数の根幹をなす概念です。