Developer

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

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

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


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

では、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;

すると今度は、各パーティションごとにパーティションの先頭から末尾までの合計が返ってきています。
次は、ORDERのみ使用してみます。

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

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

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

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

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


今回の学習ポイント

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


練習問題

問題として使用するデータは「SQL練習問題」のものを使用しています。
以下からダウンロード可能です。
また、ダウンロードしたら以下のようにしてworldcup2014データベースを作成し、インポートしてください。
※worldcup2014.zip解凍してできるworldcup2014.sqlファイルをCドライブ直下に配置した場合。

worldcup2014.zip;

CREATE DATABASE worldcup2014;

use worldcup2014

source C:\worldcup2014.sql
1.

答え

 

 

 

連載目次リンク

SQL基礎 連載目次

関連する連載リンク

SQL練習問題 一覧まとめ