PostgreSQL のウィンドウ関数の使い方

PostgreSQL のウィンドウ関数、よく知らない使い方が色々あるのが前から気になっていたので、調べてまとめてみました。

田中彁（タナカ ???）

「彁」は幽霊文字で読み方は分かりません。

本記事の SQL は PostgreSQL バージョン 14.5 で動作を確認しています。また、バージョン 14 の公式ドキュメントを参考にしています。

ウィンドウ関数
PARTITION BY
ORDER BY
ウィンドウフレーム
実行順序
ウィンドウ関数で絞り込み
FILTER 句
WINDOW 句
まとめ

ウィンドウ関数

 member_id | nickname | team | score
-----------+----------+------+-------
 M-001     | Alice    | A    |    95
 M-002     | Bob      | B    |    45
 M-003     | Carol    | C    |    38
 M-004     | Dave     | A    |    62
 M-005     | Eve      | B    |    36
 M-006     | Frank    | C    |    10
 M-007     | Grace    | A    |    78
 M-008     | Heidi    | B    |    23
 M-009     | Ivan     | C    |    41

このテーブルに対し、チームごとのポイントの合計、最大値、最小値を知りたいなら、集約関数 sum, max, min を GROUP BY とともに使えば計算できます。

SELECT team, sum(score), max(score), min(score)
FROM scores GROUP BY team
ORDER BY team;

 team | sum | max | min
------+-----+-----+-----
 A    | 235 |  95 |  62
 B    | 104 |  45 |  23
 C    |  89 |  41 |  10

結果は、当然ながらグループの数の行に集約され、各メンバーの情報は失われます。もし、元の行と一緒に、この集計結果を見たいなら、サブクエリで集約した結果と JOIN する必要があります。

WITH stats AS (
  SELECT team, sum(score), max(score), min(score)
  FROM scores GROUP BY team
)
SELECT member_id, nickname, team, score, sum, max, min
FROM scores INNER JOIN stats USING (team)
ORDER BY member_id;

 member_id | nickname | team | score | sum | max | min
-----------+----------+------+-------+-----+-----+-----
 M-001     | Alice    | A    |    95 | 235 |  95 |  62
 M-002     | Bob      | B    |    45 | 104 |  45 |  23
 M-003     | Carol    | C    |    38 |  89 |  41 |  10
 M-004     | Dave     | A    |    62 | 235 |  95 |  62
 M-005     | Eve      | B    |    36 | 104 |  45 |  23
 M-006     | Frank    | C    |    10 |  89 |  41 |  10
 M-007     | Grace    | A    |    78 | 235 |  95 |  62
 M-008     | Heidi    | B    |    23 | 104 |  45 |  23
 M-009     | Ivan     | C    |    41 |  89 |  41 |  10

しかし、集約関数をウィンドウ関数として使えば、サブクエリなしで同じ結果を得ることができます。集約関数の後ろに OVER 句を付けるとウィンドウ関数になります。

SELECT
    member_id, nickname, team, score,
    sum (score) OVER (PARTITION BY team),
    max (score) OVER (PARTITION BY team),
    min (score) OVER (PARTITION BY team)
FROM scores ORDER BY member_id;

 member_id | nickname | team | score | sum | max | min
-----------+----------+------+-------+-----+-----+-----
 M-001     | Alice    | A    |    95 | 235 |  95 |  62
 M-002     | Bob      | B    |    45 | 104 |  45 |  23
 M-003     | Carol    | C    |    38 |  89 |  41 |  10
 M-004     | Dave     | A    |    62 | 235 |  95 |  62
 M-005     | Eve      | B    |    36 | 104 |  45 |  23
 M-006     | Frank    | C    |    10 |  89 |  41 |  10
 M-007     | Grace    | A    |    78 | 235 |  95 |  62
 M-008     | Heidi    | B    |    23 | 104 |  45 |  23
 M-009     | Ivan     | C    |    41 |  89 |  41 |  10

このように、ウィンドウ関数は、元の行を残したまま、他の行の値を使って計算を行います。

PARTITION BY

OVER 句では GROUP BY の代わりに PARTITION BY を使い、PARTITION BY で分割される行の集合をパーティションと呼びます。上の例では、GROUP BY で集約される行とパーティションが同じになっています。

PARTITION BY を省略した場合は全行が 1 つのパーティションとなります。GROUP BY がないとき集約関数が全行を対象とするのと同じです。

SELECT
    member_id, nickname, team, score,
    sum (score) OVER (),
    max (score) OVER (),
    min (score) OVER ()
FROM scores ORDER BY member_id;

 member_id | nickname | team | score | sum | max | min
-----------+----------+------+-------+-----+-----+-----
 M-001     | Alice    | A    |    95 | 428 |  95 |  10
 M-002     | Bob      | B    |    45 | 428 |  95 |  10
 M-003     | Carol    | C    |    38 | 428 |  95 |  10
 M-004     | Dave     | A    |    62 | 428 |  95 |  10
 M-005     | Eve      | B    |    36 | 428 |  95 |  10
 M-006     | Frank    | C    |    10 | 428 |  95 |  10
 M-007     | Grace    | A    |    78 | 428 |  95 |  10
 M-008     | Heidi    | B    |    23 | 428 |  95 |  10
 M-009     | Ivan     | C    |    41 | 428 |  95 |  10

ORDER BY

上記の通り、集約関数であれば、ウィンドウ関数としても使うことができます。その他に、ウィンドウ関数専用の関数もあります。

（※ 以下の説明では、テーブルを簡略化して、PARTITION BY の対象カラムを p 、ORDER BY の対象カラムを n としています。）

SELECT
    id, p, n,
    rank() OVER (PARTITION BY p ORDER BY n),
    row_number() OVER (PARTITION BY p ORDER BY n)
FROM example1
ORDER BY id;

 id | p | n  | rank | row_number
----+---+----+------+------------
  1 | A |  1 |    1 |          1
  2 | A |  2 |    2 |          2
  3 | A |  3 |    3 |          4
  4 | A |  3 |    3 |          3
  5 | A |  4 |    5 |          5
  6 | B | 10 |    1 |          1
  7 | B | 20 |    2 |          2

rank は、パーティション内での順位を返します。上では ORDER BY n を指定することによって n でソートした順位を返しています。rank は ORDER BY の結果が同じ順位の行に対しては同じ値を返します。 row_number は、rank に似ていますが、同じ順位の行に対しても違う値が返ります。ORDRE BY の結果が同じ順位の行に対してはデータベースの内部状態に従った順番で値が返るので、上の id=3 と id=4 の行の値は逆になるかもしれません。

ORDER BY を指定しないと、結果は以下のようになります。

SELECT
    id, p, n,
    rank() OVER (PARTITION BY p),
    row_number() OVER (PARTITION BY p)
FROM example1
ORDER BY id;

 id | p | n  | rank | row_number
----+---+----+------+------------
  1 | A |  1 |    1 |          2
  2 | A |  2 |    1 |          3
  3 | A |  3 |    1 |          1
  4 | A |  3 |    1 |          4
  5 | A |  4 |    1 |          5
  6 | B | 10 |    1 |          1
  7 | B | 20 |    1 |          2

rank は、順位を決める手段がないため全て同じ順位と見做して全行で同じ値 1 が、row_number は、全行がデータベースの内部状態に従った値になっています。

ORDER BY の結果が同じ順位である行のことを ピア行 と呼びます。（ピア (peer) は一般には「能力等が同格の人」の意味です。）rank はピア行に対して同じ値を返すので、ORDER BY がない場合は全行がピア行となり全行で同じ値が返る、と説明することもできます。

rank, row_number のような、他の行との位置関係を返す関数において ORDER BY は重要な意味を持ちます。では、例えば sum の場合は、加算する順番が変わっても結果は変わらないから ORDER BY はあってもなくて同じ、かというと、そうではありません。

SELECT
    id, p, n,
    sum(n) OVER (PARTITION BY p) AS sum_1,
    sum(n) OVER (PARTITION BY p ORDER BY n) AS sum_2
FROM example1
ORDER BY id;

 id | p | n  | sum_1 | sum_2
----+---+----+-------+-------
  1 | A |  1 |    13 |     1
  2 | A |  2 |    13 |     3
  3 | A |  3 |    13 |     9
  4 | A |  3 |    13 |     9
  5 | A |  4 |    13 |    13
  6 | B | 10 |    30 |    10
  7 | B | 20 |    30 |    30

ORDER BY がある場合は、パーティションの先頭行から現在処理中の行の最後のピア行までの合計となります。(id=3 の行について、id=3 の行から id=4 の行までの合計 (1+2+3+3= 9) になっています。) ここでも、ORDER BY がない場合はパーティション全行の合計となるのは、全行がピア行となるから、と説明できます。

この「パーティションの先頭行から現在処理中の行の最後のピア行まで」は ORDER BY の後に何も指定しない場合のデフォルトの動作で、どこからどこまでの行を対象にするか、また、ピア行をどう扱うかは変更することができます。

ウィンドウフレーム

上記の通り、ウィンドウ関数は、必ずパーティション内の全行を処理対象にするわけではありません。ウィンドウ関数が処理対象にする範囲を ウィンドウフレーム (以下、単にフレーム) と呼びます。フレームはパーティションの部分集合です。フレームは、PARTITION BY, ORDER BY の後にフレーム句 (下記の (1), (2), (3)) を付けることによって調整できます。

SELECT id, p, n,
    sum(n) OVER (
        PARTITION BY p
        ORDER BY n
        ROWS                                -- (1)
        BETWEEN 1 PRECEDING AND 2 FOLLOWING -- (2)
        EXCLUDE GROUP                       -- (3)
    )
FROM foo;

(1) ROWS, GROUPS, RANGE の 3 つのモードを選択できます。

(2) BETWEEN frame_start AND frame_end で、フレームの開始と終了を指定します。frame_start, frame_end は以下の 5 つから選択できます。

UNBOUNDED PRECEDING
UNBOUNDED FOLLOWING
値 PRECEDING
値 FOLLOWING
CURRENT ROW

UNBOUNDED PRECEDINGは「パーティションの最初から」、UNBOUNDED FOLLOWING は「パーティションの最後まで」の意味で、各モード共通です。 値 PRECEDING は「現在行から 値 の分だけ前」、 値 FOLLOWING は「現在行から 値 の分だけ後」の意味ですが、値 が何を指すかはモードによって違います。 CURRENT ROW は現在行の意味ですが、ピア行を含むかどうかがモードによって変わります。

frame_start のみを指定することもでき、その場合、BETWEEN frame_start AND CURRENT ROW と同じ意味になります。

(3) EXCLUDE frame_exclusion で、現在行をフレームから除外するかどうかを制御します。frame_exclusion は、CURRENT ROW, GROUP, TIES, NO OTHERS の 4 つから選択できます。

フレーム句を省略した場合は、 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE NO OTHERS と同じ意味になります。

ROWS モード

値 PRECEDING, 値 FOLLOWING の 値 は行数を表し、ピア行であっても別々の行として数えます。CURRENT ROW は、正に現在の行を意味します。

フレームの範囲が分かり易いように、配列に集約する関数 array_agg を使って説明します。

-- ROWS モードで frame_start と frame_end を色々変えて比較
SELECT id, p, n,
    array_agg(n) OVER (PARTITION BY p ORDER BY n ROWS BETWEEN 1 PRECEDING AND 2 FOLLOWING) AS w1,
    array_agg(n) OVER (PARTITION BY p ORDER BY n ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS w2,
    array_agg(n) OVER (PARTITION BY p ORDER BY n ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS w3,
    array_agg(n) OVER (PARTITION BY p ORDER BY n ROWS 1 PRECEDING) AS w4,
    array_agg(n) OVER (PARTITION BY p ORDER BY n ROWS BETWEEN 1 PRECEDING AND 2 PRECEDING) AS w5
FROM example2
ORDER BY id;

 id | p | n  |    w1     |      w2       |      w3       |   w4    | w5
----+---+----+-----------+---------------+---------------+---------+----
  1 | A |  1 | {1,2,2}   | {1,2,2,3,4,5} | {1}           | {1}     |
  2 | A |  2 | {1,2,2,3} | {2,2,3,4,5}   | {1,2}         | {1,2}   |
  3 | A |  2 | {2,2,3,4} | {2,3,4,5}     | {1,2,2}       | {2,2}   |
  4 | A |  3 | {2,3,4,5} | {3,4,5}       | {1,2,2,3}     | {2,3}   |
  5 | A |  4 | {3,4,5}   | {4,5}         | {1,2,2,3,4}   | {3,4}   |
  6 | A |  5 | {4,5}     | {5}           | {1,2,2,3,4,5} | {4,5}   |
  7 | B | 10 | {10,20}   | {10,20}       | {10}          | {10}    |
  8 | B | 20 | {10,20}   | {20}          | {10,20}       | {10,20} |

id=3 の行について、フレームを確認します。

BETWEEN 1 PRECEDING AND 2 FOLLOWING (w1)：「現在行の 1 行前から現在行の 2 行後」で、フレームは id=2,3,4,5 の行となっています。
BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING (w2)：「現在行からパーティションの最後まで」で、フレームは id=3,4,5,6 の行となっています。
BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW (w3)：「パーティションの最初から現在行まで」で、フレームは id=1,2,3 の行となっています。
1 PRECEDING (w4)：frame_end を省略する書き方で BETWEEN 1 FOLLOWING AND CURRENT ROW と同じ意味になり、フレームは id=2,3 となっています。

なお、以下の設定はエラーになります。

frame_start が UNBOUNDED FOLLOWING
frame_end が UNBOUNDED PRECEDING
frame_start が CURRENT ROW で frame_end が UNBOUNDED/値 PRECEDING
frame_start が UNBOUNDED/値 FOLLOWING で frame_end が CURRENT ROW

これらがエラーになるのは frame_end が frame_start より前になってフレームが 0 行になるから当然だと思うかもしれませんが、これら以外であれば、BETWEEN 1 PRECEDING AND 2 PRECEDING (w5) のように frame_end が frame_start より前であってもエラーにならないので注意して下さい。この動作は全モード共通です。

GROUPS モード

ピア行を同じグループと捉え、グループ単位で処理します。値 PRECEDING, 値 FOLLOWING の 値 はグループ数を表し、CURRENT ROW は現在行を含むグループの意味になります。

-- 比較のため ROWS モードと並べて出力
SELECT id, p, n,
    array_agg(n) OVER (PARTITION BY p ORDER BY n ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING) AS "ROWS",
    array_agg(n) OVER (PARTITION BY p ORDER BY n GROUPS BETWEEN CURRENT ROW AND 1 FOLLOWING) AS "GROUPS"
FROM example3
ORDER BY id;

 id | p | n  |  ROWS   |   GROUPS
----+---+----+---------+-------------
  1 | A |  1 | {1,2}   | {1,2,2,2}
  2 | A |  2 | {2,2}   | {2,2,2,3,3}
  3 | A |  2 | {2,2}   | {2,2,2,3,3}
  4 | A |  2 | {2,3}   | {2,2,2,3,3}
  5 | A |  3 | {3,3}   | {3,3,4}
  6 | A |  3 | {3,4}   | {3,3,4}
  7 | A |  4 | {4}     | {4}
  8 | B | 10 | {10,20} | {10,20}
  9 | B | 20 | {20}    | {20}

id=3 の行について、CURRENT ROW は id=2,3,4 の行を指し 1 FOLLOWING は 1 つ後のグループである id=5,6 の行を指すので、フレームは id=2,3,4,5,6 の行となっています。id=3 と同じグループである id=2, id=4 の行についても同じです。

なお、GROUPS モードにおいては ORDER BY は必須で、指定しないとエラーになります。

RANGE モード

GROUPS モードと同じくピア行のグループ単位で処理しますが、値 PRECEDING, 値 FOLLOWING の 値 が、行数やグループ数ではなく、現在行の値との差分を意味します。

-- 比較のため GROUPS モードと並べて出力
SELECT id, p, n,
    array_agg(n) OVER (PARTITION BY p ORDER BY n GROUPS BETWEEN CURRENT ROW AND 1 FOLLOWING) AS "GROUPS",
    array_agg(n) OVER (PARTITION BY p ORDER BY n RANGE BETWEEN CURRENT ROW AND 1 FOLLOWING) AS "RANGE"
FROM example4
ORDER BY id;

 id | p |  n  |     GROUPS      |        RANGE
----+---+-----+-----------------+---------------------
  1 | A |   1 | {1,2,2,2}       | {1,2,2,2}
  2 | A |   2 | {2,2,2,2.5,2.5} | {2,2,2,2.5,2.5,3,3}
  3 | A |   2 | {2,2,2,2.5,2.5} | {2,2,2,2.5,2.5,3,3}
  4 | A |   2 | {2,2,2,2.5,2.5} | {2,2,2,2.5,2.5,3,3}
  5 | A | 2.5 | {2.5,2.5,3,3}   | {2.5,2.5,3,3}
  6 | A | 2.5 | {2.5,2.5,3,3}   | {2.5,2.5,3,3}
  7 | A |   3 | {3,3,4}         | {3,3,4}
  8 | A |   3 | {3,3,4}         | {3,3,4}
  9 | A |   4 | {4}             | {4}
 10 | B |  10 | {10,20}         | {10}
 11 | B |  20 | {20}            | {20}

id=3 の行に対し、CURRENT ROW は GROUPS モードと同じく id=2,3,4 の行を指しますが、1 FOLLOWING が「現在行の値 (=2) より 1 大きい値 (=3) を持つグループ」、つまり id=7,8 のグループを指し、フレームは id=2,3,4,5,6,7,8 の行になっています。

RANGE モードでは 値 PRECEDING, 値 FOLLOWING の 値 は数値とは限らず、ORDER BY の対象が timestamp なら interval になります。

SELECT
    id, p, n,
    array_agg(n) OVER (ORDER BY n RANGE BETWEEN CURRENT ROW AND '30 minutes'::interval FOLLOWING)
FROM example5
ORDER BY id;

 id | p |          n          |                   array_agg
----+---+---------------------+-----------------------------------------------
  1 | A | 2022-01-01 00:00:00 | {"2022-01-01 00:00:00"}
  2 | A | 2022-01-01 01:00:00 | {"2022-01-01 01:00:00","2022-01-01 01:30:00"}
  3 | A | 2022-01-01 01:30:00 | {"2022-01-01 01:30:00"}
  4 | A | 2022-01-01 02:30:00 | {"2022-01-01 02:30:00"}
  5 | B | 2022-01-02 00:00:00 | {"2022-01-02 00:00:00","2022-01-02 00:10:00"}
  6 | B | 2022-01-02 00:10:00 | {"2022-01-02 00:10:00"}

また、以下の点に注意して下さい。

ROWS モードと GROUPS モードでは、ORDER BY に複数カラムを指定できますが、RANGE モードでは 1 カラムしか指定できません。
RANGE モードで 値 PRECEDING, 値 FOLLOWING を指定する場合は ORDER BY も指定しないとエラーになります。

EXCLUDE

EXCLUDE は、フレームから、現在行、現在行のピア行を除外するかどうかを制御します。

-- frame_start と frame_end を同じにして EXCLUDE の 4 つのオプションを比較
SELECT id, p, n,
    array_agg(n) OVER (
        PARTITION BY p ORDER BY n ROWS BETWEEN 1 PRECEDING AND 2 FOLLOWING
        EXCLUDE NO OTHERS
    ) AS "NO OTHERS",
    array_agg(n) OVER (
        PARTITION BY p ORDER BY n ROWS BETWEEN 1 PRECEDING AND 2 FOLLOWING
        EXCLUDE CURRENT ROW
    ) AS "CURRENT ROW",
    array_agg(n) OVER (
        PARTITION BY p ORDER BY n ROWS BETWEEN 1 PRECEDING AND 2 FOLLOWING
        EXCLUDE GROUP
    ) AS "GROUP",
    array_agg(n) OVER (
        PARTITION BY p ORDER BY n ROWS BETWEEN 1 PRECEDING AND 2 FOLLOWING
        EXCLUDE TIES
    ) AS "TIES"
FROM example6;

 id | p | n  | NO OTHERS | CURRENT ROW | GROUP |  TIES
----+---+----+-----------+-------------+-------+---------
  1 | A |  1 | {1,2,2}   | {2,2}       | {2,2} | {1,2,2}
  2 | A |  2 | {1,2,2,2} | {1,2,2}     | {1}   | {1,2}
  3 | A |  2 | {2,2,2,3} | {2,2,3}     | {3}   | {2,3}
  4 | A |  2 | {2,2,3}   | {2,3}       | {3}   | {2,3}
  5 | A |  3 | {2,3}     | {2}         | {2}   | {2,3}
  6 | B | 10 | {10,20}   | {20}        | {20}  | {10,20}
  7 | B | 20 | {10,20}   | {10}        | {10}  | {10,20}

id=3 の行について、フレームを確認します。

NO OTHERS は、EXCLUDE を書かないのと同じで、何も除外しないことを明示したいときに使います。ROWS BETWEEN 1 PRECEDING AND 2 FOLLOWING なので、フレームは id=2,3,4,5 の行になっています。
CURRNT ROW は、現在行を除外します。id=3 の行が除外され、フレームは id=2,4,5 の行になっています。
GROUP は、現在行のグループ (現在行及びピア行) を除外します。id=2,3,4 の行が除外され、フレームは id=5 の行のみになっています。
TIES は、現在行は除外せずピア行のみ除外します。id=2,4 の行が除外され、フレームは id=3,5 の行になっています。

【注】

集約関数をウィンドウ関数として使用した場合は上記のようにフレーム句に従った動作となりますが、専用のウィンドウ関数の場合は、その限りではありません。公式ドキュメント「表9.62 汎用ウィンドウ関数」に記載の関数のうち、first_value, last_value, nth_value はフレーム句に従って動作しますが、その他 (row_number, rank, dense_rank, percent_rank, cume_dist, ntile, lag, lead) においては、フレーム句は無視され、パーティション単位での動作となります。

実行順序

ウィンドウ関数でない集約関数 (GROUP BY, HAVING) とウィンドウ関数を混ぜた場合は、GROUP BY, HAVING の後にウィンドウ関数が実行されます。例えば、以下の SQL_A は、

-- SQL_A
SELECT
    g, n1,
    sum(n2) AS sum2,
    sum(n1) OVER(PARTITION BY g ORDER BY n1) AS sum1
FROM example7 WHERE n2 % 2 = 1
GROUP BY g, n1 HAVING sum(n2) > 2;

SQL_B の結果に対して、ウィンドウ関数の sum が実行されることになります。

-- SQL_B
SELECT
    g, n1,
    sum(n2) AS sum2
FROM example7 WHERE n2 % 2 = 1
GROUP BY g, n1 HAVING sum(n2) > 2;

## SELECT * FROM example7;
 id | g | n1 | n2
----+---+----+----
  1 | A | 10 |  1
  2 | A | 20 |  2
  3 | A | 30 |  3
  4 | A | 10 |  4
  5 | A | 20 |  5
  6 | A | 30 |  6
  7 | B | 30 |  7
  8 | B | 20 |  8
  9 | B | 10 |  9
 10 | B | 30 | 10
 11 | B | 20 | 11
 12 | B | 10 | 12

## SQL_B の結果
 g | n1 | sum2
---+----+------
 A | 20 |    5
 A | 30 |    3
 B | 10 |    9
 B | 20 |   11
 B | 30 |    7

## SQL_A の結果
 g | n1 | sum2 | sum1
---+----+------+------
 A | 20 |    5 |   20
 A | 30 |    3 |   50
 B | 10 |    9 |   10
 B | 20 |   11 |   30
 B | 30 |    7 |   60

ウィンドウ関数で絞り込み

ウィンドウ関数が使えるのは SELECT リスト、およびトップレベルの ORDER BY 句のみです。( なぜトップレベルの ORDER BY 句でも使えるかというと、ORDER BY 句は SELECT リストの後で評価されるからです。) WHERE 句では使えないので、ウィンドウ関数の結果で絞り込みたい場合は、まずサブクエリでウィンドウ関数を実行し、その後で絞り込む必要があります。

WITH x AS (
    SELECT
        id, g, n,
        sum(n) OVER(PARTITION BY g ORDER BY n) AS sum_n
    FROM foo
)
SELECT id, g, n, sum_n FROM x
WHERE sum_n > 10;

余談ですが、Teradata には QUALIFY 句というものがあり、 QUALIFY sum(n) OVER(PARTITION BY g ORDER BY n) > 10 のようにしてウィンドウ関数の結果で絞り込むことができます。是非、PostgreSQL にも実装して欲しいものです。

FILTER 句

集約関数は、FILTER 句を付けることによって、個別に絞り込むことができます。

SELECT
    sum(n) AS all_sum,                           -- 全部の合計
    sum(n) FILTER (WHERE n % 2 = 0) AS even_sum, -- 偶数のみ合計
    sum(n) FILTER (WHERE n % 2 = 1) AS odd_sum   -- 奇数のみ合計
FROM generate_series(1, 10) AS n;

 all_sum | even_sum | odd_sum
---------+----------+---------
      55 |       30 |      25

集約関数をウィンドウ関数として使う場合にも、FILTER 句を付けることができます。

SELECT
    n,
    sum(n) OVER (ORDER BY n) AS all_sum,
    sum(n) FILTER (WHERE n % 2 = 0) OVER (ORDER BY n) AS even_sum,
    sum(n) FILTER (WHERE n % 2 = 1) OVER (ORDER BY n) AS odd_sum
FROM generate_series(1, 10) AS n;

 n  | all_sum | even_sum | odd_sum
----+---------+----------+---------
  1 |       1 |          |       1
  2 |       3 |        2 |       1
  3 |       6 |        2 |       4
  4 |      10 |        6 |       4
  5 |      15 |        6 |       9
  6 |      21 |       12 |       9
  7 |      28 |       12 |      16
  8 |      36 |       20 |      16
  9 |      45 |       20 |      25
 10 |      55 |       30 |      25

WINDOW 句

同じ条件を何度も使いたい場合は、WINDOW 句に書いて、それを引用することができます。

-- OVER 句が全て同じなので、
SELECT id, p, n,
    sum(n) OVER (PARTITION BY p ORDER BY n ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING),
    min(n) OVER (PARTITION BY p ORDER BY n ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING),
    max(n) OVER (PARTITION BY p ORDER BY n ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)
FROM foo
ORDER BY id;

-- 以下のように書ける
SELECT id, p, n,
    sum(n) OVER w,
    min(n) OVER w,
    max(n) OVER w
FROM foo
WINDOW w AS (PARTITION BY p ORDER BY n ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)
ORDER BY id;

WINDOW 句は、後から ORDER BY やフレーム句を追加することもできます。

-- 後から ORDER BY を追加
SELECT id, p, n,
    rank() OVER (w ORDER BY n ASC),
    rank() OVER (w ORDER BY n DESC)
FROM foo
WINDOW w AS (PARTITION BY p)
ORDER BY id;

-- 後からフレーム句を追加
SELECT id, p, n,
    sum(n) OVER (w ROWS 1 PRECEDING),
    sum(n) OVER (w GROUPS 1 PRECEDING),
    sum(n) OVER (w RANGE 1 PRECEDING)
FROM foo
WINDOW w AS (PARTITION BY p ORDER BY n)
ORDER BY id;

この使い方の場合は、OVER の後に必ず ( ) が必要なので注意して下さい。

なお、ORDER BY を持つ WINDOW 句に ORDER BY を追加したり、フレーム句を持つ WINDOW 句にフレーム句を追加したりするとエラーになります。

まとめ

sum 等の集約関数に OVER 句をつけるとウィンドウ関数になる
rank, row_nubmber 等のウィンドウ関数専用の関数もある
ウィンドウ関数は元の行を残したまま他の行の値を使って計算を行う
ウィンドウ関数では PARTITION BY でパーティションを区切る (集約関数の GROUP BY に相当)
ORDER BY とフレーム句によってパーティション内のどの行を処理対象にするかを制御できる
ウィンドウ関数は GROUP BY より後に実行される
ウィンドウ関数の結果で絞り込みたいときはサブクエリを使うしかない
集約関数をウィンドウ関数として使う場合にも FILTER 句が使える
WINDOW 句で OVER 句の共通部分をまとめることができる

シナジーマーケティング株式会社では一緒に働く仲間を募集しています。

TECHSCORE BLOG

クラウドCRMを提供するシナジーマーケティングのエンジニアブログです。