【SQL】ウィンドウ関数について

基幹システムの開発をする上でプログラミング以外にもSQLによるデータベースへの問い合わせが必要不可欠となります。そんなSQL構文の一つであるウィンドウ関数について今回は紹介します。

1 ウィンドウ関数とは
2 構文
3 SUM関数使った例
4 順序付けを行う例
5 前後の行を参照する例
6 まとめ

ウィンドウ関数とは

集約関数を使用しつつ、集計対象の行に対して一定の範囲（ウィンドウ）を指定して計算するための関数
GROUP BYとの違いは行ごとに処理を行い集約をしないこと
レコード間での比較を簡単に行える

構文

関数名([式] OVER [PARTITION BY 分割列] [ORDER BY ソート列] [ROWS|RANGE ウィンドウ範囲])

SUM関数使った例

SELECT
    pref_name
    ,age
    ,SUM(CASE WHEN pop.age < 20 THEN pop.population ELSE 0 END)OVER(PARTITION BY pref_name) AS child
    ,SUM(CASE WHEN pop.age >= 20 THEN pop.population ELSE 0 END)OVER(PARTITION BY pref_name) AS adult
FROM
    tbl_tpopulation pop
;

上記の例では都道府県別人口テーブルを元に都道府県で区切ったパーティションごとに20歳未満を子供、20歳以上を大人として集計を行っています。GROUP BYと違い表全体の集計を行わず1行ごとに処理を行うため集計を行いながら明細情報を表示することができます。

順序付けを行う例

SELECT
	od.order_date
	,od.item_name
	,od.price
	,RANK()OVER(PARTITION BY od.order_date ORDER BY price DESC) AS RANK
	,DENSE_RANK()OVER(PARTITION BY od.order_date ORDER BY price DESC) AS DENSE_RANK
	,ROW_NUMBER()OVER(PARTITION BY od.order_date ORDER BY price DESC) AS ROW_NUMBER
FROM
	tbl_tsample_order od
ORDER BY
	od.order_date
	,price DESC
;

上記の例では日別商品売上テーブルを元に売上日で区切ったパーティションごと金額に順番を付けています。
ここで使用されている関数『RANK』『DENSE_RANK』『ROW_NUMBER』の違いは同一順序のものが出てきた際の挙動にあります。
『RANK』は次の番号をスキップ。『DENSE_RANK』は次の番号をスキップしない。『ROW_NUMBER』は上から連番付与をするのみ。SQLの用途によって使い分けをしましょう。

前後の行を参照する例

SELECT
	od.order_date
	,od.item_name
	,od.price
	,LAG(od.price)OVER(PARTITION BY od.item_name ORDER BY price) AS before_day_price 
	,ROUND(price * 1.0/(LAG(od.price)OVER(PARTITION BY od.item_name ORDER BY price)) *100,0) || '%' AS before_rate
	,LEAD(od.price)OVER(PARTITION BY od.item_name ORDER BY price) AS next_day_price
	,ROUND(price * 1.0/(LEAD(od.price)OVER(PARTITION BY od.item_name ORDER BY price)) *100,0) || '%' AS next_rate
FROM
	tbl_tsample_order od
ORDER BY
	od.item_name
	,od.order_date
;

上記の例では日別商品売上テーブルを元に商品名で区切ったパーティションごと金額に順番を付けています。
ここで使用されている関数『LAG』『LEAD』の違いは取得する行の位置に違いがあります。
『LAG』はカレント行の上を取得。『LEAD』はカレント行の下を取得します。
※取得ができない場合は、NULLが返却されるので注意が必要です。

まとめ

今回は弊社でよく使用されるウィンドウ関数をピックアップして紹介しました。
このようにSQLを用いることで大量のデータを効率よく加工処理することが可能です。
プログラムの処理で時間がかかっていた処理のSQLを見直すことで処理時間を短縮できるケースが弊社でも多々あります。何か問題が発生した際には解決策の1つとしてSQLを検討してみるのも良いかもしれません。

TEXT BY

TIGER RACK

タイガーラック編集部