A Discoverer計算の例

この付録の項目は次のとおりです。

• 「Oracleの分析関数に関する詳細情報の参照先」

• 「この章で紹介する例の概要」

• 「計算の作成方法」

• 「パラメータを使用した計算への動的入力の提供」

• 「単純な計算の例」

• 「Oracleの分析関数の例」

• 「行ベースおよび時間ベースの間隔の使用例」

A.1 Oracleの分析関数に関する詳細情報の参照先

Oracleの関数全般の詳細は、次のドキュメントを参照してください。

• 『Oracle Database SQLリファレンス』

• 『Oracle Databaseデータ・ウェアハウス・ガイド』

A.2 この章で紹介する例の概要

以降の項で紹介する例では、Discovererに付属のVideo Stores Tutorialのチュートリアルを使用しています。Video Stores Tutorialのチュートリアルがインストールされていない場合は、Discovererマネージャに問い合せてください。

注意: 使用しているデータベース・バージョンでIF文またはCASE文がサポートされていない場合、かわりにDECODEを使用できます。あるいは、データベースでPL/SQLファンクションを作成できます。

A.3 計算の作成方法

計算の作成方法の詳細は、「計算の作成方法」を参照してください。

A.4 パラメータを使用した計算への動的入力の提供

通常は、パラメータを使用して計算への動的入力を提供します。これにより、他の値を任意に入力して、より効率的に分析できます。つまり、ワークシートをリフレッシュし、「パラメータ値の編集」ダイアログに新規の値を入力するのみで、計算に異なる値を提供できます。

計算に使用されるパラメータ値には、接頭辞コロン(:)が付いています。たとえば、Hypothetical Valueというパラメータは、計算では次のように参照されます。

RANK(:Hypothetical Value) WITHIN GROUP(ORDER BY Profit DESC NULLS FIRST)

計算にパラメータ値を使用する方法の詳細は、「パラメータを使用した動的なユーザー入力の収集」を参照してください。

A.5 単純な計算の例

この項の例は、Discovererで基本的な関数を使用してデータを操作し、分析する方法を示しています。

例:

• 「例: クエリー結果件数の計算」

• 「例: 売上の25%増の計算」

• 「例: テキストの大文字への変換」

注意:

• この項の例では、一般に使用されるコマンドを選択して使用しています。全コマンドと詳細な構文のリストは、Oracle SQL ガイドとウェアハウス・ガイドを参照してください。

• 計算の作成方法の詳細は、「計算の使用」を参照してください。

例: クエリー結果件数の計算

この例では、Oracle関数ROWCOUNT()を使用して、クエリー結果件数を計算します。

ワークシートのオプション	設定
アイテム	ビデオ分析情報: Year、Region、City、Sales SUM
ソート順序	Year、Region、City
条件	Department = Video Sale OR Department = Video Rental Year = 2000 Region = Central
計算名	Rows returned
計算	ROWCOUNT

図A-1 Rows returned計算を含むワークシート

ワークシートは、2000年のCentral地域の各都市に関するクエリー結果件数を示しています。

注意:

• ROWCOUNTは、NULL値を数えません。NULL値を含めてクエリー結果件数を計算する手順は、次のとおりです。

  1. 最初に、計算式 = '1'を指定してテンポラリ・アイテム「One record」を作成します。

  2. 計算「Rows returned」を作成し、計算式 = SUM(Video Sales Analysis.One record)を使用してOne recordの件数を数えます。

例: 売上の25%増の計算

この例では、売上高の25%増を計算します。

ワークシートのオプション	設定
アイテム	ビデオ分析情報: Year、Region、City、Sales SUM
ソート順序	Year、Region、City
条件	Department = Video Sale OR Department = Video Rental Year = 2000 Region = Central
計算名	25% Increase
計算	Sales SUM * 1.25

図A-2 25% Increase計算を含むワークシート

ワークシートは、Central地域の各都市について売上の25%増を示しています。

例: テキストの大文字への変換

Discovererでは、幅広い算術関数に加えて、数値やテキストをフォーマットする関数も使用できます。この例では、計算を使用してCityのテキスト・データを大文字にフォーマットしなおします。

ワークシートのオプション	設定
アイテム	ビデオ分析情報: Year、Region、City、Sales SUM
ソート順序	Year、Region
条件	Department = Video Sale OR Department = Video Rental Year = 2000 Region = Central
計算名	City(Upper Case)
計算	UPPER(City)

図A-3 City(Upper Case)計算を含むワークシート

上の図は、Central地域について大文字に変換された都市名を含むワークシートを示しています。

A.6 Oracleの分析関数の例

この項の例は、Oracleの分析関数をDiscovererで使用して、詳細なデータ分析を実行する方法を示しています。

この項の項目は次のとおりです。

• 「分析関数のカテゴリ」

• 「分析関数およびデータのドリルインとドリルアウト」

• 「分析関数の作成」

• 「ランク付け関数の例」

• 「バンディング関数の例」

• 「ウィンドウ集計関数の例」

• 「レポート集計関数の例」

• 「LAG/LEAD関数の例」

• 「統計関数の例」

• 「分析関数式の詳細」

• 「分析関数と処理順序」

• 「順序付けの例」

• 「逆パーセンタイルの例」

• 「PERCENTILE_CONTとPERCENTILE_DISCの相違点」

• 「仮説ランクと分散の例」

• 「バンディングの例」

• 「FIRST/LAST集計の例」

注意:

• この項の例では、一般に使用されるコマンドを選択して使用しています。全コマンドと詳細な構文のリストは、『Oracle Database SQLリファレンス』および『Oracle Databaseデータ・ウェアハウス・ガイド』を参照してください。

• 計算の作成方法の詳細は、「計算の使用」を参照してください。

ヒント: 分析関数テンプレートを使用しても、最も一般的な分析関数をベースとする計算を迅速かつ簡単に作成できます(詳細は、「分析関数テンプレートを使用して計算を作成する方法」を参照)。

A.6.1 分析関数のカテゴリ

分析関数は、次のカテゴリに分類されます。

• ランク付け: 「各地域の売上で上位10人および下位10人の営業担当者は?」のようなビジネス上の質問に答えることができます。

• バンディング: 「売上の25%を占めている商品は?」のようなビジネス上の質問に答えることができます。

• ウィンドウ集計: 「13週間における株価の移動平均は?」または「各地域の売上累積合計は?」のようなビジネス上の質問に答えることができます。

• レポート集計: クエリーが処理された後、結果の行数、行セット内の列合計などの値を集計します。「各製品の売上をその製品グループの売上に対するパーセントで示すと?」のような質問に答えることができます。

• LAG/LEAD: 「前年度の同時期の売上高は?」のようなビジネス上の質問に答えることができます。

• 統計: ビジネス・インテリジェンスのOLAP/表計算アプリケーションを使用して統計分析を行います。(例: 共分散や線形回帰の分析関数)。

A.6.2 分析関数およびデータのドリルインとドリルアウト

分析関数を使用する場合、分析関数には結果セットのドリル、ピボットまたはソートでは変化しない厳密な定義があることに注意してください。たとえば、RANK関数を使用して、四半期ごとに区切った売上高にランクを割り当てた場合、月レベルでドリルダウンしても、ランクは四半期レベルでのみ適用されます。

A.6.3 分析関数の作成

Discoverer Plus Relationalには、最も一般的な分析関数について、使いやすいテンプレートが用意されています(詳細は、「Discovererで使用可能な分析関数テンプレート」を参照)。

テンプレートを持たない分析関数を作成する場合、「新規計算」ダイアログの「計算」フィールドに入力するか、直接貼り付けることができます。または、関数リストから選択することもできます。

「新規計算」ダイアログの関数リストから分析関数を選択した場合、空の分析関数が「計算」フィールドにコピーされます。空の分析関数には、入力が必要な不足値を示すexprプロンプトが含まれます。exprプロンプトは、多くの用途に合わせた設計になっており、ガイドとしてのみ使用します。つまり、必ずしもすべてのexprプロンプトを使用して、分析関数を定義する必要はありません。

たとえば、「新規計算」ダイアログでRANK分析関数を選択すると、「計算」フィールドに次のテキストが入力されます。

RANK() OVER (PARTITION BY expr1 ORDER BY expr2)

2つの式(expr1とexpr2)を使用した複雑な関数の定義もできますが、通常は、ORDER BY式のみを使用して、次の例のような単純な関数を定義できます。

RANK() OVER(ORDER BY 'Sales')

この例では、売上高(「Sales」アイテムで定義済)をランク付けしています。

注意:

• デフォルトでは、結果データは昇順(ASC)で、NULL値が最初に(NULLS FIRST)ソートされます。

• 分析関数の入力の詳細は、「分析関数式の詳細」を参照してください。

A.6.4 ランク付け関数の例

この項では、ランク付け関数の例について説明します。

A.6.4.1 ランク付けについて

ランク付け関数は、あるアイテムのランク・リストでの位置付けを、順序リストにあるその他のアイテムと比較して計算します。

例:

• 「例: 売上高へのランクの割当て」

• 「例: 地域内の売上高へのランクの割当て」

• 「例: 売上の多い上位3都市を地域別に表示」

• 「例: 売上の多い上位3都市と売上の少ない下位3都市を地域別に表示」

ヒント: 分析関数テンプレートを使用しても、ランク付け関数をベースとする計算を迅速かつ簡単に作成できます(詳細は、「分析関数テンプレートを使用して計算を作成する方法」を参照)。

A.6.4.2例: 売上高へのランクの割当て

この例では、一連の売上高のランク・リストでの位置付けを計算します。

ワークシートのオプション	設定
アイテム	ビデオ分析情報: Year、Region、City、Sales SUM
ソート順序	Rank Sales、Year、Region
条件	Department = Video Sale OR Department = Video Rental Year = 2000
計算名	Rank Sales
計算	RANK() OVER(ORDER BY Sales SUM DESC)

図A-4 Rank Sales計算を含むワークシート

ワークシートは、2000年の各都市の売上高について、ランク・リストにおける位置付けを示しています。

注意:

• デフォルトでは、ランク付けの結果データは昇順(ASC)で、NULL値が最初に(NULLS FIRST)ソートされます。DESCパラメータを追加すると、結果データは降順でソートされ、最高額にランク1が割り当てられます。

A.6.4.3 例: 地域内の売上高へのランクの割当て

この例では、一連の売上高について地域ごとにランク・リストでの位置付けを計算します。

ワークシートのオプション	設定
アイテム	ビデオ分析情報: Year、Region、City、Sales SUM
ソート順序	Year、Region
条件	Department = Video Sale OR Department = Video Rental Year = 2000
計算名	Rank sales within Region
計算	RANK() OVER(PARTITION BY Year, Region ORDER BY Sales SUM DESC)

図A-5 Rank sales within Region計算を含むワークシート

ワークシートは、各都市の年間売上高について、ランク・リストにおける地域別の位置付けを示しています。

注意:

• デフォルトでは、ランク付けの結果データは昇順(ASC)で、NULL値が最初に(NULLS FIRST)ソートされます。DESCパラメータを追加すると、結果データは降順でソートされ、最高額にランク1が割り当てられます。

A.6.4.4 例: 売上の多い上位3都市を地域別に表示

この例では、一連の売上高のランク・リストでの位置付けを計算し、上位3都市を表示します。

ワークシートのオプション	設定
アイテム	ビデオ分析情報: 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)

図A-6 条件に使用するRank Top計算を含むワークシート

ワークシートは、各地域の上位3都市の年間売上高のランク・リストを示しています。

注意:

• デフォルトでは、ランク付けの結果データは昇順(ASC)で、NULL値が最初に(NULLS FIRST)ソートされます。DESCパラメータを追加すると、結果データは降順でソートされ、最高額にランク1が割り当てられます。

• ヒント: リストを第1位から第3位にランクされた都市にすばやくフィルタ処理するには、「Rank Top」アイテムをページ軸にピボットします。

A.6.4.5 例: 売上の多い上位3都市と売上の少ない下位3都市を地域別に表示

この例では、一連の売上高のランク・リストでの位置付けを計算し、地域別の上位3都市および下位3都市を表示します。

ワークシートのオプション	設定
アイテム	ビデオ分析情報: Year、Region、City、Sales SUM
ソート順序	Year、Region、Rank Top
条件	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)

図A-7 条件に使用するRank Top計算を含むワークシート

ワークシートは、各地域の上位3都市と下位3都市の年間売上高のランク・リストを示しています。

注意:

• この分析には次の3つの手順が必要です。

  1. 「Sales SUM」について「City」に降順でランクを割り当て、「Rank Top」の内容とします。

  2. 「Sales SUM」について「City」に昇順でランクを割り当て、「Rank Bottom」の内容とします。

  3. 「Rank Top」のみを表示し、条件を使用してデータにフィルタをかけ、上位3および下位3にランクされた都市のみを戻します。

• この例の場合、Central地域では、上位3都市は1から3にランク付けされ、下位3都市は5から7にランク付けされています。また、East地域では、上位3都市は1から3にランク付けされ、下位3都市は6から8にランク付けされています。

A.6.5 バンディング関数の例

この項では、バンディング関数の例について説明します。

A.6.5.1 バンディングについて

バンディングはランク付けの一種です。バンディングでは、パーティション内の値リストをバンドと呼ばれる(バケットとも呼ばれる)指定した数のグループに分割し、それぞれの値をバンドに割り当てます。

例:

• 「例: 値に基づくバンディング(1)」

• 「例: 値に基づくバンディング(2)」

• 「例: ランクに基づくバンディング」

ヒント: 分析関数テンプレートを使用しても、バンディング関数をベースとする計算を迅速かつ簡単に作成できます(詳細は、「分析関数テンプレートを使用して計算を作成する方法」を参照)。

一般的なバンディングには、次の2つのタイプがあります。

• 値に基づくバンディング: 値に従って値のグループ化を行います(等幅バンドとも呼ばれます)。このタイプの分析は、頻度分布とも呼ばれます。

  この場合、通常は最大値から最小値を減算した結果の値が、指定したバンド数で除算されます。この値によって、各バンドの範囲が定義されます。

  各値は、その値がどの範囲に該当するかに従って、バンドに割り当てられます。したがって、各バンドに含まれる値の数はそれぞれ異なります。たとえば、100個の値を4つの等幅バンドに分割する場合、各バンドに含まれる値の数はそれぞれ異なります。

図A-8 値に基づくバンディング

値に基づいた等幅バンドを生成するには、GREATEST関数またはCASE関数を使用します。

注意: 使用しているデータベース・バージョンでIF文またはCASE文がサポートされていない場合、かわりにDECODEを使用できます。あるいは、データベースでPL/SQLファンクションを作成できます。

ヒント: WIDTH_BUCKET関数を使用して等幅バンドを生成することもできます(「例: WIDTH_BUCKETを使用した等幅バンドの生成」を参照)。

• ランクに基づくバンディング: ランクに従って値のグループ化を行います(等高バンドとも呼ばれます)。このタイプの分析は、パーセンタイル分析とも呼ばれます(たとえば、4つのバンドの場合は四分位数)。

  この場合、パーティション内の値の数をバンド数で除算した値が、各バンドに含まれる値の数になります。したがって、値は各バンドに等分に配置されます。たとえば、100個の値を4つの等高バンドに分割する場合、各バンドに含まれる値は25個です。

図A-9 ランクに基づくバンディング

ランクに基づいた等高バンドを生成するには、NTILE関数を使用します。

A.6.5.2 例: 値に基づくバンディング(1)

この例では、売上高を値に基づいてバンドに分割します(頻度分析などに使用)。詳細は、「例: 値に基づくバンディング(2)」を参照してください。

ワークシートのオプション	設定
アイテム	ビデオ分析情報: 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)

図A-10 Sales Bands計算を含むワークシート

ワークシートは、Central地域の各都市の年間売上高に関する等幅バンドを示しています。

注意:

• Central地域と2000年を例に説明すると、この関数は、最大値(45,758)から最小値(7,749)を減算してから4で除算し((45,758-7,749)/4)、9,502.25の値を持つ4つの等しいバンドを生成します。この結果、4つのバンドに次の範囲が与えられます。

  • バンド1: 36,255.75から45,758

  • バンド2: 26,753.5から36,255.75

  • バンド3: 17,251.25から26,753.5

  • バンド4: 7,749から17,251.25

• 各値は、その「Sales SUM」の値が含まれる範囲に従って、4つのバンドのいずれかに配置されます。

• FLOOR関数は、n以下で最大の整数を戻します。たとえば、Dallasの場合、式FLOOR(Sales SUM-Min Sales for Region)は、7,749から7,749を減算した最小の整数値、つまり0(ゼロ)を戻します。この関数をGREATEST関数(前述の計算を参照)と併用すると、式GREATEST(1,4-FLOOR((Sales SUM-Min Sales for Region)は、1、または7,749から7,749を減算した最小の整数値を4から減算した値(4 - 0 = 4)のうち、大きい方の値を戻します。つまり、この式は値4を戻します。

• WIDTH_BUCKET関数を使用して等幅バンドを生成することもできます(「例: WIDTH_BUCKETを使用した等幅バンドの生成」を参照)。

A.6.5.3 例: 値に基づくバンディング(2)

この例では、売上値をそれぞれの値に従ってバンドに分割します。ここでは、「例: 値に基づくバンディング(1)」の例と同じ結果を作成しますが、GREATEST関数ではなくCASE文を使用します(頻度分析などに使用)。

ワークシートのオプション	設定
アイテム	ビデオ分析情報: 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

図A-11 Sales Bands 2計算を含むワークシート

ワークシートは、Central地域の各都市の年間売上高に関する等幅バンドを示しています。

注意:

• この関数は、CASE関数の形式で一連のIF文を使用し、売上高をバンドに割り当てます(次の「Band範囲」を参照)。

• WIDTH_BUCKET関数を使用して等幅バンドを生成することもできます(「例: WIDTH_BUCKETを使用した等幅バンドの生成」を参照)。

A.6.5.4 例: ランクに基づくバンディング

この例では、売上高をランクに基づいて2つのバンドに分割します(パーセンタイル分析などに使用)。

ワークシートのオプション	設定
アイテム	ビデオ分析情報: 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)

図A-12 Sales Bands 3計算を含むワークシート

ワークシートは、Central地域の各都市の年間売上高に関する等高バンドを示しています。

注意:

• Central地域と2000年を例に説明すると、この関数は、値の数(6個)を2で除算し、それぞれ3つの値を持つバンドを生成します。次に、値リストを「Sales SUM」でソートし、値1、2および3をバンド1に配置し、値4、5および6をバンド2に配置します。

A.6.6 ウィンドウ集計関数の例

この項では、ウィンドウ集計関数の例について説明します。

A.6.6.1 ウィンドウ集計について

ウィンドウ集計関数は、他の行の値を使用して、累積集計、移動集計およびセンター集計などを行うため使用します。

注意: 行ベースおよび時間ベースの間隔の使用に関する詳細は、「行ベースおよび時間ベースの間隔の使用例」を参照してください。

例:

• 「例: 売上の3か月移動平均の計算」

• 「例: 売上の累積値の表示」

• 「例: ウィンドウ集計を使用した異なる期間の売上高の比較」

ヒント: 分析関数テンプレートを使用しても、ウィンドウ集計関数をベースとする計算を迅速かつ簡単に作成できます(詳細は、「分析関数テンプレートを使用して計算を作成する方法」を参照)。

一般的なウィンドウ集計には、次の2つのタイプがあります。

• 時間ベースの間隔を使用したウィンドウ集計: この場合、時間ベースの間隔は、既存の値に対する相対値(ある日付の値より前の3か月間など)に基づいています。

  注意: 時間ベースの間隔は、論理オフセットとも呼ばれます。

  時間ベースの間隔は、データの行が欠けていない場合に役立ちます。たとえば、1週間(7日間)の売上高について不足している行があるケースで、週の平均を計算する場合などです。

  たとえば、月次売上高のリストがある場合、論理ウィンドウ集計では、前の3か月(現在の月を含めた)の移動平均を計算できます。この平均を計算する場合、リストにない月はNULL値と見なされます。次の例の「November」の3か月移動平均の計算では、リストにない「September」と「October」の月は、NULL値と見なされます。

図A-13 時間ベースの間隔の使用

• 行ベースの間隔を使用したウィンドウ集計: この場合、行ベースの間隔は、既存の値から指定した行数の値(現行のアイテムから3行分など)に基づいています。

  注意: 行ベースの間隔は、物理オフセットとも呼ばれます。

  行ベースの間隔は、データの行が不足していない場合に役立ちます。たとえば、1週間(7日間)の売上高について7行ある場合、6行前から現在の行まで(現在の行を含む)の週平均を計算できます。

  たとえば、月次売上高のリストがある場合、物理ウィンドウ集計では、前の3行の移動平均を計算できます。この平均を計算する場合、リストにない月は無視されます。次の例の「November」の3か月移動平均の計算では、「June」、「July」および「November」が使用されます。

図A-14 行ベースの間隔の使用

注意: 行ベースおよび時間ベースの間隔の使用に関する詳細は、「行ベースおよび時間ベースの間隔の使用例」を参照してください。

A.6.6.2例: 売上の3か月移動平均の計算

この例では、時間ベースの間隔を使用して、売上の3か月移動平均を計算します。

注意: 移動平均はローリング平均と呼ばれることもあります。

ワークシートのオプション	設定
アイテム	ビデオ分析情報: Year、Month、Sales SUM
ソート順序	Year、Month
条件	Department = Video Sale OR Department = Video Rental Year = 2000 Region = Central
計算名	Moving Average
計算	AVG(Sales SUM) OVER(ORDER BY "Calendar Month" RANGE INTERVAL '2' MONTH PRECEDING)

図A-15 Moving Average計算を含むワークシート

ワークシートは、2000年の各月の売上高の3か月移動平均を示しています。

注意:

• 3か月のウィンドウ集計を行う場合でも、RANGE INTERVALは'3'ではなく'2'であることに注意してください。ウィンドウ集計の式では、現在行が暗黙的に含まれるためです。したがって、この例では、INTERVAL'2'に現在行を加えた3か月が計算対象となります(2 + 現在行 = 3)。

A.6.6.3 例: 売上の累積値の表示

この例では、行ベースの間隔を使用して、売上の累積値を計算します。

ワークシートのオプション	設定
アイテム	ビデオ分析情報: Year、Region、City、Sales SUM
ソート順序	Year、Region
条件	Department = Video Sale OR Department = Video Rental Year = 2000 Region = Central
計算名	Cumulative Total
計算	SUM(Sales SUM) OVER(PARTITION BY "Calendar Year", Region ORDER BY Sales SUM ROWS UNBOUNDED PRECEDING)

図A-16 Cumulative Total計算を含むワークシート

ワークシートは、Central地域の各都市の売上高の累計を示しています。

A.6.6.4 例: ウィンドウ集計を使用した異なる期間の売上高の比較

この例では、時間ベースの間隔を使用して過去の年度の売上高を計算します。これにより、数年間の売上高を比較したり、過去の年度の売上高を該当年度の支出のような他の値と比較できます。

ワークシートのオプション	設定
アイテム	ビデオ分析情報: Year、Sales SUM
ソート順序	該当なし
条件	該当なし
計算名	Sales Last Year
計算	SUM(Sales SUM) OVER(ORDER BY "Calendar Year" RANGE BETWEEN INTERVAL '1' YEAR PRECEDING AND INTERVAL '1' YEAR PRECEDING)

図A-17 Sales Last Year計算を含むワークシート

このワークシートの各行は、前年度の売上合計を示します。

注意:

• 上の例で、データベースには1997年の情報が含まれていないため、1998年の「Sales Last Year」の値はNULLです。

• また、LAG/LEAD関数を使用して、複数期間にまたがる値を比較することもできます(「LAG/LEAD関数の例」を参照)。

A.6.7 レポート集計関数の例

この項では、レポート集計関数の例について説明します。

A.6.7.1 レポート集計関数について

レポート集計関数は、集計を行うために使用します。

例:

• 「例: 年間売上高の計算」

• 「例: 年間売上の地域別計算」

• 「例: 年間売上のパーセントの地域別計算」

• 「例: 都市の売上を総売上のパーセントとして計算」

ヒント: 分析関数テンプレートを使用しても、レポート集計関数をベースとする計算を迅速かつ簡単に作成できます(詳細は、「分析関数テンプレートを使用して計算を作成する方法」を参照)。

A.6.7.2 例: 年間売上高の計算

この例では、年間売上高を計算します。

ワークシートのオプション	設定
アイテム	ビデオ分析情報: Year、Region、City、Sales SUM
ソート順序	Year、Region
条件	Department = Video Sale OR Department = Video Rental Year = 2000
計算名	Annual Sales
計算	SUM(Sales SUM) OVER()

図A-18 Annual Sales計算を含むワークシート

ワークシートは、2000年の年間売上高の値を示しています。

注意:

• テーブル・ワークシートで、計算にはワークシートの行ごとに戻り値が表示されます。値を1つ戻すには、計算を「ページ・アイテム」領域に移動します(詳細は、「例: 販売トランザクション数が最も多い地域内の最大販売トランザクションの検索」の「ヒント」を参照)。

A.6.7.3 例: 年間売上の地域別計算

この例では、年間の総売上を地域別に計算します。

ワークシートのオプション	設定
アイテム	ビデオ分析情報: 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)

図A-19 Annual Sales by Region計算を含むワークシート

ワークシートは、各都市の年間売上合計を地域別に示しています。

A.6.7.4 例: 年間売上のパーセントの地域別計算

この例では、年間売上のパーセントを各都市が含まれる地域ごとに計算します。

ワークシートのオプション	設定
アイテム	ビデオ分析情報: 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)

図A-20 % of Annual Sales計算を含むワークシート

ワークシートは、売上高が地域別年間売上高に占めるパーセントを示しています。

A.6.7.5 例: 都市の売上を総売上のパーセントとして計算

この例では、都市の売上を総売上のパーセントとして計算します。

ワークシートのオプション	設定
アイテム	ビデオ分析情報: Year、Region、City、Sales SUM
ソート順序	Year、Region、% of Annual Sales
条件	Department = Video Sale OR Department = Video Rental Year = 2000 Region = Central
計算名	% of Total Sales
計算	RATIO_TO_REPORT(Sales SUM) OVER()*100

図A-21 % of Annual Sales計算を含むワークシート

ワークシートは、都市の売上値を総売上に占めるパーセントとして示しています。

注意:

• RATIO_TO_REPORT関数では、ある1つの値の、一連の値の合計に対する比率が計算されます。

A.6.8 LAG/LEAD関数の例

この項では、LAG関数とLEAD関数の例について説明します。

A.6.8.1 LAG/LEAD関数について

LAG関数とLEAD関数は、通常、異なる期間に存在する値を比較するために使用します。たとえば、2000年の売上高と2001年の売上高を比較します。

• LAG: テーブルの複数行に自己結合なしで同時にアクセスできます。

• LEAD: 現在の位置からのオフセットを指定して別の行にアクセスできます。

また、ウィンドウ集計関数を使用して期間中の値を比較することもできます(「例: ウィンドウ集計を使用した異なる期間の売上高の比較」を参照)。

ヒント: 分析関数テンプレートを使用しても、LAG/LEAD関数をベースとする計算を迅速かつ簡単に作成できます(詳細は、「分析関数テンプレートを使用して計算を作成する方法」を参照)。

例:

• 「例: LAG/LEADを使用した異なる期間の売上高の比較」

• 「例: 異なる期間にわたる売上増加の計算」

• 「例: 売上増加のランク付け」

A.6.8.2 例: LAG/LEADを使用した異なる期間の売上高の比較

この例では、月間の売上高を前年同月の売上高と比較します。たとえば、1999年1月の売上高と1998年1月の売上高を比較して調べます。

ワークシートのオプション	設定
アイテム	ビデオ分析情報: Calendar Year、Calendar Quarter、Calendar Month、Sales SUM
ソート順序	Calendar Year、Calendar Quarter、Calendar Month
条件	Department = Video Sale OR Department = Video Rental
計算名	Previous Year
計算	LAG(Sales SUM,1) OVER(PARTITION BY "Calendar Month" ORDER BY "Calendar Year")

図A-22 Calendar Monthで区切ったPrevious Year計算を含むワークシート

ワークシートは、「Sales SUM」金額ごとに前年の売上高を示す「Previous Year」計算が含まれています。たとえば、1999年1月の「Previous Year」値は$50889で、これは1998年1月の「Sales SUM」値です。

注意:

• 1998年には比較する値がないため、「1998」に対する「Previous Year」の値は空白になります。

• LAG(Sales SUM,1)句の値'1'により前年の値が計算されます。たとえば、この値を'2'に変更すると、2年前からの値が計算されます。

• この計算には'PARTITION BY Calendar Month'句が含まれています。このため、Calendar Year (ORDER BY句)とCalendar Month (PARTITION BY句)の組合せごとに値が表示されます。つまり、1999年2月の「Previous Year」値は1998年2月の「Sales SUM」値です。この句を削除すると、前月の値が計算されます(次の例を参照)。つまり、1999年2月の「Previous Year」値は、1999年1月の「Sales SUM」値となります。

図A-23 パーティションを削除したPrevious Year計算を含むワークシート

A.6.8.3 例: 異なる期間にわたる売上増加の計算

この例では、売上高を前年同月の売上高と比較して、前年比の売上成長率を計算します。そのためには、「例: LAG/LEADを使用した異なる期間の売上高の比較」で比較した売上高を使用します。

ワークシートのオプション	設定
アイテム	ビデオ分析情報: Calendar Year、Calendar Quarter、Calendar Month、Sales SUM
ソート順序	Calendar Year、Calendar Quarter、Calendar Month
条件	Department = Video Sale OR Department = Video Rental
計算名	Growth %
計算	(Sales SUM-"Previous year")*100/"Previous year"
必要な追加計算	LAG(Sales SUM,1) OVER(PARTITION BY "Calendar Month" ORDER BY "Calendar Year")

図A-24 Growth %計算を含むワークシート

ワークシートは、対前年同月の各月の売上増加率を示す計算Growth %を示しています。たとえば、1999年1月の「Growth %」値は33.40% (つまり$50889から$67887への増加)です。

注意:

• 1998年には比較する値がないため、「1998」に対する「Growth %」の値は空白になります。

• この計算では、「Sales SUM」値から「Previous Year」値が減算され、その結果が100で乗算され、さらに「Previous Year」値で除算されます。たとえば、売上が75から100に増加した場合、この計算は25×100/75、つまり33.33%の増となります。

• 計算「Previous Year」の詳細は、「例: LAG/LEADを使用した異なる期間の売上高の比較」を参照してください。

A.6.8.4例: 売上増加のランク付け

この例では、前年比の売上増加率が最大の月を示す売上増加のランク・リストを作成します。

そのためには、「例: LAG/LEADを使用した異なる期間の売上高の比較」および「例: 異なる期間にわたる売上増加の計算」で比較した売上高と成長率、およびRANK関数を使用します。

ワークシートのオプション	設定
アイテム	ビデオ分析情報: Calendar Year、Calendar Quarter、Calendar Month、Sales SUM
ソート順序	Calendar Year、Calendar Quarter、Calendar Month
条件	Department = Video Sale OR Department = Video Rental
計算名	Rank Growth
計算	RANK() OVER(PARTITION BY "Calendar Year" ORDER BY "Growth %" DESC)
必要な追加計算	Previous Year = LAG(Sales SUM,1) OVER(PARTITION BY "Calendar Month" ORDER BY "Calendar Year") Growth % = (Sales SUM-"Previous year")*100/"Previous year"

図A-25 Rank Growth計算を含むワークシート

ワークシートは、売上増加についてランク・リストにおける位置付けを示しています。たとえば、1999年1月の「Rank Growth」値は3で、これは1月が売上増加3位の月であることを意味します(つまり、1999年1月の前年同月比の売上増加は、ランク・リストでは第3位となります)。

注意:

• 1998年には比較する値がないため、「1998」に対する「Rank Growth」の値は1になります。

• 上の例を見ると、1999年10月の前年同月比の売上増加が最大(40.02%)であったことがわかります。

• 計算「Previous Year」の詳細は、「例: LAG/LEADを使用した異なる期間の売上高の比較」を参照してください。

• 計算「Growth」の詳細は、「例: 異なる期間にわたる売上増加の計算」を参照してください。

A.6.9 統計関数の例

この項では、統計関数の例について説明します。

A.6.9.1 統計関数について

統計関数は、共分散、相関関係および線形回帰の統計を計算するために使用します。各関数は、不連続な集合に対して動作します。これらの関数は、ウィンドウ集計関数およびレポート集計関数としても使用できます。

例:

「例: 線形回帰の計算」

A.6.9.2 例: 線形回帰の計算

この例では、微分最小2乗法で回帰線を計算し、月別の「Profit SUM」をその「Sales SUM」の線形関数で表します。次の関数が使用されます。

• SLOPE: 回帰線の傾きの定義

• INTERCEPT: 回帰線の切片の定義

• REGR_R2: 回帰線の係数の定義

• REGR_COUNT: アイテム数

• REGR_AVGX: 平均売上

• REGR_AVGY: 平均利益

  ワークシートのオプション	設定
  アイテム	ビデオ分析情報: Calendar Year、Calendar Month、Sales SUM、Profit SUM
  ソート順序	Year
  条件	Department = Video Sale OR Department = Video Rental Year = 2000
  計算	Slope = REGR_SLOPE(Profit SUM,Sales SUM) OVER(PARTITION BY Calendar Year ORDER BY Profit SUM) Intercept = REGR_INTERCEPT(Profit SUM,Sales SUM) OVER(PARTITION BY Calendar Year ORDER BY Profit SUM) Coefficient = REGR_R2(Profit SUM,Sales SUM) OVER(PARTITION BY Calendar Year ORDER BY Profit SUM) Count = REGR_COUNT(Profit SUM,Sales SUM) OVER(PARTITION BY Calendar Year ORDER BY Profit SUM) Average = REGR_AVGX(Profit SUM,Sales SUM) OVER(PARTITION BY Calendar Year ORDER BY Profit SUM) Average 2 = REGR_AVGY(Profit SUM,Sales SUM) OVER(PARTITION BY Calendar Year ORDER BY Profit SUM)

  
  

図A-26 統計の計算を含むワークシート

ワークシートは、各月の傾き、切片、係数、数および平均の値を示しています。

注意:

• 回帰分析の詳細は、『Oracle Database SQLリファレンス』および『Oracle Databaseデータ・ウェアハウス・ガイド』を参照してください。

A.6.10 分析関数式の詳細

「新規計算」ダイアログで分析関数を選択すると、「計算」フィールドに汎用テキストが入力され、関数の定義に使用できます。この汎用テキストには、次のものが含まれます。

OVER (PARTITION BY expr1 ORDER BY expr2)

これらの式の使用方法は次のとおりです。

• OVER: 他のクエリー句(FROM、WHERE、HAVINGなど)が適用された後、クエリー結果セットに対して動作することを示します。

• PARTITION BY: クエリー結果セットを分割またはグループ化します(PARTITION BY 'Region'など)。

• ORDER BY: 結果セットを論理的に順序付ける方法を指定します(ORDER BY 'Sales SUM'など)。

Oracleの式の詳細は、「Oracleの分析関数に関する詳細情報の参照先」を参照してください。

A.6.11 分析関数と処理順序

条件で分析関数を使用する場合、分析関数と非分析関数との結合方法は、クエリー結果のDiscovererデータに影響を与えます。次の順序付けルールが適用されます(詳細は、「順序付けの例」を参照)。

• 非分析関数のみが含まれている条件は、分析関数を含む条件より前に適用されます。次の例では、最初に「Region = 'Central'」の条件が適用された後でRankが計算され、次に「Rank <= 3」の条件(分析関数を含む)が適用されます。

• 非分析関数と分析関数の組合せが含まれている条件の場合、分析関数は非分析関数より前に適用されます。次の例では、最初にRankが評価された後で「Rank <= 3」の条件が適用され、次に「Region = 'Central'」の条件が適用されます。

A.6.12 順序付けの例

関数の処理順序がクエリー結果のDiscovererデータに与える影響について、次の2つの例で説明します。

A.6.12.1 分析関数アイテムへの単一条件の適用例

最初のシナリオでは、2つの単一条件を適用します。条件は、「Region = 'Central'」および「Rank <= 3」(Rankは分析関数)です。

• 最初に「Region = 'Central'」条件が適用されてから、「Rank <= 3」が適用されます。

• したがって、結果セットにはCentral地域でのランク付けが3位以上の売上高のみが含まれます。

A.6.12.2 分析関数アイテムへの複数条件の適用例

2番目のシナリオでは、複数条件を1つ適用します。条件は、「Region = 'Central' AND Rank <= 3」(Rankは分析関数)です。

• 最初に「Rank <= 3」条件が適用されてから、「Region = 'Central'」条件が適用されます。

• したがって、結果セットにはCentral地域のうち、全体地域でのランク付けが3位以上の売上高のみが含まれます。

A.6.13 逆パーセンタイルの例

逆パーセンタイル関数を使用して、特定のパーセンタイルに計算される値(つまり、一連の値の累積分布)を求めます。たとえば、利益の中央値(つまり、一連の値の中間の値)を計算します。

例:

• 「例: PERCENTILE_DISC関数を使用した利益の中央値の計算」

• 「例: PERCENTILE_CONT関数を使用した利益の中央値の計算」

逆パーセンタイル関数は、ウィンドウ・レポート集計関数および集計関数として使用できます。

次の2つの逆パーセンタイル関数を使用できます。

1. PERCENTILE_CONT: 内挿法により定義された連続関数(つまり、既知の2つの値の間の1つ以上の関数値の推定)。この場合、関数では、順序付けされた行間への線形内挿法によるパーセンタイルが計算されます。

2. PERCENTILE_DISC: 個別値を想定するステップ関数。この場合、関数では各グループ内の累積分布値が(CUME_DISTを使用して)スキャンされ、指定したパーセンタイル値以上の最初の値が検出されます。

注意: 逆パーセンタイル関数では、一連の値の累積分布を求めるCUME_DIST関数とは逆の計算が実行されます。

A.6.14 PERCENTILE_CONTとPERCENTILE_DISCの相違点

PERCENTILE_CONTとPERCENTILE_DISCは、計算対象となる行数によっては異なる結果を戻す場合があります。たとえば、パーセンタイル値が0.5の場合、PERCENTILE_CONTは偶数の要素を持つグループについて2つの中間値の平均を戻します。これに対して、PERCENTILE_DISCは2つの中間値内の最初の値を戻します。奇数の要素を持つ集計グループの場合は、どちらの関数も中間要素の値を戻します。

A.6.14.1 例: PERCENTILE_DISC関数を使用した利益の中央値の計算

この例では、PERCENTILE_DISC関数をレポート集計関数として使用し、各都市の利益の中央値を計算します。

ワークシートのオプション	設定
アイテム	ビデオ分析情報: City、Profit SUM
ソート順序	該当なし
条件	該当なし
計算名	Median (PERCENTILE_DISC)
計算	PERCENTILE_DISC(0.5) WITHIN GROUP(ORDER BY Profit SUM) OVER()
必要な追加計算	Cumulative Distribution = CUME_DIST() OVER(ORDER BY Profit SUM)

図A-27 Median (PERCENTILE_DISC)計算を含むワークシート

ワークシートは、各都市の利益の中央値を示しています。利益の中央値(つまり、「Cumulative Distribution」列の0.50)は、$61,942.21 (つまり、「Cumulative Distribution」列に値0.50を持つ「Pittsburgh」の値)です。

注意:

• テーブル・ワークシートで、計算にはワークシートの行ごとに戻り値が表示されます。単一値を戻すには、計算を「ページ・アイテム」領域に移動します。

A.6.14.2 例: PERCENTILE_CONT関数を使用した利益の中央値の計算

この例では、PERCENTILE_CONT関数をレポート集計関数として使用し、各都市の利益の中央値を計算します。

ワークシートのオプション	設定
アイテム	ビデオ分析情報: City、Profit SUM
ソート順序	該当なし
条件	該当なし
計算名	Median (PERCENTILE_CONT)
計算	PERCENTILE_CONT(0.5) WITHIN GROUP(ORDER BY Profit SUM) OVER()

図A-28 Median (PERCENTILE_CONT)計算を含むワークシート

ワークシートは、各都市の利益の中央値を示しています。利益の中央値は$63,076.41で、これは0.50および0.55のパーセンタイルの利益の平均値です。つまり、Pittsburghの値とDenverの値の合計を2で除算した結果です(($61,942.21 + $64,210.60)/2)。この関数の計算方法の詳細は、「PERCENTILE_CONTとPERCENTILE_DISCの相違点」を参照してください。

注意:

• 計算行数が偶数の場合は、2つの中間結果の平均値が求められます。上の例では、0.50と0.55のパーセンタイルの平均値が計算されます。

• テーブル・ワークシートで、計算にはワークシートの行ごとに戻り値が表示されます。単一値を戻すには、計算を「ページ・アイテム」領域に移動します。

A.6.15 仮説ランクと分散の例

仮説ランクおよび分布関数は、'what-if?'分析に使用します。この種の関数では、ある値を他の一連の値に挿入した場合に、その値の位置が計算されます。たとえば、売上が$1,200,000の営業担当を、営業担当の実績を示すランク・リストに位置付ける場合などです。

注意: また、次の仮想値を計算することもできます。

• DENSE_RANK: 等値に同じランクが与えられるように、値のランクを計算します(たとえば、複数の値をリーグの最上位としてランク付けできます)。

• CUME_DIST: 指定した値について、値グループでの相対位置を計算します。

• PERCENT_RANK: CUME_DISTに似ていますが、この関数は値から1を減算し、評価対象となる行数から1を減算した結果で除算した値のランクを計算します。

例:

• 「例: 仮説ランクの計算」

A.6.15.1 例: 仮説ランクの計算

この例では、利益値の仮説ランクを部門と地域の利益値と相対で計算します。たとえば、「各地域のビデオ販売部門について、売上高$500.00は値のランク・リストでどのように位置付けられるか?」という質問に答えることができます。

注意: この例では、パラメータを使用して計算に動的入力を提供します(詳細は、「パラメータを使用した計算への動的入力の提供」を参照)。

ワークシートのオプション	設定
アイテム	ビデオ分析情報: Calendar Year、Region、Department、Profit、Profit COUNT、Profit MAX、Profit MIN
ソート順序	Region (Lo to Hi)
条件	該当なし
ページ・アイテム	Year=2000、Department=Video Sale、パラメータHypothetical Valueの値=500
パラメータ	Hypothetical Value: この値は、ワークシートを開くかリフレッシュするときに、Discovererユーザーが入力します。
計算名	League table
計算	RANK(:Hypothetical Value) WITHIN GROUP(ORDER BY Profit DESC NULLS FIRST)

図A-29 League table計算を含むワークシート

• ワークシートは、地域のランク・リストにおける仮想値$500のランクを示しています。

  • 利益の仮想金額$500は、520の利益値(範囲$13.49から$537.78)を含むCentral地域でランク番号2となります。

  • 利益の仮想金額$500は、807の利益値(範囲$11.99から$539.06)を含むEast地域ではランク番号7となります。

    West地域の場合、仮想金額($500)は「Profit MAX」の金額($484.01)より大きいため、仮説ランク番号が1となることに注意してください。

注意:

• アイテム「Profit COUNT」、「Profit MAX」、「Profit MIN」は、計算には使用されません。この3つのアイテムは、関数の機能を理解しやすいようにワークシートに表示されます。たとえば、West地域の「Profit MAX」値が$484.01と表示されると、仮想値$500.00が1にランク付けされることがわかります。これは、仮想値が最大値(つまり、「Profit MAX」)より大きいためです。

• 「League table」計算では、ワークシートを開くかリフレッシュするときに入力したHypothetical Valueパラメータの値が使用されます。次の例では、Hypothetical Value (「Hypothetical amount」ページ・アイテムに表示)は500に設定されています。計算にパラメータ値を使用する方法の詳細は、「パラメータを使用した計算への動的入力の提供」を参照してください。

• RANK関数は、ORDER BY引数として非集計値を取る必要があります。たとえば、この関数を「SUM(Profit)」または「Profit AVG」に対して実行することはできません。

• Hypothetical Valueをパラメータとして設定するかわりに、計算にRank()引数としてランク値を直接入力できます。次に例を示します。

RANK(500) WITHIN GROUP(ORDER BY Profit DESC NULLS FIRST)

• パラメータを使用しない場合は、仮想値が変更されるように計算を変更する必要があります。

A.6.16 バンディングの例

Oracle9iのWIDTH_BUCKET関数を使用すると、各値をそれぞれの値に従ってグループ(バンドまたはバケットとも呼ばれます)に分割できます(詳細は、「バンディングについて」を参照)。たとえば、棒グラフ作成用にデータをグループ化します。

ヒント: GREATEST関数とCASE関数を使用して、等幅バンドを計算することもできます(「例: 値に基づくバンディング(1)」および「例: 値に基づくバンディング(2)」を参照)。

例:

• 「例: WIDTH_BUCKETを使用した等幅バンドの生成」

A.6.16.1 例: WIDTH_BUCKETを使用した等幅バンドの生成

この例では、利益値をそれぞれの値に従って3つのバンドに分割します。

ワークシートのオプション	設定
アイテム	ビデオ分析情報: City、Profit SUM
ソート順序	Equi-width bands
条件	Department = Video Sale OR Department = Video Rental Year = 2000 Region = Central
計算名	Equi-width bands
計算	WIDTH_BUCKET(Profit SUM,0,30000,3)

図A-30 Equi-width bands計算を含むワークシート

ワークシートは、各都市の利益値の等幅バンドを示しています。最初のバンド(0から9,999)には、Nashville、Minneapolis、DallasおよびChicagoが含まれています。2番目のバンド(10,000から19,999)にはSt. Louis、3番目のバンド(20,000から30,000)にはCincinnatiおよびLouisvilleが含まれています。

注意:

• WIDTH_BUCKET関数は、次の4つの引数を取ります。

  • ワークシート・アイテム = Profit SUM

  • 最小値 = 0

  • 最大値 = 30000

  • バンド数 = 3

• バンドを逆順で割り当てるには、最大値と最小値を逆にします。たとえば、WIDTH_BUCKET(Profit SUM,30000,0,3)関数では、次のワークシートが生成されます。

図A-31 逆順によるEqui-width bands計算を含むワークシート

ワークシートは、各都市の利益値の等幅バンドを示しています。最初のバンド(20,000から30,000)には、CincinnatiおよびLouisvilleが含まれています。2番目のバンド(10,000から19,999)にはSt. Louis、3番目のバンド(0から9,999)にはChicago、Dallas、Minneapolis、およびNashvilleが含まれています。

A.6.17 FIRST/LAST集計の例

FIRST/LAST集計関数を使用して、順序付きグループ内の最初または最後の値を検索します。これにより、ある列のデータを順序付けて、別の列を戻すことができます。たとえば、期間中の販売トランザクション数が最も多い地域について、販売トランザクションの平均金額を求めます。

例:

• 「例: 販売トランザクション数が最も多い地域内の最大販売トランザクションの検索」

• 「例: 販売トランザクション数が最も少ない地域内の平均販売トランザクションの検索」

FIRST/LAST関数を使用すると、自己結合や副クエリーを実行する必要がないため、Discovererのパフォーマンスを最大限まで高めることができます。

注意: FIRST/LAST関数は、次と併用できます。

• MIN: 値リスト内の最小値を検索します。

• MAX: 値リスト内の最大値を検索します。

• AVG: 値リストの平均値を求めます。

• STDDEV: 値リストの標準偏差を求めます。

• VARIANCE: 値リストの偏差を求めます。

A.6.17.1 例: 販売トランザクション数が最も多い地域内の最大販売トランザクションの検索

この例では、期間中の販売トランザクションが最も多い都市について、最大の販売トランザクション金額を検索します。

ワークシートのオプション	設定
アイテム	ビデオ分析情報: City、Sales MAX、Sales Count
ソート順序	該当なし
条件	Department = Video Sale OR Department = Video Rental Year = 2000 Region = Central
計算名	Maximum sales in city with largest sales volume
計算	MAX(Sales MAX) KEEP(DENSE_RANK LAST ORDER BY Sales COUNT) OVER(PARTITION BY "Calendar Year", Region)

図A-32 Maximum sales in city with largest sales volume計算を含むワークシート

ワークシートは、販売トランザクション数が最も多い都市で最大の販売トランザクション値を示しています。Cincinnatiの販売トランザクション数(1220)が最大です。Cincinnatiの最大販売トランザクションは$667.53です。

注意:

• Sales MAX: 最大の販売トランザクション金額を示します。

• Sales COUNT: 期間中の販売トランザクション数を示します。

• この関数を適用するために、Discovererでは次が実行されます。

  • データベース内で「Sales COUNT」列が順序付けされます(デフォルトの順序は昇順です)。

  • 「Sales COUNT」列の最大数である最後の値(つまりLAST引数)を使用して、この行の都市名(Cincinnati)が調べられます。

  • データベース内で「Cincinnati」のトランザクションが順序付けされ、LAST値である$667.53が戻されます(デフォルトの順序付けは昇順です)。

• 「Sales COUNT」アイテムと「Sales MAX」アイテムは、計算が正しい結果を戻すことを示すために含まれています。この2つのアイテムは結果の計算には使用されません。結果はデータベース内の集計を使用して計算されます。

• ヒント: テーブル・ワークシートで、計算にはワークシートの行ごとに戻り値が表示されます。単一値を戻すには、計算を「ページ・アイテム」領域に移動します。

A.6.17.2 例: 販売トランザクション数が最も少ない地域内の平均販売トランザクションの検索

この例では、期間中の販売トランザクション数が最も少ない都市について、販売トランザクションの平均金額を計算します。

ワークシートのオプション	設定
アイテム	ビデオ分析情報: City、Sales COUNT、Sales AVG
ソート順序	該当なし
条件	Department = Video Sale OR Department = Video Rental Region = Central Year = 2000
計算名	Average sales in city with smallest sales volume
計算	MIN(Sales AVG) KEEP(DENSE_RANK FIRST ORDER BY Sales COUNT) OVER(PARTITION BY "Calendar Year", Region)

図A-33 Average sales in city with smallest sales volume計算を含むワークシート

ワークシートは、販売トランザクション数が最も少ない都市の販売トランザクションの平均値を示しています。Nashvilleの期間中のトランザクション数(219)が最小です。したがって、この計算では、Nashvilleのトランザクションの平均値($38.39)が戻されます。

注意:

• Sales COUNT: 期間中の販売トランザクション数が含まれます。

• Sales AVG: 期間中の販売トランザクションの平均金額が含まれます。

• この関数を適用するために、Discovererでは次が実行されます。

  • データベース内で「Sales COUNT」列が順序付けされます(デフォルトの順序は昇順です)。

  • 「Sales COUNT」列の最小数である最初の値(つまりFIRST引数)を使用して、この行の都市名(Nashville)が調べられます。

  • Nashvilleの平均売上額$38.39が計算されます。

• 「Sales COUNT」アイテムと「Sales AVG」アイテムは、計算が正しい結果を戻すことを示すために含まれています。この2つのアイテムは結果の計算には使用されません。結果はデータベース内の集計を使用して計算されます。

• ヒント: テーブル・ワークシートで、計算にはワークシートの行ごとに戻り値が表示されます。単一値を戻すには、計算を「ページ・アイテム」領域に移動します。

A.7 行ベースおよび時間ベースの間隔の使用例

この項の例は、分析関数を行ベースおよび時間ベースの間隔とともに使用して、Discovererで最も理想的な結果を得る方法を示しています。たとえば、前の行の値、または前年の値を戻す計算を作成できます。

注意: 行ベースおよび時間ベースの間隔は、ウィンドウ集計とも呼ばれます。ウィンドウ集計の詳細は、「ウィンドウ集計について」を参照してください。Discovererでのウィンドウ集計の使用例は、「ウィンドウ集計関数の例」を参照してください。

この例では、Discovererの分析関数テンプレートを使用して作成された分析関数を使用します。各例は、「分析関数テンプレート」ダイアログの「次を区切りとして計算を再開」リストで間隔を選択した場合に、計算に与える影響を示しています。たとえば、現行値より前の1か月を間隔に選択すると、ある月の売上を別の月の売上と比較できます。分析関数テンプレートを使用した計算式の作成方法の詳細は、「分析関数テンプレートを使用して新しい計算を作成する方法」を参照してください。

注意: 「計算」フィールドに手動で分析関数テキストを入力する場合、式にPARTITION BY句を追加することにより間隔を指定できます。計算式を手動で作成する方法の詳細は、「分析関数の概要」を参照してください。

この項の項目は次のとおりです。

• 「例: 行ベースの間隔を使用した偏差計算の作成」

• 「例: 時間ベースの間隔を使用した偏差計算の作成」

• 「例: 時間ベースの間隔を使用した「前の値」計算の作成」

A.7.1 例: 行ベースの間隔を使用した偏差計算の作成

この例では、行ベースの間隔に基づいた偏差計算を使用します。サンプル・ワークシートには、「Year」、「Region」および「Sales SUM」の各アイテムが表示されています。各売上値の前行の値を戻すには、次のように「偏差」ダイアログを使用して、「Change」という名前の計算をワークシートに追加します。

• 「値を比較」ドロップダウン・リストから「Sales SUM」を選択します。

• 「前の値」フィールドから「1」および「現行値より前の行」を選択します。

• 「行の順序付け」フィールドから「Calendar Year」を選択します。

• 「行の順序付け(2次)」フィールドから「Region」を選択します。

• 残りのフィールドについては、デフォルトの値を使用します。

サンプル・ワークシートは、偏差式を含む「Change」アイテムを示しています。たとえば、West地域の2000年の「Change」列は-142,670です。この値は、West地域の2000年の値(すなわち130,982)から、East地域の2000年の値(すなわち273,651)を減算して導かれた値です。

注意: 正の値は黒色で表示されます。負の値は赤色で表示されます。

注意: 「次を区切りとして計算を再開」リストの「Region」チェック・ボックスを選択した場合、各値は常に同じ地域の前年の「Sales SUM」値と比較されます。

次のサンプル・ワークシートは、「次を区切りとして計算を再開」リストの「Region」チェック・ボックスが選択された状態での、偏差式を含む「Change」アイテムを示しています。つまり、次のようになります。

• West地域の2000年の「Change」値は-85,192です。この値は、West地域の2000年の「Sales SUM」値(すなわち130,982)から、West地域の1999年の「Sales SUM」値(すなわち216.174)を減算して導かれた値です。

• East地域の2000年の「Change」値は-128,331です。この値は、East地域の2000年の「Sales SUM」値(すなわち273,651)から、East地域の1999年の「Sales SUM」値(すなわち401,983)を減算して導かれた値です。

注意: 行ベースの間隔を選択し、「次を区切りとして計算を再開」リストの「Region」チェック・ボックスを選択すると、値は前年の値と比較されます。値は、時間ベースの間隔を使用して前年の値と比較することもできます(時間ベースの間隔を使用する方法の詳細は、「例: 時間ベースの間隔を使用した偏差計算の作成」を参照)。

A.7.2 例: 時間ベースの間隔を使用した偏差計算の作成

この例では、時間ベースの間隔に基づいた偏差計算を使用します。サンプル・ワークシートには、「Year」、「Region」および「Sales SUM」の各アイテムが表示されています。前年からの売上の変化を計算するには、次のように「偏差」ダイアログを使用して、「Yearly change」という名前の計算をワークシートに追加します。

• 「値を比較」ドロップダウン・リストから「Sales SUM」を選択します。

• 「前の値」フィールドから「1」および「現行値より前の年」を選択します。

• 「次を区切りとして計算を再開」リストの「Region」チェック・ボックスを選択します。

• 残りのフィールドについては、デフォルトの値を使用します。

サンプル・ワークシートは、偏差式を含む「Yearly change」アイテムを示しています。たとえば、「Yearly change」列では、West地域の2000年の「Sales SUM」値が、West地域の1999年の値より85,192小さいことがわかります。

注意: 正の値は黒色で表示されます。負の値は赤色で表示されます。

注意: 「次を区切りとして計算を再開」リストの「Region」チェック・ボックスを選択しない場合、各値は常に前年の最後の地域の「Sales SUM」値と比較されます。

次のサンプル・ワークシートは、「次を区切りとして計算を再開」リストの「Region」チェック・ボックスが選択解除された状態での、前の値式を含む「Yearly change」アイテムを示しています。つまり、次のようになります。

• West地域の2000年の「Yearly change」値は-85,192です。この値は、West地域の2000年の「Sales SUM」値(すなわち130,982)から、West地域の1999年の「Sales SUM」値(すなわち216.174)を減算して導かれた値です。

• East地域の2000年の「Yearly change」値は57,478です。この値は、East地域の2000年の「Sales SUM」値(すなわち273,651)から、West地域の1999年の「Sales SUM」値(すなわち216.174)を減算して導かれた値です。

A.7.3 例: 時間ベースの間隔を使用した「前の値」計算の作成

この例では、時間ベースの間隔に基づいた「前の値」計算を使用します。サンプル・ワークシートには、「Year」、「Region」および「Sales SUM」の各アイテムが表示されています。各売上値の前年の値を戻すには、次のように「前の値」ダイアログを使用して、「Previous year」という名前の計算をワークシートに追加します。

• 「前の値」ドロップダウン・リストから「Sales SUM」を選択します。

• 「戻り値」フィールドから「1」および「現行値より前の年」を選択します。

• 「次を区切りとして計算を再開」リストの「Region」チェック・ボックスを選択します。

• 残りのフィールドについては、デフォルトの値を使用します。

サンプル・ワークシートは、前の値式を含む「Previous year」アイテムを示しています。たとえば、West地域の2000年の「Previous year」の値が216,174で、West地域の1999年の「Sales SUM」値と同じであることがわかります。

注意: 「次を区切りとして計算を再開」リストの「Region」チェック・ボックスを選択しない場合、常に前年の最後の地域の「Sales SUM」値が戻されます。

次のサンプル・ワークシートは、「次を区切りとして計算を再開」リストの「Region」チェック・ボックスが選択解除された状態での、前の値式を含む「Previous year」アイテムを示しています。つまり、West、East、Central地域における2000年の「Previous year」値が216,174で、West地域の1999年の「Sales SUM」値と同じであることがわかります。