【SQL基礎】集約関数をウィンドウ関数として使用してみよう

本項は実際に集約関数のSUMをウィンドウ関数として使用して動きを見ていきます。

集約関数をウィンドウ関数として使ってみる

では、SUM関数を使ってみましょう。
OVER句には、PARTITIONやORDER、ROWS、RANGEなどを指定するのですが
まずは、下記の用にOVER句の中を全て省略してやってみましょう。

SELECT *,
SUM(score) OVER() AS max
FROM score;

このように、全体の合計が返ってきます。
次に、PARTITIONだけ指定してみましょう。

SELECT *,
SUM(score) OVER(PARTITION BY name) AS max
FROM score;

すると今度は、各パーティションごとにパーティションの先頭から末尾までの合計が返ってきています。
指定はname列なので、nameが同じ部分（ジロウ、タロウ、ハナコ、ユキコ）の４つのパーティションに分かれています。
次は、ORDERのみ使用してみます。

SELECT *,
SUM(score) OVER(ORDER BY name) AS max
FROM score;

すると、ORDER BYで指定した列を並べ替えた後、全体の先頭からORDER BYで指定した列の現在行と同値の末尾の行までの合計が返ってきています。
今回の例でいうとnameがジロウの行は、全体の先頭からジロウの末尾の行までの合計、
タロウの行は、全体の先頭からタロウの末尾の行までの合計といった具合です。
合計の開始位置が全体の先頭からになっているのは、PARTITION BYを省略しているためです。

次は、ROWSのみを使用してみます。

SELECT *,
SUM(score) OVER(ROWS UNBOUNDED PRECEDING) AS max
FROM score;

開始行のみをUNBOUNDED PRECEDINGと指定しているので、パーティションの先頭から現在行までの合計が表示されています。
また、今回の場合もPARTITION BYを省略しているので全体の先頭からになっています。

PARTITION、ORDER、ROWSのそれぞれ個別の動きを確認してきましたが、最後に全て利用してみましょう。

SELECT *, 
SUM(score) OVER(PARTITION BY name ORDER BY id DESC ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS max
FROM score;

PARTITIONにnmaeを、ORDERでidを降順で、ROWSで現在の行から1行前（PRECEDING ）から1行後（FOLLOWING）指定しているので
nameが同じ部分（ジロウ、タロウ、ハナコ、ユキコ）の４つのパーティションに分かれていて、その中でidの値が降順になるように並べ替えられています。
そして、合計する範囲が同一パーティション内の現在の行から1行前から1行後になるので、6行目は1行前は別パーティションなので、現在行と1行後の合計で150。7行目は210といった具合になっています。

今回の学習ポイント

・OVER句のオプションは省略できる

練習問題

SELECT p.id, p.kickoff, COUNT(g.id) AS score
FROM pairings p LEFT JOIN goals g ON p.id = g.pairing_id 
WHERE p.my_country_id = 1 
GROUP BY p.id, p.kickoff;

連載目次リンク

SQL基礎 連載目次

関連する連載リンク

SQL練習問題 一覧まとめ