SQL: Window関数を理解できた時の思い出2
前の続きです。
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などを作っていると使う時も多いかなと思います。