SQLのWINDOW関数を簡単な例を見て理解する
SQLのWINDOW関数をパッと思い出す
SQLのWindow関数はsyntaxがややこしい気がする
新規に登場する予約語が多すぎる
Window関数単体を理解するだけならWindow関数を簡単な例を見て理解するのほうがわかりやすい
基本的な構文としてはこうなる
sql(関数(..) OVER (..))これで1つのcolumnを表す
外側の括弧は不要だがわかりやすさのために書いてる
table全体を
OVER (..) で区切ったあとに、それぞれに 関数() を適用している簡単な例
GROUP BYと比較してみるとわかりやすい
ref 
p.65-
OVERの中身は、PARTITION BY句が何やってるかイメージしやすい
例.sqlCOUNT (*) OVER (PARTITION BY address)こういう経過を辿る
1 table全体を、
address の値で区切るaa三重県 1
----
千葉県 1
千葉県 1
----
和歌山県 1
---
東京都 1
東京都 1
東京都 1こういう感じでaddressの値に従って、tableが区切られる
この区切られたtableがそれぞれ小さなtableとイメージするとわかりやすい気がする
この小さなtableのことをwindowと呼んでいる
window同士は独立した存在だとイメージしたほうが良い
2 それぞれのwindowに対して
COUNT(*) をするaa三重県 1
----
千葉県 2
千葉県 2
----
和歌山県 1
---
東京都 3
東京都 3
東京都 3集約をしていないので、元のtableと同じ行数になる
ちょっとだけ複雑にした例
左3columnはそのままで、「(会社ごとに)前年度の増減」を
+ / - で表示するcolumnを追加するref p.127, p.139
sqlSIGN(sale - MAX(sale)
OVER ( PARTITION BY company
ORDER BY year
ROWS BETWEEN 1 PRECEDING
AND 1 PRECEDING) )まず全体の括弧の付き方に注意しておく
名前が紛らわしいので、
SIGN(sale1 - MAX(sale2) OVER (..)) と命名しておく省略して書くと、
SIGN(sale1 - <MAX(sale2) OVER (...)> ) である括弧の対応のわかりやすさのために括弧の種類を変えてる
OVER(..) は、 MAX(sale2) の方にのみかかっていることに注意 sale1 には影響を及ぼしていない sale1 は元のtableのただの sale columnのことを表しているこういう経過を辿る
まず
OVER(..) 内を見る1
PARTITION BY company companyごとに別々のwindowに分ける
これでcompanyごとの小さなwindowが3つできたことになる
以降これらは互いに干渉しない
aA 2002 50
A 2003 52
A 2004 55
A 2007 55
---
B 2001 27
B 2005 28
B 2006 28
B 2009 30
---
C 2001 40
C 2005 392
ORDER BY year そのwindow内でyearでsortする
今回の例では元からsortされてるので1の時と特に変わっていない
が、実際の状況ではやっておくべきだろう
aA 2002 50
A 2003 52
A 2004 55
A 2007 55
---
B 2001 27
B 2005 28
B 2006 28
B 2009 30
---
C 2001 40
C 2005 39ここ以降、理解が不十分なため推測が交じる
3
ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING 前提として今処理している行のことを
CURRENT ROW と言うそれに対して、
1 PRECEDING は、その名の通り CURRENT ROW の1つ前の行を表す ROWS BETWEEN .. AND .. で行の集合を表す今回は、
1 PRECEDING から 1 PRECEDING なので、結局、1つ前の行のみのことを表すaA 2002 50 <-②ここが`ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING`
A 2003 52 <-①ここが`CURRENT ROW`のとき、これによって、「1つ前の行」が選択できた
ちなみに他の指定の仕方の例としては、↓とかがある
sqlROWS BETWEEN 2 PRECEDING AND CURRENT ROW次に
OVER(..) を飛び出る4
<MAX(sale2) OVER (...)> OVER (..) の結果に対して MAX() を適用している紛らわしいがこれは
MAX() である必要はない目的は
OVER(..) の結果、得られた1行を取得したいだけ1行に対して
MAX() しているので、ただその値が取得できる、ということをやっている実際
MIN(..) を使っても結果は変わらないSQLが
IDENTITY(..) みたいな何もしない関数を用意していないので紛らわしくなっている5
sale1 - <MAX(sale2) OVER (...)> SQLは上から処理される
だから、
sale1 - <MAX(sale2) OVER (...)> と書いた場合、 sale1 は、 CURRENT ROW を表すそして、WINDOW関数により
<..> はその前列を表すだから、
sale1 - <..> によって、「前回との差分」を表現できるここでの、
sale1 が本当にCURRENT ROWを表しているのかどうかが不明 #??でもそう解釈しないと理解できない
これはSQLの実行計画とかを調べるとたぶん理解できるはず
6
SIGN(..) (これはもはやWINDOW関数とは関係ないが、)
.. の符号によって -1 / 0 / 1 を返すこれをCASEでゴニョゴニョすれば目的が達成される