crmprogrammer38の日記

主にDWHやSalesforceのプログラムメモです。

【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などを作っていると使う時も多いかなと思います。