Beyond The Limit

はじまりは2001年

SQL学び直し13

第八章:SQLで高度な処理を行う

8-1:ウインドウ関数

  • ウインドウ関数とはOLAP(OnLine Analytical Processing)関数とも呼ばれる
    • OLAPとはリアルタイムにデータ分析を行う処理のこと
  • 構文:ウインドウ関数 OVER ( PARTITION BY <列リスト> ORDER BY <ソート用列リスト>)
    • rank over (partition by A orderby B) as ranking from table といった形で使う、A列毎にB列順にrankingカラムでランクを付けてくれる
  • ウインドウ関数は集約関数をウインドウ関数として使う、ウインドウ専用関数を使う、の2種類に分けられることができる
  • partition byによって区切られたレコードの集合をウインドウと呼ぶ、窓ではなく範囲を表わす
  • partition byを使わない場合、テーブル全体が1つのウインドウとしてみなされる(order byのカラム基準になる)
  • 以下、各ウインドウ専用関数の違い
    • rank()の場合に1位が3つある場合、1位、1位、1位、4位と飛ぶ
    • dense_rank()の場合に1位が3つある場合は1位、1位、1位、2位と飛ばない、デンスランクと読む
    • row_number()の場合に1位が3つある場合は1位、2位、3位、4位と同じ値でも2位と3位になる、同じ値の場合はDBMSが適当に値を振る
      • ウインドウ専用関数を使う時は()の中は空のまま使う、これは全てのウインドウ専用関数で同じ、引数を取らない
  • ウインドウ関数h原則としてSELECT句のみで使える、AVGやSUMを利用すると降順・昇順でのその時点での累計や累計の平均を出力出来る
  • 移動平均を出す場合
    • avg(カラムA) over(order by カラムB rows 直近の何行か数値を入れる preceding) as ~
    • その値の直近3行分の平均値をasの中に入れられる、precedingは前のという意味
    • カレントレコードから後ろを対象にするには、following(後の)を使う
    • 前後を対象にする場合は(order by A rows between 前の分の数値 preceding and 後ろの分の数値 following) as ~とする
  • OVER句の中のorder byはウインドウ関数がどういった順序で計算数かを決めるだけの役割しか持っていない、結果の並び替えには影響しない

8-2:GROUPING演算子

  • グループ化した結果の合計を求めることができる、ROLLUP、CUBE、GROUPING SETSの3種類がある
  • と思ったら、postgresqlではサポートされていないらしい

今日はここまで、長かったけど終わった。
とても勉強になったし、実務でも役立ったし、良い本でした。