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

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

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

■レポート関数を利用した分析

　次はレポート関数を利用して、分析してみましょう。レポート関数を使用して、総売り上げに対する各店舗の売り上げ比率やマーケットシェアの計算などを行うことができます。

　今回は、レポート関数を簡単に理解してもらうために、RATIO_TO_REPORT関数を例に説明します。RATIO_TO_REPORT関数は、グループ（値の集合）の合計に対する値（各行）の割合を計算します。構文は、以下になります。

構文
  RATIO_TO_REPORT ( expr ) OVER ( [query_partition_clause] )

　では、実際にRATIO_TO_REPORT関数を使用して分析してみます。ここでもSCOTTユーザーが所有するEMP表を使ってやってみます（EMP表のスキーマ定義は前ページのリスト1を参照）。RATIO_TO_REPORT関数を使用して、全員の給料の合計に対する各従業員給料の割合を見てみましょう。

SQL> select ename, sal, sum(sal) over() as total_sal
          , ratio_to_report(sal) over() as ratio_sal
       from emp
      order by sal ;

ENAME             SAL  TOTAL_SAL  RATIO_SAL
---------- ---------- ---------- ----------
SMITH             800      29025 .027562446
JAMES             950      29025 .032730405
ADAMS            1100      29025 .037898363
WARD             1250      29025 .043066322
MARTIN           1250      29025 .043066322
MILLER           1300      29025 .044788975
TURNER           1500      29025 .051679587
ALLEN            1600      29025 .055124892
CLARK            2450      29025 .084409991
BLAKE            2850      29025 .098191214
JONES            2975      29025 .102497847
SCOTT            3000      29025 .103359173
FORD             3000      29025 .103359173
KING             5000      29025 .172265289

14行が選択されました。
リスト3　RATIO_TO_REPORT関数を使用した給与割合の分析

　リスト3では分析対象のグループをEMP表全体とし、全員の給料の合計に対する各従業員給料の割合をRATIO_TO_REPORT関数を使用して分析してみました。

　RATIO_TO_REPORT関数を使用して、いろいろな割合を求めてみましょう。次は、職種（JOB）ごとの給料合計に対する各従業員給料の割合を見てみましょう。

SQL> select job, ename, sal
          , sum(sal) over(partition by job) as total_sal
          , ratio_to_report(sal) over(partition by job) as ratio_sal
       from emp
      order by job, sal ;

JOB       ENAME             SAL  TOTAL_SAL  RATIO_SAL
--------- ---------- ---------- ---------- ----------
ANALYST   SCOTT            3000       6000         .5
ANALYST   FORD             3000       6000         .5
CLERK     SMITH             800       4150 .192771084
CLERK     JAMES             950       4150 .228915663
CLERK     ADAMS            1100       4150 .265060241
CLERK     MILLER           1300       4150 .313253012
MANAGER   CLARK            2450       8275 .296072508
MANAGER   BLAKE            2850       8275 .344410876
MANAGER   JONES            2975       8275 .359516616
PRESIDENT KING             5000       5000          1
SALESMAN  WARD             1250       5600 .223214286
SALESMAN  MARTIN           1250       5600 .223214286
SALESMAN  TURNER           1500       5600 .267857143
SALESMAN  ALLEN            1600       5600 .285714286

14行が選択されました。
リスト4　RATIO_TO_REPORT関数を使用した職種ごとの給与割合の分析

　リスト4は分析対象のグループを職種（JOB）ごととし、各職種に属する従業員給料の割合を分析してみました。さらに、全従業員の給料合計に対する各職種（JOB）の給料合計の割合を見てみましょう（リスト5）。

SQL> select job, sum(sal) as job_total_sal
          , sum(sum(sal)) over() as total_sal
          , ratio_to_report(sum(sal)) over() as ratio_sal
       from emp
      group by job
      order by 2 ;

JOB       JOB_TOTAL_SAL  TOTAL_SAL  RATIO_SAL
--------- ------------- ---------- ----------
CLERK              4150      29025 .142980189
PRESIDENT          5000      29025 .172265289
SALESMAN           5600      29025 .192937123
ANALYST            6000      29025 .206718346
MANAGER            8275      29025 .285099053
リスト5　RATIO_TO_REPORT関数を使用した職種の給与全体に対する割合の分析

　このようにRATIO_TO_REPORT関数を使用して、さまざまな集合に対する各行の割合を求めることができることを理解してもらえたと思います。おさらいになりますが、前回に説明した分析関数が実行される手順に示したとおり、

  1. GROUP BY句で指定された処理を実行
  2. 分析関数の実行
  3. ORDER BY句で指定された処理を実行

の順で実行されていることが上の結果より理解できるでしょう。

◇

　今回は、分析関数であるウィンドウ関数とレポート関数について説明を行いました。次回は、残りの分析関数について解説する予定です。この機会に実際に試して、さらに理解を深めてみてください。（次回に続く）

筆者プロフィール
株式会社インサイトテクノロジー
Oracleに特化した製品開発、コンサルティングを手掛けるエンジニア集団。中島 益次郎はパフォーマンスで悩めるユーザーのためにPerformance Insightの開発に全力を注ぎ込む熱き魂の開発者。

　 2/2  

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


SQLクリニック

TechTargetジャパン

Database Expert フォーラム 新着記事

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

RSSフィード

＠IT 新着記事

キャリアアップ

- PR -
＠IT Sepcial
→ ＠IT Special ヘ

イベントカレンダー

PickUpイベント

- PR -
もっと見る
- PR -

お勧め求人情報

ホワイトペーパー（TechTargetジャパン）

＠IT Sepcial
→ ＠IT Special ヘ
ソリューションFLASH