Go to Qiita Advent Calendar 2024 Top
LoginSignup
134
114

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 3 years have passed since last update.

@ngyukiin株式会社ヘッドジャパン

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

Last updated at Posted at 2019-02-25

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

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 関数が適当されるわけなのでまあそうですね。

簡単な例

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

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 ごとの合計を出すと次のようになります。

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

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

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 は未指定にもできます。未指定だと全体をひとつのパーティションとして扱います。

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 で指定した順番で順序付けされた結果が返ります。

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 で使用すると次のような結果になります。

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 の昇順に並べ、パーティションの先頭から現在行までが集計に対象になります。つまり次のようになっています。

/*
+-----+-----+-------+-------+
| 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 ～～ の部分がフレームの指定です。

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 でフレームの開始位置と終了位置の両方を指定できます。

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 行の範囲」は次のように指定します。

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 の範囲」となります。

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 のみ）となります。

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 です。

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 句から参照することができます。

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 を指定しています。

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 が指定されているのでエラーになります。

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 を使うこともできます。

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 関数としても利用できます。

134
114
1

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
134
114

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?