プログラマ38の日記

主にプログラムメモです。

【SQL】Window関数を理解できた時の思い出

はじめて扱ったデータベースは、Oracle8で、その時はWindow関数自体がなく、検索条件と結合条件が入り混じるSQLを書いていました。

 

その後Oracle9になって、SQLも変化していましたが、Window関数とは距離を置いていました。あまり使う必要性を感じなかったのもありますが、よく理解できなかったというのが正直なところです。

 

ちょっと理解できて嬉しかったときの思い出を書こうと思います。

 

まず、Windowを理解する

最初わけがわからなかったのですが、Windowや、関数の動く順番をイメージできたら理解できました。

Window関数は、検索結果を取得後、その結果に対して動く関数

このイメージを持ててからは早かったです。

例えば次のデータがあります。

年月 出費
2016 201611 27,000
2016 201612 24,000
2017 201701 18,000
2017 201702 3,000
2017 201703 15,000
2017 201704 27,000

 このデータの検索結果全体に対してWindow関数を指定すると次のようになります。Window内で集計した結果が各行に表示されます。

年月 出費 合計件数 合計出費
2016 201610 27,000 6 114,000
2016 201611 24,000 6 114,000
2016 201612 18,000 6 114,000
2017 201701 3,000 6 114,000
2017 201702 15,000 6 114,000
2017 201703 27,000 6 114,000

この時のSQLは次のようになります。

select
  年
 ,年月
 ,出費
 ,count(*)  over() as 合計件数
 ,sum(出費) over() as 合計出費
from
  データ

over() を指定すると検索結果全体がWindowになります。

 

そして、年単位でWindowを指定することもでき、その場合次の結果となります。

年月 出費 合計件数 合計出費 年別_合計件数 年別_合計出費
2016 201611 27,000 6 114000 2 51,000
2016 201612 24,000 6 114000 2 51,000
2017 201701 18,000 6 114000 4 63,000
2017 201702 3,000 6 114000 4 63,000
2017 201703 15,000 6 114000 4 63,000
2017 201704 27,000 6 114000 4 63,000

 この時のSQLは次の通りです。

select
  年
 ,年月
 ,出費
 ,count(*)  over() as 合計件数
 ,sum(出費) over() as 合計出費
 ,count(*)  over(partition by 年) as 年別_合計件数
 ,sum(出費) over(partition by 年) as 年別_合計出費
from
  データ

 over(partition by 年) を指定することで年毎でWindowになります。

 

Windowの考え方は基本は上記の通りです。

rows between ( preceding や following、current row )の指定はあるのですが、上記を理解できた後は早かったように思います。別の機会に残りは書いてみたいと思います。