| Oracle® Databaseデータ・ウェアハウス・ガイド 12c リリース1 (12.1) B71318-06 |
|
 前 |
 次 |
この章では、高度なビジネス・インテリジェンス問合せを実行するためのテクニックについて説明します。この章は、様々なSQL機能を組み合せて複雑な分析を実行する方法をより深く理解することを目的としています。この章で扱う機能は、「データ・ウェアハウスにおける集計のためのSQL」、「分析計算およびレポート用SQL関数」および「モデリングのSQL」でも個別に説明していますが、各機能を個々に見ていくだけでは、それらを組み合せて使用する方法を十分に理解することはできません。ここでは、各機能を組み合せることによって、どのような分析が可能になるかを示します。
「高度な」ビジネス・インテリジェンス問合せとは一体何でしょうか。「高度な」という形容詞が最もふさわしいのは、複数のディメンション階層を伴うことの多い複数手順の問合せです。複数手順の問合せでは、最終的な結果は、複数セットの取得データおよび複数の計算手順によって導かれ、取得されるデータには、ディメンション階層の複数のレベルが含まれる場合があります。高度な問合せの主な例としては、複数の条件に基づく市場シェアの計算や、データのギャップ補完を必要とする売上予測があげられます。
この章では、ネストしたインライン・ビュー、CASE式、パーティション外部結合、MODEL句、WITH句、分析SQL関数などの使用方法を、例を示しながら説明します。必要に応じて、問合せ計画についても説明します。この章の内容は次のとおりです。
この章の問合せは、様々なビジネス・インテリジェンス・タスクを示しています。これらの問合せのトピックおよび各問合せで使用されている機能は次のとおりです。
複雑な複数手順の条件に基づく市場シェアの変化率。ネストしたインライン・ビュー、CASE式および分析SQL関数の使用方法を示します。
データのギャップ補完を伴う売上予測。MODEL句をパーティション外部結合およびCASE式と組み合せて使用する方法を示します。
ビジネス・インテリジェンス問合せの例2: 欠損データを補完する売上予測を参照してください。
購入額別のバケットに顧客をグループ化する顧客分析。WITH句(問合せの副ファクタリング)と、分析SQL関数percentile_contおよびwidth_bucketの使用方法を示します。
項目セットにグループ化する顧客項目。DBMS_FREQUENT_ITEMSET.FI_TRANSACTIONALをテーブル・ファンクションとして使用する高頻度項目セットの計算方法を示します。
ビジネス・インテリジェンス問合せの例4: 高頻度項目セットを参照してください。
この例では、収益が20%以上増加したアカウントについて、現在の3か月期間における売上上位20%を占める製品グループの市場シェアの、前年の同期間との変化率を求めます。
ここでは、市場シェアを「総売上高に占める当該製品の売上高の割合」と定義します。このように定義するのは、shサンプル・スキーマに競合企業のデータがなく、自社製品と競合企業製品の売上を比較する通常のシェア計算を行うことができないためです。ただし、ここでのシェア計算に必要な作業は、競合的な市場シェアの計算と論理的にはそれほど変わりません。
次に、この問合せで取得する情報を示します。これらの情報は、この順序で取得する必要があります。
この例で使用しているテクニックは次のとおりです。
この問合せは、WITH句およびネストしたインライン・ビューを使用して実行されます。各インライン・ビューにはそのデータ要素の内容を示す別名が付けられており、各インライン・ビューの境界はコメント行で示されています。インライン・ビューは高機能ですが、WITH句を最大限に使用するような形で問合せを構成すると、可読性およびメンテナンス性が大幅に向上します。
この問合せでは、WITH句の使用を控えめにしています。一部のネストしたインライン・ビューは、WITH句の独立した副次句として記述することも可能です。たとえば、メインの問合せでは、1つの値を戻すのに2つのインライン・ビューを使用しています。これらのインライン・ビューは、シェア計算の分母を戻すために使用されています。ここでインライン・ビューを使用せずに、その処理をWITH句で行うようにすると、可読性が向上します。顧客の購買分析に関するビジネス・インテリジェンス問合せの例3: 顧客のバケットへのグループ化による顧客分析を参照して、WITH句を最大限に使用する問合せと比較してください。
SUM関数に対する引数の中で、CASE式を使用している点に注目してください。これらのCASE式をWHERE句の後に追加のデータ・フィルタとして機能させることで、SQLの記述が簡潔になっています。またこれらの式によって、指定の日付の売上列と、別の日付の別の列を合計することが可能になっています。
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);
この問合せは、2000年と2001年の売上に基づいて2002年の売上を予測します。2000年と2001年の売上について最大の変化率を求めてから、これを2002年の売上に追加します。これは単純な計算ですが、1つ注意が必要な点があります。それは、多くの製品において、2000年と2001年に売上データのない月が存在することです。これらの空白値には、実際の売上が存在する月から算出した、その年の平均売上値を埋め込みます。またこの問合せでは、国別の通貨値をUSドルに変換します。最終的に、この問合せは2002年の予測値のみを戻します。
この例で使用しているテクニックは次のとおりです。
MODEL句の前に、クロス結合を使用して考え得るすべてのデータ行を事前定義することで、MODEL句で必要となる処理の量を減らしています。
通貨の変換を行うために、MODEL句で参照モデルを使用しています。
CV関数を多用することで、必要なルールの総数を3つにまで減らしています。
最も興味深い処理を行っているのは最後のルールです。ここでは、ネストしたルールを使用して通貨の変換係数を求めています。この式で必要となる国名を提供するため、参照モデルとメイン・モデルの両方で、国をそれぞれディメンションc、メジャーccとして定義しています。
この例の処理は、通貨の変換係数の参照表を作成するところから始まります。この表は、各国の各月の変換係数を格納します。表に挿入する行を指定するために、クロス結合を使用している点に注目してください。変換係数を設定する国は、例の目的に合せて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 partitioned 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;
顧客を理解する1つの方法として重要なのは、顧客の購買パターンを調べ、顧客ごとの利益率を明らかにすることです。これは、その顧客に営業活動を行う価値があるかどうか、どのような活動が有効かを判断するのに役立ちます。shサンプル・スキーマのデータセットには多数の顧客が含まれているため、利益率の分析は、高レベルのビューから始めるのが適しています。ここでは、顧客利益率のヒストグラムのためのデータを求め、利益率を10の範囲(ヒストグラム分析では通常「バケット」と呼ばれる)に分割します。国ごとに、月の集計レベルで次のデータを求めます。
10の等幅バケットからなる、顧客利益率のヒストグラムに必要なデータ。つまり、10の利益率バケットのそれぞれにグループ化される顧客の数を求めます。結果として取得される行は10行のみですが、大がかりな計算が必要になります。
利益率バケットごとに、次のデータも求めます。
顧客ごとの月間トランザクション数の中央値(同日の単一の顧客による単一のチャネルを介した購買を1つのトランザクションとして扱う)
顧客ごとの(各国通貨での)トランザクション・サイズの中央値
利益が最大の製品と最小の製品
トランザクション数の中央値とトランザクション・サイズの中央値の、前年からの変化率
この例で使用しているテクニックは次のとおりです。
WITH句を使用して問合せの可読性を向上させます。必要なデータを論理的なチャンクに分割し、各チャンクを専用のWITHの副次句で記述することで、ネストしたインライン・ビューと比較して可読性およびメンテナンス性が大幅に向上しています。WITH句を最大限に使用すると、メインのSELECT句で、取得したデータに対する計算を行わずに済みます。このことも、問合せの可読性およびメンテナンス性の向上に寄与しています。
等幅のヒストグラム・バケットを得るためのwidth_bucket、およびトランザクション・サイズとトランザクション数の中央値を求めるためのpercentile_contという2つの分析SQL関数を使用します。
この問合せには、データ・ウェアハウスの設計に起因する分析上の問題点が含まれています。それは、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;
あるマーケティング・マネージャが、自社製品の各種ホワイト・ペーパーについて、ユーザーがそれぞれのセッションでどのような種類のものをダウンロードするかを把握しようとしているとします。その場合このマネージャが必要としている情報は、ホワイト・ペーパーのどのようなグルーピングが頻度の高い項目であるかということです。そのような情報は、ダウンロードされたホワイト・ペーパーごとにユーザー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」がすべてのホワイト・ペーパーの中で最上位にあるので、ダウンロード数の多かったホワイト・ペーパーの組合せでも、その大部分にこのホワイト・ペーパーが含まれていると予想されます。しかし、上位3つの組合せのうち、このホワイト・ペーパーが含まれる組合せは1つしかありません。
高頻度項目セットを使用してWebのログ情報を分析すれば、ダウンロード数の多いホワイト・ペーパーを表示するだけの簡単なレポートよりもはるかに多くの情報を抽出することが可能になります。この結果から、このWebサイトの訪問者はセッションごとに特定のテーマに沿った情報を探す傾向が強いということがわかります。スケーラビリティに関心のある訪問者は、当然、圧縮についてのホワイト・ペーパーと大規模データ・ウェアハウスについてのホワイト・ペーパーをダウンロードし、複合問合せ機能に関心のある訪問者は、問合せの最適化についてのホワイト・ペーパーとマテリアライズド・ビューについてのホワイト・ペーパーをダウンロードすると考えられます。このような情報は、マーケティング・マネージャにとっては、将来的にどのような内容のホワイト・ペーパーを執筆すればよいかを判断するのに役立ち、Webデザイナにとっては、Webサイトの構成についてのヒントとして役立ちます。
詳細は、SQL分析での高頻度項目セットを参照してください。
