はじめて扱ったデータベースは、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 ）の指定はあるのですが、上記を理解できた後は早かったように思います。別の機会に残りは書いてみたいと思います。
