目次 1 集計関数 / 分析関数とは 2 集計関数 / 分析関数のパフォーマンス効果 3 ケーススタディグループ小計やクロス集計を計算するランキングを表示する前月比較を表示する累計を計算する移動平均を計算する構成比を計算する Oracle8i SQL Oracle8i Oracle Oracle C

(1)

! !

Creation Date: Oct. 11, 2000

Last Update: Oct. 11, 2000

Version: 1.0

Oracle8

i

データウェアハウス機能活用法

　　　　

　　　　～

レポーティングに有効な集計関数・分析関数

～

「

「Oracle8

Oracle8

Oracle8i

データウェアハウス機能活用法

∼

レポーティングに有効な集計関数・分析関数

∼」

【概要】データウェアハウス（DWH）を構築し、それを活用する形態の１つに「分析結果のレポーティング」があります。ここでいう「レポーティング」とは、ランキング・前年同月比・移動平均・累計計算 …etc など、ビジネス・インテリジェンス（Business Intelligence）には欠かすことの出来ない分析要件が挙げられます。 本資料では、Oracle8iで大幅に強化されたデータウェアハウス機能のうち集計関数・分析関数にフォー カスし、これらの概要と使用方法を、実際の分析要件と照らし合わせながら詳しく解説していきます。【対象ユーザ】基本的なRDBMSの知識を持っている方、 DWHを用いたシステム構築に携わる方、 Oracle8iの新機能を学習されたい方

(2)

Oracle Corporation Japan 2

①　集計関数／分析関数とは

②　集計関数／分析関数のパフォーマンス効果

③　ケーススタディ

グループ小計やクロス集計を計算するグループ小計やクロス集計を計算するグループ小計やクロス集計を計算するグループ小計やクロス集計を計算するランキングを表示するランキングを表示するランキングを表示するランキングを表示する前月比較を表示する前月比較を表示する前月比較を表示する前月比較を表示する累計を計算する累計を計算する累計を計算する累計を計算する移動平均を計算する移動平均を計算する移動平均を計算する移動平均を計算する構成比を計算する構成比を計算する構成比を計算する構成比を計算する

【目次】 ① 集計関数／分析関数とは Oracle8iから新たに追加された集計関数と分析関数について、概要を説明します。 ② 集計関数／分析関数のパフォーマンス効果ここでは、「集計関数や分析関数はどれくらい有用なものなのか」「いままでのSQL文と比べて何が変わるのか」について説明します。特に、アプリケーション開発者の方々が気にされている「検索のパフォーマンス」についてフォーカスし、パフォーマンスの観点から見た集計関数や分析関数の効果を少しだけ紹介します。 ③ ケーススタディここでは、実際の分析要件を例に挙げ、 Oracle8iの集計関数／分析関数でどのようにインプリすればよいのかを詳しく解説します。もし、途中で新しい考え方や用語が出てきた場合には、その場で詳しく解説します。関数の説明から入り、使用例の紹介で終わる「マニュアル」方式とは若干アプローチが異なりますが、「まず何をやりたいか」というアプローチから集計・分析関数を学ぶことが出来ます。 Oracleデータベースの製品マニュアルと併せてご一読いただけると幸いです。

(3)

DWHシステムの分析要件で必要とされる計算

システムの分析要件で必要とされる計算

　ランキング・移動平均・累計計算　

　ランキング・移動平均・累計計算　……etc

　　　↓

今までの

今までのSQL言語では

言語では

高度なプログラミングによるインプリが必要

　　　↓

検索パフォーマンスが劣化検索パフォーマンスが劣化検索パフォーマンスが劣化検索パフォーマンスが劣化高度なインプリ知識が必要高度なインプリ知識が必要高度なインプリ知識が必要高度なインプリ知識が必要ヒストグラム・前年同月比：ヒストグラム・前年同月比：ヒストグラム・前年同月比： ヒストグラム・前年同月比：PL/SQLファンクションファンクションファンクションファンクションランキング：ランキング：ランキング： ランキング：rownum サブトータル：サブトータル：サブトータル： サブトータル：union

集計関数／分析関数とは

【集計関数／分析関数とは】 DWHを用いたBusiness Intelligence（BI）システムでは、例えばこのような分析要件が挙げられます。 -商品部門別トップ１０ランキング -毎月の総売上の３ヶ月移動平均 -預金額の累計計算 -利益額の前年同月比 …etc しかし、これらの分析要件を、Oracle8i以前のDBを対象としてインプリしようとすると、上図のような高度なプログラム能力を要すると共に、複雑な問い合わせとなるために生じる検索パフォーマンスの悪化が常についてまわります。

(4)

〔利点〕

　　　　-　　　　Oracleをターゲットとした主要な分析要件を直接サポートをターゲットとした主要な分析要件を直接サポートをターゲットとした主要な分析要件を直接サポートをターゲットとした主要な分析要件を直接サポート　　　　-　複雑な計算をサーバ側で処理させることが可能　複雑な計算をサーバ側で処理させることが可能　複雑な計算をサーバ側で処理させることが可能　複雑な計算をサーバ側で処理させることが可能　　　　→パフォーマンスとスケーラビリティーが飛躍的に向上　　　　→パフォーマンスとスケーラビリティーが飛躍的に向上　　　　→パフォーマンスとスケーラビリティーが飛躍的に向上　　　　→パフォーマンスとスケーラビリティーが飛躍的に向上　　　　-　　　　SQL開発の簡素化（複雑な開発の簡素化（複雑な開発の簡素化（複雑な開発の簡素化（複雑なSQL文からの解放）文からの解放）文からの解放）文からの解放） Oracle ReportsなどのなどのなどのOracle BIツール製品との組み合わせが可能などのツール製品との組み合わせが可能ツール製品との組み合わせが可能ツール製品との組み合わせが可能　　　　　　　　→→→→ 定型帳票などのアプリケーションに応用できる定型帳票などのアプリケーションに応用できる定型帳票などのアプリケーションに応用できる定型帳票などのアプリケーションに応用できる ANSIにおいて分析関数をにおいて分析関数をにおいて分析関数をにおいて分析関数をSQL規格に加えることを検討中（集計関数はすでに準拠規格に加えることを検討中（集計関数はすでに準拠規格に加えることを検討中（集計関数はすでに準拠規格に加えることを検討中（集計関数はすでに準拠（※）（※）（※）（※）））））（※）（※）（※）

（※） ANSI and ISO proposals for SQL3, a draft standard for enhancements to SQL

集計関数

（（（（Oracle8i R8.1.5～）～）～）～）

・分析関数

・・

・

分析関数

（（（（ Oracle8i R8.1.6～）～）～）～）

　

　Oracle RDBMSを対象とした

を対象とした

　集計・分析計算を強化するためにサポートされた

　集計・分析計算を強化するためにサポートされたSQL関数群

関数群

集計関数

（（（Oracle8i R8.1.5～）（～）～）～）

・分析関数

・・

・

分析関数

（（（ Oracle8i R8.1.6～）（～）～）～）

　

　Oracle RDBMSを対象とした

を対象とした

　集計・分析計算を強化するためにサポートされた

　集計・分析計算を強化するためにサポートされたSQL関数群

関数群

集計関数／分析関数とは

そこで、Oracle8iからBIの分析要件にSQL言語レベルでサポートするために「集計関数」「分析関数」の２つの機能を提供しています。「集計関数」はOracle8i R8.1.5、「分析関数」はOracle8i R8.1.6からサポートされています。主な利点は上図のように、・Oracleをターゲットとした主要な分析要件を直接サポート・複雑な計算をサーバ処理させることが可能（パフォーマンスが飛躍的に向上）・SQL開発の簡素化などが挙げられます。

Oracle ReportsなどのOracle BIツール製品との組み合わせにより、これまで複雑なインプリが必要だったビジネス帳票が簡単に作成できるとともに、パフォーマンスやスケーラビリティも向上します。

(5)

例）　売上

例）　売上Top５

５ ５ランキング

５ 集計関数／分析関数のパフォーマンス効果

集計関数／分析関数のパフォーマンス効果

【集計関数／分析関数のパフォーマンス効果】ここでは、集計関数や分析関数を用いるとどのような恩恵を受けるかについて、「検索パフォーマンス」の観点から比較してみたいと思います。ここで取り上げる例はEmp表とDept表の結合スキーマを対象とした給与トップ５を検索する場合です。トップ５のレポートには「順位数」と「従業員名」・「職種名」・「給与額」が含まれています。

(6)

select

e.ename ename,d.dname dname,e.salary salary from emp e, dept d where e.deptno = d.deptno and 5 > ( select count(distinct(salary)) from emp where salary > e.salary )

order by salary desc ;

今までの

今までのSQLインプリ例

インプリ例

集計関数／分析関数のパフォーマンス効果

まずは集計関数や分析関数を用いない問い合わせをみてみます。インラインビューを用いて、対象の給与が全体と比べて５番目以内である場合に表示するようなSQL文です。 ※インラインビュー（Inline View）スキーマやオブジェクトとは違い、SQL文でビューと同様に使用できる副問い合わせのこと。基本的にはSelect文に括弧を用いてSelect文を記述する上図のような利用法が一般的である。

(7)

Execution Plan

___________________________________________________

0 SELECT STATEMENT Optimizer=CHOOSE

1 0 SORT (ORDER BY) 2 1 FILTER

3 2 NESTED LOOPS

4 3 TABLE ACCESS (FULL) OF ‘EMP’

5 3 TABLE ACCESS (BY INDEX ROWID) OF ‘DEPT’ 6 5 INDEX (UNIQUE SCAN) OF ‘PK_DEPTNO’ (UNIQUE) 7 2 SORT (GROUP BY)

8 7 TABLE ACCESS (FULL) OF ‘EMP’ 実行計画実行計画実行計画実行計画

集計関数／分析関数のパフォーマンス効果

実行計画は次のようになります。 ここでは、EMP表に対して２回のフルスキャンが走っていること表に対して２回のフルスキャンが走っていること表に対して２回のフルスキャンが走っていること表に対して２回のフルスキャンが走っていること、２回のソート処理が実行されている２回のソート処理が実行されている２回のソート処理が実行されている２回のソート処理が実行されていることことことことがわかります。

(8)

Oracle Corporation Japan 8 Statistics ___________________________________________________ 39984 recursive calls 47185 db block gets 322485 consistent gets 44157 physical reads 0 redo size

521 bytes sent via SQL*Net to client 416 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 2153 sorts (memory) 4998 sorts (disk) 5 rows processed パフォーマンス統計パフォーマンス統計パフォーマンス統計パフォーマンス統計

集計関数／分析関数のパフォーマンス効果

次にパフォーマンス統計（SQL*PlusのAUTOTRACE機能を使用）を取ってみると、アクセスブロック数アクセスブロック数アクセスブロック数アクセスブロック数（（（

（DB BLOCK GETS,CONSISTENT GETS））））やソート回数（やソート回数（やソート回数（やソート回数（SORTS(MEMORY),SORTS(DISK)）））の値が大）の値が大の値が大の値が大きいこと

きいこときいこと

(9)

select

ename,dname,salary,rank_sal from

(

select e.ename ename,d.dname dname,e.salary salary, rank() over(order by e.salary desc) as rank_sal from emp e,dept d

where e.deptno = d.deptno ) where rank_sal < 6

_{分析関数を使用した}

インプリ例

集計関数／分析関数のパフォーマンス効果

これに対し、分析関数を用いて同じ要件を実現するSQL文は次のようになります。ここで用いられるrank()が分析関数に当たり、rank()は順位を決定する関数です。

(10)

Execution Plan

___________________________________________________

0 SELECT STATEMENT Optimizer=CHOOSE

1 0 VIEW

2 1 WINDOW (SORT PUSHED RANK) 3 2 NESTED LOOPS

4 3 TABLE ACCESS (FULL) OF ‘EMP’

5 3 TABLE ACCESS (BY INDEX ROWID) OF ‘DEPT’ 6 5 INDEX (UNIQUE SCAN) OF ‘PK_DEPTNO’ (UNIQUE) 実行計画実行計画実行計画実行計画《比較》《比較》《比較》《比較》　・　・　・　・ EMP表へのフルスキャンの回数が減っている表へのフルスキャンの回数が減っている表へのフルスキャンの回数が減っている表へのフルスキャンの回数が減っている　・　・　・　・ソート処理の回数が減っているソート処理の回数が減っているソート処理の回数が減っているソート処理の回数が減っている

集計関数／分析関数のパフォーマンス効果

実行計画は次のようになります。 「分析関数なし」のSQL文（７ページ）と比べて、EMP表のフルスキャンが２回から１回に減っているこ表のフルスキャンが２回から１回に減っているこ表のフルスキャンが２回から１回に減っているこ表のフルスキャンが２回から１回に減っていることととと、ソート処理の回数が２回から１回に減っていることソート処理の回数が２回から１回に減っていることソート処理の回数が２回から１回に減っていることソート処理の回数が２回から１回に減っていることがわかります。

注）ウィンドウ関数によるソート（シート中「WINDOW(SORT PUSHEC RANK)とある部分」）が『ORDER BY SORT』のアルゴリズムを使用しているため、ここではソート処理としてカウントしています。

(11)

Statistics 8 recursive calls 6 db block gets 28682 consistent gets 1 physical reads 0 redo size

917 bytes sent via SQL*Net to client 424 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 1 sorts (disk) 5 rows processed パフォーマンス統計（分析関数使用）パフォーマンス統計（分析関数使用）パフォーマンス統計（分析関数使用）パフォーマンス統計（分析関数使用） Statistics 0 57184 1157984 0 0 830 424 2 14296 0 5 分析関数不使用分析関数不使用分析関数不使用分析関数不使用《比較》《比較》《比較》《比較》　・　・　・　・アクセスブロック数が減少しているアクセスブロック数が減少しているアクセスブロック数が減少しているアクセスブロック数が減少している　・　・　・　・ソート処理の回数が少なくて済むソート処理の回数が少なくて済むソート処理の回数が少なくて済むソート処理の回数が少なくて済むパフォーマンスがパフォーマンスがパフォーマンスがパフォーマンスが改善されている改善されている改善されている改善されている経過：経過：経過： 経過：00:00.91 経過：経過：09:57.70経過：経過：

集計関数／分析関数のパフォーマンス効果

さらに、パフォーマンス統計を取ると、分析関数無しの場合との違いがはっきりと現れてきます。・アクセスしたブロック数

DB BLOCK GETS + CONSISTENT GETS

分析関数不使用と比べて非常に少ないブロック数であることがわかります。すなわち、同じ結果を得るために５７０００ものブロック数にアクセスする必要があった検索が、分析関数を用いることによりアクセスブロック数を減らすことができ、パフォーマンスの向上につながります。・ソート処理の回数 SORT(MEMORY) + SORT(DISK) この数値も不使用時に比べて激減していることから、分析関数を用いることによる検索パフォーマンス向上が見込まれます。実際、このスキーマに問い合わせを行った結果、上図のような問い合わせ時間になりました。不使用時では、EMP表１レコード毎にインラインビューの処理が走るためこのような結果になりますが、分析関数で同じものを実現させた場合は検索時間が大幅に短縮されていることがわかります。

(12)

Oracle Corporation Japan 12 　１．　グループ小計やクロス集計を計算する　１．　グループ小計やクロス集計を計算する　１．　グループ小計やクロス集計を計算する　１．　グループ小計やクロス集計を計算する　２．　ランキングを表示する　２．　ランキングを表示する　２．　ランキングを表示する　２．　ランキングを表示する　３．　前月比較を表示する　３．　前月比較を表示する　３．　前月比較を表示する　３．　前月比較を表示する　４．　累計を計算する　４．　累計を計算する　４．　累計を計算する　４．　累計を計算する　５．　移動平均を計算する　５．　移動平均を計算する　５．　移動平均を計算する　５．　移動平均を計算する　６．　構成比を計算する　６．　構成比を計算する　６．　構成比を計算する　６．　構成比を計算する →　 →　 →　 →　ROLLUP/CUBE　（　（集計関数）　（　（集計関数）集計関数）集計関数） →　ランキング関数 →　ランキング関数 →　ランキング関数 →　ランキング関数　（分析関数）　（分析関数）　（分析関数）　（分析関数） →　 →　 →　 →　LAG/LEAD関数関数関数関数　（分析関数）　（分析関数）　（分析関数）　（分析関数） →　ウィンドウ関数 →　ウィンドウ関数 →　ウィンドウ関数 →　ウィンドウ関数　（分析関数）　（分析関数）　（分析関数）　（分析関数） →　ウィンドウ関数 →　ウィンドウ関数 →　ウィンドウ関数 →　ウィンドウ関数　（分析関数）　（分析関数）　（分析関数）　（分析関数） →　レポート関数 →　レポート関数 →　レポート関数 →　レポート関数　（分析関数）　（分析関数）　（分析関数）　（分析関数）

ケーススタディ

【ケーススタディ】ここからは、これら集計関数・分析関数を使用法を解説します。解説方法は「ケーススタディ」方式となっております。まず、BIの分析要件としてもっともポピュラーな要件を６つ挙げると上図のようなケースにまとまります。これらのケースの対し、どのような関数を用いてインプリしていくのか、またインプリする際に注意すべき点とは何か、について一つずつ解説していきます。【補足：定型帳簿の例】

(13)

１．　グループ小計やクロス集計を計算する１．　グループ小計やクロス集計を計算する１．　グループ小計やクロス集計を計算する１．　グループ小計やクロス集計を計算する

Gender Dname Ename SUM(Salary)

女性女性女性女性エンジニアエンジニアエンジニアエンジニア smith 2000 女性女性女性女性マーケティングマーケティングマーケティングマーケティング allen 3000 女性女性女性女性エンジニアエンジニアエンジニアエンジニア ward 5000 女性女性女性女性マーケティングマーケティングマーケティングマーケティング jones 1500 女性女性女性女性　　：　　：　　：　　：　：　：　：　：　：　：　：　：女性女性女性女性　　：　　：　　：　　：　：　：　：　：　：　：　：　：男性男性男性男性マーケティングマーケティングマーケティングマーケティング scott 12500 男性男性男性男性エンジニアエンジニアエンジニアエンジニア king 12500 女性女性女性女性　　：　　：　　：　　：　：　：　：　：　：　：　：　：女性女性女性女性　　：　　：　　：　　：　：　：　：　：　：　：　：　：女性総計女性総計女性総計女性総計女性総計女性総計女性総計女性総計 _27,50027,500 　エンジニア　エンジニア　エンジニア　エンジニア 15,000 　マーケティング　マーケティング　マーケティング　マーケティング 7,500 男性総計男性総計男性総計男性総計男性総計男性総計男性総計男性総計 _30,80030,800 　エンジニア　エンジニア　エンジニア　エンジニア 19,800 　マーケティング　マーケティング　マーケティング　マーケティング 11,000

! 性別・職種ごとのグループ小計

性別・職種ごとのグループ小計

! 性別・職種のクロス集計

性別・職種のクロス集計

!

性別・職種ごとのグループ小計

!

性別・職種のクロス集計

《１．グループ小計やクロス集計を計算する》この例では性別・職種毎の給与小計を求めています。「性別・職種」の組み合わせでの小計だけではなく、「性別」レベルのみの小計や総計などを含めたグループ小計形式や、これらに加え「職種」レベルのみの小計を加えたクロス集計形式も１つのSQL文で実現しています。従来、集計計算にはgroup by句を使用してきました。ただし、この場合には小計毎にselect文を書き、union allなどで結果セットを連結して表示する必要がありました。この連結は組み合わせの数が多くなればなるほど複雑かつ多くなってきます。（例）「性別・職種」の組み合わせによるグループ小計 → ３つのselect文の連結が必要（「性別・職種」の小計＋「性別」の小計＋総計）「性別・職種」の組み合わせによるクロス集計 → ４つのselect文の連結が必要（上記の３つ＋「職種」の小計）：： N個の組み合わせによるグループ小計 → N+1個のselect文の連結が必要 N個の組み合わせによるクロス集計 → 2N個のselect文の連結が必要このような検索には集計関数である「ROLLUP」と「CUBE」の２つの関数を使用します。

(14)

集計関数

　従来の　従来の　従来の　従来の GROUP BY 句から拡張された演算子句から拡張された演算子句から拡張された演算子句から拡張された演算子

集計関数

　従来の　従来の　従来の　従来の GROUP BY 句から拡張された演算子句から拡張された演算子句から拡張された演算子句から拡張された演算子

ROLLUP

指定した集計軸の並びに沿ったサブ・トータル及び総合計を返す指定した集計軸の並びに沿ったサブ・トータル及び総合計を返す指定した集計軸の並びに沿ったサブ・トータル及び総合計を返す指定した集計軸の並びに沿ったサブ・トータル及び総合計を返す

CUBE

指定した集計軸のクロス集計及び総合計を返す指定した集計軸のクロス集計及び総合計を返す指定した集計軸のクロス集計及び総合計を返す指定した集計軸のクロス集計及び総合計を返す

ROLLUP

CUBE

12500

小計小計小計小計

5000

3000

2000

女性女性女性女性

7500

4500

3000

男性男性男性男性小計小計小計小計マーケティングマーケティングマーケティングマーケティングエンジニアエンジニアエンジニアエンジニア Dname Gender 【集計関数について】集計関数は従来のGROUP BY句からの拡張として位置づけられる演算子です。従って、集計関数を使用する際にはGROUP BY句を併用することになります。「ROLLUP」指定した集計軸に沿ったグループ小計、例えば上図で言えば「性別・職種の組み合わせでの小計」＋「性別のみの小計」＋「総計」の３つを返します。

(15)

集計関数

　従来の　従来の　従来の　従来の GROUP BY 句から拡張された演算子句から拡張された演算子句から拡張された演算子句から拡張された演算子

ROLLUP

CUBE

ROLLUP

CUBE

12500

7500

5000

小計小計小計小計

5000

3000

2000

女性女性女性女性

7500

4500

3000

男性男性男性男性小計小計小計小計マーケティングマーケティングマーケティングマーケティングエンジニアエンジニアエンジニアエンジニア Dname Gender 【集計関数について】「CUBE」指定した集計軸のクロス集計、例えば上図で言えば「性別・職種の組み合わせでの小計」＋「性別のみの小計」＋「職種のみの小計」＋「総計」の３つを返します。

(16)

Gender Dname SUM(Salary)

女性女性女性女性エンジニアエンジニアエンジニアエンジニア 2000 女性女性女性女性マーケティングマーケティングマーケティングマーケティング 3000 女性女性女性女性 5000 男性男性男性男性エンジニアエンジニアエンジニアエンジニア 3000 男性男性男性男性マーケティングマーケティングマーケティングマーケティング 4500 男性男性男性男性 7500 12500 〔性別・職種ごとのグループ小計〕〔性別・職種ごとのグループ小計〕〔性別・職種ごとのグループ小計〕〔性別・職種ごとのグループ小計〕 select gender,dname,sum(salary) from emp,dept

group by rollup (gender,dname);

引数：グループ化列の順序づけリスト引数：グループ化列の順序づけリスト引数：グループ化列の順序づけリスト引数：グループ化列の順序づけリスト ①　右 ①　右 ①　右 ①　右 →→→ 左の順にレベル付け→左の順にレベル付け左の順にレベル付け左の順にレベル付け ②　レベル順に小計を作成 ②　レベル順に小計を作成 ②　レベル順に小計を作成 ②　レベル順に小計を作成 - Genderに対するそれぞれのに対するそれぞれのに対するそれぞれのに対するそれぞれの Dnameにわたって集計にわたって集計にわたって集計にわたって集計 - 各各各各Genderに対して全てのに対して全てのに対して全てのに対して全てのDname にわたって集計にわたって集計にわたって集計にわたって集計 - 総計を計算総計を計算総計を計算総計を計算第１レベル：第１レベル：第１レベル： 第１レベル：dname、、、第２レベル：、第２レベル：第２レベル：gender第２レベル：

集計関数　

〔〔

〔ROLLUP〕〕〕〕

〔

　　　　従来の従来の従来の従来の GROUP BY 句から拡張された演算子句から拡張された演算子句から拡張された演算子句から拡張された演算子

集計関数　

〔

〔〔

〔ROLLUP〕〕〕〕

　　　　従来の従来の従来の従来の GROUP BY 句から拡張された演算子句から拡張された演算子句から拡張された演算子句から拡張された演算子【集計関数の使用例】上図は、集計関数を用いて要件を満たすSQL文を書いた例です。ケーススタディでは基本的に左上の（赤色の）ボックス → SQL文の例左下の（黒色の）ボックス → 上記SQL文を用いて表示される結果例となっております。

グループ小計の場合は、Group by句の後にrollupを記述します。そして、 rollupの引数として「グループ化列の順序づけリスト」を記述する必要があります。このリストに沿って小計を計算していきます。上図の例では、第１レベルにdname（職種）、第２レベルにgender（性別）を指定しています。従って、 Rollupによる集計は ① 「性別・職種の組み合わせによる小計」（性別に対するそれぞれの職種にわたって集計計算） ② 「性別のみの小計」（各性別に対する全職種にわたって集計計算） ③ 「総計」という順番で処理・表示されます。

(17)

〔性別・職種のクロス集計〕〔性別・職種のクロス集計〕〔性別・職種のクロス集計〕〔性別・職種のクロス集計〕 select gender,dname,sum(salary) from emp,dept

group by cube (gender,dname);

引数：グループ化列の順序づけリスト引数：グループ化列の順序づけリスト引数：グループ化列の順序づけリスト引数：グループ化列の順序づけリスト ①　右 ①　右①　右 ①　右 →→→ 左の順にレベル付け→左の順にレベル付け左の順にレベル付け左の順にレベル付け ②　レベル順に小計を作成 ②　レベル順に小計を作成②　レベル順に小計を作成 ②　レベル順に小計を作成 - Genderに対する全てのに対する全てのに対する全てのに対する全てのDname にわたって集計にわたって集計にわたって集計にわたって集計 - 各各各各Genderに対して全てのに対して全てのに対して全てのに対して全てのDname にわたって集計にわたって集計にわたって集計にわたって集計 - 各各各各Dnameに対して全てのに対して全てのに対して全てのに対して全てのGender にわたって集計にわたって集計にわたって集計にわたって集計 - 総計を計算総計を計算総計を計算総計を計算第１レベル：第１レベル：第１レベル： 第１レベル：dname、、、、第２レベル：第２レベル：第２レベル：第２レベル：gender

Gender Dname SUM(Salary)

女性女性女性女性エンジニアエンジニアエンジニアエンジニア 2000 女性女性女性女性マーケティングマーケティングマーケティングマーケティング 3000 女性女性女性女性 5000 男性男性男性男性エンジニアエンジニアエンジニアエンジニア 3000 男性男性男性男性マーケティングマーケティングマーケティングマーケティング 4500 男性男性男性男性 7500 エンジニアエンジニアエンジニアエンジニア 5000 マーケティングマーケティングマーケティングマーケティング 7500 12500

集計関数　

〔〔

〔CUBE〕〕〕〕

〔

　　　　従来の従来の従来の従来の GROUP BY 句から拡張された演算子句から拡張された演算子句から拡張された演算子句から拡張された演算子

集計関数　

〔

〔〔

〔CUBE〕〕〕〕

　　　　従来の従来の従来の従来の GROUP BY 句から拡張された演算子句から拡張された演算子句から拡張された演算子句から拡張された演算子【集計関数の使用例】

クロス集計の場合は、Group by句の後にcubeを記述します。そして、 rollup同様、cubeの引数として「グループ化列の順序づけリスト」を記述する必要があります。このリストに沿ってクロス集計を計算していきます。上図の例では、第１レベルにdname（職種）、第２レベルにgender（性別）を指定しています。従って、 cubeによる集計は ① 「性別・職種の組み合わせによる小計」（性別に対するそれぞれの職種にわたって集計計算） ② 「性別のみの小計」（各性別に対する全職種にわたって集計計算） ③ 「職種のみの小計」（各職種に対する全性別にわたって集計計算） ③ 「総計」という順番で処理・表示されます。

(18)

参考：

参考：GROUPING関数

関数

　　　　『全体』を表す『全体』を表す『全体』を表す『全体』を表すNULL値と実際の値と実際の値と実際の値と実際のNULL値を区別する値を区別する値を区別する値を区別する

参考：

参考：GROUPING関数

関数

　　　　『全体』を表す『全体』を表す『全体』を表すNULL値と実際の『全体』を表す値と実際の値と実際の値と実際のNULL値を区別する値を区別する値を区別する値を区別する

Gender Dname SUM(Salary) G D

女性女性女性女性エンジニアエンジニアエンジニアエンジニア 2000 0 0 女性女性女性女性マーケティングマーケティングマーケティングマーケティング 3000 0 0 女性女性女性女性 5000 0 1 男性男性男性男性エンジニアエンジニアエンジニアエンジニア 3000 0 0 男性男性男性男性マーケティングマーケティングマーケティングマーケティング 4500 0 0 男性男性男性男性 7500 0 1 エンジニアエンジニアエンジニアエンジニア 5000 1 0 マーケティングマーケティングマーケティングマーケティング 7500 1 0 12500 1 1 select gender,dname,sum(salary),grouping(gender) G,grouping(dname) D from emp,dept

group by cube (gender,dname);

引数：グループ化列引数：グループ化列引数：グループ化列引数：グループ化列『全体』を表す『全体』を表す『全体』を表す 『全体』を表すNULL値である場合には値である場合には値である場合には値である場合には『『『 『1』を返す』を返す』を返す』を返すそれ以外は『それ以外は『それ以外は『 それ以外は『0』を返す』を返す』を返す』を返す【GROUPING関数について】出力例（黒のボックス）にあるとおり、集計関数を用いて小計・総計を表示する際、小計対象ではないグループ化列のセルには空白が表示されます。例えば「各性別に対する全職種にわたって集計計算」する場合には、職種のセルには空白が表示されます。この空白とNULL値による空白を区別するために、Oracleではgrouping関数を用意しています。この関数を用いると、全体を表す空白 → １ NULL値による空白 → ０を表示してくれます。このgrouping関数を使えば、例えば「性別のみの集計と総計だけを表示したい」という要求にはSQL文に where grouping(gender) = 1 という条件句を付けるだけで対応が可能になります。

(19)

Gender Dname SUM(Salary) 女性女性女性女性エンジニアエンジニアエンジニアエンジニア 2000 女性女性女性女性マーケティングマーケティングマーケティングマーケティング 3000 女性女性女性女性 5000 男性男性男性男性エンジニアエンジニアエンジニアエンジニア 3000 男性男性男性男性マーケティングマーケティングマーケティングマーケティング 4500 男性男性男性男性 7500 〔性別・職種ごとのグループ小計〕〔性別・職種ごとのグループ小計〕〔性別・職種ごとのグループ小計〕〔性別・職種ごとのグループ小計〕 select gender,dname,sum(salary) from emp,dept

group by gender rollup (dname); rollup/cubeの前にレベルを書くと、の前にレベルを書くと、の前にレベルを書くと、の前にレベルを書くと、

そのレベルまでの集計を行い、そのレベルまでの集計を行い、そのレベルまでの集計を行い、そのレベルまでの集計を行い、それ以降のレベルと総計のそれ以降のレベルと総計のそれ以降のレベルと総計のそれ以降のレベルと総計の計算を行わない計算を行わない計算を行わない計算を行わない例では総計の計算が行われない例では総計の計算が行われない例では総計の計算が行われない例では総計の計算が行われない

参考：集計関数に関する

参考：集計関数に関する Oracle8i R8.1.6の新機能

の新機能

参考：集計関数に関する

参考：集計関数に関する Oracle8i R8.1.6の新機能

の新機能

　従来の　従来の　従来の　従来の GROUP BY 句から拡張された演算子句から拡張された演算子句から拡張された演算子句から拡張された演算子 ※ ※※ ※ Oracle8i R8.1.6からの新機能からの新機能からの新機能からの新機能【参考：集計関数に関するOracle8i R8.1.6からの新機能】 Oracle8i R8.1.6から、rollup/cubeの前にレベルを書き込むことが出来ます。上図の例では、第１レベルにdname、第２レベルにgenderを指定し、第２レベルをrollupの外側に記述しています。このように記述すると、第２レベルまでの集計、すなわち ① 「性別・職種の組み合わせによる小計」（性別に対するそれぞれの職種にわたって集計計算） ② 「性別のみの小計」（各性別に対する全職種にわたって集計計算）までは実行し、それ以降のレベル、すなわち ③ 「総計」の計算を行わない、という指定が可能になります。この手法と、groupingによる条件指定により、rollup/cubeを用いた集計計算に様々なバリエーションを与えることが可能になります。【参考：組み合わせが３つある場合】例えば、性別・職種の他に地域（Location）がある場合を考えます。この場合、 group by gender,location rollup(dname)

とあった場合は「性別・地域・職種」の小計と「性別・地域」の小計を計算し、これ以降の「性別のみの小計」と「総計」の計算・表示を省きます。

(20)

Oracle Corporation Japan 20 ２．　ランキングを表示する２．　ランキングを表示する２．　ランキングを表示する２．　ランキングを表示する９月度　総合給与ランキング９月度　総合給与ランキング９月度　総合給与ランキング９月度　総合給与ランキング１位１位１位１位 king 8000 ２２２２位位位位 smith 7500 ３３３３位位位位 scott 5500 ＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿４４４４位位位位 adams ５５５５位位位位 clark ６６６６位位位位 allen ７７７７位位位位 turner ８８８８位位位位 jones ９９９９位位位位 blake １０１０１０１０位位位位 martin

! 総合給与ランキング

総合給与ランキング

! 総合給与ランキング　

総合給与ランキング　

総合給与ランキング　（同順位の場合はインセンティブでランク付け）

（同順位の場合はインセンティブでランク付け）（同順位の場合はインセンティブでランク付け）（同順位の場合はインセンティブでランク付け）

! 性別給与ランキング　

性別給与ランキング　

性別給与ランキング　（同順位の場合はインセンティブでランク付け）

（同順位の場合はインセンティブでランク付け）（同順位の場合はインセンティブでランク付け）（同順位の場合はインセンティブでランク付け）

!

総合給与ランキング

!

総合給与ランキング　

（同順位の場合はインセンティブでランク付け）（同順位の場合はインセンティブでランク付け）（同順位の場合はインセンティブでランク付け）（同順位の場合はインセンティブでランク付け）

!

性別給与ランキング　

（同順位の場合はインセンティブでランク付け）（同順位の場合はインセンティブでランク付け）（同順位の場合はインセンティブでランク付け）（同順位の場合はインセンティブでランク付け）９月度　給与ランキング９月度　給与ランキング９月度　給与ランキング９月度　給与ランキング（男性）（男性）（男性）（男性）１位１位１位１位 king 8000 ２２２２位位位位 scottt 5500 ３３３３位位位位 adams 5300 （（（（女性）女性）女性）女性）１位１位１位１位 smith 7500 ２２２２位位位位 allen 5000 ３３３３位位位位 jones 4600 《２．ランキングを表示する》この例では給与の高い順に総合ランキングを表示しています。さらに、総合ランキングだけでははなく、性別の給与トップ３ランキングというような細かい指定のランキングを表示します。従来、ランキングの数字を表示するためにはrownumなどの高度な関数が必要でした。さらに上位（下位）Ｎ件表示という要求には、インラインビューやファンクションを用いて複雑なインプリを行う必要がありました。当然、これらの手法には検索パフォーマンスの劣化がついてまわります。このようなランキング検索には分析関数である「rank」という関数を使用します。

(21)

分析関数

（ランキング関数）（ランキング関数）（ランキング関数）（ランキング関数）

分析関数

（ランキング関数）（ランキング関数）（ランキング関数）（ランキング関数）データセットのレコードに対し、データセットのレコードに対し、データセットのレコードに対し、データセットのレコードに対し、レコードのランクレコードのランクレコードのランクレコードのランクを計算するを計算するを計算するを計算する〔総合給与ランキング〕〔総合給与ランキング〕〔総合給与ランキング〕〔総合給与ランキング〕 select ename,gender,salary,comm,

rank () over (order by sal desc) as rank_sal

from emp ;

Ename Gender Salary comm rank_sal

king 男性男性男性男性 8000 3000 1 smith 女性女性女性女性 5000 2000 2 adams 男性男性男性男性 5000 1400 2 clark 男性男性男性男性 5000 1000 2 scott 男性男性男性男性 4050 1200 5 blake 女性女性女性女性 ₃₄₀₀ ₁₀₀₀ ₆ allen 女性女性女性女性 3250 500 7 turner 男性男性男性男性 2500 8 jones 女性女性女性女性 2050 9 ward 女性女性女性女性 2000 10 martin 女性女性女性女性 1500 11 【分析関数（ランキング関数）】ランキング関数「rank」は、検索データセットのレコードに対し、レコードのランキングの数を割り振ることができます。上図にある使用例のうち、「over」から始まる引数により、「どの列を対象にランク付けするか」「昇順か降順か」という指定を行うことが出来ます。 - order by …… この句の後に来る列名を元にランク付けを行います。 ASC/DESCによる昇順・降順設定も可能です。この例では、sal（給与）の高い順（降順）にランク付けを行う設定を行っています。

(22)

分析関数

king 男性男性男性男性 8000 3000 1 smith 女性女性女性女性 5000 2000 2 adams 男性男性男性男性 5000 1400 3 clark 男性男性男性男性 5000 1000 4 scott 男性男性男性男性 4050 1200 5 blake 女性女性女性女性 ₃₄₀₀ ₁₀₀₀ ₆ allen 女性女性女性女性 3250 500 7 turner 男性男性男性男性 2500 8 jones 女性女性女性女性 2050 9 ward 女性女性女性女性 2000 10 martin 女性女性女性女性 1500 11 最初の式で同じ値が解決できない最初の式で同じ値が解決できない最初の式で同じ値が解決できない最初の式で同じ値が解決できない２番目の式を用いて解決２番目の式を用いて解決２番目の式を用いて解決２番目の式を用いて解決データセットのレコードに対し、データセットのレコードに対し、データセットのレコードに対し、データセットのレコードに対し、レコードのランクレコードのランクレコードのランクレコードのランクを計算するを計算するを計算するを計算する〔総合給与ランキング　〔総合給与ランキング　〔総合給与ランキング　〔総合給与ランキング　（同順位の場合はインセンティブでランク付け）（同順位の場合はインセンティブでランク付け）（同順位の場合はインセンティブでランク付け）（同順位の場合はインセンティブでランク付け）_〕〕〕〕 select ename,gender,salary,comm,

rank () over (order by sal desc, comm desc) as rank_sal

from emp ; 【分析関数（ランキング関数）】さらに、順序付けには第２候補・第３候補‥‥というような設定を行うことも可能です。上図の例で言うと、第１候補としてsal（給与）の高い順（DESC）で順位付けを行い、もし同値のレコードが複数現れた場合にはcomm（インセンティブ）の高い順（DESC）で細かい順序づけを行うように指定しています。

(23)

分析関数

（ランキング関数）（ランキング関数）（ランキング関数）（ランキング関数）データセットのレコードに対し、データセットのレコードに対し、データセットのレコードに対し、データセットのレコードに対し、レコードのランクレコードのランクレコードのランクレコードのランクを計算するを計算するを計算するを計算する

smith 女性女性女性女性 5000 2000 1 blake 女性女性女性女性 3400 1000 2 allen 女性女性女性女性 3250 500 3 jones 女性女性女性女性 2050 4 ward 女性女性女性女性 2000 5 martin 女性女性女性女性 ₁₅₀₀ ₆ king 男性男性男性男性 8000 3000 1 adams 男性男性男性男性 5000 1400 2 clark 男性男性男性男性 5000 1000 3 scott 男性男性男性男性 4050 1200 4 turner 男性男性男性男性 2500 5 partition by オプションによってオプションによってオプションによってオプションによって rank が操作するグループにデーが操作するグループにデーが操作するグループにデーが操作するグループにデータセットが分割されるタセットが分割されるタセットが分割されるタセットが分割されるグループが変更されるたびグループが変更されるたびグループが変更されるたびグループが変更されるたびランクがリセットされるランクがリセットされるランクがリセットされるランクがリセットされる〔性別給与ランキング　〔性別給与ランキング　〔性別給与ランキング　〔性別給与ランキング　（同順位の場合はインセンティブでランク付け）（同順位の場合はインセンティブでランク付け）（同順位の場合はインセンティブでランク付け）（同順位の場合はインセンティブでランク付け）_〕〕〕〕 select ename,gender,salary,comm,

rank () over (partition by gender

order by sal desc, comm desc) as rank_sal

from emp ; 性別によってカテゴリー分け性別によってカテゴリー分け性別によってカテゴリー分け性別によってカテゴリー分け ↓ ↓ ↓ ↓ カテゴリー別にランク表示カテゴリー別にランク表示カテゴリー別にランク表示カテゴリー別にランク表示【分析関数（ランキング関数）】 - partition by …. 引数にこの句を指定すると、パーティション（後述参照のこと）により結果セットが分割され、そのセット内で順位付けが実行されます。上図の例で言うと、partition by genderにより「性別のランキング」が実現します。男性の結果セットでランク付けを行い、女性の結果セットになるとランク付けをリセットします。

(24)

Oracle Corporation Japan 24 （参考）　結果セット・パーティションについて

（参考）　結果セット・パーティションについて（参考）　結果セット・パーティションについて（参考）　結果セット・パーティションについて

Ename Gender Salary smith 女性女性女性女性 5000 allen 女性女性女性女性 3250 ward 女性女性女性女性 2000 jones 女性女性女性女性 2050 martin 女性女性女性女性 1500 blake 女性女性女性女性 ₃₄₀₀ clark 男性男性男性男性 5000 scott 男性男性男性男性 4050 king 男性男性男性男性 8000 turner 男性男性男性男性 2500 adams 男性男性男性男性 1000 select ename,gender,salary from emp 【参考：結果セット・パーティションについて】結果セットパーティションは、分析関数のみに有効な考え方です。Oracleのテーブル等で使用するパーティのテーブル等で使用するパーティのテーブル等で使用するパーティのテーブル等で使用するパーティションとは全く異なるものですのでご注意ください。ションとは全く異なるものですのでご注意ください。ションとは全く異なるものですのでご注意ください。ションとは全く異なるものですのでご注意ください。上図はパーティションがない普通のselect文と、その結果表示例を示しています。問い合わせにより得たデータの集まりをここでは「結果セット」と呼びます。

(25)

Ename Gender Salary max_sal smith 女性女性女性女性 5000 5000 allen 女性女性女性女性 3250 5000 ward 女性女性女性女性 2000 5000 jones 女性女性女性女性 2050 5000 martin 女性女性女性女性 1500 5000 blake 女性女性女性女性 ₃₄₀₀ ₅₀₀₀ clark 男性男性男性男性 5000 8000 scott 男性男性男性男性 4050 8000 king 男性男性男性男性 8000 8000 turner 男性男性男性男性 2500 8000 adams 男性男性男性男性 1000 8000 問い合わせ結果セットを分析関数問い合わせ結果セットを分析関数問い合わせ結果セットを分析関数問い合わせ結果セットを分析関数によって分割した行グループのことによって分割した行グループのことによって分割した行グループのことによって分割した行グループのこと Oracleの表のパーティションとは異の表のパーティションとは異の表のパーティションとは異の表のパーティションとは異なることに注意するなることに注意するなることに注意するなることに注意する

結果セット・パーティション

select ename,gender,salary,

max(salary) over(partition by gender) as max_sal

from emp （参考）　結果セット・パーティションについて（参考）　結果セット・パーティションについて（参考）　結果セット・パーティションについて（参考）　結果セット・パーティションについて【参考：結果セット・パーティションについて】上図の例で言うと、overの後にくる引数にpartition by ….と記述すると、この句の後にくる列名により結果セットが論理的に分割されます。この論理的区切りは分析関数を適用する際に関わってきます。例ではgender（性別）によって分割されてたパーティションに対し分析関数maxが適用されるため、性別毎に異なる結果が帰ってきます（maxにより、男女別の最大値がレコードに表示されています。maxについては後述「レポート関数」をご参照ください）。