2. - OTN

Oracle9iAS Discoverer 活用ガイド
―関数を用いた分析手法―
Disco 分析関数
目次
1.
日付関数：
はじめに
年月日アイテムを作成：EUL_DATE_TRUNC の利用
和暦アイテムを作成：NLS_CALENDAR の利用
会計年度アイテムを作成：代替ソートの利用
会計四半期アイテムを作成：Decode 関数の利用
2.
分析関数：
はじめに
分析関数の利用とそのメリット
Discoverer から分析関数を利用する手順
Discoverer から実現する分析手法
◆ 総売上高に占める売上比率は?
◆ 売上高ランキングは? 売上高上位 3 位は?
◆ 3 ヶ月移動平均は?
◆ 売上高の前年対比は?
◆ 売上高と利益の関係性は?(回帰分析)
表記方法について
今後、Oracle9iAS Discoverer は｢Discoverer｣と明記します。
はじめに
このドキュメントでは、Oracle9iAS Discoverer で実現できる関数を用いた分析手法をご紹
介します。
企業における売上げデータや顧客情報から迅速に必要なデータを取得し、多角的に分析を
加える事で、ビジネスに有用な知識を見つけ出す事は、今後ますます重要となってきます。
ここでは、具体的な分析例を挙げながら、エンド･ユーザーは特に意識することなく利用で
きる、日付関数や RDBMS の分析関数を用いた分析手法をご紹介します。
また、このドキュメントは Oracle9iAS Discoverer R4.1(4i)を対象として作成されています。
2
Disco 分析関数
1.日付関数の利用
はじめに
企業における売上データを分析し、ビジネスに有用な知識を見つけ出す際に、日付情報や
顧客情報、売上高を一つの切り口として分析する事も多いでしょう。その際に、データベ
ース内部の識別子を用いて分析するのは容易ではありません。そこで、Discoverer ではデ
ータベースの複雑な構造を隠蔽し、ビジネス用語でデータを抽出する事を可能にしていま
す。このビジネス用語への変換作業は、EUL(End User Layer)というメタレイヤを介する
ことで実現しています。
データベース内部
EUL
ユーザー側
顧客 ID
Customer_id
cl_tel_number||‘,’||cl_areacode||
‘,’||cl_countrycode
Sum(nvl(qty*unit_price,0))
顧客電話番号
売上高
上記のイメージ図のように、データベース内部には｢customer_id｣や｢cl_tel_number｣ 「cl_
areacode」「cl_countrycode｣ ｢qty｣｢unit_price｣が格納されています。「売上高」を求め
一つの分析対象をする場合には、商品の単価(unit_price)と売上げ個数(qty)を掛け合わせる
必要があります。「売上高」を事前に計算し「Sum(nvl(qty*unit_price,0))」、Discoverer
が提供する EUL を介することで、データベース内部のカラム名とビジネス用語をマッピン
グさせます。ゆえに、エンドユーザーはデータベース内部の複雑な構造を特に意識する事
なく、｢顧客 ID｣や｢顧客電話番号｣｢売上高｣などのビジネス用語で分析する事をできます。
図 1 ビジネス用語を用いて分析
3
Disco 分析関数
年月日アイテムを作成：
EUL_DATE_TRUNC 関数の利用
売上分析を行う際に、日付情報を一つの切り口として多次元分析を実施する事も多いでし
ょう。売上高の傾向を把握する場合は、チャートなどを活用して｢年度｣や｢四半期｣など粒
度の粗い分析が求められます。一方、売上高が伸び悩む原因を追求する際には、｢月｣や｢曜
日｣など粒度の細かいデータが必要となります。
ここでは、売上年や売上月、売上日などのビジネス用語を用いた日付アイテムを作成する
方法を紹介します。また、エンドユーザーが分析する際にデータの粒度を自由に選択でき
るよう、ドリリング機能を有効にするための階層も作成します。
図 2 地区別の売上分析
図 2 のワークシートでは、地区別の売上高を、｢年度別｣｢四半期別｣の切り口で分析してい
ます。データベース内部に格納されている日付データ(DD-MON-YY: HH24:MI:SS 形式)を、
｢年度｣や｢四半期｣などのビジネス用語に変換させて利用しています。
まず、データベース内部の日付データを、日付関数を用いてビジネス用語に変換します。
次に、作成した「年度」－「四半期」－「月」－「売上日」階層構造を作成する手順（下
記の 3 つの手順）を紹介します。
①
②
③
Date 型アイテムが入っているフォルダに新規アイテムを作成
作成したアイテムに対して、アイテムクラス(値リスト)を生成
作成したアイテムを利用して、時系列階層を作成
4
Disco 分析関数
① Date 型アイテムが入っているフォルダに新規アイテムを作成
Discoverer Administration Edition を起動させ、ビジネスエリアを選択し、Date 型ア
イテム(例えば transaction_date)が入っているフォルダを右クリックします。「新規
アイテムの作成｣を選択する(図 3)と、｢新規アイテム｣ダイアログ(図 4)が起動しま
す。
図 3 日付アイテムを新規に作成
｢新規アイテム｣ダイアログ(図 4)では、左側にユーザーが利用できる｢アイテム｣や
｢関数｣の一覧が表示されます。利用するアイテムや関数を右側の｢計算｣欄に貼付
け、計算式を作成します。また、作成するアイテムの名前(アイテム名)は任意です。
図 4 ｢新規アイテム｣ダイアログ
5
Disco 分析関数
データベース内部の日付データをビジネス用語｢年度｣などに変換する方法を例に
具体的に計算式を紹介しましょう。
「新規アイテム」ダイアログの「関数」より EUL_DATE_TRUNC を選択
データベース内部の日付データを｢年｣「月」「日｣などのビジネス用語に変換する
には、EUL_DATE_TRUNC 関数を利用します。
まず、｢関数｣より EUL_DATE_TRUNC を選択します。すると、右側の｢計算｣欄に
は下記の構文が表示されます。
EUL_DATE_TRUNC の構文
EUL_DATE_TRUNC(d, fmt)
この EUL_DATE_TRUNC 関数は、日付データ(上の構文では d の部分)を、与えら
れた書式マスク(上の構文では fmt の部分)に切り捨てる関数です。｢d｣の部分には
date 型日付アイテムを埋め込み、fmt には変換する要素を入力します。
Sysdate「2002 年 3 月 21 日」を例に EUL_DATE_TRUNC を適用してみましょう。
EUL_TRUNC_DATE(date, ’month’)とすると、Date 型の日付アイテム“3 月”が返さ
れます。出力される値は TO_CHAR(date, ’month’)でも同じです。しかし、TO_CHAR
関数を用いて出力された“3 月”という値は Char 型に変換されます。一方、
EUL_DATE_TRUNC 関数を用いると、Date 型のまま日付のアイテムを表示する事
が可能となります。
日付データを Date 型のまま利用できる事は、後述します分析関数を利用する際に
大変便利です。例えば、3 ヶ月移動平均値を求めたい場合、1 月や 2 月が Date 型な
ので「時間」という論理な範囲での指定が可能です。一方、1 月や 2 月が Char 型
の場合は「行数」という物理的な単位で範囲を指定する必要があります(詳細は後
述します)。
「新規アイテム」ダイアログの「計算」欄に計算式を入力。名前（アイテム名）
は任意でよい。
【計算式】
EUL_DATE_TRUNC（西暦.Transaction Date, YYYY）
6
Disco 分析関数
図 5 ｢新規アイテム｣ダイアログに計算式を入力
上記では、｢年度｣アイテムを作成する計算式を例に紹介しましたが、
EUL_DATE_TRUNC の構文の要素(fmt)を変更するだけで、｢四半期｣や「月」のア
イテムを作成する事もできます。下記に、利用可能な要素をまとめました。ここ
では、2002 年 3 月 21 日を例に EUL_DATE_TRUNC 関数を適用しています。
EUL_DATE_TRUNC 関数の要素
yyyy
西暦 4 桁の年(0～9999)
ex) 2002
yyy
西暦の最後 3 桁表示
ex) 002
yy
西暦の最後 2 桁表示
ex) 02
y
西暦の最後 1 桁表示
ex) 2
mm
2 桁で示した月
ex) 03
mon
月の情報(省略)
ex) Mar
month
月の情報
ex) March
dd
日付の情報
ex) 21
Q
年の四半期
ex) Q1
※ 注意：四半期のデフォルト表示は 01-January からカウントして計算
するため｢Q1｣は 1～3 月、｢Q2｣は 4～6 月、｢Q3｣は 7～9 月、｢Q4｣
は 10～12 月と表示します。オラクル社のように、6 月から会計年
度が始まる場合は、代替ソート機能(後述)を利用してカスタマイズ
する必要があります。
7
Disco 分析関数
② 作成したアイテムに対して、アイテムクラス(値リスト)を生成
作成したアイテム「年度」を右クリックして、｢新規アイテムクラスの作成｣を選
択し、アイテムクラスウィザード(図 6)を起動します。
図 6 作成したアイテムに対して値リストを生成
図 6 の例では、作成したアイテム｢年度｣に対して、アイテムクラス(値リスト)を生
成(図 7)しています。
図 7 アイテムクラス(値リスト)
※アイテムクラス(値リスト)を生成すると、ユーザーがワークシートを作
成しデータを取得する際の検索パフォーマンスが向上します。特に、カ
ーディナリティの低いアイテムに対しては事前にアイテムクラス(値リス
ト)を生成しておくと良いでしょう。
8
Disco 分析関数
③ 最後に、作成したアイテムを利用して時系列階層を作成
作成した「年度」「四半期」などのアイテムを用いて階層を作成します。時系列階
層を作成する事で、各ユーザーがドリルアップ/ドリルダウン操作する事ができま
す。
図 7 時系列階層の生成
9
Disco 分析関数
和暦アイテムを作成：
NLS_CALENDAR の利用
ここでは、西暦(2002 年)ではなく和暦(平成 14 年)アイテムを作成する方法を紹介します。
和暦アイテムを作成（Discoverer Administration Edition より）
① 変数 NLS_CALENDAR をレジストリに追加
Discoverer Administration Edition をインストールしたマシン上で、｢regedt32｣コマン
ドを実行してレジストリエディタを起動させ、下記に値を追加します。
◆ ￥￥HKEY_LOCAL_MACHINE¥SOFTWARE¥ORACLE以下に値
NLS_CALENDAR を追加
◆ 文字列に Japanese Imperial と設定
(データタイプは REG_EXPAND_SZ を選択)
② Date 型アイテムが入っているフォルダに新規アイテムを作成
Discoverer Administration Edition を起動させ、ビジネスエリアを選択し、Date 型ア
イテムが入っているフォルダに新規アイテムを作成します。｢新規アイテム｣ダイ
アログの｢計算｣欄には下記の計算式を入力します。
和暦年：
和暦月：
和暦日：
TO_CHAR(DATE 型アイテム,'EEYY') || "年"
TO_CHAR(DATE 型アイテム,'MM') || "月"
TO_CHAR(DATE 型アイテム,'DD') || "日"
作成するアイテム名(上の例では和暦年、和暦月、和暦日)は任意です。作成され
るアイテムは Chara 型なので、3 ヶ月移動平均を求める際などに、論理的な範囲
（Month）指定は出来ません。その際には物理的な範囲（ROW）で指定します。
※ 注意
Discoverer Administration Edition がインストールされているマシン
のレジストリに設定されている NLS_CALENDAR が有効になって
いる場合、EUL_DATE_TRUNC 関数を用いたアイテムの作成や編
集は出来ませんので、ご注意ください。EUL_DATE_TRUNC 変数
を用いて西暦アイテムを作成する場合には、NLS_CALENDAR 変数
を無効にしてください。
(同じビジネスエリア内に和暦と西暦を共存させる事は可能です。)
10
Disco 分析関数
③ 作成したアイテムに対して、アイテムクラス(値リスト)の作成
作成したアイテムを右クリックして、｢新規アイテムクラスの作成｣を選択すると、
アイテムクラスウィザード(図 6)を起動します。ウィザードに従ってアイテムクラ
ス(値リスト)を作成します。
図 8 和暦年のアイテムクラス(値リスト)
上の例では、和暦を用いて年度を表示するアイテム作成しています。アイテムを
作成すると｢平成｣の元号が付随したアイテムクラス（図 8）が生成されます。
④ 作成したアイテムを利用して、時系列アイテム階層を作成
和暦アイテムを Discoverer Desktop Edition（C/S）より利用
上記で作成した和暦アイテムを、Discoverer Desktop Edition（C/S）より利用して
ワークブックを作成する場合、各クライアントマシンに下記の設定が必要です。
※ 利用上の注意事項は、上項「和暦アイテムを作成(Discoverer Administration
Edition)より」と同様です。ご参照下さい。
11
Disco 分析関数
① Discoverer Desktop Edition をインストールしたマシンのシステム環境変数
に NLS_CALENDAR を設定する
【NT 版】
｢スタート｣－｢コントロールパネル｣－｢システム｣－｢環境｣－｢システム環境変数｣に
下記の変数を追加する。
変数：NLS_CALENDAR
値： Japanese Imperial
【Windows 2000 版】
｢スタート｣－｢コントロールパネル｣－｢システム｣－｢詳細｣－｢環境変数｣－｢システム
環境変数｣に下記の変数を追加する。
変数：NLS_CALENDAR
値： Japanese Imperial
② システム環境変数を反映させるために、OS を再起動します。
和暦アイテムを Discoverer Plus（Web）より利用
上記で作成した和暦アイテムを Discoverer Plus(Web 版)より利用して、ワークブ
ックを作成する場合には、Discoverer Server 側に設定を行う必要があります。
※ 利用上の注意事項は、上項「和暦アイテムを作成(Discoverer Administration
Edition)より」と同様です。ご参照下さい。
① Discoverer Server を終了
② Discoverer Server 側の環境変数 NLS_CALENDAR を設定する
【Solaris 版】
% su Discoverer Server インストールユーザー/パスワード
% setenv NLS_CALENDAR “Japanese imperial”
【NT 版】
｢スタート｣－｢コントロールパネル｣－｢システム｣－｢環境｣－｢システム環境変数｣に
下記の変数を追加する。
変数：NLS_CALENDAR
値： Japanese Imperial
【Windows 2000 版】
｢スタート｣－｢コントロールパネル｣－｢システム｣－｢詳細｣－｢環境変数｣－｢システム
環境変数｣に下記の変数を追加する。
変数：NLS_CALENDAR
値： Japanese Imperial
12
Disco 分析関数
③ [Windows 系の OS] システム環境変数を反映させるために、OS を再起動
④ Discoverer Server を再起動
※その他：西暦アイテムと和暦アイテムを併用する際の注意事項
【西暦から和暦の利用に変更する場合】
NLS_CALEDAR を”Japanese Imperial”に設定すると、和暦アイテムを利用して新規にワーク
ブックを作成する事が出来るようになります。但し、西暦アイテムを利用した新規ワーク
ブックの作成は出来なくなりますのでご注意ください。西暦アイテムを利用した既存のワ
ークブックを開く事は可能です。
=======================================================================
【和暦から西暦の利用に変更する場合】
NLS_CALENDAR ”Japanese Imperial”を利用していた環境で、Discoverer Server を再起動し、
環境変数 NLS_CALENDAR を無効にする場合は、和暦アイテムを利用したワークブックを
新規に作成出来なくなるだけでなく、既存ワークブックも利用できなくなってしまいます
ので、ご注意ください。
【和暦アイテムを含むサマリーの作成】
和暦アイテムを含むサマリーを作成する場合、NLS_CALENDAR では DBMS_JOB の利用が
できません。和暦アイテムを含むサマリーのリフレッシュのタイミングをスケジュールす
る事ができませんので、ご注意ください。
=======================================================================
13
Disco 分析関数
会計年度アイテムを作成：
代替ソートの利用
Discoverer から利用するアイテムのデフォルト表示は、アルファベット順(あいうえお順)
です。具体的には、曜日一覧を表示させる場合、火(か)曜日、金(き)曜日、月(け)曜日、水(す)
曜日、土(と)曜日、日曜日(に)、木(も)曜日とあいうえお順でソートされます。また、日付
データの場合、1 月 1 日～12 月 31 日の順にソートされ表示されます。
ここでは、売上月の日付データを図 9 のデフォルト表示から、図 10 の会計年度(6 月～5 月)
で表示させる方法を紹介します。
図 9 デフォルト表示
図 10 カスタマイズ後
表示順序のカスタマイズは、Discoverer の代替ソート機能を利用します。
14
Disco 分析関数
① Discoverer Administration Edition を起動させ、ビジネスエリアを選択。カ
スタムフォルダの作成
Discoverer Administration Edition を起動させ、ビジネスエリアを選択し、右クリッ
クして「新規カスタムフォルダの作成」を選択します。作成するカスタムフォル
ダの SQL 文に、ソート順序を指定します。
図 11 新規カスタムフォルダの作成
下記の SQL 文を図 11 の「カスタムフォルダ」ダイアログに入力し、カスタムフォ
ルダ(フォルダ名は任意)を作成します。(Discoverer からは SQL 文のチェック機能
を提供しています)
Select
union
Select
union
Select
union
Select
union
Select
union
Select
union
Select
union
Select
union
Select
union
'6 月' US_CAL, 1 Alternate_Sort from dual
'7 月' US_CAL, 2 Alternate_Sort from dual
'8 月' US_CAL, 3 Alternate_Sort from dual
'9 月' US_CAL, 4 Alternate_Sort from dual
'10 月' US_CAL, 5 Alternate_Sort from dual
'11 月' US_CAL, 6 Alternate_Sort from dual
'12 月' US_CAL, 7 Alternate_Sort from dual
'1 月' US_CAL, 8 Alternate_Sort from dual
'2 月' US_CAL, 9 Alternate_Sort from dual
15
Disco 分析関数
Select '3 月' US_CAL, 10 Alternate_Sort from dual
union
Select '4 月' US_CAL, 11 Alternate_Sort from dual
union
Select '5 月' US_CAL, 12 Alternate_Sort from dual
前ページの SQL 文では、”6 月”から”5 月”までをアイテム US_CAL に置き換え、1
から 12 までの番号を割り振り、ソート順序を指定しています。
② 代替ソート機能を用いてアイテムクラス(値リスト)を生成。
アイテム US_CAL のアイテムクラス(値リスト)を作成する際に、代替ソート機能
を用いて①のソート順を適用させます。代替ソートとは、カスタムフォルダで生
成した順序に従って、値リストのソートを行う機能です。
まず、図 1 のように、①で作成したカスタムフォルダのアイテム US_CAL を選択
して、右クリックすると新規アイテムクラスウィザード（図 12）が起動します。
図 12 代替ソート機能を用いてアイテムクラスを生成
起動したアイテムクラスウィザードから代替ソートを選択し、ウィザードに従っ
てアイテムクラス(値リスト)を作成します。アイテムクラス名は任意です。
16
Disco 分析関数
図 13 アイテムクラスウィザード①
図 14 アイテムクラスウィザード②
図 15 アイテムクラスウィザード③
17
Disco 分析関数
図 16 アイテムクラスウィザード④
アイテムクラスウィザードに従って、Discoverer から提供されている代替ソート機
能を利用すると、値リスト表示する順序を自由自在にカスタマイズすることがで
きます。
マニュアル『Oracle Discoverer Administration Edition for Windows 管理ガイ
ド』の 4 章には、曜日の一覧を表示させる例を用いて説明しています。
マニュアルも合わせてご参照ください。
18
Disco 分析関数
会計四半期アイテムを作成：
Decode 関数の利用
前ページで表示順序を変更した｢会計年度に合わせた売上月(6 月～5 月)｣を用いて、図 17 のよう
に｢四半期｣アイテムを作成する方法を紹介します。
図 17 会計四半期
① 時系列となるアイテムが入っているフォルダに新規アイテムを作成（計算式に
は DECODE 関数を用いる）
前章で作成したアイテム｢売上月｣の値が｢06 月｣と返された場合には｢第 1 四半期｣
を表示させるように、条件付き照会(IF～THEN)を可能にします。DECODE 関数を
用いて実現します。
「新規アイテム」ダイアログの「関数」から DECODE を選択すると、
「計算」欄
には下記の構文が表示されます。
DECODE(expr, search1, result1[, searchN[, resultN[, default]]])
「expr」には利用するアイテムを埋め込みます。DECODE 関数は「expr」を検索し
ている際に返された値「serach1」を「result1」に置換える式です。指定がなければ
「default」値に置換えます。
ここでは、6 月～8 月までを｢第 1 四半期｣、9 月～11 月までを｢第 2 四半期｣、12 月
19
Disco 分析関数
～2 月までを｢第 3 四半期｣と割当て、それ以外の値であれば、デフォルト値｢第 4
四半期｣が返されるよう指定します。計算式は下記の通りになります。作成するア
イテム名は任意です。
【計算式】
DECODE(売上月,'06 月','第 1 四半期','07 月','第 1 四半期','08 月','第 1 四半期',
‘09 月','第 2 四半期','10 月', '第 2 四半期','11 月','第 2 四半期',
'12 月','第 3 四半期','01 月','第 3 四半期','02 月','第 3 四半期','第 4 四半期')
------------------------------------------------------------------------------------------------------------参考) 同様に、会計四半期のデータを元に、6 月～11 月までを第 1 半期、12 月～5
月までを第 2 四半期をする事も可能です。
【計算式】DECODE(売上四半期,'Q1','第 1 半期','Q2','第 1 半期','第 2 半期')
------------------------------------------------------------------------------------------------------------② 作成したアイテムに対して値リストを生成
作成したアイテムにアイテムクラス（値リスト）を生成します。
③ 作成したアイテムを時系列アイテム階層に追加して、完成
事前に作成した時系列階層｢年－月－日｣を右クリックすると、「階層の編集」
ダイアログ（図 18）が起動します。アイテム｢四半期｣を組込み、階層構造を
再作成します。
図 18 時系列階層の編集
20
Disco 分析関数
2.分析関数の利用
はじめに
この章では Oracle RDBMS から提供されている分析関数を Discoverer より利用した分析手法を紹
介します。
なお、単純な分析は RDBMS の関数を用いなくても、実現します。たとえば、単純な分析とは、下
記のような分析方法を指します。
データの総計(シグマ)
データのソート(昇順、降順、アルファベット順)
データ数のカウント(COUNT)
図 19 画面上のシグマ(Σ)を利用
図 19 の画面右上にあるシグマ(Σ)をクリックすると、｢総計｣ダイアログ(図 20)が起動し、
下端総計や右端総計をグラフィカルに行を作成する事が出来ます。
21
Disco 分析関数
図 20 「総計」ダイアログ 合計値の作成
また、この「総計」ダイアログでは、地区毎や年度毎の「グループごとの副総
計」も指定できます。
分析関数の利用とそのメリット
Discoverer は Oracle RDBMS(Oracle8i R8.1.6 以降)との親和性に優れ、エンド･ユーザーが特
に意識する事なく、分析関数を利用する事が出来ます。
まず、Discoverer が内部的に分析関数を利用できる事のメリットを紹介します。そのメリットを一言
で言うと、優れた検索パフォーマンスの実現です。
ここでは検索パフォーマンスの観点から、分析関数を利用した場合と利用しなかった場合の
Autotrace の結果を比較してみましょう。例として、月別売上高のランキングを検索する際に、RANK
関数を用いた場合(図 21)と、RANK 関数を用いなかった場合(図 22)の Autotrace の結果を掲載し
ます。
図 21
図 22
この検索では、それぞれ 192 行のデータを取得(rows)しています。丸で囲まれた経過時間
(elapsed)の値に注目してください。図 21 の RANK 関数を用いなかった場合は、72.19 秒検索に
かかっているのに対して、図 22 の RANK 関数を用いた場合は 5.56 秒と、10 倍以上の検索パフ
ォーマンスが上がった事がわかります。
22
Disco 分析関数
Discoverer から分析関数を利用する手順
Discoverer から、RDBMS の分析関数を利用するには、分析しているワークシートにユーザー定
義アイテムを追加作成します。ユーザー定義アイテムは Discoverer Desktop Edition(C/S 版)及
び Discoverer Plus(Web 版)より作成、編集できます。
① ユーザー定義アイテムの作成
Discoverer Plus（または Discoverer Desktop Edition）を起動し、｢ツール｣－｢ユーザ
ー定義アイテム｣を選択（図 25）して、「ユーザー定義アイテム」ダイアログ（図
26） を起動します。
図 25 「ユーザー定義アイテム」ダイアログを起動
｢ユーザー定義アイテム｣ダイアログ(図 26)では、新規にユーザー定義アイテムを作
成したり、編集したりする事が出来ます。左側には Discoverer から選択できる｢ア
イテム｣や｢関数｣が表示されます。一つの関数を選択すると、その構文が図 26 の右側
の｢計算｣欄に表示されます。その構文に従って、右側の「計算」欄に、ランキングを表示
させるための計算式や 3 ヶ月移動平均を表示させるための計算式を入力すると、分析関
数を利用したアイテムを生成できます。
23
Disco 分析関数
図 26 「ユーザー定義アイテム」の生成
作成した計算式が間違えていた場合は、構文エラーが返されます。
Discoverer から実現する分析手法
では、優れた検索パフォーマンスを実現する分析関数を用いたら、どのような分析が実現するの
でしょう。Discoverer からの分析手法の例をいくつか挙げてみましょう。
・
｢総売上高に占める売上比率?｣
・
｢売上高ランキングは? 上位 3 位の都道府県は?｣
・
｢3 ヶ月移動平均株価?｣
・
｢売上高の先月対比?｣
・
｢売上高と利益の関係は?｣
上記の分析手法例を実現するための計算式を紹介します。
売上高データが格納されているデータウェアハウスを例に Discoverer からの分析手法を
説明します。このデータウェアハウスには、売上高データのほかに、売上年、売上四半期、
売上月、売上日の日付情報や、売上地区、売上都道府県、商品、販売部門の情報なども格
納されているとします。
まず、各年の地区別、都道府県別の売上高を元に、より多くの売上高を上げている都道府
県を調べてみましょう。
24
Disco 分析関数
図 27 ソースデータ ＝ 売上高一覧
図 27 では、作成されたワークシートの一部が表示されています。売上高一覧は、順不同の
ように思われますが、各地区単位でソート(あいうえお順)されています。
注意: デフォルトで表示される結果データは、昇順、あいうえお順、ア
ルファベット順でソートされ、NULL 値が先頭になります。
売上高ランキングと、上位 3 位の都道府県は?
まず、数値データをランク付けする分析手法を紹介します。売上高をランク付
けしたり、地区別の営業成績上位(下位)3 位の都道府県を求めたりする際に有効で
す。
図 27 のワークシート｢売上高一覧｣に対して、売上高の高い順にランク付けするア
イテムを生成すると、図 28 のようになります。
図 28 各年毎の地区別売上高ランキング
25
Disco 分析関数
では、図 28 の「ランキング」列を構成する方法を紹介しましょう。
① ユーザー定義アイテムを生成
ある数値データを元にランク付けするには、RANK 関数を利用します。｢ユーザー
定義アイテム｣ダイアログより関数 RANK を選択します。すると、下記の構文が「計
算」欄に表示されます。
ランク付けをするための構文
RANK () OVER (PARTITION BY expr1 ORDER BY expr2)
ORDER BY 句の｢expr2｣には、順序付けするアイテムを入力します。
PARTITION BY 句では、どのような範囲でランク付けするかを指定します。
たとえば、年度ごとの売上高をランク付けする場合には「PARTITION BY 年
度」とし、さらに、年度ごとの地区別にランク付けする場合には「PARTITION
BY 年度, 地区」とサブ・パーティションを指定します。この句を省略すると
問合せた結果セットのすべての行を単一パーティション（グループ）として
扱います。
用語の説明
年度単位でのパーティション
(グループ)
地区単位でのサブ･
パーティション
(サブ･グループ)
問合せた結果セット
26
Disco 分析関数
問合せた結果セットに対して、パーティション単位、サブ･パーティション単位で
ランク付けする事ができます。ここでは、図 28 のように各年の地区別に売上高を
ランク付けしたいので、｢PARTITION BY 年, 地区｣とします。また、都道府県別に
売上高を総計した｢売上高 SUM｣を降順でソートしたいので、｢ORDER BY 売上高
SUM DESC｣と、次の計算式を生成します。PARTITION BY 句で指定した各パーティ
ション(グループ)単位で SUM(売上高)がまず計算されます。その後 ORDER BY 句を元に、
売上高を基準に相対ランクを計算します。
【計算式】
RANK () OVER (PARTITION BY 年, 地区 ORDER BY 売上高 SUM DESC)
この計算式を組込んだアイテムが図 28 の｢ランキング｣列になります。
総売上高に占める売上比率は?
次に、年間総売上高に対する売上比率を求めてみましょう。
図 29 の｢売上比率｣列は、年間総売上に対して、各都道府県の売上高の比率を求め
ています。2002 年度の総売上高に対する神奈川県の売上比率は 7.18％、東京都の
売上比率は 6.58％である事がわかります。
図 29 総売上高に占める売上比率
では、図 29 の総売上高に占める売上比率を求める方法を紹介しましょう。
27
Disco 分析関数
① 各都道府県別の売上高を総計する
まず、｢地区｣の値でグループソートします。すると、売上高 SUM を求めた際に、
｢地区｣の値リストである「都道府県」別の売上高総計が計算されます。
② ユーザー定義アイテムの生成
年間総売上高に占める売上比率は、RATIO_TO_REPOT 関数を利用して求めます。
｢ユーザー定義アイテム｣ダイアログより関数 RATIO_TO_REPORT を選択すると、
下記の構文が表示されます。
RATIO_TO_REPOT の構文
RATIO_TO_REPORT(expr) OVER (PARTITION BY expr1)
ここでは、年間総売上高と各都道府県の売上高を比較したいので、｢PARTITION BY
年｣と指定し、「年度」単位での売上げ比率を求めます。①で｢地区｣アイテムでグ
ループソートしているので、RATIO_TO_RATIO 関数を用いて｢売上高 SUM｣を計算
すると、都道府県別の SUM(売上高)が計算されます。
【計算式】
RATIO_TO_REPORT(売上高 SUM) OVER(PARTITION BY 年)*100
この計算式を組込んだアイテムが図 29 の｢売上比率｣列になります。
3 ヶ月移動平均は?
続いて 3 ヶ月移動平均を求める方法を紹介しましょう。
図 30 ソースデータ ＝ 月々の売上高(2002 年度)
28
Disco 分析関数
例えば、図 30 のように、月々の売上高一覧があるとします。この売上高一覧を元
に各月の 3 ヶ月移動平均を求めると、図 31 のようになります。
図 31 売上高の 3 ヶ月移動平均
3 ヶ月移動平均とは、現在が 3 月とすると、過去 2 ヶ月間にさかのぼった 3 ヶ月間、
つまり 1 月、2 月、3 月の売上高の合計を 3 で割った平均値が 2002 年 3 月の 3 ヶ
月移動平均になります。
2002 年 3 月の 3 ヶ月移動平均値(¥24,841) ＝ {1 月の売上高(¥34,859)＋
2 月の売上高(¥21,280)＋3 月の売上高(¥18,384)} ÷ 3
① ユーザー定義アイテムの生成
3 ヶ月移動平均を求めるには、AVG 関数を利用します。｢ユーザー定義アイテム｣
ダイアログの「関数」より AVG を選択すると、下記の構文が表示されます。
AVG 関数の構文
AVG(n) OVER (PARTITION BY expr1 ORDER BY expr2
RANGE BETWEEN expr3 AND expr4)
3 ヶ月移動平均を求めるには、下記の計算式を｢ユーザー定義アイテム｣ダイアロ
グの｢計算｣欄に埋め込みます。
【計算式】
AVG(売上高 SUM) OVER (PARTITION BY 年 ORDER BY 月
RANGE INTERVAL ‘2’ MONTH PRECEDING)
ORDER BY 句で指定しているアイテムが Date 型なので、RANGE 句を用いて
「時間」という論理的な範囲を指定し、その範囲内での平均値を求めます。
29
Disco 分析関数
PRECEDING または FOLLOWING の前に｢N 日前/N 日後｣と指定しますが、こ
この場合のように、2 ヶ月間の売上をさかのぼって平均値を取得したい場合
は｢RANGE INTERVAL ‘2’ MONTH PRECEDING｣と指定します。N に入る数字
はシングルクォーテーションでくくって記述します。
※ 注意： 3 ヶ月の移動平均を求める際に RANGE INTERVAL は’3’で
はなく’2’になります。これは、現在行が暗黙的に含まれているた
めです。AVG 関数を用いて計算式を作成する際に注意が必要です。
数字の単位(day, month, year…)等を PRECEDING または FOLLOWING の前
に指定します。
和暦アイテムのように CHAR 型で日付情報を表示している場合は、3 ヶ月移動平
均を求める場合、論理的な範囲｢MONTH｣を認識しません。よって、N 行前/後な
ど、物理的に範囲行数を指定して、平均値を集計計算します。
【計算式】
AVG(売上高 SUM) OVER (PARTITION BY 年 ORDER BY 月
ROWS BETWEEN ‘2’ PRECEDING AND ‘0’ FOLLOWING)
売上高の先月対比は?
売上高の先月対比を求めてみましょう。
図 32 のように、売上高の先月対比を求めるには、同時に複数行へのアクセスを可
能とする LAG 関数を利用します。
図 32 先月対比
30
Disco 分析関数
① ユーザー定義アイテムを生成
｢ユーザー定義アイテム｣ダイアログの｢関数｣から LAG 関数を選択し、計算式を作
成します。LAG 関数を｢計算｣欄に貼り付けると、下記の構文が表示されます。
LAG 関数の構文
LAG(expr, n) OVER (PARTITION BY expr1 ORDER BY expr2)
ここでは、まず｢PARTITION BY 年｣と指定し、結果セットを各年のパーティショ
ンに分けます。さらに、｢ORDER BY 月｣指定する事で、時系列にデータを並び替
え、各年のパーティション内で毎月の売上高を先月の売上高と比較します。LAG
句の｢expr｣には比較対象となるアイテムを入力し、｢n｣には比較する行数を指定す
るので、今月の売上高 SUM を 1 ヶ月前の売上高 SUM と比較するよう｢LAG (売上
高 SUM, 1)｣と指定します。
LAG 関数を用いると、内部結合なしに、同時複数の行へアクセスできます。下記
の計算式のように”1 行”前と指定すると、今月の売上高と 1 ヶ月前の売上高が表示
されます。
【計算式】
LAG (売上高 SUM, 1) OVER (PARTITION BY 年 ORDER BY 月)
売上高の翌月対比?
LAG 関数は現レコードの前の任意行でしたが、LEAD 関数は現レコードの後の
任意行を返します。LEAD 関数を用いて作成したアイテムが図 33 の「翌月対比」
列です。｢売上高と翌月対比｣を比較すると、売上高の推移が推測できます。
図 33 翌月対比
31
Disco 分析関数
【計算式】
LEAD (売上高 SUM, 1) OVER (PARTITION BY 年 ORDER BY 月)
売上高と利益の関係性は?
最後に、月別の売上高と利益の関係を調べてみましょう。図 34 のように月別の
売上高と利益一覧がある場合に、どの位の売上高があると、どの位の利益が見込
めるのかを割出すには、回帰分析(リグレッション分析)が有効です。回帰分析の結
果導き出された、売上高と利益の関係性を元に、売上高、利益予測を立てる事も
可能です。
図 34 ソースデータ ＝ 月別の売上高と利益
売上高を X 軸、利益を Y 軸として散布図を作成し、売上高と利益の関係性(傾向)
を把握してみましょう。
図 35 月別の売上高と利益の散布図
32
Disco 分析関数
上記のプロットに類似した直線、回帰直線｢y = A*x + B｣を割出す方法が、回帰分
析です。計算された回帰直線に来月の売上高(予測値)を当てはめる事で、より正確
な利益を割出します。
ここでは、代表的な回帰分析(回帰直線の傾きと切片、係数)を計算してみましょう。
【 回帰直線の傾き 】
まずは回帰直線の傾きを求めてみましょう。回帰直線の傾きとは、｢y = A*x + B｣
の式で言うところの｢A｣の値で、X(売上高)が 1 増えた時に Y(利益)がどのくらい増
えるかを表します。｢A｣の値がマイナスであれば、回帰直線は右下がりとなります。
また、回帰分析では、X(売上高)の事を｢独立変数｣といい、Y(利益)の事を独立変数
の変動に従って変化する変数として｢従属変数｣といいます。
①ユーザー定義アイテムの生成
回帰直線の傾きを求めるには、｢ユーザー定義アイテム｣ダイアログの｢関数｣から
REGR_SLOPE 関数を選択し、計算式を作成します。REGR_SLOPE 関数を｢計算｣
欄に貼り付けると、下記の構文が表示されます。
REGR_SLOPE(arg1, arg2) OVER (PARTITION BY expr1 ORDER BY expr2
RANGE BETWEEN expr3 AND expr4)
RANGE 句では、平均値を求めるアイテム範囲を論理的に指定します。物理的
な範囲を指定する場合は、ROWS 句を利用します。
BETWEEN～AND 句で、分析関数 AVG で操作する範囲を指定します。｢expr3｣
が範囲のスタートポイントで、｢expr4｣が範囲のエンドポイントです。
REGR_SLOPE 句の｢arg1｣には独立変数 X を入力し、｢arg2｣には従属変数 Y を
入力します。
【計算式】
REGR_SLOPE (利益 SUM, 売上高 SUM) OVER (ORDER BY 利益 SUM)
33
Disco 分析関数
図 25 月別の売上高と利益の関係性：回帰直線の傾き
1 行目｢1.14｣は 10 月と 11 月の売上高と利益を元に計算された回帰直線の傾きで、
2 行目｢0.7｣は 10 月と 11 月、12 月の売上高と利益を元に計算された回帰直線の傾
きです。
【 回帰直線の切片 】
続いて、回帰直線の切片を求めてみましょう。回帰直線の切片とは、｢y = A*x + B｣
の式で言うところの｢B｣の値で、一時直線が Y 軸と交わるところの値を示していま
す。
REGR_INTERCEPT (arg1, arg2) OVER (PARTITION BY expr1 ORDER BY expr2)
回帰直線の傾き(REGR_SLOPE 句)と同様に、｢arg1｣には独立変数 X を入力し、
｢arg2｣には従属変数 Y を入力します。
【計算式】
REGR_INTERCEPT (利益, 売上高) OVER (ORDER BY 利益)
図 26 月別売上高と利益の関係性：回帰直線の切片
【 回帰直線の係数 】
さらに、回帰直線の係数を求める事もできます。係数とは、R2 乗または適合度を
表し、傾きが NULL でない場合 0～1 までの値を返します。この係数は、｢y = A*x +
B｣の式で言うところの｢*｣の値で、この値がより 1 に近い程、｢回帰直線がデータ
に適合している｣、つまり直線に近いことを表します。
34
Disco 分析関数
では回帰直線の係数を求める構文は、下記の通りです。
REGR_R2 (arg1, arg2) OVER (PARTITION BY expr1 ORDER BY expr2
RANGE BETWEEN expr3 AND expr4)
回帰直線の傾き(REGR_SLOPE 句)と同様に、｢arg1｣には独立変数 X を入力し、
｢arg2｣には従属変数 Y を入力します。
【計算式】
REGR_R2 (利益, 売上高) OVER (ORDER BY 売上高)
図 26 月別売上高と利益の関係性：回帰直線の係数
今回の例では、係数の値がほぼ 1 に近いため、回帰直線がデータに適合し｢y = A1x
+ B｣であると言えます。
【 回帰分析の結果 】
図 24（回帰直線の傾き）と、図 25（回帰直線の切片）、図 26（回帰直線の係数）
を取得し、6 ヶ月分の結果から月別売上高と利益の関係は、｢y = 0.67x – 296.4｣とい
う類似直線が描ける事がわかります。ここで、翌 4 月に¥120,000 の売上を上げる
と予測すると、利益は上記の回帰直線の計算式に当てはめてはじき出す事ができ
ます。
利益 ＝ 0.671 × 120000 － 296.4
つまり、80103.6 つまり約 80,104 円と予測する事が可能です。
35
Disco 分析関数
まとめ
このように、Discoverer では、便利な分析手法をいろいろと提供しています。売上デー
タや顧客情報からビジネスに有用な知識を導き出す際に、ご活用ください。
このドキュメントでは、代表的な分析手法をまとめました。その他に関
しましては、マニュアル『Oracle Discoverer Plus for the Web ユーザーズ・
ガイド リリース 4.1』又は『Oracle Discoverer Plus for Windows ユーザー
ズ･ガイド･リリース 4.1』の｢A. ユーザー定義アイテムの例｣ をご参照く
ださい。
36
Disco 分析関数
日本オラクル株式会社
Copyright(C) Oracle Corporation Japan. All Rights Reserved.
無断転載を禁ず
この文書はあくまでも参考資料であり、掲載されている情報は予告なしに変更されるこ
とがあります。日本オラクル社は本書の内容に関していかなる保証もいたしません。また、
本書の内容に関連したいかなる損害についても責任を負いかねます。
Oracle は、オラクル社の登録商標です。Oracle8、Oracle8i、Net8 は、オラクル社の商
標または登録商標です。
他のすべての企業名と製品名は、識別のためにのみ掲載されており、それぞれの所有者
の商標の場合があります。
37