Zenn
🦁

ウィンドウ関数に関して(Window Functions)

2022/12/19に公開
PostgreSQL
SQL
ウィンドウ関数
tech

私は普段、Active Record経由でSQLを使っているので、ウィンドウ関数を使用する機会が滅多にないです。今回はウィンドウ関数に関して理解を深めるために整理していきます。

ウィンドウ関数(Window Functions)

PostgreSQLのドキュメントには下記の様に記載がありました。

※DeepL先生による翻訳

窓関数は、現在の行に何らかの関連があるテーブル行のセットに対して計算を実行します。これは、集約関数で実行できる計算の種類に匹敵します。しかし、窓関数は、非窓集約呼び出しのように行を1つの出力行にグループ化することはありません。その代わり、行はそれぞれ独立した個性を保ちます。裏側では、ウィンドウ関数は、クエリ結果の現在の行以外にもアクセスすることができます。

「指定した基準のまとまりに区切って、計算を行う。この時に集約は行わない」機能がウィンドウ関数。語弊があるかも知れませんが、ウィンドウ関数は、GROUP BY句から「集約」を抜いたような機能だと認識しています。※後述でGROUP BY句とウィンドウ関数を比較して説明しています。

ウィンドウ関数の種類

ウィンドウ関数構文内で呼び出すことができるのは、集計関数(SUM(),COUNT()、AVG()..etc)と汎用ウィンドウ関数です。

汎用ウィンドウ関数の種類について詳しく知りたい方は👇を参照してみてください。
https://www.postgresql.jp/document/14/html/functions-window.html

AVG(price) OVER(PARTITION BY color)のような感じで利用します。詳しくは後述します。

GROUP BY句と比較しながら利用

GROUP BY句とウィンドウ関数を比較した方が理解が進み易いと思うので、まずは比較しながら利用方法をみていきたいと思います。

まずはテーブルとデータを用意。
※説明用なのでテーブルは適当です。

CREATE TABLE products (
　id SERIAL PRIMARY KEY,
　name varchar(10) NOT NULL,
　price int NOT NULL
);

"id"	"name"	       "price"	"color"
1	"product1"	1500	"green"
2	"product2"	50	"white"
3	"product3"	3500	"white"
4	"product4"	2980	"green"
5	"product5"	750	"red"
6	"product6"	999	"blue"
7	"product7"	400	"red"

「製品の色ごとの平均価格を出す」場合を、GROUP BY句とウィンドウ関数で比較してみます。

GROUP BY句の場合

SELECT color, AVG(price) as average_price
	FROM products
	GROUP BY color;
	
"color"	"average_price"
"green"	2240
"blue"	999
"red"	575
"white"	1775

見慣れた形の結果が出てきました。
指定した基準のまとまり(色)に区切って、計算(平均価格算出)を行い、指定した基準のまとまり(色)ごとで集約・まとめてあげています。

ウィンドウ関数の場合

SELECT color,
	AVG(price) OVER(PARTITION BY color) as average_price
	FROM products;
"color"	"average_price"
"blue"	999
"green"	2240
"green"	2240
"red"	575
"red"	575
"white"	1775
"white"	1775

GROUP BYとは違い、各行を集約・まとめずに、平均価格が算出されています。
指定した基準のまとまり(色)に区切って、計算(平均価格算出)を行います。(指定した基準のまとまり(色)で集約・まとめない。)

ウィンドウ関数の構文

上記でなんとなくウィンドウ関数の利用方法が分かったと思うので、構文と注意点をみていきます。

構文

  • 集約関数orウィンドウ関数(SUM,AVG,RANK)の後にOVER句を記述。
    • ex)RANK() OVER()
  • OVER句の中にまとまりの基準をPARTITION BY句の後ろに記述。
    • ex)RANK() OVER(PARTITION BY color)
  • ソートが必要な場合はOVER句の中にORDER BY句を記述。
    • ex)RANK() OVER(PARTITION BY color ORDER BY price)

注意点

  • ウィンドウ関数はクエリのSELECTリストとORDER BY句でのみ記述が許可されている。
    • 基本的にはSELECT句のみ
  • PARTITION BYとORDER BYを省略することも可能。
  • 後述しますが、OVER句の中にORDER BY句を書く場合は一癖あり。

先述の例ではAVG(price) OVER(PARTITION BY color)と使用しており、ORDER BYは省略していました。試しにORDER BY priceをつけて、price順にしてみます。 (分かり易いようにpriceもselectしています)

SELECT color, price,
	AVG(price) OVER(PARTITION BY color ORDER BY price ) as average_price
	FROM products;
"color"	"price"	"average_price"
"blue"	999	999
"green"	1500	1500
"green"	2980	2240
"red"	400	400
"red"	750	575
"white"	50	50
"white"	3500	1775

おそらく想像した値とは違う数値がaverage_priceに入っているのではないでしょうか。
実はウィンドウ関数のORDER BY句には少しクセがあります。ウィンドウ関数内でORDER BY句を書くと「行を指定したソート順で並べた上で、対象のまとまりの先頭行から現在の行までのみを集計」しているのです。
具体的には、先ほどの出力のwhiteに着目すると、上段のaverage_priceは50となっており、上段のpriceと同値が入っています。つまりは下段のpriceは加味されていない値となっています。
下段を見てみると、average_priceに1775となっています。この値は上段と下段のpriceを足して、平均を求めた数値となっています。つまりは、対象のまとまりの先頭行(上段)から現在の行(下段)までを集計しています。

少し癖がありますね...。
もし集計結果に影響を与えない単純なソートをしたいなら下記の様にすることができます。

SELECT color, price,
	AVG(price) OVER(PARTITION BY color) as average_price
	FROM products
	ORDER BY color, price;
"color"	"price"	"average_price"
"blue"	999	999
"green"	1500	2240
"green"	2980	2240
"red"	400	575
"red"	750	575
"white"	50	1775
"white"	3500	1775

せっかくなので、PARTITION BYをつけないパターンもみてみます。

SELECT color, price,
	AVG(price) OVER() as average_price
	FROM products;
	
"color"	"price"	"average_price"
"green"	1500	1454.142857142857
"white"	50	1454.142857142857
"white"	3500	1454.142857142857
"green"	2980	1454.142857142857
"red"	750	1454.142857142857
"blue"	999	1454.142857142857
"red"	400	1454.142857142857

PARTITION BYがない = まとまりの基準がない　ので、price全体での平均値が入ります。

使用例

基本的な使い方を見てきました。
他にもいくつか使用例をみて、もう少し理解を深めていきます。

RANKを使った例

SELECT color, price,
	RANK() OVER(PARTITION BY color ORDER BY price)
	FROM products ;
"color"	"price"	"rank"
"blue"	999	1
"green"	1500	1
"green"	2980	2
"red"	400	1
"red"	750	2
"white"	50	1
"white"	3500	2

色ごとの値段による、ランキングデータを表示することができました。
※この場合はORDER BYは必要です。

PostgreSQLのドキュメントに応用的な使い方？が乗っていたので、今回作成したテーブルで同じようなクエリを作ってみました。(説明用なので、冗長な記述です)

SELECT color, price
	FROM
	(SELECT color, price,
	  rank() OVER(PARTITION BY color ORDER BY price) AS r
	  FROM products
	) as ss
WHERE r = 1;

"color"	"price"
"green"	1500
"red"	400
"white"	50

上記はウィンドウ関数が実行された後に、行のフィルタリングやグループ化を行う必要がある場合に利用する例です。色々と応用できそうですね。

終わり

今回はウィンドウ関数について色々と整理してみました。
パーフォーマンスチューニングの場面などで利用されることもあると聞いたことがあるので、実務でも利用できる場面があれば活用してみたいです。

参考記事・サイト

https://qiita.com/tlokweng/items/fc13dc30cc1aa28231c5

https://www.postgresql.org/docs/current/tutorial-window.html

https://www.postgresql.jp/document/14/html/functions-window.html

Discussion