SQL中級者になる！（その02/12）

初めに

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

power-of-awareness.com

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

達人に学ぶSQL徹底指南書 第2版 初級者で終わりたくないあなたへ

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

前回

power-of-awareness.com

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

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

2.1　ウィンドウとは何か

　ウィンドウ関数は句から選択されたレコードの集合に対してによる順位付けやによるフレーム定義が行われた上でのデータセットである。
　ウィンドウを暗黙的に定義する方法と明示的に定義する方法の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つの機能に集約できる。

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

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

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

• 直近値を求める

-- サンプル
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

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

		移動単位を行で設定する
		句で指定した列を基準に移動単位を列の値で設定する
		だけ前へ(小さい方へ)移動する
		だけ後へ(大きい方へ)移動する
		無制限に遡る方へ移動する
		無制限に下る方へ移動する
		現在行

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　まとめ

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

次回

power-of-awareness.com