Tweet

SQLクリニック（9）

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

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

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

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

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

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

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

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

　「ウィンドウ」の概念は、分かりましたか？ 言葉や図で理解するのは、難しいですね。では、実際に実行された値を見て、「ウィンドウ」（ウィンドウ関数）の理解を深めていきましょう。おなじみのSCOTTユーザーが所有するEMP表を使ってやってみます。

　ウィンドウ関数のSUM()を使用して、EMP表の各JOB（職種）別に給料（SAL）の累積集計を行います。

　上の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は、

を表しています。ウィンドウ開始点に指定した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メールマガジン　新着情報やスタッフのコラムがメールで届きます（無料）

RSSフィード