SQLクリニック（9）　Page 1/2

SQL分析関数をさらに深く追求してみよう

株式会社インサイトテクノロジー
中島 益次郎
2005/10/29

本連載はSQLの応用力を身に付けたいエンジニア向けに、さまざまなテクニックを紹介する。SQLの基本構文は平易なものだが、実務で活用するには教科書的な記述を理解するだけでは不十分だ。本連載は、著名なメールマガジン「おら！ オラ！ Oracle - どっぷり検証生活」を発行するインサイトテクノロジーのコンサルタントを執筆陣に迎え、SQLのセンス向上に役立つ大技小技を紹介していく。（編集局）

主な内容 --Page 1-- ウィンドウ関数を使った分析 --Page 2-- レポート関数を利用した分析

　今回も、前回「極めよう！分析関数によるSQL高速化計画」に引き続き、分析関数の中からウィンドウ関数とレポート関数を取り上げて説明します。

■ウィンドウ関数を使った分析

　それでは、ウィンドウ関数を利用して、分析してみましょう。ウィンドウ関数を使用して、累積集計、移動集計、集中集計を計算できます。今回は、ウィンドウ関数を簡単に理解してもらうために、累積集計について説明します。

　ウィンドウ関数には、SUM()、AVG()、MAX()、MIN()、COUNT()、STDDEV()、FIRST_VALUE()、LAST_VALUE()などが存在します。普段よく使用するSUM()やAVG()が、なぜウィンドウ関数なの？ 集計関数じゃないの？ と思われた方も多いと思います（ウィンドウ関数は、集計ウィンドウ関数と呼ばれる場合もあります）。

　ウィンドウ関数を理解するには、「ウィンドウ」という概念を理解する必要があります。ウィンドウの概念を図で表してみましょう（集計関数と分析関数の違いは、前回の内容を参照してください）。

図1　「ウィンドウ」の概念 「グループ」は分析関数が分析を行う範囲を指定し、「ウィンドウ」はグループ内のどの範囲のデータを集計するかを指定する。

　「ウィンドウ」の概念は、分かりましたか？ 言葉や図で理解するのは、難しいですね。では、実際に実行された値を見て、「ウィンドウ」（ウィンドウ関数）の理解を深めていきましょう。おなじみの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)

リスト1　EMP表のスキーマ定義

　ウィンドウ関数の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行が選択されました。

リスト2　ウィンドウ関数のSUM()を使用した累積集計

　上のSQL文は、以下の処理を行います。

1. 集合（EMP表全体）をJOBごとにグループ分けするように指定
   （partition by job）
2. グループ内のデータをどのような順番で分析するかを指定
   （order by sal）
3. グループ内のデータをどのように集計するかウィンドウを指定
   （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列の結果が各行で集計されていることが確認できます。ウィンドウ終了点が、グループの最後にたどり着いた時点で、累積計算が終了していることも上の結果より確認することができます。

　一連の流れをまとめてみましょう。カレント行はグループの最初の行からグループの最後の行までスライドします。スライドするたびにカレント行ではその時点でのウィンドウを用いて累積計算を行い、結果をカレント行に格納します。

　ウィンドウ関数を使いこなすうえで、理解すべき「ウィンドウ」の概念は、理解できましたか？（次ページへ続く）

1/2

Index
連載 SQLクリニック（9） SQL分析関数をさらに深く追求してみよう
	Page 1 ・ウィンドウ関数を使った分析
	Page 2 ・レポート関数を利用した分析

SQLクリニック

＠ITメールマガジン　新着情報やスタッフのコラムがメールで届きます（無料）