MySQL の Window 関数を完全に理解する

MySQL でも 8.0 から Window 関数が使えるようになりましたが、かなり長いこと Window 関数のない世界を生きてきたので SQL を考えるときに頭の外にあることがよくあります。

- https://dev.mysql.com/doc/refman/8.0/en/window-functions.html

## Window 関数とは

普通 SELECT 句での射影は FROM や WHERE による結果のセットの行と１対１となるので、SELECT 句の式には現在行の値に基づく式しか書くことができません。

例えば次のようなテーブルがあるとき、

| uid | gid | val |
| --- | --- | --- |
| 1   | 100 | 34  |
| 2   | 100 | 26  |
| 3   | 200 | 59  |
| 4   | 200 | 87  |

`uid = 1` の行の式にそれ以外の行の値に基づいた式を書くことはできません（もちろんサブクエリを使えばできますが）。

しかし、Window 関数を使えば `uid = 1` の行の式に `gid = 100` となるすべての行を元に計算した値を使ったりできます。

という説明だけだと「GROUP BY のこと？」と思ってしまうかもしれませんが、GROUP BY だと集計した行が単一行にまとまるのに対して、Window 関数だと元の行はそのままで複数の行から計算した値を結果に含めることができます。

Window 関数は SELECT 句と ORDER BY 句でのみ使用できます。FROM や WHERE や GROUP BY や HAVING による結果のセットに対して Window 関数が適当されるわけなのでまあそうですね。

## 簡単な例

結果をわかりやすくするために、極端なデータを使います。

```sql
CREATE TABLE t (
    uid INT NOT NULL PRIMARY KEY auto_increment,
    gid INT NOT NULL,
    val INT NOT NULL
);

INSERT INTO t VALUES
    ( 1, 100,     1),
    ( 2, 200,     2),
    ( 3, 100,    10),
    ( 4, 200,    20),
    ( 5, 100,   100),
    ( 6, 200,   200),
    ( 7, 100,  1000),
    ( 8, 200,  2000),
    ( 9, 100, 10000),
    (10, 200, 20000)
;
```

`GROUP BY` で `gid` ごとの合計を出すと次のようになります。

```sql
SELECT gid, SUM(val) AS total FROM t GROUP BY gid;
/*
+-----+-------+
| gid | total |
+-----+-------+
| 100 | 11111 |
| 200 | 22222 |
+-----+-------+
*/
```

Window 関数で似たことをすると次のようになります。

```sql
SELECT uid, gid, val, SUM(val) OVER (PARTITION BY gid) AS total FROM t;
/*
+-----+-----+-------+-------+
| uid | gid | val   | total |
+-----+-----+-------+-------+
|   1 | 100 |     1 | 11111 |
|   3 | 100 |    10 | 11111 |
|   5 | 100 |   100 | 11111 |
|   7 | 100 |  1000 | 11111 |
|   9 | 100 | 10000 | 11111 |
|   2 | 200 |     2 | 22222 |
|   4 | 200 |    20 | 22222 |
|   6 | 200 |   200 | 22222 |
|   8 | 200 |  2000 | 22222 |
|  10 | 200 | 20000 | 22222 |
+-----+-----+-------+-------+
*/
```

`GROUP BY` では集計のキー `gid` が同じ値の行が 1 行にまとめられるのに対して、Window 関数では行はまとまらずに集計した結果が列値に含まれています。

## PARTITION BY

前述の例の `OVER (PARTITION BY gid)` の部分が Window 関数のポイントです。Window 関数がどの範囲の行を対象とするかを指定します。この範囲は「パーティション」と呼ばれます。紛らわしいですが `CREATE TABLE` のパーティションとは無関係です。

`PARTITION BY` で指定した式値で `FROM` や `WHERE` による結果のセットをパーティションが分割されます。Window 関数はそのパーティションを元に計算されます。

`PARTITION BY` は未指定にもできます。未指定だと全体をひとつのパーティションとして扱います。

```sql
SELECT uid, gid, val, SUM(val) OVER () AS total FROM t;
/*
+-----+-----+-------+-------+
| uid | gid | val   | total |
+-----+-----+-------+-------+
|   1 | 100 |     1 | 33333 |
|   2 | 200 |     2 | 33333 |
|   3 | 100 |    10 | 33333 |
|   4 | 200 |    20 | 33333 |
|   5 | 100 |   100 | 33333 |
|   6 | 200 |   200 | 33333 |
|   7 | 100 |  1000 | 33333 |
|   8 | 200 |  2000 | 33333 |
|   9 | 100 | 10000 | 33333 |
|  10 | 200 | 20000 | 33333 |
+-----+-----+-------+-------+
*/
```

## ORDER BY

`OVER` 句には `ORDER BY` も指定できます。`ORDER BY` は Window 関数がパーティションの中で行を処理する順番を指定します。

代表的な例として、`ROW_NUMBER` などの順序を導出する系の Window 関数での使用が挙げられます。`PARTITION BY` で指定したパーティションの中で `ORDER BY` で指定した順番で順序付けされた結果が返ります。

```sql
SELECT uid, gid, val, ROW_NUMBER() OVER (PARTITION BY gid ORDER BY val) AS num FROM t;
/*
+-----+-----+-------+-----+
| uid | gid | val   | num |
+-----+-----+-------+-----+
|   1 | 100 |     1 |   1 |
|   3 | 100 |    10 |   2 |
|   5 | 100 |   100 |   3 |
|   7 | 100 |  1000 |   4 |
|   9 | 100 | 10000 |   5 |
|   2 | 200 |     2 |   1 |
|   4 | 200 |    20 |   2 |
|   6 | 200 |   200 |   3 |
|   8 | 200 |  2000 |   4 |
|  10 | 200 | 20000 |   5 |
+-----+-----+-------+-----+
*/
```

## フレーム

前述の `ORDER BY` を `SUM` で使用すると次のような結果になります。

```sql
SELECT uid, gid, val, SUM(val) OVER (PARTITION BY gid ORDER BY val) AS total FROM t;
/*
+-----+-----+-------+-------+
| uid | gid | val   | total |
+-----+-----+-------+-------+
|   1 | 100 |     1 |     1 |
|   3 | 100 |    10 |    11 |
|   5 | 100 |   100 |   111 |
|   7 | 100 |  1000 |  1111 |
|   9 | 100 | 10000 | 11111 |
|   2 | 200 |     2 |     2 |
|   4 | 200 |    20 |    22 |
|   6 | 200 |   200 |   222 |
|   8 | 200 |  2000 |  2222 |
|  10 | 200 | 20000 | 22222 |
+-----+-----+-------+-------+
*/
```

`gid` で分割されたパーティションの中を `val` の昇順に並べ、パーティションの先頭から現在行までが集計に対象になります。つまり次のようになっています。

```sql
/*
+-----+-----+-------+-------+
| uid | gid | val   | total |
+-----+-----+-------+-------+
|   1 | 100 |     1 |     1 | 1
|   3 | 100 |    10 |    11 | 1 + 10
|   5 | 100 |   100 |   111 | 1 + 10 + 100
|   7 | 100 |  1000 |  1111 | 1 + 10 + 100 + 1000
|   9 | 100 | 10000 | 11111 | 1 + 10 + 100 + 1000 + 10000
|   2 | 200 |     2 |     2 | 2
|   4 | 200 |    20 |    22 | 2 + 20
|   6 | 200 |   200 |   222 | 2 + 20 + 200
|   8 | 200 |  2000 |  2222 | 2 + 20 + 200 + 2000
|  10 | 200 | 20000 | 22222 | 2 + 20 + 200 + 2000 + 20000
+-----+-----+-------+-------+
*/
```

この「パーティションの先頭から現在行まで」のような範囲のことは「フレーム」と呼ばれます。フレームの範囲も `OVER` 句で指定できます。次の `ROWS ～～` の部分がフレームの指定です。

```sql
SELECT uid, gid, val,
    SUM(val) OVER (PARTITION BY gid ORDER BY val ROWS CURRENT ROW)         AS `CURRENT ROW`,
    SUM(val) OVER (PARTITION BY gid ORDER BY val ROWS UNBOUNDED PRECEDING) AS `UNBOUNDED PRECEDING`,
    SUM(val) OVER (PARTITION BY gid ORDER BY val ROWS 1 PRECEDING)         AS `1 PRECEDING`
FROM t;
/*
+-----+-----+-------+-------------+---------------------+-------------+
| uid | gid | val   | CURRENT ROW | UNBOUNDED PRECEDING | 1 PRECEDING |
+-----+-----+-------+-------------+---------------------+-------------+
|   1 | 100 |     1 |           1 |                   1 |           1 |
|   3 | 100 |    10 |          10 |                  11 |          11 |
|   5 | 100 |   100 |         100 |                 111 |         110 |
|   7 | 100 |  1000 |        1000 |                1111 |        1100 |
|   9 | 100 | 10000 |       10000 |               11111 |       11000 |
|   2 | 200 |     2 |           2 |                   2 |           2 |
|   4 | 200 |    20 |          20 |                  22 |          22 |
|   6 | 200 |   200 |         200 |                 222 |         220 |
|   8 | 200 |  2000 |        2000 |                2222 |        2200 |
|  10 | 200 | 20000 |       20000 |               22222 |       22000 |
+-----+-----+-------+-------------+---------------------+-------------+
*/
```

`ROWS ～～～` でフレームの開始位置が指定できます。フレームの終了位置はデフォルトで現在行です。なので `ROWS CURRENT ROW` だけ指定すると「現在行～現在行」です。

`BETWEEN` でフレームの開始位置と終了位置の両方を指定できます。

```sql
SELECT uid, gid, val,
    SUM(val) OVER (PARTITION BY gid ORDER BY val ROWS BETWEEN CURRENT ROW AND CURRENT ROW)         AS `CURRENT ROW`,
    SUM(val) OVER (PARTITION BY gid ORDER BY val ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS `UNBOUNDED FOLLOWING`,
    SUM(val) OVER (PARTITION BY gid ORDER BY val ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING)         AS `1 FOLLOWING`
FROM t;
/*
+-----+-----+-------+-------------+---------------------+-------------+
| uid | gid | val   | CURRENT ROW | UNBOUNDED FOLLOWING | 1 FOLLOWING |
+-----+-----+-------+-------------+---------------------+-------------+
|   1 | 100 |     1 |           1 |               11111 |          11 |
|   3 | 100 |    10 |          10 |               11110 |         110 |
|   5 | 100 |   100 |         100 |               11100 |        1100 |
|   7 | 100 |  1000 |        1000 |               11000 |       11000 |
|   9 | 100 | 10000 |       10000 |               10000 |       10000 |
|   2 | 200 |     2 |           2 |               22222 |          22 |
|   4 | 200 |    20 |          20 |               22220 |         220 |
|   6 | 200 |   200 |         200 |               22200 |        2200 |
|   8 | 200 |  2000 |        2000 |               22000 |       22000 |
|  10 | 200 | 20000 |       20000 |               20000 |       20000 |
+-----+-----+-------+-------------+---------------------+-------------+
*/
```

開始位置、終了位置には次が指定できます。

- `CURRENT ROW`
    - 現在行
- `UNBOUNDED PRECEDING`
    - パーティションの先頭
- `UNBOUNDED FOLLOWING`
    - パーティションの終端
- `1 PRECEDING`
    - 現在行の 1 行前（任意の数を指定）
- `1 FOLLOWING`
    - 現在行の 1 行後（任意の数を指定）

例えば、「現在行の前後 1 行の範囲」は次のように指定します。

```sql
SELECT uid, gid, val,
    SUM(val) OVER (PARTITION BY gid ORDER BY val ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS total
FROM t;
/*
+-----+-----+-------+-------+
| uid | gid | val   | total |
+-----+-----+-------+-------+
|   1 | 100 |     1 |    11 |
|   3 | 100 |    10 |   111 |
|   5 | 100 |   100 |  1110 |
|   7 | 100 |  1000 | 11100 |
|   9 | 100 | 10000 | 11000 |
|   2 | 200 |     2 |    22 |
|   4 | 200 |    20 |   222 |
|   6 | 200 |   200 |  2220 |
|   8 | 200 |  2000 | 22200 |
|  10 | 200 | 20000 | 22000 |
+-----+-----+-------+-------+
*/
```

`ROWS` の部分には `RANGE` も指定できます。`ROWS` は行番号でフレームの範囲を指定するのに対して、`RANGE` は値でフレームの範囲を指定できます。

例えば、次の場合は「現在行の `val` の値の前後 500 の範囲」となります。

```sql
SELECT uid, gid, val,
    SUM(val) OVER (PARTITION BY gid ORDER BY val RANGE BETWEEN 500 PRECEDING AND 500 FOLLOWING) AS total
FROM t;
/*
+-----+-----+-------+-------+
| uid | gid | val   | total |
+-----+-----+-------+-------+
|   1 | 100 |     1 |   111 |
|   3 | 100 |    10 |   111 |
|   5 | 100 |   100 |   111 |
|   7 | 100 |  1000 |  1000 |
|   9 | 100 | 10000 | 10000 |
|   2 | 200 |     2 |   222 |
|   4 | 200 |    20 |   222 |
|   6 | 200 |   200 |   222 |
|   8 | 200 |  2000 |  2000 |
|  10 | 200 | 20000 | 20000 |
+-----+-----+-------+-------+
*/
```

`RANGE` の `CURRENT ROW` は「現在の行と同じ値を持つ行」です。ややわかりにくいですが次の 2 つを見比べてください。前者は同じ `gid` 値の行のカウント、後者は同じ行のカウント（なので 1 のみ）となります。

```sql
SELECT uid, gid, val,
    COUNT(*) OVER (ORDER BY gid RANGE BETWEEN CURRENT ROW AND CURRENT ROW) AS cnt
FROM t;
/*
+-----+-----+-------+-----+
| uid | gid | val   | cnt |
+-----+-----+-------+-----+
|   1 | 100 |     1 |   5 |
|   3 | 100 |    10 |   5 |
|   5 | 100 |   100 |   5 |
|   7 | 100 |  1000 |   5 |
|   9 | 100 | 10000 |   5 |
|   2 | 200 |     2 |   5 |
|   4 | 200 |    20 |   5 |
|   6 | 200 |   200 |   5 |
|   8 | 200 |  2000 |   5 |
|  10 | 200 | 20000 |   5 |
+-----+-----+-------+-----+
*/

SELECT uid, gid, val,
    COUNT(*) OVER (ORDER BY gid ROWS BETWEEN CURRENT ROW AND CURRENT ROW) AS cnt
FROM t;
/*
+-----+-----+-------+-----+
| uid | gid | val   | cnt |
+-----+-----+-------+-----+
|   1 | 100 |     1 |   1 |
|   3 | 100 |    10 |   1 |
|   5 | 100 |   100 |   1 |
|   7 | 100 |  1000 |   1 |
|   9 | 100 | 10000 |   1 |
|   2 | 200 |     2 |   1 |
|   4 | 200 |    20 |   1 |
|   6 | 200 |   200 |   1 |
|   8 | 200 |  2000 |   1 |
|  10 | 200 | 20000 |   1 |
+-----+-----+-------+-----+
*/
```

なお、フレームを指定しないときのデフォルトは、`ORDER BY` がなければ `RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW` で、`ORDER BY` があるなら `RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING` です。

```sql
SELECT uid, gid, val,
    SUM(val) OVER (PARTITION BY gid ORDER BY val RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS total1,
    SUM(val) OVER (PARTITION BY gid RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS total2
FROM t;
/*
+-----+-----+-------+--------+--------+
| uid | gid | val   | total1 | total2 |
+-----+-----+-------+--------+--------+
|   1 | 100 |     1 |      1 |  11111 |
|   2 | 100 |    10 |     11 |  11111 |
|   3 | 100 |   100 |    111 |  11111 |
|   4 | 100 |  1000 |   1111 |  11111 |
|   5 | 100 | 10000 |  11111 |  11111 |
|   6 | 200 |     2 |      2 |  22222 |
|   7 | 200 |    20 |     22 |  22222 |
|   8 | 200 |   200 |    222 |  22222 |
|   9 | 200 |  2000 |   2222 |  22222 |
|  10 | 200 | 20000 |  22222 |  22222 |
+-----+-----+-------+--------+--------+
*/
```

最初の `SUM` の結果は `RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW` だったからですね。

なお、下記の Window 関数ではフレームの指定はできず、常にパーティション全体が使用されます。

- `CUME_DIST()`
- `DENSE_RANK()`
- `LAG()`
- `LEAD()`
- `NTILE()`
- `PERCENT_RANK()`
- `RANK()`
- `ROW_NUMBER()`

## 名前付き Window

クエリの中で同じ `OVER` 句を複数回記述するときなどに、`OVER` の中身を名前付きで定義して `OVER` 句から参照することができます。

```sql
SELECT uid, gid, val,
    ROW_NUMBER() OVER w AS num,
    SUM(val) OVER w AS total,
    AVG(val) OVER w AS average
FROM t
WINDOW w AS (PARTITION BY gid);
/*
+-----+-----+-------+-----+-------+-----------+
| uid | gid | val   | num | total | average   |
+-----+-----+-------+-----+-------+-----------+
|   1 | 100 |     1 |   1 | 11111 | 2222.2000 |
|   3 | 100 |    10 |   2 | 11111 | 2222.2000 |
|   5 | 100 |   100 |   3 | 11111 | 2222.2000 |
|   7 | 100 |  1000 |   4 | 11111 | 2222.2000 |
|   9 | 100 | 10000 |   5 | 11111 | 2222.2000 |
|   2 | 200 |     2 |   1 | 22222 | 4444.4000 |
|   4 | 200 |    20 |   2 | 22222 | 4444.4000 |
|   6 | 200 |   200 |   3 | 22222 | 4444.4000 |
|   8 | 200 |  2000 |   4 | 22222 | 4444.4000 |
|  10 | 200 | 20000 |   5 | 22222 | 4444.4000 |
+-----+-----+-------+-----+-------+-----------+
*/
```

`OVER` 句で名前付き Window を参照するとき、元の定義で指定されていたかった句を追加で指定することができます。
例えば、次の例では名前付き Window の定義では `PARTITION BY` しか指定していませんが、`OVER` で参照時に `ORDER BY` を指定しています。

```sql
SELECT uid, gid, val,
    SUM(val) OVER w AS total,
    SUM(val) OVER (w ORDER BY val ASC) AS total_asc,
    SUM(val) OVER (w ORDER BY val DESC) AS total_desc
FROM t
WINDOW w AS (PARTITION BY gid);
/*
+-----+-----+-------+-------+-----------+------------+
| uid | gid | val   | total | total_asc | total_desc |
+-----+-----+-------+-------+-----------+------------+
|   9 | 100 | 10000 | 11111 |     11111 |      10000 |
|   7 | 100 |  1000 | 11111 |      1111 |      11000 |
|   5 | 100 |   100 | 11111 |       111 |      11100 |
|   3 | 100 |    10 | 11111 |        11 |      11110 |
|   1 | 100 |     1 | 11111 |         1 |      11111 |
|  10 | 200 | 20000 | 22222 |     22222 |      20000 |
|   8 | 200 |  2000 | 22222 |      2222 |      22000 |
|   6 | 200 |   200 | 22222 |       222 |      22200 |
|   4 | 200 |    20 | 22222 |        22 |      22220 |
|   2 | 200 |     2 | 22222 |         2 |      22222 |
+-----+-----+-------+-------+-----------+------------+
*/
```

同じ種類の句を追加することはできません。次の例では、名前付き Window の定義と `OVER` の両方で同じ `PARTITION BY` が指定されているのでエラーになります。

```sql
SELECT uid, gid, val,
    SUM(val) OVER (w PARTITION BY val) AS total
FROM t
WINDOW w AS (PARTITION BY gid);
/*
ERROR 3581 (HY000): A window which depends on another cannot define partitioning.
*/
```

名前付き Window の定義に名前付き Window を使うこともできます。

```sql
SELECT uid, gid, val,
    SUM(val) OVER w1 AS total,
    SUM(val) OVER w2 AS total_asc,
    SUM(val) OVER w3 AS total_desc
FROM t
WINDOW w1 AS (PARTITION BY gid),
       w2 AS (w1 ORDER BY val ASC),
       w3 AS (w1 ORDER BY val DESC);
/*
+-----+-----+-------+-------+-----------+------------+
| uid | gid | val   | total | total_asc | total_desc |
+-----+-----+-------+-------+-----------+------------+
|   9 | 100 | 10000 | 11111 |     11111 |      10000 |
|   7 | 100 |  1000 | 11111 |      1111 |      11000 |
|   5 | 100 |   100 | 11111 |       111 |      11100 |
|   3 | 100 |    10 | 11111 |        11 |      11110 |
|   1 | 100 |     1 | 11111 |         1 |      11111 |
|  10 | 200 | 20000 | 22222 |     22222 |      20000 |
|   8 | 200 |  2000 | 22222 |      2222 |      22000 |
|   6 | 200 |   200 | 22222 |       222 |      22200 |
|   4 | 200 |    20 | 22222 |        22 |      22220 |
|   2 | 200 |     2 | 22222 |         2 |      22222 |
+-----+-----+-------+-------+-----------+------------+
*/
```

## Window 関数の種類

Window 関数には、Window 関数専用の関数が幾つかあります。これらの関数には `OVER` が必須です。

- `CUME_DIST()`
- `DENSE_RANK()`
- `FIRST_VALUE()`
- `LAG()`
- `LAST_VALUE()`
- `LEAD()`
- `NTH_VALUE()`
- `NTILE()`
- `PERCENT_RANK()`
- `RANK()`
- `ROW_NUMBER()`

ほとんどの集計関数も Window 関数として使用可能です。例で使用した `SUM` などは集計関数ですが Window 関数としても利用できます。