[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|

```sql:グループ毎の集計値
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の合計が付与されています。

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

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

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

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

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

```sql:グループ毎に連番
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を指定しています。

:::note warn
本来はORDER BYの後にROWS/RANGE等で対象範囲を指定しますが、今回の例のように
それを省略した場合、ソートした順の先頭行から現在行までが処理範囲となります。
範囲指定の方法は、次の例を参照してください。
:::
②SUM()がROW_NUMBER()に代わっています。これはPARTITIONで区切りORDERで
　並び替えた順に連番を振るメソッドとなります。

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

ひとつ前の世代の人口合計と一つ後の世代の人口合計を取得し、表示してみます。
```sql:異なる行のデータを取得する①
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～」です。
```sql:異なる行のデータを取得する
	-- 一つ前の世代の人口合計を取得する
	,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行前まで、つまり、現在のレコードの一つ前のレコードのみを範囲指定し、
　データを取得しています。

:::note info
複数行前のデータや現在行を含めた範囲指定や後行の指定することも可能です。
・二つ前の世代まで合計：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()関数でも同じことができます。

```sql:異なる行のデータを取得する②
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根さん「ちょっと今更やありませんか？」
ワイ「なにがいかんのや？おおん？」