| Oracle® Database 2日でデータ・ウェアハウス・ガイド 11g リリース2(11.2) B56298-04 |
|
 前 |
 次 |
この章では、ビジネス問合せから導出された効果的なビジネス・レポートの作成方法を説明します。この章の内容は次のとおりです。
Oracle Databaseでは、集計および分析SQL関数のファミリの導入によってSQLの分析処理機能を強化しています。これらの関数によって、ランキング、パーセンタイルおよび移動平均の計算など、次の問合せに応答できます。
上位10製品の国別売上は何ですか。
在庫のある製品の毎週の移動平均はどうですか。
総売上高の何パーセントが第4四半期に発生していますか。
第4四半期の平均割引は年間の平均割引よりどれくらい高いですか。
国内の精油所の20パーセントが閉鎖された場合、既存の精油所の収益性ランキングはどうなりますか。
集計関数は、合計の異なるタイプを導出でき、追加計算のこれらの合計を使用できるデータ・ウェアハウスの基礎となる部分です。データ・ウェアハウスの集計パフォーマンスを向上させるには、Oracle DatabaseはGROUP BY列に対するいくつかの拡張を提供します。CUBE、ROLLUP、GROUPINGおよびGROUPING SETS関数により、より早く簡単に問合せおよびレポート作成ができます。ROLLUP関数は、個人的で詳細なレベルからサマリー合計まで、集計レベルを上げながらSUM、COUNT、MAX、MINおよびAVGなどの集計を計算します。CUBE関数はROLLUPに似た拡張で、単一の文で集計可能なすべての組合せを計算できます。
分析関数は行のグループに基づいて集計値を計算します。これらの関数は各グループに対して複数の行を戻すという点で集計関数とは異なります。この行のグループはウィンドウと呼ばれます。このウィンドウを使用すると、移動平均や累積合計などを計算できます。行ウィンドウは各行に対して定義されます。このウィンドウでは現行の行の計算を実行するために使用する行の範囲を決定します。ウィンドウのサイズは時間などの論理間隔、または行の物理的な数に基づくことができます。一部の関数はウィンドウとのみ使用され、ウィンドウ関数として参照されます。
パフォーマンスを向上させるために集計関数と分析関数をそれぞれパラレルで実行することができ、これは複数のプロセスでこれらのすべての関数を同時に実行できることを意味します。こうした機能によって計算、分析およびレポート作成がより容易かつ効率的になるため、データ・ウェアハウスのパフォーマンス、スケーラビリティおよび簡易性が向上します。
高度なSQLおよびPL/SQL機能を活用でき、Oracle Databaseはビジネス問合せをSQLに変換します。この項では、これらの高度な機能について、次のとおり説明します。
ROLLUP関数によって、SELECT文で総計やディメンションの特定のグループを横断する複数の小計レベルを計算できます。ROLLUP関数は、GROUP BY句の単純な拡張であるため、この構文は簡単に使用できます。ROLLUP関数は非常に効率的で、問合せにかかるオーバーヘッドは最小限に抑えられます。ROLLUP関数のアクションは単純で、最も詳細なレベルから総計まで、ROLLUP関数で指定されたグループ・リストに従ってロールアップする小計を作成します。ROLLUP関数は引数として、グルーピング列の順序付けリストを取ります。最初に、GROUP BY句で指定された標準の集計値を計算します。次に、グルーピング列のリストを右から左に移動しながら、順番に高いレベルの小計を作成します。最後に、総計を作成します。
タスクに小計が含まれる場合、特に小計が時間または地理などの階層ディメンションに基づいている場合、ROLLUP関数は有効です。また、ROLLUP関数によって、マテリアライズド・ビューのメンテナンスを簡素化および高速化することもできます。
ビジネス・レポートの準備時に共通して要求されるのは、収入額順で、異なる製品カテゴリにわたる四半期の総売上高の検索です。次の問合せはこれを達成し、後に実行するより複雑な問合せの構築の開始点に使用されます。
ROLLUP関数を使用する手順
SELECT t.calendar_quarter_desc quarter
, p.prod_category category
, TO_CHAR(SUM(s.amount_sold),'L999G999G990D00') revenue
FROM times t
, products p
, sales s
WHERE t.time_id = s.time_id
AND p.prod_id = s.prod_id
AND s.time_id BETWEEN TO_DATE('01-JAN-2001','dd-MON-yyyy')
AND TO_DATE('31-DEC-2001','dd-MON-yyyy')
GROUP BY t.calendar_quarter_desc, p.prod_category
ORDER BY t.calendar_quarter_desc
, SUM(s.amount_sold);
QUARTER CATEGORY REVENUE
------- ------------------------------ --------------
2001-01 Software/Other $860,819.81
2001-01 Electronics $1,239,287.71
2001-01 Hardware $1,301,343.45
2001-01 Photo $1,370,706.38
2001-01 Peripherals and Accessories $1,774,940.09
2001-02 Software/Other $872,157.38
2001-02 Electronics $1,144,187.90
2001-02 Hardware $1,557,059.59
2001-02 Photo $1,563,475.51
2001-02 Peripherals and Accessories $1,785,588.01
2001-03 Software/Other $877,630.85
2001-03 Electronics $1,017,536.82
2001-03 Photo $1,607,315.63
2001-03 Hardware $1,651,454.29
2001-03 Peripherals and Accessories $2,042,061.04
2001-04 Software/Other $943,296.36
2001-04 Hardware $1,174,512.68
2001-04 Electronics $1,303,838.52
2001-04 Photo $1,792,131.39
2001-04 Peripherals and Accessories $2,257,118.57
この問合せは便利ですが、同じレポート上で異なるカテゴリの総計を確認する必要がある可能性があります。次の例は、ROLLUP関数を使用して、元の問合せに総計を追加する方法を示しています。
SELECT t.calendar_quarter_desc quarter
, p.prod_category category
, TO_CHAR(SUM(s.amount_sold),'L999G999G990D00') revenue
FROM times t
, products p
, sales s
WHERE t.time_id = s.time_id
AND p.prod_id = s.prod_id
AND s.time_id BETWEEN TO_DATE('01-JAN-2001','dd-MON-yyyy')
AND TO_DATE('31-DEC-2001','dd-MON-yyyy')
GROUP BY ROLLUP(t.calendar_quarter_desc, p.prod_category)
ORDER BY t.calendar_quarter_desc
, SUM(s.amount_sold);
QUARTER CATEGORY REVENUE
------- ------------------------------ -------------
2001-01 Software/Other $860,819.81
2001-01 Electronics $1,239,287.71
2001-01 Hardware $1,301,343.45
2001-01 Photo $1,370,706.38
2001-01 Peripherals and Accessories $1,774,940.09
2001-01 $6,547,097.44
2001-02 Software/Other $872,157.38
2001-02 Electronics $1,144,187.90
2001-02 Hardware $1,557,059.59
2001-02 Photo $1,563,475.51
2001-02 Peripherals and Accessories $1,785,588.01
2001-02 $6,922,468.39
2001-03 Software/Other $877,630.85
2001-03 Electronics $1,017,536.82
2001-03 Photo $1,607,315.63
2001-03 Hardware $1,651,454.29
2001-03 Peripherals and Accessories $2,042,061.04
2001-03 $7,195,998.63
2001-04 Software/Other $943,296.36
2001-04 Hardware $1,174,512.68
2001-04 Electronics $1,303,838.52
2001-04 Photo $1,792,131.39
2001-04 Peripherals and Accessories $2,257,118.57
2001-04 $7,470,897.52
$28,136,461.98
CUBE関数は指定されたグループ化列の集合を取り、それらが取り得るすべての組合せに対して小計を作成します。多次元分析によって、CUBE関数は指定されたディメンションを持つデータ・キューブに対して計算されるすべての小計を生成します。CUBE(time, region, department)を指定した場合、結果セットには同等のROLLUP関数および追加の組合せに含まれるすべての値が含まれます。
複数の表にわたるレポートを必要とする状況では、CUBE関数の使用を検討してください。複数の表にわたるレポートに必用なデータは、CUBE関数を使用する単一のSELECT文で生成できます。ROLLUPと同様に、CUBE関数もマテリアライズド・ビューの生成に便利です。CUBE関数を含む問合せがパラレルに実行される場合は、マテリアライズド・ビューの移入が速くなることに注意してください。
四半期の合計に加えて、選択した期間における様々な製品カテゴリの合計も取得する必要がある場合があります。次の例に示すように、CUBE関数を使用してこの計算を行うことができます。
CUBE関数を使用する手順
SELECT t.calendar_quarter_desc quarter
, p.prod_category category
, TO_CHAR(SUM(s.amount_sold),'L999G999G990D00') revenue
FROM times t
, products p
, sales s
WHERE t.time_id = s.time_id
AND p.prod_id = s.prod_id
AND s.time_id BETWEEN TO_DATE('01-JAN-2001','dd-MON-yyyy')
AND TO_DATE('31-DEC-2001','dd-MON-yyyy')
GROUP BY CUBE(t.calendar_quarter_desc, p.prod_category)
ORDER BY t.calendar_quarter_desc
, SUM(s.amount_sold);
QUARTER CATEGORY REVENUE
------- ------------------------------ -------------
2001-01 Software/Other $860,819.81
2001-01 Electronics $1,239,287.71
2001-01 Hardware $1,301,343.45
2001-01 Photo $1,370,706.38
2001-01 Peripherals and Accessories $1,774,940.09
2001-01 $6,547,097.44
2001-02 Software/Other $872,157.38
2001-02 Electronics $1,144,187.90
2001-02 Hardware $1,557,059.59
2001-02 Photo $1,563,475.51
2001-02 Peripherals and Accessories $1,785,588.01
2001-02 $6,922,468.39
2001-03 Software/Other $877,630.85
2001-03 Electronics $1,017,536.82
2001-03 Photo $1,607,315.63
2001-03 Hardware $1,651,454.29
2001-03 Peripherals and Accessories $2,042,061.04
2001-03 $7,195,998.63
2001-04 Software/Other $943,296.36
2001-04 Hardware $1,174,512.68
2001-04 Electronics $1,303,838.52
2001-04 Photo $1,792,131.39
2001-04 Peripherals and Accessories $2,257,118.57
2001-04 $7,470,897.52
Software/Other $3,553,904.40
Electronics $4,704,850.95
Hardware $5,684,370.01
Photo $6,333,628.91
Peripherals and Accessories $7,859,707.71
$28,136,461.98
ROLLUPおよびCUBE関数を使用する際には、2つの課題があります。第一に、どの結果セット行が小計であるかをプログラム上でどのように判断し、指定された小計の正確な小計レベルをどのように探し出すかということです。合計に対する割合を計算する場合に小計を使用する必要があるため、どの行が求める小計であるかを判別する方法が必要です。第2に、格納されるNULL値およびROLLUP、またはCUBE関数によって作成されるNULL値の両方が問合せ結果に含まれる場合、どう処理するかという問題です。この2つをどのように区別するかが問題になります。
この問題はGROUPING関数で処理します。単一の列を引数として使用し、ROLLUPまたはCUBE関数により作成されたNULL値が発生した場合、GROUPING関数は1を戻します。つまり、NULL値が小計の行であることを示す場合、GROUPING関数は1を戻します。格納されたNULL値を含むその他のタイプの値では0を戻します。
NULL値またはROLLUPまたはCUBE操作で作成されたNULL 値を処理する必要がある場合、GROUPING関数を使用します。NULL値を使用する1つの理由に、NULLフィールドに説明を置くことです。たとえば、番号が総計を示すテキストなどです。
値が総計を示している場合にわかりにくいため、レポート内の説明の列がさらに必要になる場合があります。GROUPING関数を使用すると、次の例のように、問合せ結果に総計を示すラベルを挿入することができます。
GROUPING関数を使用する手順
SELECT DECODE(GROUPING(t.calendar_quarter_desc) , 0, t.calendar_quarter_desc , 1, 'TOTAL' ) quarter , DECODE(GROUPING(p.prod_category) , 0, p.prod_category , 1, 'TOTAL' ) category , TO_CHAR(SUM(s.amount_sold),'L999G999G990D00') revenue FROM times t , products p , sales s WHERE t.time_id = s.time_id AND p.prod_id = s.prod_id AND s.time_id BETWEEN TO_DATE('01-JAN-2001','dd-MON-yyyy') AND TO_DATE('31-DEC-2001','dd-MON-yyyy') GROUP BY CUBE(t.calendar_quarter_desc, p.prod_category) ORDER BY t.calendar_quarter_desc , SUM(s.amount_sold); QUARTER CATEGORY REVENUE ------- ------------------------------ ------------- 2001-01 Software/Other $860,819.81 2001-01 Electronics $1,239,287.71 2001-01 Hardware $1,301,343.45 2001-01 Photo $1,370,706.38 2001-01 Peripherals and Accessories $1,774,940.09 2001-01 TOTAL $6,547,097.44 2001-02 Software/Other $872,157.38 2001-02 Electronics $1,144,187.90 2001-02 Hardware $1,557,059.59 2001-02 Photo $1,563,475.51 2001-02 Peripherals and Accessories $1,785,588.01 2001-02 TOTAL $6,922,468.39 2001-03 Software/Other $877,630.85 2001-03 Electronics $1,017,536.82 2001-03 Photo $1,607,315.63 2001-03 Hardware $1,651,454.29 2001-03 Peripherals and Accessories $2,042,061.04 2001-03 TOTAL $7,195,998.63 2001-04 Software/Other $943,296.36 2001-04 Hardware $1,174,512.68 2001-04 Electronics $1,303,838.52 2001-04 Photo $1,792,131.39 2001-04 Peripherals and Accessories $2,257,118.57 2001-04 TOTAL $7,470,897.52 TOTAL Software/Other $3,553,904.40 TOTAL Electronics $4,704,850.95 TOTAL Hardware $5,684,370.01 TOTAL Photo $6,333,628.91 TOTAL Peripherals and Accessories $7,859,707.71 TOTAL TOTAL $28,136,461.98
GROUP BY句でGROUPING SETS関数を使用して、作成するグループの集合を選択的に指定できます。これにより、データ・キューブ全体を計算せずに、複数のディメンションにまたがる正確な指定ができます。これは、すべてのディメンションの総計が必要ではないことを意味します。
販売チャネルに基づく営業番号の総計を参照する場合があります。チャネル・クラスごとの総計を取得するため別の問合せを追加するかわりに、次の例のように、GROUPING SETS関数を使用できます。
GROUPING SETS関数を使用する手順
SELECT DECODE(GROUPING(t.calendar_quarter_desc)
, 0, t.calendar_quarter_desc
, 1, 'TOTAL'
) quarter
, DECODE(GROUPING(c.channel_class)
, 0, c.channel_class
, 1 , '--all--'
) channel
, DECODE(GROUPING(p.prod_category)
, 0, p.prod_category
, 1, 'TOTAL'
) category
, TO_CHAR(SUM(s.amount_sold),'L999G999G990D00') revenue
FROM times t
, products p
, channels c
, sales s
WHERE t.time_id = s.time_id
AND p.prod_id = s.prod_id
AND c.channel_id = s.channel_id
AND s.time_id BETWEEN TO_DATE('01-JAN-2001','dd-MON-yyyy')
AND TO_DATE('31-DEC-2001','dd-MON-yyyy')
GROUP BY GROUPING SETS(c.channel_class,
CUBE(t.calendar_quarter_desc, p.prod_category))
ORDER BY t.calendar_quarter_desc
, SUM(s.amount_sold);
QUARTER CHANNEL CATEGORY REVENUE
------- ------------- ---------------------------- -------------
2001-01 --all-- Software/Other $860,819.81
2001-01 --all-- Electronics $1,239,287.71
2001-01 --all-- Hardware $1,301,343.45
2001-01 --all-- Photo $1,370,706.38
2001-01 --all-- Peripherals and Accessories $1,774,940.09
2001-01 --all-- TOTAL $6,547,097.44
2001-02 --all-- Software/Other $872,157.38
2001-02 --all-- Electronics $1,144,187.90
2001-02 --all-- Hardware $1,557,059.59
2001-02 --all-- Photo $1,563,475.51
2001-02 --all-- Peripherals and Accessories $1,785,588.01
2001-02 --all-- TOTAL $6,922,468.39
2001-03 --all-- Software/Other $877,630.85
2001-03 --all-- Electronics $1,017,536.82
2001-03 --all-- Photo $1,607,315.63
2001-03 --all-- Hardware $1,651,454.29
2001-03 --all-- Peripherals and Accessories $2,042,061.04
2001-03 --all-- TOTAL $7,195,998.63
2001-04 --all-- Software/Other $943,296.36
2001-04 --all-- Hardware $1,174,512.68
2001-04 --all-- Electronics $1,303,838.52
2001-04 --all-- Photo $1,792,131.39
2001-04 --all-- Peripherals and Accessories $2,257,118.57
2001-04 --all-- TOTAL $7,470,897.52
TOTAL --all-- Software/Other $3,553,904.40
TOTAL --all-- Electronics $4,704,850.95
TOTAL --all-- Hardware $5,684,370.01
TOTAL --all-- Photo $6,333,628.91
TOTAL Indirect TOTAL $6,709,496.66
TOTAL --all-- Peripherals and Accessories $7,859,707.71
TOTAL Others TOTAL $8,038,529.96
TOTAL Direct TOTAL $13,388,435.36
TOTAL --all-- TOTAL $28,136,461.98
ビジネス情報の処理には、複雑なランキング、小計、移動平均およびリード/ラグ比較を含む高度な計算が要求されます。これらの集計および分析タスクはビジネス・インテリジェンス問合せに不可欠であり、ウィンドウ関数の使用によって解決します。
四半期の収入番号のランクを示す追加の列を確認する場合があります。次の例では、RANK関数を使用してこれを実行しています。
RANK関数を使用する手順
SELECT DECODE(GROUPING(t.calendar_quarter_desc)
, 0, t.calendar_quarter_desc
, 1, 'TOTAL'
) quarter
, DECODE(GROUPING(t.calendar_quarter_desc) + GROUPING(p.prod_category)
, 0, RANK() OVER (PARTITION BY t.calendar_quarter_desc
ORDER BY SUM(s.amount_sold))
, 1, null
) ranking
, DECODE(GROUPING(c.channel_class)
, 0, c.channel_class
, 1 , '--all--'
) channel
, DECODE(GROUPING(p.prod_category)
, 0, p.prod_category
, 1, 'TOTAL'
) category
, TO_CHAR(SUM(s.amount_sold),'L999G999G990D00') revenue
FROM times t
, products p
, channels c
, sales s
WHERE t.time_id = s.time_id
AND p.prod_id = s.prod_id
AND c.channel_id = s.channel_id
AND s.time_id BETWEEN TO_DATE('01-JAN-2001','dd-MON-yyyy')
AND TO_DATE('31-DEC-2001','dd-MON-yyyy')
GROUP BY GROUPING SETS(c.channel_class,
CUBE(t.calendar_quarter_desc, p.prod_category))
ORDER BY t.calendar_quarter_desc
, SUM(s.amount_sold);
QUARTER RANKING CHANNEL CATEGORY REVENUE
------- ------- -------- ---------------------------- --------------
2001-01 1 --all-- Software/Other $860,819.81
2001-01 2 --all-- Electronics $1,239,287.71
2001-01 3 --all-- Hardware $1,301,343.45
2001-01 4 --all-- Photo $1,370,706.38
2001-01 5 --all-- Peripherals and Accessories $1,774,940.09
2001-01 --all-- TOTAL $6,547,097.44
2001-02 1 --all-- Software/Other $872,157.38
2001-02 2 --all-- Electronics $1,144,187.90
2001-02 3 --all-- Hardware $1,557,059.59
2001-02 4 --all-- Photo $1,563,475.51
2001-02 5 --all-- Peripherals and Accessories $1,785,588.01
2001-02 --all-- TOTAL $6,922,468.39
2001-03 1 --all-- Software/Other $877,630.85
2001-03 2 --all-- Electronics $1,017,536.82
2001-03 3 --all-- Photo $1,607,315.63
2001-03 4 --all-- Hardware $1,651,454.29
2001-03 5 --all-- Peripherals and Accessories $2,042,061.04
2001-03 --all-- TOTAL $7,195,998.63
2001-04 1 --all-- Software/Other $943,296.36
2001-04 2 --all-- Hardware $1,174,512.68
2001-04 3 --all-- Electronics $1,303,838.52
2001-04 4 --all-- Photo $1,792,131.39
2001-04 5 --all-- Peripherals and Accessories $2,257,118.57
2001-04 --all-- TOTAL $7,470,897.52
TOTAL --all-- Software/Other $3,553,904.40
TOTAL --all-- Electronics $4,704,850.95
TOTAL --all-- Hardware $5,684,370.01
TOTAL --all-- Photo $6,333,628.91
TOTAL Indirect TOTAL $6,709,496.66
TOTAL --all-- Peripherals and Accessories $7,859,707.71
TOTAL Others TOTAL $8,038,529.96
TOTAL Direct TOTAL $13,388,435.36
TOTAL --all-- TOTAL $28,136,461.98
In this example, the PARTITION BY clause defines the boundaries for the RANK function.
共通のビジネス・インテリジェンス要求は、特定の時間間隔に基づく総収入に対する各製品カテゴリの寄与率を計算します。
四半期ベースで収入番号の差異を取得します。次の例のように、PARTITION BY製品カテゴリを持つウィンドウ関数を使用して実行できます。
総計に対する相対的な寄与率を計算する手順
SELECT DECODE(GROUPING(t.calendar_quarter_desc)
, 0, t.calendar_quarter_desc
, 1, 'TOTAL'
) quarter
, DECODE(GROUPING(t.calendar_quarter_desc) + GROUPING(p.prod_category)
, 0, RANK() OVER (PARTITION BY t.calendar_quarter_desc
ORDER BY SUM(s.amount_sold))
, 1, null
) RANKING
, DECODE(GROUPING(c.channel_class)
, 0, c.channel_class
, 1 , '--all--'
) channel
, DECODE(GROUPING(p.prod_category)
, 0, p.prod_category
, 1, 'TOTAL'
) category
, TO_CHAR(SUM(s.amount_sold),'L999G999G990D00') revenue
, TO_CHAR(100 * RATIO_TO_REPORT(SUM(s.amount_sold))
OVER (PARTITION BY (TO_CHAR(GROUPING(p.prod_category) ||
t.calendar_quarter_desc))),'990D0') percent
FROM times t
, products p
, channels c
, sales s
WHERE t.time_id = s.time_id
AND p.prod_id = s.prod_id
AND c.channel_id = s.channel_id
AND s.time_id BETWEEN TO_DATE('01-JAN-2001','dd-MON-yyyy')
AND TO_DATE('31-DEC-2001','dd-MON-yyyy')
GROUP BY GROUPING SETS(c.channel_class,
CUBE(t.calendar_quarter_desc, p.prod_category))
ORDER BY t.calendar_quarter_desc
, SUM(s.amount_sold);
QUARTER RANKING CHANNEL CATEGORY REVENUE PERC
------- ------- ------- ------------- ------------ ----
2001-01 1 --all-- Software/Other $860,819.81 13.1
2001-01 2 --all-- Electronics $1,239,287.71 18.9
2001-01 3 --all-- Hardware $1,301,343.45 19.9
2001-01 4 --all-- Photo $1,370,706.38 20.9
2001-01 5 --all-- Peripherals $1,774,940.09 27.1
2001-01 --all-- TOTAL $6,547,097.44 100.0
2001-02 1 --all-- Software/Other $872,157.38 12.6
2001-02 2 --all-- Electronics $1,144,187.90 16.5
2001-02 3 --all-- Hardware $1,557,059.59 22.5
2001-02 4 --all-- Photo $1,563,475.51 22.6
2001-02 5 --all-- Peripherals $1,785,588.01 25.8
2001-02 --all-- TOTAL $6,922,468.39 100.0
2001-03 1 --all-- Software/Other $877,630.85 12.2
2001-03 2 --all-- Electronics $1,017,536.82 14.1
2001-03 3 --all-- Photo $1,607,315.63 22.3
2001-03 4 --all-- Hardware $1,651,454.29 22.9
2001-03 5 --all-- Peripherals $2,042,061.04 28.4
2001-03 --all-- TOTAL $7,195,998.63 100.0
2001-04 1 --all-- Software/Other $943,296.36 12.6
2001-04 2 --all-- Hardware $1,174,512.68 15.7
2001-04 3 --all-- Electronics $1,303,838.52 17.5
2001-04 4 --all-- Photo $1,792,131.39 24.0
2001-04 5 --all-- Peripherals $2,257,118.57 30.2
2001-04 --all-- TOTAL $7,470,897.52 100.0
TOTAL --all-- Software/Other $3,553,904.40 12.6
TOTAL --all-- Electronics $4,704,850.95 16.7
TOTAL --all-- Hardware $5,684,370.01 20.2
TOTAL --all-- Photo $6,333,628.91 22.5
TOTAL Indirect TOTAL $6,709,496.66 11.9
TOTAL --all-- Peripherals $7,859,707.71 27.9
TOTAL Others TOTAL $8,038,529.96 14.3
TOTAL Direct TOTAL $13,388,435.36 23.8
TOTAL --all-- TOTAL $28,136,461.98 50.0
"Peripherals" was used instead of "Peripherals and Accessories" to save space.
ビジネス・インテリジェンスに共通する問題は、特定の結果がどのように他の結果と関連しているかということです。単一問合せでこれを行うには、ウィンドウ関数を使用して単一の文で行間計算を実行できます。
四半期ごとに、総収入に対する各製品カテゴリの寄与率を調べる場合があります。次の例に示すように、ウィンドウ関数RATIO_TO_REPORTを使用してこの結果を求めることができます。四半期ごとのRATIO_TO_REPORTから総計を除外するには、GROUPING(p.prod_category)を持つ連結を使用する必要があることに注意してください。
行間計算を行う手順
SELECT DECODE(GROUPING(t.calendar_quarter_desc)
, 0, t.calendar_quarter_desc
, 1, 'TOTAL'
) quarter
, DECODE(GROUPING(t.calendar_quarter_desc) + GROUPING(p.prod_category)
, 0, RANK() OVER (PARTITION BY t.calendar_quarter_desc
ORDER BY SUM(s.amount_sold))
, 1, null
) RANKING
, DECODE(GROUPING(c.channel_class)
, 0, c.channel_class
, 1 , '--all--'
) channel
, DECODE(GROUPING(p.prod_category)
, 0, p.prod_category
, 1, 'TOTAL'
) category
, TO_CHAR(SUM(s.amount_sold),'L999G999G990D00') revenue
, TO_CHAR(100 * RATIO_TO_REPORT(SUM(s.amount_sold))
OVER (PARTITION BY (TO_CHAR(GROUPING(p.prod_category) ||
t.calendar_quarter_desc))),'990D0') percent
, DECODE(GROUPING(t.calendar_quarter_desc) + GROUPING(p.prod_category)
, 0, TO_CHAR(SUM(s.amount_sold) - LAG(SUM(s.amount_sold),1)
OVER (PARTITION BY p.prod_category
ORDER BY t.calendar_quarter_desc),'L999G990D00')
, 1, null
) q_q_diff
FROM times t
, products p
, channels c
, sales s
WHERE t.time_id = s.time_id
AND p.prod_id = s.prod_id
AND c.channel_id = s.channel_id
AND s.time_id BETWEEN TO_DATE('01-JAN-2001','dd-MON-yyyy')
AND TO_DATE('31-DEC-2001','dd-MON-yyyy')
GROUP BY GROUPING SETS(c.channel_class,
CUBE(t.calendar_quarter_desc, p.prod_category))
ORDER BY t.calendar_quarter_desc
, SUM(s.amount_sold);
QUARTER RANKING CHANNEL CATEGORY REVENUE PERC Q_Q_DIFF
------- ------- ------- ------------- ------------ ---- ----------
2001-01 1 --all-- Software/Other $860,819.81 13.1
2001-01 2 --all-- Electronics $1,239,287.71 18.9
2001-01 3 --all-- Hardware $1,301,343.45 19.9
2001-01 4 --all-- Photo $1,370,706.38 20.9
2001-01 5 --all-- Peripherals $1,774,940.09 27.1
2001-01 --all-- TOTAL $6,547,097.44 100.0
2001-02 1 --all-- Software/Other $872,157.38 12.6 $11,337.57
2001-02 2 --all-- Electronics $1,144,187.90 16.5 -$95,099.81
2001-02 3 --all-- Hardware $1,557,059.59 22.5 $255,716.14
2001-02 4 --all-- Photo $1,563,475.51 22.6 $192,769.13
2001-02 5 --all-- Peripherals $1,785,588.01 25.8 $10,647.92
2001-02 --all-- TOTAL $6,922,468.39 100.0
2001-03 1 --all-- Software/Other $877,630.85 12.2 $5,473.47
2001-03 2 --all-- Electronics $1,017,536.82 14.1 -$126,651.08
2001-03 3 --all-- Photo $1,607,315.63 22.3 $43,840.12
2001-03 4 --all-- Hardware $1,651,454.29 22.9 $94,394.70
2001-03 5 --all-- Peripherals $2,042,061.04 28.4 $256,473.03
2001-03 --all-- TOTAL $7,195,998.63 100.0
2001-04 1 --all-- Software/Other $943,296.36 12.6 $65,665.51
2001-04 2 --all-- Hardware $1,174,512.68 15.7 -$476,941.61
2001-04 3 --all-- Electronics $1,303,838.52 17.5 $286,301.70
2001-04 4 --all-- Photo $1,792,131.39 24.0 $184,815.76
2001-04 5 --all-- Peripherals $2,257,118.57 30.2 $215,057.53
2001-04 --all-- TOTAL $7,470,897.52 100.0
TOTAL --all-- Software/Other $3,553,904.40 12.6
TOTAL --all-- Electronics $4,704,850.95 16.7
TOTAL --all-- Hardware $5,684,370.01 20.2
TOTAL --all-- Photo $6,333,628.91 22.5
TOTAL Indirect TOTAL $6,709,496.66 11.9
TOTAL --all-- Peripherals $7,859,707.71 27.9
TOTAL Others TOTAL $8,038,529.96 14.3
TOTAL Direct TOTAL $13,388,435.36 23.8
TOTAL --all-- TOTAL $28,136,461.98 50.0
"Peripherals" was used instead of "Peripherals and Accessories" to save space.
ウィンドウ関数を使用して移動集計を作成できます。移動関数は物理行の数に基づくか、論理時間間隔である可能性があります。ウィンドウ関数はPARTITIONキーワードを使用し、パーティションの各行に対してデータのウィンドウの変動を定義できます。このウィンドウでは現行の行の計算を実行するために使用する行の範囲を決定します。ウィンドウ・サイズは行の物理的な数または時間などの論理間隔に基づきます。ウィンドウには開始行および終了行が含まれます。ウィンドウは、その定義に応じて、片方の端または両方の端で移動できます。たとえば、累積SUM関数を定義したウィンドウには、パーティションの最初の行に固定された開始行が含まれ、終了行は開始点からパーティションの最後の行までスライドします。反対に、移動平均を定義したウィンドウには開始点と終了点の変動が含まれるため、定数の物理的範囲または論理的範囲が維持されます。
ウィンドウ関数は一般的に、移動および累積バージョンのSUM、AVERAGE、COUNT、MAX、MIN、および他の多くの関数の計算に使用されます。また、問合せのSELECT句、ORDER BY句でのみ使用できます。ウィンドウ関数には、ウィンドウで最初の値を戻すFIRST_VALUE関数、およびウィンドウで最終値を戻すLAST_VALUE関数が含まれます。これらの関数によって自己結合なしに表内の複数行にアクセスできます。
次の例は、論理的な時間間隔を使用した、各製品の製品収入の7日間の変動平均を取得する問合せを示しています。
変動平均を計算する手順は、次のとおりです。
SELECT time_id
, prod_name
, TO_CHAR(revenue,'L999G990D00') revenue
, TO_CHAR(AVG(revenue) OVER (PARTITION BY prod_name ORDER BY time_id
RANGE INTERVAL '7' DAY PRECEDING),'L999G990D00') mv_7day_avg
FROM
( SELECT s.time_id, p.prod_name, SUM(s.amount_sold) revenue
FROM products p
, sales s
WHERE p.prod_id = s.prod_id
AND s.time_id BETWEEN TO_DATE('25-JUN-2001','dd-MON-yyyy')
AND TO_DATE('16-JUL-2001','dd-MON-yyyy')
AND p.prod_name LIKE '%Memory%'
AND p.prod_category = 'Photo'
GROUP BY s.time_id, p.prod_name
)
ORDER BY time_id, prod_name;
TIME_ID PROD_NAME REVENUE MV_7DAY_AVG
--------- ----------------- ------------------ --------------
26-JUN-01 256MB Memory Card $560.15 $560.15
30-JUN-01 256MB Memory Card $844.00 $702.08
02-JUL-01 128MB Memory Card $3,283.74 $3,283.74
02-JUL-01 256MB Memory Card $3,903.32 $1,769.16
03-JUL-01 256MB Memory Card $699.37 $1,501.71
08-JUL-01 128MB Memory Card $3,283.74 $3,283.74
08-JUL-01 256MB Memory Card $3,903.32 $2,835.34
10-JUL-01 256MB Memory Card $138.82 $1,580.50
データは通常、スパースな形式で格納されています。つまり、ディメンション値の特定の組合せで値が存在しない場合、ファクト表(売上などの重要なファクトを含むデータ・ウェアハウスの表)には行が存在していません。しかし、ファクト・データが存在しない場合でも、ディメンション値のすべての組合せの行を表示し、データを稠密な形式で表示する必要がある場合があります。たとえば、製品が特定の期間販売されていない場合でも、その期間の売上値をゼロとして製品の横に表示する場合などです。さらに、データが時間ディメンションに沿って稠密であれば、時系列の計算を簡単に実行できます。これは、稠密なデータが期間ごとに一定数の行を占めているため、物理オフセットを指定した分析ウィンドウ関数の使用が単純化されるためです。
データの稠密化は、スパース・データを稠密な形式に変換するプロセスです。スパース性の問題を解決するために、パーティション外部結合を使用して時系列または他のディメンションとのギャップを埋めることができます。このタイプの結合は問合せで定義した各論理パーティションに外部結合を適用することで、従来の外部結合の構文を拡張したものです。Oracle Databaseは、PARTITION BY句で指定した式に基づいて、問合せの行を論理的にパーティション化します。パーティション外部結合の結果は、結合のもう一方の表を含む論理的にパーティション化された表にある各パーティションの外部結合のUNION操作です。時間ディメンションのみではなく他のディメンションのギャップを埋めるためにも、このタイプの結合を使用してください。
特定の製品の数週間の売上を表示する必要がある場合があります。この例では、フォト・カテゴリのメモリー・カードを使用します。これらの製品は頻繁に売れるものではなく、数週間売れないこともあるためです。簡単に比較するため、次の例のように、パーティション外部結合を使用してデータを稠密にする必要があります。
パーティション外部結合を使用する手順は、次のとおりです。
SELECT tim.week_ending_day
, rev.prod_name product
, nvl(SUM(rev.amount_sold),0) revenue
FROM (SELECT p.prod_name, s.time_id, s.amount_sold
FROM products p
, sales s
WHERE s.prod_id = p.prod_id
AND p.prod_category = 'Photo'
AND p.prod_name LIKE '%Memory%'
AND s.time_id BETWEEN TO_DATE('25-JUN-2001','dd-MON-yyyy')
AND TO_DATE('16-JUL-2001','dd-MON-yyyy')
) rev
PARTITION BY (prod_name)
RIGHT OUTER JOIN (SELECT time_id, week_ending_day FROM times
WHERE week_ending_day
BETWEEN TO_DATE('01-JUL-2001','dd-MON-yyyy')
AND TO_DATE('16-JUL-2001','dd-MON-yyyy')
) tim
ON (rev.time_id = tim.time_id)
GROUP BY tim.week_ending_day
, rev.prod_name
ORDER BY tim.week_ending_day
, rev.prod_name;
WEEK_ENDI PRODUCT REVENUE
--------- ---------------------------------------- ----------
01-JUL-01 128MB Memory Card 0
01-JUL-01 256MB Memory Card 1404.15
08-JUL-01 128MB Memory Card 6567.48
08-JUL-01 256MB Memory Card 8506.01
15-JUL-01 128MB Memory Card 0
15-JUL-01 256MB Memory Card 138.82
異なるタイプの結合およびウィンドウ関数を活用して多くの表にアクセスする問合せは、複雑になります。WITH句を使用すると、問合せの増分的な構築によりこの複雑さを解消できます。複雑な問合せで問合せブロックが複数回発生する場合、SELECT文の同じ問合せブロックを再使用できます。Oracle Databaseは問合せブロックの結果を取得して、ユーザーの一時表領域に結果を格納します。
2001年7月締めの最初の3週間のフォト・カテゴリのメモリー・カード製品の売上を比較すると想定します。次の問合せはこの期間に売れなかった一部の製品を考慮し、前の週と関連する売上で増加または減少を戻します。最終的に、問合せは特定の週におけるメモリー・カードの売上に対する寄与率の割合を取得します。WITH句の使用のために、問合せの個々のセクションの複雑さは解消されます。
WITH句を使用する手順は、次のとおりです。
WITH sales_numbers AS
( SELECT s.prod_id, s.amount_sold, t.week_ending_day
FROM sales s
, times t
, products p
WHERE s.time_id = t.time_id
AND s.prod_id = p.prod_id
AND p.prod_category = 'Photo'
AND p.prod_name LIKE '%Memory%'
AND t.week_ending_day BETWEEN TO_DATE('01-JUL-2001','dd-MON-yyyy')
AND TO_DATE('16-JUL-2001','dd-MON-yyyy')
)
, product_revenue AS
( SELECT p.prod_name product, s.week_ending_day, SUM(s.amount_sold) revenue
FROM products p
LEFT OUTER JOIN (SELECT prod_id, amount_sold, week_ending_day
FROM sales_numbers) s
ON (s.prod_id = p.prod_id)
WHERE p.prod_category = 'Photo'
AND p.prod_name LIKE '%Memory%'
GROUP BY p.prod_name, s.week_ending_day
)
, weeks AS
( SELECT distinct week_ending_day week FROM times WHERE week_ending_day
BETWEEN TO_DATE('01-JUL-2001','dd-MON-yyyy')
AND TO_DATE('16-JUL-2001','dd-MON-yyyy')
)
, complete_product_revenue AS
( SELECT w.week, pr.product, nvl(pr.revenue,0) revenue
FROM product_revenue pr
PARTITION BY (product)
RIGHT OUTER JOIN weeks w
ON (w.week = pr.week_ending_day)
)
SELECT week
, product
, TO_CHAR(revenue,'L999G990D00') revenue
, TO_CHAR(revenue - lag(revenue,1) OVER (PARTITION BY product
ORDER BY week),'L999G990D00') w_w_diff
, TO_CHAR(100 * RATIO_TO_REPORT(revenue) OVER (PARTITION BY week),'990D0') percentage
FROM complete_product_revenue
ORDER BY week, product;
WEEK PRODUCT REVENUE W_W_DIFF PERCENT
--------- ----------------- ------- -------- -------
01-JUL-01 128MB Memory Card $0.00 0.0
01-JUL-01 256MB Memory Card $1,404.15 100.0
01-JUL-01 64MB Memory Card $0.00 0.0
08-JUL-01 128MB Memory Card $6,567.48 $6,567.48 43.6
08-JUL-01 256MB Memory Card $8,506.01 $7,101.86 56.4
08-JUL-01 64MB Memory Card $0.00 $0.00 0.0
15-JUL-01 128MB Memory Card $0.00 -$6,567.48 0.0
15-JUL-01 256MB Memory Card $138.82 -$8,367.19 100.0
15-JUL-01 64MB Memory Card $0.00 $0.00 0.0
