Oracle® Fusion Middleware Oracle Business Intelligence Discoverer Desktopユーザーズ・ガイド 11gリリース1 (11.1.1) E51908-01 |
|
前 |
次 |
分析関数およびOracleの関数全般の詳細は、次のマニュアルを参照してください。
『Oracle Database SQLリファレンス』
Oracle Databaseデータウェアハウス・ガイド
次の項に示す例では、Discoverer Desktopサンプル・データを使用します。
この付録の例では、次のフォーマットを使用しています。
通貨は、小数点以下の桁数なしで先頭にドル記号($)を付けて表示されています。
整数は、小数点以下の桁数なしで表示されています。たとえば、ランクなどです。
この項では、計算の作成に役立つ次の例を紹介します。
この例では、クエリー結果件数を計算します。
ワークブックのオプション | 詳細 |
---|---|
アイテムの選択 | Video Analysis Information: Year(Calendar Year)、Region、City、Sales SUM |
ソート順序 | Year、Region、City |
条件 | Department = Video Sale OR Department = Video Rental
Year = 2000 AND Region = Central |
計算名 | Rows returned |
計算 | ROWCOUNT |
注意 | ROWCOUNTでは、NULL値を数えません。NULL値を含めてクエリーから戻される行数を計算するには、まず一時的なアイテム、One record,(Calculation='1')を作成します。次に、One record,(SUM(Video Sales Analysis.One record))の出現回数をカウントする計算、Rows returnedを作成します。 |
表示されるデータ |
この例では、売上高の25%増を計算します。
ワークブックのオプション | 詳細 |
---|---|
アイテムの選択 | Video Analysis Information: Year(Calendar Year)、Region、City、Sales SUM |
ソート順序 | Year、Region、City |
条件 | Department = Video Sale OR Department = Video Rental
Year = 2000 AND Region = Central |
計算名 | 25% Increase |
計算 | Sales SUM * 1.25 |
表示されるデータ |
Discoverer Desktopでは、幅広い算術関数に加えて、数値やテキストをフォーマットする関数も使用できます。この例では、計算を使用してCityのテキスト・データを大文字にフォーマットしなおします。
ワークブックのオプション | 詳細 |
---|---|
アイテムの選択 | Video Analysis Information: Year(Calendar Year)、Region、City、Sales SUM |
ソート順序 | Year、Region |
条件 | Department = Video Sale OR Department = Video Rental
Year = 2000 AND Region = Central |
計算名 | City(Upper Case) |
計算 | UPPER(City) |
表示されるデータ |
Oracle Business Intelligence Discovererでは、Oracleデータベースに含まれる分析関数をサポートしています。また、分析関数をネストできることで、標準SQLの機能性が拡張されます。これらの高度な機能を使用して、洗練されたデータ分析を実行できます。
この項は、次の項目で構成されています。
ランキング: 「地域別の売上が上位10位および下位10位までの営業担当者は?」のようなビジネス上の質問を処理します。
バンディング: 「売上の25%を占めているブランドは?」のようなビジネス上の質問を処理します。
ウィンドウ集計: 「13週間の移動平均株価は?」または「地域別の累積売上高は?」のようなビジネス上の質問を処理します。
集計レポート: クエリーの処理後に、生成された行の数や、一連の行における1列の合計などの値を集計します。「製品グループの売上高における各製品の売上比率は?」のようなビジネス上の質問を処理します。
LAG/LEAD: 「1998年の売上増加率が、対1997年比で20%を超えている地域は?」や、「1997年度の売上高と1996年度の売上高との差は?」のようなビジネス上の質問を処理します。
統計: Business Intelligence OLAPやスプレッドシート・アプリケーションを使用して統計分析を実行します。たとえば、共分散関数や線形回帰関数などです。
分析関数を使用する場合、分析関数は厳密な定義を持っており、結果セットをドリル、ピボットまたはソートしてもこれらの定義は変化しない、という点に注意してください。たとえば、RANK関数を使用して、四半期別の売上高にランクを割り当てる場合、月レベルにドリルダウンしても、ランクが適用されるのは四半期レベルのみです。
Discoverer Desktopで分析関数を作成する場合、作成する関数を「計算」ダイアログ・ボックスに直接入力したり、貼り付けたり、関数リストから選択できます。
関数リストから分析関数を選択すると、汎用の分析関数テンプレートが表示され、ユーザー側からの指定が必要な情報が指示されるので、関数の定義が容易になります。テンプレートはあくまでも参考として使用してください。テンプレートは、多くの使用目的に適合するように設計されているため、必ずしもそのすべてを使用する必要はありません。
たとえば、新規のRANK分析関数を「計算」ボックスに貼り付けると、次のテンプレートが提示されます。
OVER (PARTITION BY expr1 ORDER BY expr2)
2つの式(expr1とexpr2)を使用した複雑な関数の定義もできますが、通常は、ORDER BY式のみを使用して、次の例のような単純な関数を定義できます。
RANK()OVER(ORDER BY 'Sales')
この例では、売上高(「Sales」アイテムで定義済)をランク付けしています。
注意: デフォルトでは、結果データは昇順(ASC)でソートされ、NULL値が先頭に(NULLS FIRST)なります。
分析関数テンプレートで使用する式の詳細は、「分析関数テンプレートの詳細」を参照してください。
この例では、一連の売上高にランクを付けています。
この例では、一連の売上高にランクを割り当てて、地域別の売上高の上位3都市を表示します。
ワークブックのオプション | 詳細 |
---|---|
アイテムの選択 | Video Analysis Information: Year(Calendar Year)、Region、City、Sales SUM |
ソート順序 | Year、Region |
条件 | Department = Video Sale OR Department = Video Rental
Year = 2000 Rank Top <= 3 |
計算名 | Rank Top |
計算 | RANK() OVER(PARTITION BY Year, Region ORDER BY Sales SUM DESC) |
ヒント | リストをフィルタ処理して、上位1、2または3位の都市をすばやく表示するには、「Rank Top」アイテムをページ軸にピボットします(「テーブル上のデータのピボット」も参照)。 |
表示されるデータ |
この例では、一連の売上高にランクを割り当てて、地域別の上位3都市および下位3都市を表示します。
ワークブックのオプション | 詳細 |
---|---|
アイテムの選択 | Video Analysis Information: Year(Calendar Year)、Region、City、Sales SUM |
ソート順序 | Year、Region |
条件 | Department = Video Sale OR Department = Video Rental
Year = 2000 Rank Top <= 3 OR Rank Bottom <= 3 |
計算名 | Rank Top |
計算 | RANK() OVER(PARTITION BY Year, Region ORDER BY Sales SUM DESC) |
その他の必須の計算 | Rank Bottom = RANK() OVER(PARTITION BY Year, Region ORDER BY Sales SUM ASC) |
注意 | この分析には次の3つの手順が必要です。
1: 「City」の「Sales SUM」に、「Rank Top」として降順のランクを割り当てます。 2: 「City」の「Sales SUM」に、「Rank Bottom」として昇順のランクを割り当てます。 3: 「Rank Top」のみを表示し、条件を使用してデータにフィルタを適用し、上位3つと下位3つの「Brand」のみを戻します。 次の例に示す「Central」地域では、上位3都市にランク1、2および3が割り当てられ、下位3都市にランク5、6および7が割り当てられています。「East」地域では、上位3都市にランク1、2および3が割り当てられ、下位3都市にランク6、7および8が割り当てられています。 |
表示されるデータ |
バンディングは、ランキングの一種であり、パーティション内の値リストを、バンド(またはバケット)と呼ばれる指定数のグループに分割し、それぞれの値をバンドに割り当てます。
一般的なバンディングには、次の2つのタイプがあります。
値によるバンディング: 値が、その値自体に従ってグループに分割されます(等幅バンドとも呼ばれます)。
ここでは、通常は関数によって、最大値から最小値が減算され、その結果が必要なバンド数で除算されます。この値によって、各バンドの範囲が定義されます。
その後、値は、該当する範囲に応じて各バンドに割り当てられます。したがって、各バンドに含まれる値の数は、それぞれ異なる場合があります。たとえば、100個の値を4つの等幅バンドに分割した場合、各バンドに含まれる値の数は異なる可能性があります。
値に基づいた等幅バンドを生成するには、GREATEST関数またはCASE関数を使用します。
ランクに基づくバンディング: 値が、値自体のランクに従ってグループに分割されます(等高バンドとも呼ばれます)。
ここでは、関数によって区分内の値の数がバンド数で除算され、各バンドに入る値の数が算出されます。
その後、各バンドには等しい数の値が配分されます。たとえば、100個の値を4つの等高バンドに分割すると、各バンドに含まれる値の数は25になります。
ランクに基づいた等高バンドを生成するには、NTILE関数を使用します。
この例では、売上高を、その売上高の値に従ってバンドに分割します(等幅バンドとも呼ばれます)。
ワークブックのオプション | 詳細 |
---|---|
アイテムの選択 | Video Analysis Information: Year(Calendar Year)、Region、City、Sales SUM |
ソート順序 | Year、Region |
条件 | Department = Video Sale OR Department = Video Rental
Year = 2000、Region = Central |
計算名 | Sales Bands |
計算 | GREATEST(1,4-FLOOR((Sales SUM-Min Sales for Region)/GREATEST(1,FLOOR((Max Sales for Region-Min Sales for Region+1)/4)))) |
その他の必須の計算 | Max Sales for Region = MAX(Sales SUM) OVER(PARTITION BY Region,Year)
Min Sales for Region = MIN(Sales SUM) OVER(PARTITION BY Region,Year) |
注意 | 「Central Region」と「Year 2000」を例として使用するこの関数は、最大値(45,758)から最小値(7,749)を減算し、さらに4で除算して((45,758-7,749)/4)、値9,502.25を持つ4つの等しいバンドを算出しています。これによって、4つのバンドの範囲は次のようになります。
それぞれの値は、「Sales SUM」の値が該当する範囲に従って、4つのバンドのいずれかに割り当てられます。 |
表示されるデータ |
この例では、「等幅バンドの生成(1)」の例と同じ結果が作成されますが、GREATEST関数ではなくCASE文を使用します。また、この例では、CASE関数を使用し、売上高をその値に従って各バンドに分割します(「等幅バンドの生成(1)」も参照)。
ワークブックのオプション | 詳細 |
---|---|
アイテムの選択 | Video Analysis Information: Year(Calendar Year)、Region、City、Sales SUM |
ソート順序 | Year、Region |
条件 | Department = Video Sale OR Department = Video Rental
Year = 2000、Region = Central |
計算名 | Sales Bands 2 |
計算 | CASE WHEN Sales SUM < Q1 THEN 4 WHEN Sales SUM < Q2 THEN 3 WHEN Sales SUM < Q3 THEN 2 WHEN Sales SUM >= Q3 THEN 1 END |
その他の必須の計算 | MAX Sales = MAX(Sales SUM) OVER(PARTITION BY Year)
MIN Sales = MIN(Sales SUM) OVER(PARTITION BY Year) Range = (MAX Sales - MIN Sales)/4 Q1 = MIN Sales + Range Q2 = MIN Sales + (Range*2) Q3 = MAX Sales ? Range |
注意 | この関数は、CASE関数の形式で一連のIF文を使用し、売上高をバンドに割り当てます(次の「バンドの範囲」を参照)。 |
表示されるデータ |
ワークブックのオプション | 詳細 |
---|---|
アイテムの選択 | Video Analysis Information: Year(Calendar Year)、Region、City、Sales SUM |
ソート順序 | Year、Region |
条件 | Department = Video Sale OR Department = Video Rental
Year = 2000、Region = Central |
計算名 | Sales Bands 3 |
計算 | NTILE(2) OVER(PARTITION BY Year, Region ORDER BY Sales SUM DESC) |
注意 | 「Central Region」と「Year 2000」を例として使用するこの関数は、値の数(6)を求めて、その数を2で除算し、各バンドに値を3個ずつ割り当てています。次に「Sales SUM」順の値リストを作成し、値1、2および3をバンド1に割り当て、値4、5および6をバンド2に割り当てます。 |
表示されるデータ |
ウィンドウ集計関数は、他の行の値を使用して、累積集計、移動集計およびセンター集計などを行うため使用します。
一般的なウィンドウ集計には、次の2つのタイプがあります。
論理オフセットを使用したウィンドウ集計: このオフセットは、既存の値に対する相対値(ある日付の値より前の3か月間など)に基づいています。
たとえば、月次売上高のリストがある場合、論理ウィンドウ集計では、前の3か月(現在の月を含む)の移動平均を計算できます。
この平均を計算する場合、リストにない月はNULL値とみなされます。この例の11月の3か月移動平均の計算では、リストにない9月と10月は、NULL値とみなされます。
物理オフセットを使用したウィンドウ集計: このオフセットは、既存の値から指定した行数の値(現行のアイテムから3行分など)に基づいています。
たとえば、月次売上高のリストがある場合、物理ウィンドウ集計では、リストにない月は無視して前の3行の移動平均を計算できます。
この平均を計算する場合、リストにない月は無視されます。この例の11月の3か月移動平均の計算では、6月、7月および11月が使用されます。
この例では、論理ウィンドウ集計を使用して、3か月の売上高の移動平均を計算します。
ワークブックのオプション | 詳細 |
---|---|
アイテムの選択 | Video Analysis Information: Year(Calendar Year)、Region、City、Sales SUM |
ソート順序 | Year、Region |
条件 | Department = Video Sale OR Department = Video Rental
Year = 2000、Region = Central |
計算名 | Sales Bands 3 |
計算 | NTILE(2) OVER(PARTITION BY Year, Region ORDER BY Sales SUM DESC) |
注意 | 「Central Region」と「Year 2000」を例として使用するこの関数は、値の数(6)を求めて、その数を2で除算し、各バンドに値を3個ずつ割り当てています。次に「Sales SUM」順の値リストを作成し、値1、2および3をバンド1に割り当て、値4、5および6をバンド2に割り当てます。 |
表示されるデータ |
この例では、年度別および地域別の年間総売上高を計算しています。
ワークブックのオプション | 詳細 |
---|---|
アイテムの選択 | Video Analysis Information: Year(Calendar Year)、Region、City、Sales SUM |
ソート順序 | Year、Region |
条件 | Department = Video Sale OR Department = Video Rental
Year = 2000 |
計算名 | Annual Sales by Region |
計算 | SUM(Sales SUM) OVER(PARTITION BY Year, Region ORDER BY Year, Region) |
表示されるデータ |
この例では、都市ごとに年度別・地域別の年間売上比率を計算しています。
ワークブックのオプション | 詳細 |
---|---|
アイテムの選択 | Video Analysis Information: Year(Calendar Year)、Region、City、Sales SUM |
ソート順序 | Year、Region、% of Annual Sales |
条件 | Department = Video Sale OR Department = Video Rental
Year = 2000 |
計算名 | % of Annual Sales |
計算 | Sales SUM*100/Annual Sales by Region |
その他の必須の計算 | Annual Sales by Region= SUM(Sales SUM) OVER(PARTITION BY Year, Region ORDER BY Year, Region) |
表示されるデータ |
LAG関数とLEAD関数は、通常、異なる期間中の値を比較するために使用します。たとえば、2000年の売上高と2001年の売上高を比較します。
LAG: テーブルの複数行に内部結合なしで同時にアクセスできます。
LEAD: 現在の位置からのオフセットを指定して別の行にアクセスできます。
この例では、期間別の売上高を比較します。
ワークブックのオプション | 詳細 |
---|---|
アイテムの選択 | Video Analysis Information: Year (Calendar Year), Sales SUM |
ソート順序 | Year |
条件 | Department = Video Sale OR Department = Video Rental
Region = Central |
計算名 | Previous Year |
計算 | LAG(Sales SUM,1) OVER(ORDER BY Year) |
注意 | 1998年には比較する値がないため、1998年に対する「Previous Year」の値は空白です。 |
表示されるデータ |
この例では、A.5.8.2の例からの売上高比較を使用して、期間別の売上成長率を計算します。
ワークブックのオプション | 詳細 |
---|---|
アイテムの選択 | Video Analysis Information: Year (Calendar Year), Sales SUM |
ソート順序 | Year |
条件 | Department = Video Sale OR Department = Video Rental
Region = Central |
計算名 | Growth |
計算 | (Sales SUM-Previous Year)*100/Previous Year |
その他の必須の計算 | Previous Year = LAG(Sales SUM,1) OVER(ORDER BY Year) |
注意 | 1998年には比較する値がないため、1998年に対する「Previous Year」の値は空白です。 |
表示されるデータ |
この例では、A.5.8.2およびA.5.8.3の例で使用した売上高比較を使用して、年度別の売上成長率ランクを割り当てます。
ワークブックのオプション | 詳細 |
---|---|
アイテムの選択 | Video Analysis Information: Year (Calendar Year), Sales SUM |
ソート順序 | Year |
条件 | Department = Video Sale OR Department = Video Rental
Region = Central |
計算名 | Rank Growth |
計算 | RANK() OVER(ORDER BY Growth DESC) |
その他の必須の計算 | Previous Year = LAG(Sales SUM,1) OVER(PARTITION BY 'Year' ORDER BY 'Year' )
Growth = (Sales SUM-Previous Year)*100/Previous Year |
注意 | 1998年には比較する値がないため、1998年に対する「Previous Year」と「Growth」の値は空白になり、「Rank Growth」の計算結果は1となります。 |
表示されるデータ |
統計関数は、共分散、相関関係および線形回帰の統計を計算するために使用します。各関数は、順序付けのないセットとして動作します。これらの関数は、ウィンドウ集計関数および集計レポート関数としても使用できます。
この例では、「Month」別の「Profit SUM」を「Sales SUM」の一次関数として表す微分最小2乗法で、回帰直線を計算しています。使用される関数は次のとおりです。
SLOPE: 回帰直線の解傾き
REGR_AVGY: 平均賞与
ワークブックのオプション | 詳細 |
---|---|
アイテムの選択 | Video Analysis Information: Year (Calendar Year), Month (Calendar Month), Sales SUM, Profit SUM |
ソート順序 | Year |
条件 | Department = Video Sale OR Department = Video Rental
Year = 2000 |
計算 | Slope = REGR_SLOPE(Profit SUM,Sales SUM) OVER(ORDER BY Profit SUM)
Intercept = REGR_INTERCEPT(Profit SUM,Sales SUM) OVER(ORDER BY Profit SUM) Coefficient = REGR_R2(Profit SUM,Sales SUM) OVER(ORDER BY Profit SUM) Count = REGR_COUNT(Profit SUM,Sales SUM) OVER(ORDER BY Profit SUM) Average = REGR_AVGX(Profit SUM,Sales SUM) OVER(ORDER BY Profit SUM) Average 2 = REGR_AVGY(Profit SUM,Sales SUM) OVER(ORDER BY Profit SUM) |
表示されるデータ |
新規の分析関数を「計算」ボックスに貼り付けると、次の汎用テンプレートが提示されます。
OVER (PARTITION BY expr1 ORDER BY expr2)
これらの式の使用方法は次のとおりです。
OVER: 他のクエリー句(FROM、WHERE、HAVINGなど)が適用された後、クエリー結果セットを処理することを示します。
PARTITION BY: クエリー結果セットを分割またはグループ化します。例: PARTITION BY 'Region'。
ORDER BY: クエリー結果セットを論理的に順序付ける方法を指定します。例: ORDER BY 'Sales SUM'。
Oracleの式の詳細は、「詳細情報の参照先」を参照してください。
条件に分析関数を使用する場合、分析関数以外との組合せ方法は、クエリーから戻されるデータに影響を与えます。次の順序付け規則が適用されます。
この例では、最初に「Region = 'Central'」の条件が適用された後で「Rank」が計算され、次に「'Rank <= 3'」の条件(分析関数を含む条件)が適用されます。
非分析関数と分析関数の組合せが含まれている条件の場合、分析関数は非分析関数より前に適用されます。
この例では、最初に「Rank」が評価された後で、「Rank <= 3」の条件が適用され、次に「Region = 'Central'」の条件が適用されます。
次の2つの例を使用して、関数の処理順序がクエリー結果のデータに与える影響を示します。
前述の最初の使用例では、2つの単純な条件を適用します。条件は、「Region = 'Central'」および「Rank <= 3」(「Rank」は分析関数)です。最初に「Region = 'Central'」の条件が適用されてから、「Rank <= 3」の条件が適用されます。したがって、「Central」地域の売上高のうち、ランクが3以下の値のみが結果セットに格納されます。
前述の2番目の使用例では、高度な条件を1つ適用します。条件は、「Region = 'Central' AND Rank <= 3」(「Rank」は分析関数)です。最初に「Rank <= 3」の条件が適用されてから、「Region = 'Central'」の条件が適用されます。したがって、Central地域の売上高のうち、全体のランクが3以下の値のみが結果セットに格納されます。