本連載はSQLの応用力を身に付けたいエンジニア向けに、さまざまなテクニックを紹介する。SQLの基本構文は平易なものだが、実務で活用するには教科書的な記述を理解するだけでは不十分だ。本連載は、著名なメールマガジン「おら！オラ！ Oracle - どっぷり検証生活」を発行するインサイトテクノロジーのコンサルタントを執筆陣に迎え、SQLのセンス向上に役立つ大技小技を紹介していく。（編集局）
今回も、前回「極めよう！分析関数によるSQL高速化計画」に引き続き、分析関数の中からウィンドウ関数とレポート関数を取り上げて説明します。
ウィンドウ関数を使った分析
それでは、ウィンドウ関数を利用して、分析してみましょう。ウィンドウ関数を使用して、累積集計、移動集計、集中集計を計算できます。今回は、ウィンドウ関数を簡単に理解してもらうために、累積集計について説明します。
ウィンドウ関数には、SUM()、AVG()、MAX()、MIN()、COUNT()、STDDEV()、FIRST_VALUE()、LAST_VALUE()などが存在します。普段よく使用するSUM()やAVG()が、なぜウィンドウ関数なの？
集計関数じゃないの？ と思われた方も多いと思います（ウィンドウ関数は、集計ウィンドウ関数と呼ばれる場合もあります）。
ウィンドウ関数を理解するには、「ウィンドウ」という概念を理解する必要があります。ウィンドウの概念を図で表してみましょう（集計関数と分析関数の違いは、前回の内容を参照してください）。
「ウィンドウ」の概念は、分かりましたか？ 言葉や図で理解するのは、難しいですね。では、実際に実行された値を見て、「ウィンドウ」（ウィンドウ関数）の理解を深めていきましょう。おなじみのSCOTTユーザーが所有するEMP表を使ってやってみます。
SQL> desc emp 名前 NULL? 型 ----------------------------------------- -------- ------------ EMPNO NOT NULL NUMBER(4) ENAME VARCHAR2(10) JOB VARCHAR2(9) MGR NUMBER(4) HIREDATE DATE SAL NUMBER(7,2) COMM NUMBER(7,2) DEPTNO NUMBER(2)
ウィンドウ関数のSUM()を使用して、EMP表の各JOB（職種）別に給料（SAL）の累積集計を行います。
SQL> select job, ename, sal,
sum(sal) over(partition by job order by sal
rows between unbounded preceding
and current row) as amount_sal
from emp
order by job, sal, ename ;
JOB ENAME SAL AMOUNT_SAL
--------- ---------- ---------- ----------
ANALYST FORD 3000 3000 ─┐
ANALYST SCOTT 3000 6000 ←┘
CLERK SMITH 800 800 ─┐ウィンドウ開始点
CLERK JAMES 950 1750 │ ↓各行でウィンドウ
終了点がスライド
CLERK ADAMS 1100 2850 │ ↓
CLERK MILLER 1300 4150 ←┘ウィンドウ終了点
MANAGER CLARK 2450 2450 ─┐
MANAGER BLAKE 2850 5300 │
MANAGER JONES 2975 8275 ←┘
PRESIDENT KING 5000 5000 ─
SALESMAN MARTIN 1250 1250 ─┐
SALESMAN WARD 1250 2500 │
SALESMAN TURNER 1500 4000 │←これらは、グループ
SALESMAN ALLEN 1600 5600 ←┘
14行が選択されました。
上のSQL文は、以下の処理を行います。
- 集合（EMP表全体）をJOBごとにグループ分けするように指定
（partition by job） - グループ内のデータをどのような順番で分析するかを指定
（order by sal） - グループ内のデータをどのように集計するかウィンドウを指定
（rows between unbounded preceding and current row）
3番目のウィンドウの指定について、もう少し詳しく説明しておきます。
rows between unbounded preceding and current rowは、
rows between ウィンドウ開始点 and ウィンドウ終了点
を表しています。ウィンドウ開始点に指定したunbounded precedingは、「グループの最初の行をウィンドウの開始点とする」ことを意味しています。リスト2の結果では、JOBごとにグループ分けをしたので、1行目（ANALYST）、3行目（CLERK）、7行目（MANAGER）、10行目（PRESIDENT）、11行目（SALESMAN）がウィンドウ開始点と指定されたことになります。
ウィンドウ終了点に指定したcurrent rowは、「ウィンドウの終了点を常にカレント行とする」ことを意味しています。カレント行が移動するとウィンドウ終了点も移動します。リスト2の結果で、JOBがCLERKの結果を見てみると、AMOUNT_SAL列の結果が各行で集計されていることが確認できます。ウィンドウ終了点が、グループの最後にたどり着いた時点で、累積計算が終了していることも上の結果より確認することができます。
一連の流れをまとめてみましょう。カレント行はグループの最初の行からグループの最後の行までスライドします。スライドするたびにカレント行ではその時点でのウィンドウを用いて累積計算を行い、結果をカレント行に格納します。
ウィンドウ関数を使いこなすうえで、理解すべき「ウィンドウ」の概念は、理解できましたか？（次ページへ続く）
Copyright © ITmedia, Inc. All Rights Reserved.