[SQL]Window関数

Window関数とは

　テーブルのデータを任意の列の値でグループ分けし、その各グループ毎に、サマッたり
　平均を出したりランク付け(連番を振る等)したり、別の行の値をもってきてどうこうしたり
　(行間比較)と、色々便利なことができるやつです。
　
　Window関数でできることの中には、他のやり方でできる事が多くあります。
　ですが、例えば行間比較等、相関サブクエリと自己結合を使って行うよりも、Window関数
　を使った方が可読性が高いSQLで記述でき、また、テーブルアクセス回数も少なく済み
　パフォーマンスにも良い影響を与える(内容にもよりますが)といった利点があります。
　　
　Window関数の使い方や例を、いくつか記載したいと思います。

環境

・SQLServer2019 Expressで実行しています
・ PostgreSQL、MySQL、oracleでも問題なく実行できるのではないかと(かと！)

グループ毎の集計値を取得する

以下のような、5年毎の世代別の人口合計を示す表から
西暦毎(にグループ分けし、そのグループ毎)の各人口合計を取得してみます。

ad(西暦)	age_class(世代)	total_population(人口合計)
1920	0～4歳	100
1920	5～9歳	200
1920	…	1000
1920	85歳以上	010
1925	0～4歳	300
1925	…	2000
2015	…	10000
2015	110歳以上	3

グループ毎の集計値

SELECT 
	ad
	,age_class
	,total_population
	,SUM(total_population) OVER (
		PARTITION BY ad
	) AS [世代別合計]
FROM 
    demographics

結果は、以下の通りとなります。

ad(西暦)	age_class(世代)	total_population(人口合計)	世代別合計
1920	0～4歳	100	1310
1920	5～9歳	200	1310
1920	…	1000	1310
1920	85歳以上	010	1310
1925	0～4歳	300	2300
1925	…	2000	2300
2015	…	10000	10013
2015	110歳以上	3	10013

行数は元のまま、各行にその世代のtotal_populationの合計が付与されています。

	,SUM(total_population) OVER (
		PARTITION BY ad
	) AS [世代別合計]

この部分を解説していきます。

①西暦(ad列)の値毎にグループを分ける為、それをPARTITON BY 列名で指定します。
②グループ分けしたものをどう処理したいかを、先頭に書きます。
今回はグループ分けしたデータ単位で人口合計をサマリしたいので、SUM(total_population)
と記載します。OVERは、その後ろの()内がWindowで、その前のメソッド(SUM()等)は、
　そのWindowに対して行うということを示すお約束みたいなもの(と思ってます。僕は)です。

SUMの場合、PARTITION BYは省略可能です。省略した場合、データ全量を一つの分けられた
グループとみなし、全量を合計します。

グループ毎に連番を振る

西暦毎に、世代が高い順に1から始まる連番を振ってみます。
(*)1920年のデータについて、世代が一番高いものから1から連番、
　1925年のデータについて、世代が一番高いものから、また1から連番を付与

グループ毎に連番

SELECT 
	ad
	,age_class
	,total_population
	,ROW_NUMBER() OVER (
		PARTITION BY ad ORDER BY age_class DESC
	) AS [行番号]
FROM demographics

結果は、以下の通りとなります。

ad(西暦)	age_class(世代)	total_population(人口合計)	行番号
1920	85歳以上	010	1
1920	…	1000	…
1920	5～9歳	200	17
1920	0～4歳	100	18
1925	85歳以上	300	1
1925	…	1000	…
1925	0～4歳	100	18

各西暦の各世代毎に連番が付与されていますね。
先ほどのSQLとの違いですが、
①PARTITION BYの後に、ORDER BYが追加されています。
　これは、PARTION BYで区切られたグループ内のデータについて、
　指定の項目で並び変えた順で指定した範囲内のデータを処理するという意味になります。
　今回の場合、西暦ごとにデータを分け、その中で世代が高い順に連番を振りたいので、
　PARTITION BYにad(西暦)を指定し、ORDER BYにage_class(世代) DESCを指定しています。

本来はORDER BYの後にROWS/RANGE等で対象範囲を指定しますが、今回の例のように
それを省略した場合、ソートした順の先頭行から現在行までが処理範囲となります。
範囲指定の方法は、次の例を参照してください。

②SUM()がROW_NUMBER()に代わっています。これはPARTITIONで区切りORDERで
　並び替えた順に連番を振るメソッドとなります。

SUMの場合と同様、ROW_NUMBERの場合はPARTITION BYは省略可能です。省略した場合、
データ全量を一つの分けられたグループとみなし、全量をソートした後、連番を振ります。

異なる行のデータを取得する①

ひとつ前の世代の人口合計と一つ後の世代の人口合計を取得し、表示してみます。

異なる行のデータを取得する①

SELECT 
	ad
	,age_class
	,total_population
	-- 一つ前の世代の人口合計を取得する
	,SUM(total_population) OVER (
		PARTITION BY ad 
		ORDER BY age_class
		ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING
	) AS bef_age_total
	-- 一つ後の世代の人口合計を取得する
	,SUM(total_population) OVER (
		PARTITION BY ad 
		ORDER BY age_class
		ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING
	) AS aft_age_total
FROM demographic

結果は、以下の通りとなります。

ad(西暦)	age_class(世代)	total_population(人口合計)	bef_age_total	aft_age_total
1920	0～4歳	200	NULL	500
1920	5～9歳	500	200	300
1920	10～14歳	300	500	600
…	…	…	…	…
1920	100～104歳	5	10	NULL

各レコードの[bef_age_total]に一つ前のレコードの total_population(人口合計) が、
[aft_age_total]に、一つ後のレコードのtotal_population(人口合計)が表示されていますね。
0～4歳は前のレコードが無いため、100～104歳は後の世代が無いため、それぞれ
bef_age_total/aft_age_totalがNULLとなっています。

SQLを見ていきます。
今回変わったのは、以下の「ROWS BETWEEN～」です。

異なる行のデータを取得する

	-- 一つ前の世代の人口合計を取得する
	,SUM(total_population) OVER (
		PARTITION BY ad 
		ORDER BY age_class
		ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING
	) AS bef_age_total

これは、
①PARTITION BYにてad(西暦)ごとにグループ分けしたデータを、age_class(世代)で昇順にソート。
②ROWS_BETWEEN 取得範囲From AND 取得範囲Toとすることで、取得対象行範囲を指定。
　今回の例では、1(行) PRECEDING(前) AND(から) 1(行) PRECEDING(前)とすることで、
　1行前から1行前まで、つまり、現在のレコードの一つ前のレコードのみを範囲指定し、
　データを取得しています。

複数行前のデータや現在行を含めた範囲指定や後行の指定することも可能です。
・二つ前の世代まで合計：2 PRECEDING AND 1 PRECEDING(2行前から1行前)
・二つ前の世代だけを取得：2 PRECEDING AND 2 PRECEDING(2行前から2行前)
・後の行を指定：PRECEDINGをFOLLOWINGに変更
・現在の行：CURRENT ROW
・無制限に前の行を取得：UNBOUNDED PRECEDING
・無制限に後ろの行を取得：UNBOUNDED FOLLOWING

③SUM(total_population)で、範囲取得した値をサマリしています。
　今回は1行しか取得してないのでサマリもクソもありませんが、複数行を範囲指定した場合、
　SUMやAVGといった関数では、範囲内全行の値が処理されます。

異なる行のデータを取得する②

単一行の値を取得する場合は、lag() 、lead()関数でも同じことができます。

異なる行のデータを取得する②

SELECT 
	ad
	,age_class
	,total_population
	-- 一つ前の世代の人口合計を取得する
	,LAG(total_population,1) OVER (
		PARTITION BY ad 
		ORDER BY age_class 
	) AS bef_age_total
	-- 一つ前の世代の人口合計との差分を取得する
	,total_population -
	LEAD(total_population,1) OVER (
		PARTITION BY ad 
		ORDER BY age_class
	) AS aft_age_total 
FROM demographics

結果は、「異なる行のデータを取得する①」と同一です。
LAG(total_population,n)で、n行前の値を、
LEAD(total_population,n)で、n行後の値を取得します。

WINDOW関数では、今回の例示したSUM()やLAG()、LEAD()、ROW_NUMBER以外にも、
組み込みウィンドウ関数が存在しますので、調べて色々と試してみてください。

M根さん「ちょっと今更やありませんか？」
ワイ「なにがいかんのや？おおん？」