RDBMS：ウィンドウ関数とGROUPING演算子

ウィンドウ関数

ウィンドウ関数は、ランキング、連番生成等の集約関数で通常できないような操作をする際に用いる。別名としてOLAP関数(OnLine Analytical Processing)とも呼ばれ、データベースをリアルタイムに処理して、オンラインでデータ分析などを行う際に使用する。よって、ウィンドウ関数は、OLAP用途のために、SQLに追加された機能。

具体的にどんなものかというと次のようなもの。

ウィンドウにすることの旨味としては、区切られたウィンドウ内でデータを操作できる点。ウィンドウ関数の構文は次のようになっている。

ウィンドウ関数 OVER ([PARTITION BY 列リスト]) ORDER BY ソート用列リスト)
-- []内は省略可

構文内に登場するウインドウ関数として使えるものをまとめる。

• 集約関数：SUM,AVG,COUNT,MAX,MIN
• ウィンドウ専用関数：RANK,DENSE_RANK,ROW_NUMBER

例のごとく次のテーブルに対して、使ってみてどんな感じかを確認する。

RANK関数：レコードのランキングを算出

RANK関数はレコードの順位を算出する。

結果をみても分かる通り、ウィンドウ関数はGROUP BYのカット機能とORDER BYの順序付を合わせたような機能となっている。ただし、PARTION BYは集約機能は持たない。

また、PARTION BYは省略可能で、省略するとテーブル全体のランキングが算出される。

DENSE_RANK関数、ROW_RANK関数：色々な順位付

DENSE_RANKもROW_RANKも名前の通り順位付のウィンドウ専用関数。RANKとの違いをまとめると次の通り。

関数名	順位付ルール
RANK	同順位がいる場合に、後ろの順位を飛ばす (１位、１位、３位)
DENSE_RANK	同順位がいる場合に、後ろの順位を飛ばさない (１位、１位、２位)
ROW_RANK	一意の連番にする （１位が３レコードある：１位、２位、３位）

集約関数をウィンドウ関数として用いる

集約関数をウィンドウ関数として用いる場合、ウィンドウ内の結果が集約される。例を示す。

• SUMの場合

  

• AVGの場合

  

• PRECEDINGとFOLLOWINGでウィンドウフレームを動かす

  範囲を指定して、当該部分だけに対して集約関数を実行することもできる

  

GROUPING演算子

GROUP BY句と集約関数だけでは、小計と合計を同時に求めることができなかったが、GROUPING演算子を用いれば一気に計算できる。GROUPING演算子は下記の３種類がある。

• ROLLUP
• CUBE
• GROUPING SETS

ROLLUP：合計と小計を一度に求める

GROUPING:データとしてのNULLを見分ける

操作した結果得られるテーブルは、実際のテーブルのデータが本当にNULLの場合、とGROUP BYによって集約キーがないためNULLとなっている場合の区別がつけることが難しい。そんな時に使うのが、GROUPING。

これも具体例を見た方がわかりやすい。

CUBE：データで積み木を作る

CUBEは『全ての可能な組み合わせ』を１つの結果にまとめるようなもの。

イメージ的には、次のようなもの。

コマンドを実行して得られる結果は次のとおり。

GROUPING SETS：欲しい積み木だけ取得

GROUPING SETSは、GROUPINGやCUBEで求めた結果の一部を取得する演算子。

参考