WNDPROCとかじゃアリマセン。集権関数とちょっと勘違いされそうなモノなんですが、別物です。ひそかにANSI SQLでも定義されている立派な標準SQL構文です。
ウィンドウ関数とは
簡単に言うと「Group byの様にデータのグループを決められる。集計はせず、さらにその範囲の中で特定のデータのみを対象とできる」ものです。
う～ん、いまいち訳分かりませんね。
とりあえず、使ってみましょう。
ウィンドウ関数を使ってみる
OracleのHRスキーマを使用して、ウィンドウ関数を使ってみます。やりたいことは次の通り。
- Employeesテーブルの中で、部署（Department_id）ごとに給料の高い順にランク付けする。
- ランク付けしたら、部署の中で何人中何位だったわかるようにする。
- 取得する項目は、Employee_id, 名前, 順位, 部署の人数
部署の人数などは、EmployeesテーブルをDepartment_idでgroup byした結果をさらにEmployeesテーブルのDepartment_idと結合してたり…なんてことでも取れますが、そんな面倒なことをしなくてもウィンドウ関数を使えば楽勝です。
|
SELECT
a.EMPLOYEE_ID,
a.FIRST_NAME || ', ' || a.LAST_NAME ,
a.DEPARTMENT_ID,
RANK() OVER (PARTITION BY a.DEPARTMENT_ID ORDER BY a.SALARY DESC),
COUNT(a.DEPARTMENT_ID) OVER (PARTITION BY a.DEPARTMENT_ID)
FROM
HR.EMPLOYEES a |
ポイントは5行目と6行目。
関数(expr) OVER (PARTITION BY XXX)
というのが、ウィンドウ関数の基本的な構文です。PARTITION BY のXXXX項目でグループを指定します（group byみたいな感じ）
5行目では、部署（Department_id）別にウィンドウを設定し、給料の高い順にランク付けします。6行目では、部署別にカウントを取ります。
これだけで完了。実行計画もすっきり。
|
SELECT STATEMENT
WINDOW SORT
TABLE ACCESS FULL EMPLOYEES |
毎回毎回使うようなものではありませんが、かゆい所に手が届くといった感じで結構便利です。
参考文献
Oracle Database SQL リファレンス 10.2 - 分析ファンクション
CodeZine - 分析関数の衝撃