「大人の教養・知識・気付き」を伸ばすブログ

一流の大人(ビジネスマン、政治家、リーダー…)として知っておきたい、教養・社会動向を意外なところから取り上げ学ぶことで“気付く力”を伸ばすブログです。目下、データ分析・語学に力点を置いています。

MENU

SQL中級者になる!(その02/X)

初めに

 データベースとSQLは現在のデータサイエンスに必須な知識・スキルの1つ。その基礎を

power-of-awareness.com

を基に学んできた。
 この知識をより昇華させてSQL中級者になりたい!ため、同じ著者の

を参考に、更なるSQLの知識を拡充していこう。

2. 必ずわかるウィンドウ関数

 ウィンドウ関数は様々な応用を持つが、特にこれまで行間比較において相関サブクエリに頼らなければならなかったケースにおいてウィンドウ関数を用いることで\mathrm{SQL}を綺麗に書けるようになった。

2.1 ウィンドウとは何か

 ウィンドウ関数は\mathrm{FROM}句から選択されたレコードの集合に対して\mathrm{ORDER\ BY}による順位付けや\mathrm{ROWS\ BETWEEN}によるフレーム定義が行われた上でのデータセットである。
 ウィンドウを暗黙的に定義する方法と明示的に定義する方法の2つが存在する。

-- 移動平均を求める
-- (1) 無名ウィンドウ構文
SELECT Tbl.ID,Tbl.name, Tbl.Price,
             AVG(Tbl.Price) OVER (ORDER BY ID ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS MAVG
FROM Tbl
-- (2) 名前付きウィンドウ構文
SELECT Tbl.ID,Tbl.name, Tbl.Price,
             AVG(Tbl.Price) OVER W AS MAVG
FROM Tbl
WINDOW W AS (ORDER BY ID ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)

2.2 1枚でわかるウィンドウ関数

 ウィンドウ関数の機能を整理すると、以下の3つの機能に集約できる。

  • \mathrm{PARTITION\ BY}句によるレコード集合のカット
  • \mathrm{ORDER\ BY}句によるレコードの順序付け
  • フレーム句によるカレントレコードを中心としたサブセットの定義

2.3 フレーム句を用いた行の取得

 フレーム句はカレントレコードを基準に計算する統計指標を\mathrm{SQL}で簡単に算出するために導入された。それ以外にもフレーム句を導入することで従来\mathrm{SQL}で難しかった行間比較を自在に行うことができる。

  • 直近値を求める
-- サンプル
SELECT Tbl.calendar_date as [date],
             Tbl.load_val AS [cur_load],
           MIN(calendar_date) OVER (ORDER BY calendar_date ASC ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) AS latest_date,
           MIN(load_val) OVER (ORDER BY calendar_date ASC ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) AS latest_load
FROM Tbl

 フレーム句で利用できるオプションは以下のとおり:

  \mathrm{ROWS} 移動単位を行で設定する
  \mathrm{RANGE} \mathrm{ORDER\ BY}句で指定した列を基準に移動単位を列の値で設定する
  n\ \mathrm{PRECEDING} nだけ前へ(小さい方へ)移動する
  n\ \mathrm{FOLLOWING} nだけ後へ(大きい方へ)移動する
  \mathrm{UNBOUNDED\ PRECEDING} 無制限に遡る方へ移動する
  \mathrm{UNBOUNDED\ FOLLOWING} 無制限に下る方へ移動する
  \mathrm{CURRENT\ ROW} 現在行
2.3.1 行間比較の一般化
SELECT sample_date,
     min(sample_date)
          OVER (ORDER BY sample date ASC
                      ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) AS latest_1,
     min(sample_date)
          OVER (ORDER BY sample date ASC
                      ROWS BETWEEN 2 PRECEDING AND 2 PRECEDING) AS latest_2,
     min(sample_date)
          OVER (ORDER BY sample date ASC
                      ROWS BETWEEN 3 PRECEDING AND 3 PRECEDING) AS latest_3
FROM Tbl

2.4 ウィンドウ関数の内部動作

 ウィンドウ関数は内部でレコード集合に対してソートを行なっている。

2.4 まとめ

  • ウィンドウ関数の「ウィンドウ」は順序を持つ「範囲」の意味である。
  • ウィンドウ関数の構文上では\mathrm{PARTITION\ BY}句と\mathrm{ORDER\ BY}句で特徴づけられたレコード集合を意味し、一般的に簡略的の構文が使われるため、却ってウィンドウの存在を意識しにくい。
  • \mathrm{PARTITION\ BY}句は\mathrm{GROUP\ BY}句から集約の機能を引いて、カットの機能だけを残し、\mathrm{ORDER\ BY}句はレコードの順序を付ける。
  • フレーム句はカーソルの機能を\mathrm{SQL}の構文に持ち込むことで「カレントレコード」を中心にしたレコード集合の範囲を定義することができる。
  • フレーム句を使うことで、異なる行のデータを1つの行に持ってくることができるようになり、行間比較が簡単に行えるようになった。
  • ウィンドウ関数の内部動作としては、現在のところ、レコードのソートが行われている。将来的にハッシュが採用される可能性も零ではない。
プライバシーポリシー お問い合わせ