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