ヘッダーをスキップ
Oracle Databaseデータ・ウェアハウス・ガイド
10gリリース2(10.2)
B19217-02
  目次へ
目次
索引へ
索引

前へ
前へ
次へ
次へ
 

24 高度なビジネス・インテリジェンス問合せ

この章では、高度なビジネス・インテリジェンス問合せを実行するためのテクニックについて説明します。この章は、様々なSQL機能を組み合せて複雑な分析を実行する方法をより深く理解することを目的としています。この章で扱う機能は、第20章「データ・ウェアハウスにおける集計のためのSQL」第21章「分析計算およびレポート用SQL関数」および第22章「モデリングのSQL」でも個別に説明していますが、各機能を個々に見ていくだけでは、それらを組み合せて使用する方法を十分に理解することはできません。ここでは、各機能を組み合せることによって、どのような分析が可能になるかを示します。

「高度な」ビジネス・インテリジェンス問合せとは一体何でしょうか。「高度な」という形容詞が最もふさわしいのは、複数のディメンション階層を伴うことの多い複数手順の問合せです。複数手順の問合せでは、最終的な結果は、複数セットの取得データおよび複数の計算手順によって導かれ、取得されるデータには、ディメンション階層の複数のレベルが含まれる場合があります。高度な問合せの主な例としては、複数の条件に基づく市場シェアの計算や、データのギャップ埋めを必要とする売上予測があげられます。

この章では、ネストしたインライン・ビュー、CASE式、パーティション外部結合、MODEL句、WITH句、分析SQL関数などの使用方法を、例を示しながら説明します。必要に応じて、問合せ計画についても説明します。この章の内容は次のとおりです。

ビジネス・インテリジェンス問合せの例

この章の問合せは、様々なビジネス・インテリジェンス・タスクを示しています。これらの問合せのトピックおよび各問合せで使用されている機能は次のとおりです。


例1 計算セット内での製品の市場シェアの変化率

この例では、収益が20%以上増加したアカウントについて、現在の3か月期間における売上上位20%の製品グループの市場シェアと、前年の同期間における市場シェアとの変化率を求めます。

ここでは、市場シェアを「総売上高に占める当該製品の売上高の割合」と定義します。このように定義するのは、shサンプル・スキーマに競合企業のデータがなく、自社製品と競合企業製品の売上を比較する通常のシェア計算を行うことができないためです。ただし、ここでのシェア計算に必要な作業は、競合的な市場シェアの計算と論理的にはそれほど変わりません。

次に、この問合せで取得する情報を示します。これらの情報は、この順序で取得する必要があります。

  1. 指定した3か月期間の間に、前年の同期間と比較して購入額が20%以上増加した都市。都市は1か国に限定され、売上に宣伝は関与していないことに注意してください。

  2. 前の手順で求めた都市グループにおける、売上上位20%の製品。つまり、この顧客グループ全体での製品別総売上高を求め、最も売上の多い製品20%を選択します。

  3. 前の手順で求めた各製品の売上高の割合。つまり、前の手順で求めた製品グループを使用して、全製品の総売上高に占める各製品の売上高の割合を求めます。前年の同期間における割合を求め、2つの年における割合の変化率を計算します。

この例で使用しているテクニックは次のとおりです。

WITH prod_list AS                     --START: Top 20% of products
   ( SELECT prod_id prod_subset, cume_dist_prod
    FROM                              --START: All products Sales for city subset
    ( SELECT s.prod_id, SUM(amount_sold),
         CUME_DIST() OVER (ORDER BY SUM(amount_sold)) cume_dist_prod
      FROM sales s, customers c, channels ch,  products p, times t
      WHERE s.prod_id = p.prod_id AND p.prod_total_id = 1 AND
            s.channel_id = ch.channel_id AND ch.channel_total_id = 1 AND
            s.cust_id = c.cust_id AND
            s.promo_id = 999 AND
            s.time_id  = t.time_id AND t.calendar_quarter_id = 1776 AND
            c.cust_city_id IN
       (SELECT cust_city_id            --START: Top 20% of cities
       FROM
         (
           SELECT cust_city_id, ((new_cust_sales - old_cust_sales)
                / old_cust_sales ) pct_change, old_cust_sales
           FROM
           (
              SELECT cust_city_id, new_cust_sales, old_cust_sales
              FROM
              (          --START: Cities AND sales for 1 country in 2 periods
                SELECT cust_city_id,
                  SUM(CASE WHEN t.calendar_quarter_id = 1776
                     THEN amount_sold  ELSE  0  END ) new_cust_sales,
                  SUM(CASE WHEN t.calendar_quarter_id = 1772
                     THEN amount_sold ELSE 0 END) old_cust_sales
                FROM sales s, customers c, channels ch,
                     products p, times t
                WHERE s.prod_id = p.prod_id AND p.prod_total_id = 1 AND
                      s.channel_id = ch.channel_id AND ch.channel_total_id = 1 AND
                      s.cust_id = c.cust_id AND c.country_id = 52790 AND
                      s.promo_id = 999 AND
                      s.time_id  = t.time_id AND
                     (t.calendar_quarter_id = 1776 OR t.calendar_quarter_id =1772)
                GROUP BY cust_city_id
              ) cust_sales_wzeroes
              WHERE old_cust_sales > 0
           )  cust_sales_woutzeroes
         )         --END: Cities and sales for country in 2 periods
         WHERE old_cust_sales > 0 AND  pct_change >= 0.20)
                                 --END: Top 20% of cities
GROUP BY s.prod_id
)  prod_sales                    --END: All products sales for city subset
    WHERE cume_dist_prod > 0.8   --END: Top 20% products
)
                                 --START: Main query bloc
SELECT  prod_id, ( (new_subset_sales/new_tot_sales)
              -    (old_subset_sales/old_tot_sales)
                 ) *100  share_changes
FROM
(                                --START: Total sales for country in later period
  SELECT  prod_id,
     SUM(CASE WHEN t.calendar_quarter_id = 1776
                   THEN amount_sold  ELSE  0  END )  new_subset_sales,
        (SELECT SUM(amount_sold) FROM sales s, times t, channels ch,
                customers c, countries co, products p
          WHERE s.time_id  = t.time_id AND t.calendar_quarter_id = 1776 AND
                s.channel_id = ch.channel_id AND ch.channel_total_id = 1 AND
                s.cust_id = c.cust_id AND
                c.country_id = co.country_id AND co.country_total_id = 52806 AND
                s.prod_id = p.prod_id AND p.prod_total_id = 1 AND
                s.promo_id = 999
        )   new_tot_sales,

                                --END: Total sales for country in later period
                                --START: Total sales for country in earlier period
     SUM(CASE WHEN t.calendar_quarter_id = 1772
                   THEN amount_sold  ELSE  0  END)  old_subset_sales,
        (SELECT SUM(amount_sold) FROM sales s, times t, channels ch,
                customers c, countries co, products p
          WHERE s.time_id  = t.time_id AND t.calendar_quarter_id = 1772 AND
                s.channel_id = ch.channel_id AND ch.channel_total_id = 1 AND
                s.cust_id = c.cust_id AND
                c.country_id = co.country_id AND co.country_total_id = 52806 AND
                       s.prod_id = p.prod_id AND p.prod_total_id = 1 AND
                s.promo_id = 999
        )   old_tot_sales
                                --END: Total sales for country in earlier period
 FROM sales s, customers c, countries co, channels ch, times t
 WHERE s.channel_id = ch.channel_id AND ch.channel_total_id = 1 AND
       s.cust_id = c.cust_id AND
       c.country_id = co.country_id AND co.country_total_id = 52806 AND
       s.promo_id = 999 AND
       s.time_id  = t.time_id AND
      (t.calendar_quarter_id = 1776 OR t.calendar_quarter_id = 1772)
         AND s.prod_id IN
     (SELECT prod_subset FROM prod_list)
  GROUP BY prod_id);

例2 欠損データを埋める売上予測

この問合せは、2000年と2001年の売上に基づいて2002年の売上を予測します。2000年と2001年の売上の最大の変化率を求めてから、これを2002年の売上に追加します。これは単純な計算ですが、1つ注意が必要な点があります。それは、多くの製品において、2000年と2001年に売上データのない月が存在することです。これらの空白値には、実際の売上が存在する月から算出した、その年の平均売上値を埋め込みます。またこの問合せでは、国別の通貨値をUSドルに変換します。最終的に、この問合せは2002年の予測値のみを戻します。

この例で使用しているテクニックは次のとおりです。

この例の処理は、通貨の変換係数の参照表を作成するところから始まります。この表は、各国の各月の変換係数を格納します。表に挿入する行を指定するために、クロス結合を使用している点に注目してください。変換係数を設定する国は、例の目的に合せて1か国(カナダ)のみとしています。

CREATE TABLE currency (
   country         VARCHAR2(20),
   year            NUMBER,
   month           NUMBER,
   to_us           NUMBER);

INSERT INTO currency
(SELECT distinct
SUBSTR(country_name,1,20), calendar_year, calendar_month_number, 1
FROM countries
CROSS JOIN times t
WHERE calendar_year IN (2000,2001,2002)
);
UPDATE currency set to_us=.74 WHERE country='Canada';

次に示すのは、予測用の問合せです。まず、2つの副次句を含むWITH句を指定しています。最初の副次句では、2000年、2001年および2002年の製品ごとの月次売上高を国別に求めています。2つ目の副次句では、月レベルの個別時間値のリストを求めています。

WITH  prod_sales_mo AS       --Product sales per month for one country
(
SELECT country_name c, prod_id p, calendar_year  y,
   calendar_month_number  m, SUM(amount_sold) s
FROM sales s, customers c, times t, countries cn, promotions p, channels ch
WHERE  s.promo_id = p.promo_id AND p.promo_total_id = 1 AND
       s.channel_id = ch.channel_id AND ch.channel_total_id = 1 AND
       s.cust_id=c.cust_id  AND
       c.country_id=cn.country_id AND country_name='France' AND
       s.time_id=t.time_id  AND t.calendar_year IN  (2000, 2001,2002)
GROUP BY cn.country_name, prod_id, calendar_year, calendar_month_number
),
                    -- Time data used for ensuring that model has all dates
time_summary AS(  SELECT DISTINCT calendar_year cal_y, calendar_month_number cal_m
  FROM times
  WHERE  calendar_year IN  (2000, 2001, 2002)
)
                   --START: main query block
SELECT c, p, y, m, s,  nr FROM (
SELECT c, p, y, m, s,  nr
FROM prod_sales_mo s
                   --Use partition outer join to make sure that each combination
                   --of country and product has rows for all month values
  PARTITION BY (s.c, s.p)
     RIGHT OUTER JOIN time_summary ts ON
        (s.m = ts.cal_m
         AND s.y = ts.cal_y
        )
MODEL
  REFERENCE curr_conversion ON
      (SELECT country, year, month, to_us
      FROM currency)
      DIMENSION BY (country, year y,month m) MEASURES (to_us)
                                --START: main model
   PARTITION BY (s.c c)
   DIMENSION BY (s.p p, ts.cal_y y, ts.cal_m m)
   MEASURES (s.s s, CAST(NULL AS NUMBER) nr,
             s.c cc ) --country is used for currency conversion
   RULES (
                      --first rule fills in missing data with average values
      nr[ANY, ANY, ANY]
         = CASE WHEN s[CV(), CV(), CV()] IS NOT NULL
              THEN s[CV(), CV(), CV()]
              ELSE ROUND(AVG(s)[CV(), CV(), m BETWEEN 1 AND 12],2)
           END,
                      --second rule calculates projected values for 2002
      nr[ANY, 2002, ANY] = ROUND(
         ((nr[CV(),2001,CV()] - nr[CV(),2000, CV()])
          / nr[CV(),2000, CV()]) * nr[CV(),2001, CV()]
         + nr[CV(),2001, CV()],2),
                      --third rule converts 2002 projections to US dollars
      nr[ANY,y != 2002,ANY]
         = ROUND(nr[CV(),CV(),CV()]
           * curr_conversion.to_us[ cc[CV(),CV(),CV()], CV(y), CV(m)], 2)
)
ORDER BY c, p, y, m)
WHERE y = '2002'
ORDER BY c, p, y, m;

例3 顧客のバケットへのグループ化による顧客分析

顧客を理解する1つの方法として重要なのは、顧客の購買パターンを調べ、顧客ごとの利益率を明らかにすることです。これは、その顧客に営業活動を行う価値があるかどうか、どのような活動が有効かを判断するのに役立ちます。shサンプル・スキーマのデータセットには多数の顧客が含まれているため、利益率の分析は、高レベルのビューから始めるのが適しています。ここでは、顧客利益率のヒストグラムのためのデータを求め、利益率を10の範囲(ヒストグラム分析では通常「バケット」と呼ばれる)に分割します。国ごとに、月の集計レベルで次のデータを求めます。

利益率バケットごとに、次のデータも求めます。

この例で使用しているテクニックは次のとおりです。

この問合せには、データ・ウェアハウスの設計に起因する分析上の問題点が含まれています。それは、shのデータには各トランザクションのエントリおよびトランザクション数のエントリが含まれていないため、これらの数値については仮定を設定する必要があるということです。この問合せでは、最小主義に則って、同じ日に単一の顧客が単一のチャネルを介して購入した製品をすべて同じトランザクションに属するものと見なします。実際には、同じ日に同じチャネルで複数の購買を行っている顧客もいるはずなので、このアプローチでは、トランザクション数は必然的に実際よりも少なくなります。

次に示す問合せは、先頭部分cust_prod_mon_profitに対するマテリアライズド・ビューを作成してから実行すべきであることに注意が必要です。また、そのマテリアライズド・ビューを作成する前に、索引を2つ作成しておくことも必要です。このような準備作業をしないままこの問合せを実行すると、場合によっては終了までにかなりの時間がかかります。必要となる2つの索引および問合せの主要部分は次のとおりです。

CREATE BITMAP INDEX costs_chan_bix
      ON costs (channel_id)
      LOCAL NOLOGGING COMPUTE STATISTICS;

CREATE BITMAP INDEX costs_promo_bix
      ON costs (promo_id)
      LOCAL NOLOGGING COMPUTE STATISTICS;

WITH cust_prod_mon_profit AS
-- profit by cust, prod, day, channel, promo
  (SELECT s.cust_id, s.prod_id, s.time_id,
          s.channel_id, s.promo_id,
          s.quantity_sold*(c.unit_price-c.unit_cost) profit,
          s.amount_sold dol_sold, c.unit_price price, c.unit_cost cost
   FROM sales s, costs c
   WHERE s.prod_id=c.prod_id
     AND s.time_id=c.time_id
     AND s.promo_id=c.promo_id
     AND s.channel_id=c.channel_id
     AND s.cust_id in (SELECT cust_id FROM customers cst
                       WHERE cst.country_id = 52770
     AND s.time_id IN (SELECT time_id FROM times t
                       WHERE t.calendar_month_desc = '2000-12'
   ),
-- Transaction Definition:  All products sold through a single channel to a
-- single cust on a single day are assumed to be sold in 1 transaction.
-- Some products in a transacton
-- may be on promotion
-- A customers  daily transaction amount is the sum of ALL products
-- purchased in the same channel in the same day
cust_daily_trans_amt  AS
(  SELECT cust_id, time_id, channel_id,  SUM(dol_sold) cust_daily_trans_amt
   FROM  cust_prod_mon_profit
   GROUP BY cust_id, time_id, channel_id
--A customers  monthly transaction count is the count of all channels
--used to purchase items in the same day, over all days in the month.
--It is really a count of the minimum possible number of transactions
cust_purchase_cnt  AS(  SELECT cust_id,  COUNT(*) cust_purchase_cnt
   FROM  cust_daily_trans_amt
   GROUP BY cust_id
),
--  Total profit for a customer over 1 month
cust_mon_profit AS
( SELECT cust_id, SUM(profit) cust_profit
       FROM  cust_prod_mon_profit
       GROUP BY cust_id
-- Minimum and maximum profit across all customer
-- sets endpoints for histogram data.
min_max_p AS
-- Note max profit + 0.1 to allow 10th bucket to include max value
(SELECT 0.1 + MAX(cust_profit) max_p, MIN(cust_profit) min_p
FROM cust_mon_profit),
-- Profitability bucket found for each customer
cust_bucket AS
(SELECT cust_id, cust_profit,
   width_bucket(cust_profit,
         min_max_p.min_p,
FROM cust_mon_profit,  min_max_p
-- Aggregated data needed for each bucket
histo_data AS
(  SELECT bucket,
     bucket*(( max_p-min_p) /10) top_end , count(*)  histo_count
   FROM  cust_bucket, min_max_p
   GROUP BY bucket, bucket*(( max_p - min_p) /10)
-- Median count of transactions per cust per month median_trans_count AS
-- Find median count of transactions per cust per month
(SELECT cust_bucket.bucket,
      PERCENTILE_CONT(0.5) WITHIN GROUP
          (ORDER BY cust_purchase_cnt.cust_purchase_cnt) median_trans_count
    FROM cust_bucket, cust_purchase_cnt
    WHERE cust_bucket.cust_id=cust_purchase_cnt.cust_id
    GROUP BY cust_bucket.bucket
-- Find Mmedian transaction size for custs by profit bucket
cust_median_trans_size AS
(  SELECT cust_bucket.bucket,
      PERCENTILE_CONT(0.5) WITHIN GROUP
          (ORDER BY cust_daily_trans_amt.cust_daily_trans_amt)
           cust_median_trans_ size
    FROM cust_bucket, cust_daily_trans_amt
    WHERE cust_bucket.cust_id=cust_daily_trans_amt.cust_id
    GROUP BY cust_bucket.bucket
-- Profitability of each product sold within each bucket
bucket_prod_profits AS
(  SELECT  cust_bucket.bucket, prod_id, SUM(profit) tot_prod_profit
   FROM  cust_bucket, cust_prod_mon_profit
   WHERE  cust_bucket.cust_id=cust_prod_mon_profit.cust_id
   GROUP BY cust_bucket.bucket, prod_id
),  -- Most and least profitable product by bucket
prod_profit AS
(  SELECT bucket, MIN(tot_prod_profit) min_profit_prod,
                  MAX(tot_prod_profit) max_profit_prod
   FROM bucket_prod_profits
   GROUP BY bucket
-- Main query block
SELECT  histo_data.bucket, histo_data.histo_count,
        median_trans_count.median_trans_count,
        cust_median_trans_size.cust_median_trans_size,
        prod_profit.min_profit_prod, prod_profit.max_profit_prod
FROM  histo_data, median_trans_count, cust_median_trans_size,
      prod_profit
WHERE  histo_data.bucket=median_trans_count.bucket
  AND  histo_data.bucket=cust_median_trans_size.bucket
  AND  histo_data.bucket=prod_profit.bucket;

例4 高頻度項目セット

あるマーケティング・マネージャが、自社製品の各種ホワイト・ペーパーについて、ユーザーがそれぞれのセッションでどのような種類のものをダウンロードするかを把握しようとしているとします。その場合このマネージャが必要としている情報は、ホワイト・ペーパーのどのようなグルーピングが頻度の高い項目であるかということです。そのような情報は、ダウンロードされたホワイト・ペーパーごとにユーザーIDとセッションIDがWebサイトのアクティビティ・ログに記録されていれば、すでに組み込まれている高頻度項目機能を使用することで容易に取得できます。まず、個々のホワイト・ペーパーについてダウンロード数を集計したリストを次に示します。(この例では、Oracle製品のホワイト・ペーパー名を使用しています。)

White paper titles                                               #
-------------------------------------------------------          ----
Table Compression in Oracle Database 10g                          696
Field Experiences with Large Data Warehouses                      439
Key Data Warehouse Features: A Comparative Performance Analysis   181
Materialized Views in Oracle Database 10g                         167
Parallel Execution in Oracle Database 10g                         166

次に示すのは、このような分析に使用できる問合せの一例です。この問合せには、テーブル・ファンクションとしてDBMS_FREQUENT_ITEMSET.FI_TRANSACTIONALが使用されています。問合せの構造の詳細は、『Oracle Database PL/SQLパッケージ・プロシージャおよびタイプ・リファレンス』を参照してください。この問合せでは、同一のセッションの中でダウンロードされたホワイト・ペーパーの組合せの項目セットが戻されます。

SELECT itemset, support, length, rnk
FROM
  (SELECT itemset, support, length,
   RANK(), OVER (PARTITION BY length ORDER BY support DESC) rnk
FROM
(SELECT CAST(itemset AS fi_char) itemset, support, length, total_tranx
 FROM table(DBMS_FREQUENT_ITEMSET.FI_TRANSACTIONAL
  (CURSOR(SELECT session_id, command
          FROM web_log
          WHERE time_stamp BETWEEN '01-APR-2002' AND '01-JUN-2002'),
          (60/2600), 2, 2, CURSOR(SELECT 'a' FROM DUAL WHERE 1=0),
          CURSOR(SELECT 'a' FROM DUAL WHERE 1=0)))))
   WHERE rnk <= 10;


上位3項目が次のような結果であったとします。

White paper titles                                                    #
---------------------------------------------------------         -----
Table Compression in Oracle Database 10g                            115
Field Experiences with Large Data Warehouses

Data Warehouse Performance Enhancements with Oracle Database 10g    109
Oracle Performance and Scalability in DSS Environments

Materialized Views in Oracle Database 10g                           107
Query Optimization in Oracle Database 10g

この分析からは、興味深い結果が導き出されています。個々のホワイト・ペーパーに関するダウンロード数のリストを見ると、「Table Compression in Oracle10g」が最上位にあります。したがって当然、ダウンロード数の多かったホワイト・ペーパーの組合せでも、その大部分に「Table Compression in Oracle10g」が含まれていると予想されます。しかし、上位3つの組合せのうち、このホワイト・ペーパーが含まれる組合せは1つしかありません。

高頻度項目セットを使用してWebのログ情報を分析すれば、ダウンロード数の多いホワイト・ペーパーを表示するだけの簡単なレポートよりもはるかに多くの情報を抽出することが可能になります。この結果から、このWebサイトの訪問者はセッションごとに特定のテーマに沿った情報を探す傾向が強いということがわかります。スケーラビリティに関心のある訪問者は、当然、圧縮についてのホワイト・ペーパーと大規模データ・ウェアハウスについてのホワイト・ペーパーをダウンロードし、複合問合せ機能に関心のある訪問者は、問合せの最適化についてのホワイト・ペーパーとマテリアライズド・ビューについてのホワイト・ペーパーをダウンロードすると考えられます。このような情報は、マーケティング・マネージャにとっては、将来的にどのような内容のホワイト・ペーパーを執筆すればよいかを判断するのに役立ち、Webデザイナーにとっては、Webサイトの構成についてのヒントとして役立ちます。

詳細は、「高頻度項目セット」を参照してください。