前の続きです。
crmprogrammer38.hatenablog.com
Window関数では、Windowの中で並び替えをした順番に各行で計算する仕組みがあります。累計の計算を例にします。（BIツールで、ランニングサムの名称がついていたりします）
以下の年別累計の項目は、年毎のWindowの中で年月の古い順に出費を足した値となります。
| 年 | 年月 | 出費 | 年別累計 |
| 2016 | 201611 | 27,000 | 27,000 |
| 2016 | 201612 | 24,000 | 51,000 |
| 2017 | 201701 | 18,000 | 18,000 |
| 2017 | 201702 | 3,000 | 21,000 |
| 2017 | 201703 | 15,000 | 36,000 |
| 2017 | 201704 | 27,000 | 63,000 |
これを出力するSQLは次になります。(rows betweenを省略しないで書いています)
select
年
,年月
,出費
,sum(出費) over(partition by 年 order by 年月
rows between unbounded preceding and current row ) as 年別累計
from データ;
このSQLでrows between～の記載がありますが、ここで行を計算する際に、Windowのどこからどこまでを計算対象とするかの指定をしています。
英語の通りなのですが、Window内の、先頭行(unbounded preceding)から現在行(current row)までをsumするという指定になります。
※補足ですが、sumやcountなどのWindow関数でorder byを指定した場合は、"rows between unbounded preceding and current row"がデフォルトなので省略ができます。
rows betweenで次を指定できます。
| unbounded preceding | 先頭行 |
| N preceding | N行前の行 |
| current row | 現在行 |
| N following | N行後の行 |
| unbounded following | 最終行 |
上の指定を組み合わせると以下のような結果がとれます。
- 1行前の出費は、1段下がったように値が表示されます。
- 1行後の出費は、1段上がったように値が表示されます。
- 現在の行数は、年単位に行毎に数が増えています（row_numberと同じ結果です）
- 残り行数は、そのWindowで現在行を含めずに何行残っているが表示されます。
| 年 | 年月 | 出費 | 1行前の出費 | 1行後の出費 | 現在の行数 | 残り行数 |
| 2016 | 201611 | 27,000 | 24000 | 1 | 1 | |
| 2016 | 201612 | 24,000 | 27,000 | 2 | 0 | |
| 2017 | 201701 | 18,000 | 3000 | 1 | 3 | |
| 2017 | 201702 | 3,000 | 18,000 | 15000 | 2 | 2 |
| 2017 | 201703 | 15,000 | 3,000 | 27000 | 3 | 1 |
| 2017 | 201704 | 27,000 | 15,000 | 4 | 0 |
これを出力するSQLは次になります。
select
年
,年月
,出費
,sum(出費) over(partition by 年 order by 年月
rows between 1 preceding and 1 preceding ) as "1行前の出費"
,sum(出費) over(partition by 年 order by 年月
rows between 1 following and 1 following ) as "1行後の出費"
,count(*) over(partition by 年 order by 年月
rows between unbounded preceding and current row) as "現在の行数"
,count(*) over(partition by 年 order by 年月
rows between 1 following and unbounded following ) as "残り行数"
from データ;
rows between で範囲をどこからどこまでにするかで欲しい値が取得できます。
最後に
rows between以外にもrange betweenというのもありますが触れないでおこうと思います。
Window関数があると便利だなと思っているのは次です。
| キー | 開始日 | その他属性・・ |
| KEY001 | 2016-01-01 | ・・ |
| KEY001 | 2016-08-15 | ・・ |
| KEY001 | 2017-03-01 | ・・ |
| KEY002 | 2016-04-10 | ・・ |
| KEY002 | 2017-02-01 | ・・ |
上記は、いわゆる履歴管理されているマスタですが、開始日だけ持っています。
結合条件で使うため終了日を計算する場合があります。終了日は同じキーの次の開始日の1日前、最終行の場合は固定で2100-12-31とします。
その時にWindow関数を使うと次のようなデータが作れます。
| キー | 開始日 | 終了日 | その他属性・・ |
| KEY001 | 2016-01-01 | 2016-08-14 | ・・ |
| KEY001 | 2016-08-15 | 2017-02-28 | ・・ |
| KEY001 | 2017-03-01 | 2100-12-31 | ・・ |
| KEY002 | 2016-04-10 | 2017-01-31 | ・・ |
| KEY002 | 2017-02-01 | 2100-12-31 | ・・ |
この結果となるSQLは次の通りです。（Oracle用のため日付の計算はDB毎で変更が必要です。）
Select
キー
,開始日
,coalesce(
max( 開始日 ) over( partition by キー order by 開始日
rows between 1 following and 1 following ) -1
, to_date('21001231','YYYYMMDD') ) as 終了日
,その他属性
From
履歴を持つマスタ
DWHなどを作っていると使う時も多いかなと思います。
