ヘッダーをスキップ
Oracle Databaseデータ・ウェアハウス・ガイド
11gリリース1(11.1)
E05763-01
  目次へ
目次
索引へ
索引

前へ
前へ
 
次へ
次へ
 

21 分析計算およびレポート用SQL関数

この章では、データ・ウェアハウスにおける分析SQL問合せの改善方法について説明します。内容は次のとおりです。

分析計算およびレポート用SQL関数の概要

Oracleでは、新しい分析用SQL関数ファミリの導入により、SQLの分析処理機能が強化されています。このような分析用の関数を使用すると、次の計算が可能です。

ランキング関数には、累積分散、パーセント・ランクおよびNタイルなどがあります。変動ウィンドウの計算では、合計や平均などの変動集計および累積集計の検索が可能になります。LAG/LEAD分析では、行間の直接参照が可能になるため、周期ごとの変更を計算できます。FIRST/LAST分析では、順序付けされたグループ内の最初の値または最後の値を検索できます。

SQLに対するその他の拡張には、CASE式とパーティション外部結合があります。CASE式では、様々な状況で有効なif-thenロジックが提供されます。パーティション外部結合は、特定のディメンションを選択的に稠密化しながら、その他のディメンションをスパースなままにできるANSI外部結合構文の拡張です。これにより、たとえば、その他のディメンションはスパースなままで、クロス集計レポートに表示されるディメンションをレポート・ツールで選択的に稠密化できます。

パフォーマンスを向上させるには、分析関数をパラレル化する方法があります。つまり、複数のプロセスですべての文を同時に実行できます。こうした機能によって計算がより容易かつ効率的になるので、データベースのパフォーマンス、拡張性および簡易性が向上します。

分析関数は、表21-1に示すように分類されます。

表21-1 分析関数およびその使用目的

種類 使用目的

ランキング

結果セットのランク、パーセンタイルおよびnタイルの値を計算する。

ウィンドウ

累積集計および変動集計を計算する。SUMAVGMINMAXCOUNTVARIANCESTDDEVFIRST_VALUELAST_VALUEおよび新しい統計関数とともに動作する。DISTINCTキーワードはMAXおよびMINを除くウィンドウ関数ではサポートされない。

レポート

市場占有率などのシェアを計算する。SUMAVGMINMAXCOUNTDISTINCT付き/なし)、VARIANCESTDDEVRATIO_TO_REPORTおよび新しい統計関数とともに動作する。集計モードでDISTINCTキーワードがサポートされるレポート関数では、DISTINCTを使用できる。

LAG/LEAD

現在行から指定した行数を移動した行の値を検索する。

FIRST/LAST

順序付けされたグループ内の最初または最後の値。

線形回帰

線形回帰およびその他の統計情報(傾き、切片など)を計算する。

逆パーセンタイル

データセット内で指定されたパーセンタイルと一致する値。

仮説ランクおよび仮説分布

行が指定されたデータセットに挿入された場合に与えられるランクまたはパーセンタイル。


こうした処理を行うため、分析関数ではSQL処理に新しい要素がいくつか追加されています。これらの要素は、既存のSQL上に作成され、柔軟で強力な計算式を可能にします。分析関数には、いくつかの例外を除きこの新しい要素群が含まれます。図21-1に、処理フローを表します。

次に、分析関数における重要な概念を示します。

図21-2 スライド・ウィンドウの例

図21-2の説明は図の下のリンクをクリックしてください。
「図21-2 スライド・ウィンドウの例」の説明

ランキング関数

ランキング関数では、メジャーの集合の値に基づいたデータセット内の他のレコードとの比較により、レコードのランクが計算されます。ランキング関数の種類を次に示します。

RANK関数およびDENSE_RANK関数

RANK関数およびDENSE_RANK関数では、グループ内での項目のランク付けができます。たとえば、昨年カリフォルニアでよく売れた製品の上位3位までを検索する場合などに利用できます。次の構文で示すように、ランキングを実行する関数には2種類あります。

RANK ( ) OVER ( [query_partition_clause] order_by_clause )
DENSE_RANK ( ) OVER ( [query_partition_clause] order_by_clause )

RANKDENSE_RANKの違いは、同じ値の項目がある場合、DENSE_RANKではランキングの順序に抜けができないという点です。つまり、ある競争についてDENSE_RANKを使用してランキングした結果、3人が同点で第2位であった場合、3人全員が第2位となり、次点の人が第3位になります。RANK関数でも3人全員が第2位になりますが、次点の人は第5位になります。

次に、RANKに関する注意点を示します。

  • デフォルトのソート順は昇順です。必要に応じて降順に変更できます。

  • オプションのPARTITION BY句の式を使用すると、問合せ結果セットがRANK関数の適用範囲となるグループ群に分割されます。つまり、グループが変更されるたびに、RANKがリセットされます。実際には、PARTITION BY句の値式でリセットの境界が定義されます。

  • PARTITION BY句がない場合、ランクは問合せ結果セット全体にわたって計算されます。

  • ORDER> BY句によって、ランキングが実行されるメジャー(<value expression>)が指定され、各グループ(またはパーティション)でソートされる行の順序が定義されます。各パーティション内でデータがソートされると、各行が1からランク付けされます。

  • NULLS FIRST | NULLS LAST句によって、順序付けされた行セットでNULLの位置が最初になるか最後になるかが示されます。順序付けによって、NULLが、NULL以外の値より高いか低いかが比較されます。順序が昇順であった場合、NULLS FIRSTNULLが他のどのNULL以外の値よりも小さいことを示し、NULLS LASTNULL以外の値よりも大きいことを示します。降順では、その逆になります。「NULLの処理」の例を参照してください。

  • NULLS FIRST | NULLS LAST句が省略されている場合、NULL値の順序付けはASC引数またはDESC引数に依存します。NULL値は、他のどの値よりも大きいとみなされます。順序付け順序がASCの場合、NULLは最後に表示されます。それ以外の場合は、最初に表示されます。NULLは他のNULLと同等とみなされるため、NULLが表示されている順序は確定的ではありません。

ランキング順序

次の例では、[ASC | DESC]オプションによるランキング順序の変化を示します。

例21-1 ランキング順序

SELECT channel_desc, TO_CHAR(SUM(amount_sold), '9,999,999,999') SALES$,
   RANK() OVER (ORDER BY SUM(amount_sold)) AS default_rank,
   RANK() OVER (ORDER BY SUM(amount_sold) DESC NULLS LAST) AS custom_rank
FROM sales, products, customers, times, channels, countries
WHERE sales.prod_id=products.prod_id AND sales.cust_id=customers.cust_id
  AND customers.country_id = countries.country_id AND sales.time_id=times.time_id
  AND sales.channel_id=channels.channel_id
  AND times.calendar_month_desc IN ('2000-09', '2000-10')
  AND country_iso_code='US'
GROUP BY channel_desc;

CHANNEL_DESC         SALES$         DEFAULT_RANK CUSTOM_RANK
-------------------- -------------- ------------ -----------
Direct Sales              1,320,497            3           1
Partners                    800,871            2           2
Internet                    261,278            1           3

この結果のデータはメジャーSALES$で順序付けされていますが、一般にRANK関数では、データがメジャーでソートされるという保証はありません。結果のデータがSALES$でソートされるようにするには、SELECT文の最後にORDER BY句で明示的にソートを指定する必要があります。

複数の式でのランキング

ランキング関数では、セット内にある同じ内容の値について解決する必要があります。最初の式で同じ内容の値が解決されない場合、2番目の式が同じ内容の値の解決に使用され、以降同様に続きます。たとえば、売上高(ドル)に基づいて2か月間の販売チャネルのうち3つをランキングし、単位売上で同じ内容の値を解決する問合せの例を次に示します(ここでは、この問合せ用に同じ内容の値を作成するためだけにTRUNC関数を使用しています)。

例21-2 複数の式でのランキング

SELECT channel_desc, calendar_month_desc, TO_CHAR(TRUNC(SUM(amount_sold),-5),
  '9,999,999,999') SALES$, TO_CHAR(SUM(quantity_sold), '9,999,999,999')
  SALES_Count, RANK() OVER (ORDER BY TRUNC(SUM(amount_sold), -5)
  DESC, SUM(quantity_sold) DESC) AS col_rank
FROM sales, products, customers, times, channels
WHERE sales.prod_id=products.prod_id AND sales.cust_id=customers.cust_id
  AND sales.time_id=times.time_id AND sales.channel_id=channels.channel_id
  AND times.calendar_month_desc IN ('2000-09', '2000-10')
  AND channels.channel_desc<>'Tele Sales'
GROUP BY channel_desc, calendar_month_desc;

CHANNEL_DESC         CALENDAR SALES$         SALES_COUNT     COL_RANK
-------------------- -------- -------------- -------------- ---------
Direct Sales         2000-10       1,200,000         12,584          1
Direct Sales         2000-09       1,200,000         11,995          2
Partners             2000-10         600,000          7,508          3
Partners             2000-09         600,000          6,165          4
Internet             2000-09         200,000          1,887          5
Internet             2000-10         200,000          1,450          6

sales_count列で、3組の同じ内容の値が解決されています。

RANKとDENSE_RANKの違い

RANK関数とDENSE_RANK関数の違いを例21-3に示します。

例21-3 RANKとDENSE_RANK

SELECT channel_desc, calendar_month_desc,
   TO_CHAR(TRUNC(SUM(amount_sold),-4), '9,999,999,999') SALES$,
      RANK() OVER (ORDER BY TRUNC(SUM(amount_sold),-4) DESC) AS RANK,
DENSE_RANK() OVER (ORDER BY TRUNC(SUM(amount_sold),-4) DESC) AS DENSE_RANK
FROM sales, products, customers, times, channels
WHERE sales.prod_id=products.prod_id AND sales.cust_id=customers.cust_id
  AND sales.time_id=times.time_id AND sales.channel_id=channels.channel_id
  AND times.calendar_month_desc IN ('2000-09', '2000-10')
  AND channels.channel_desc<>'Tele Sales'
GROUP BY channel_desc, calendar_month_desc;

CHANNEL_DESC         CALENDAR SALES$              RANK DENSE_RANK
-------------------- -------- -------------- --------- ----------
Direct Sales         2000-09       1,200,000          1          1
Direct Sales         2000-10       1,200,000          1          1
Partners             2000-09         600,000          3          2
Partners             2000-10         600,000          3          2
Internet             2000-09         200,000          5          3
Internet             2000-10         200,000          5          3

DENSE_RANKでは、最大のランク値がデータセット内の個別値の数を示すことに注意します。

グループごとのランキング

RANK関数は、グループ内部を処理対象として使用できます。この場合、グループが変更されるたびに、ランクがリセットされます。これを可能にするには、PARTITION BY句を使用します。PARTITION BY副次句のグループ式を使用すると、データセットが、RANKの操作対象となるグループに分割されます。たとえば、ドル単位の売上高によって各チャネル内で製品を順序付ける場合は、次の文を発行できます。

例21-4 グループごとのランキングの例1

SELECT channel_desc, calendar_month_desc, TO_CHAR(SUM(amount_sold),
 '9,999,999,999') SALES$, RANK() OVER (PARTITION BY channel_desc
  ORDER BY SUM(amount_sold) DESC) AS RANK_BY_CHANNEL
FROM sales, products, customers, times, channels
WHERE sales.prod_id=products.prod_id AND sales.cust_id=customers.cust_id
  AND sales.time_id=times.time_id AND sales.channel_id=channels.channel_id
  AND times.calendar_month_desc IN ('2000-08', '2000-09', '2000-10', '2000-11')
  AND channels.channel_desc IN ('Direct Sales', 'Internet')
GROUP BY channel_desc, calendar_month_desc;

単一の問合せブロックに、複数のランキング関数を含めることができます。これらの各関数によって、種々のグループにデータがパーティション化(種々の境界上でリセット)されます。そうしたグループは、相互に排他的にできます。次の問合せでは、各月(rank_of_product_per_region)および各チャネル(rank_of_product_total)内において、ドル単位の売上高に基づき製品が順序付けされます。

例21-5 グループごとのランキングの例2

SELECT channel_desc, calendar_month_desc, TO_CHAR(SUM(amount_sold),
  '9,999,999,999') SALES$, RANK() OVER (PARTITION BY calendar_month_desc
  ORDER BY SUM(amount_sold) DESC) AS RANK_WITHIN_MONTH, RANK() OVER (PARTITION
 BY channel_desc ORDER BY SUM(amount_sold) DESC) AS RANK_WITHIN_CHANNEL
FROM sales, products, customers, times, channels, countries
WHERE sales.prod_id=products.prod_id AND sales.cust_id=customers.cust_id
  AND customers.country_id = countries.country_id AND sales.time_id=times.time_id
  AND sales.channel_id=channels.channel_id
  AND times.calendar_month_desc IN ('2000-08', '2000-09', '2000-10', '2000-11')
  AND channels.channel_desc IN ('Direct Sales', 'Internet')
GROUP BY channel_desc, calendar_month_desc;

CHANNEL_DESC  CALENDAR    SALES$     RANK_WITHIN_MONTH  RANK_WITHIN_CHANNEL
------------- --------    ---------  -----------------  -------------------
Direct Sales   2000-08    1,236,104                  1                    1
Internet       2000-08      215,107                  2                    4
Direct Sales   2000-09    1,217,808                  1                    3
Internet       2000-09      228,241                  2                    3
Direct Sales   2000-10    1,225,584                  1                    2
Internet       2000-10      239,236                  2                    2
Direct Sales   2000-11    1,115,239                  1                    4
Internet       2000-11      284,742                  2                    1

CUBEグループおよびROLLUPグループごとのランキング

RANKなどの分析関数は、CUBEROLLUPまたはGROUPING SETS演算子によるグルーピングに基づいて再設定されます。これは、CUBEROLLUPおよびGROUPING SETS問合せで作成されたグループにランクを割り当てる場合に有効です。GROUPING関数の詳細は、第20章「データ・ウェアハウスにおける集計のためのSQL」を参照してください。

CUBEおよびROLLUP問合せの例を次に示します。

SELECT channel_desc, country_iso_code,
  TO_CHAR(SUM(amount_sold), '9,999,999,999')
 SALES$, RANK() OVER (PARTITION BY GROUPING_ID(channel_desc, country_iso_code)
    ORDER BY SUM(amount_sold) DESC) AS RANK_PER_GROUP
FROM sales, customers, times, channels, countries
WHERE sales.time_id=times.time_id AND sales.cust_id=customers.cust_id
  AND sales.channel_id = channels.channel_id AND channels.channel_desc
   IN ('Direct Sales', 'Internet') AND times.calendar_month_desc='2000-09'
   AND country_iso_code IN ('GB', 'US', 'JP')
GROUP BY CUBE(channel_desc, country_iso_code);

CHANNEL_DESC         CO SALES$         RANK_PER_GROUP
-------------------- -- -------------- --------------
Direct Sales         GB      1,217,808              1
Direct Sales         JP      1,217,808              1
Direct Sales         US      1,217,808              1
Internet             GB        228,241              4
Internet             JP        228,241              4
Internet             US        228,241              4
Direct Sales                 3,653,423              1
Internet                       684,724              2
                     GB      1,446,049              1
                     JP      1,446,049              1
                     US      1,446,049              1
                             4,338,147              1

NULLの処理

NULLは、通常の値と同様に処理されます。また、ランクの計算では、NULL値は別のNULL値と同等であると想定されています。NULLは、メジャーに設定したASC | DESCオプション、およびNULLS FIRST | NULLS LAST句に従って高低にソートされ、適切にランク付けされます。次の例では、NULLが様々なケースにおいてどのようにランク付けされるかを示します。

SELECT  times.time_id time,  sold,
  RANK() OVER (ORDER BY (sold) DESC NULLS LAST) AS NLAST_DESC,
  RANK() OVER (ORDER BY (sold) DESC NULLS FIRST) AS NFIRST_DESC,
  RANK() OVER (ORDER BY (sold) ASC NULLS FIRST) AS NFIRST,
  RANK() OVER (ORDER BY (sold) ASC NULLS LAST) AS NLAST
FROM
  (
   SELECT time_id, SUM(sales.amount_sold)  sold
   FROM sales, products, customers, countries
   WHERE sales.prod_id=products.prod_id
    AND customers.country_id = countries.country_id
    AND sales.cust_id=customers.cust_id
    AND prod_name IN ('Envoy Ambassador', 'Mouse Pad') AND country_iso_code ='GB'
   GROUP BY  time_id)
 v, times
WHERE v.time_id (+) = times.time_id
  AND calendar_year=1999
  AND calendar_month_number=1
ORDER BY sold  DESC NULLS LAST;

TIME            SOLD NLAST_DESC NFIRST_DESC     NFIRST      NLAST
--------- ---------- ---------- ----------- ---------- ----------
25-JAN-99    3097.32          1          18         31         14
17-JAN-99    1791.77          2          19         30         13
30-JAN-99     127.69          3          20         29         12
28-JAN-99     120.34          4          21         28         11
23-JAN-99      86.12          5          22         27         10
20-JAN-99      79.07          6          23         26          9
13-JAN-99       56.1          7          24         25          8
07-JAN-99      42.97          8          25         24          7
08-JAN-99      33.81          9          26         23          6
10-JAN-99      22.76         10          27         21          4
02-JAN-99      22.76         10          27         21          4
26-JAN-99      19.84         12          29         20          3
16-JAN-99      11.27         13          30         19          2
14-JAN-99       9.52         14          31         18          1
09-JAN-99                    15           1          1         15
12-JAN-99                    15           1          1         15
31-JAN-99                    15           1          1         15
11-JAN-99                    15           1          1         15
19-JAN-99                    15           1          1         15
03-JAN-99                    15           1          1         15
15-JAN-99                    15           1          1         15
21-JAN-99                    15           1          1         15
24-JAN-99                    15           1          1         15
04-JAN-99                    15           1          1         15
06-JAN-99                    15           1          1         15
27-JAN-99                    15           1          1         15
18-JAN-99                    15           1          1         15
01-JAN-99                    15           1          1         15
22-JAN-99                    15           1          1         15
29-JAN-99                    15           1          1         15
05-JAN-99                    15           1          1         15

ボトムNランキング

ボトムNはトップNに似ていますが、ランク式内の順序付け順序が異なります。前述の例の場合でいうと、降順のかわりに昇順でSUM(s_amount)を順序付けできます。

CUME_DIST関数

CUME_DIST関数(統計書によっては、パーセンタイルの逆と定義されている関数)によって、値の集合に対する特定の値の相対位置が計算されます。この順序は、昇順または降順にできます。デフォルトは昇順です。CUME_DISTで戻される値の範囲は、0(ゼロ)〜1です。サイズNの集合Sに含まれる値XのCUME_DISTを計算するには、次の計算式を使用します。

CUME_DIST(x) =  number of values in S coming before
   and including x in the specified order/ N


構文は次のとおりです。

CUME_DIST ( ) OVER ( [query_partition_clause] order_by_clause )

CUME_DIST関数の様々なオプションは、意味的にはRANK関数のオプションと同様です。デフォルトの順序は昇順であり、最小値が最小のCUME_DISTを取得します(他のすべての値はこの値の後に順序付けされます)。NULLは、RANK関数の場合と同様に処理されます。NULL以外の値と同様に処理されるため、分子および分母の両方が考慮されます。次の例では、各月におけるチャネル別の売上の累積分布がわかります。

SELECT calendar_month_desc AS MONTH, channel_desc,
     TO_CHAR(SUM(amount_sold) , '9,999,999,999') SALES$,
     CUME_DIST() OVER (PARTITION BY calendar_month_desc ORDER BY
         SUM(amount_sold) ) AS CUME_DIST_BY_CHANNEL
FROM sales, products, customers, times, channels
WHERE sales.prod_id=products.prod_id AND sales.cust_id=customers.cust_id
  AND sales.time_id=times.time_id AND sales.channel_id=channels.channel_id
  AND times.calendar_month_desc IN ('2000-09', '2000-07','2000-08')
GROUP BY calendar_month_desc, channel_desc;

MONTH    CHANNEL_DESC         SALES$         CUME_DIST_BY_CHANNEL
-------- -------------------- -------------- --------------------
2000-07  Internet                    140,423           .333333333
2000-07  Partners                    611,064           .666666667
2000-07  Direct Sales              1,145,275                    1
2000-08  Internet                    215,107           .333333333
2000-08  Partners                    661,045           .666666667
2000-08  Direct Sales              1,236,104                    1
2000-09  Internet                    228,241           .333333333
2000-09  Partners                    666,172           .666666667
2000-09  Direct Sales              1,217,808                    1

PERCENT_RANK関数

PERCENT_RANKCUME_DISTと似ていますが、分子に行カウントではなくランク値が使用されます。したがって、PERCENT_RANKでは値グループに対する値の相対的なパーセント・ランクが戻されます。この関数は、一般的なスプレッドシートで使用できます。ある行のPERCENT_RANKは次のように計算されます。

(rank of row in its partition - 1) / (number of rows in the partition - 1)

PERCENT_RANKでは、0(ゼロ)〜1の範囲の値が戻されます。ランク1の行は、PERCENT_RANKが0(ゼロ)になります。構文は次のとおりです。

PERCENT_RANK () OVER ([query_partition_clause] order_by_clause)

NTILE関数

NTILEを使用すると、三分位数、四分位数、十分位数およびその他の一般的な集計統計情報を簡単に計算できます。この関数では、順序付けられたパーティションがバケットと呼ばれる特定数のグループに分割され、バケット番号がパーティションの各行に割り当てられます。NTILE計算は、データセットを4分割、3分割およびその他のグループ数に分割できるため、非常に便利です。

各バケットは、それぞれに同数の行が割り当てられるか、他のバケットとの差が最大でも1行となるように計算されます。たとえば、パーティションに100の行があって、バケットが4つになるようにNTILE関数に要求した場合、最初の25行に1の値、次の25行に2の値が割り当てられ、残りも同様に割り当てられます。これらのバケットは、等度数バケットと呼ばれます。

パーティションの行数が所定のバケット数に等分に(余りなく)分割されない場合、各バケットに割り当てられる行数には最大で1行の差ができます。余りの行は、バケット番号が最小のバケットから順に、バケットごとに1行ずつ分配されます。たとえば、NTILE(5)関数を持つパーティションに103の行がある場合、最初の21行は第1バケットに、次の21行は第2バケットに、次の21行は第3バケットに、次の20行は第4バケットに、最後の20行は第5バケットに分割されます。

NTILE関数の構文は次のとおりです。

NTILE (expr) OVER ([query_partition_clause] order_by_clause)

NTILE(N)のNは、定数(5など)または式になります。

この関数では、RANKCUME_DISTと同様に、グループごとの計算用にPARTITION BY句、メジャーおよびそのソート順序の指定用にORDER BY句、および特定のNULL処理用にNULLS FIRST | NULLS LAST句を使用できます。例として、各月の総売上を4つのバケットのそれぞれに割り当てる場合を次に示します。

SELECT calendar_month_desc AS MONTH , TO_CHAR(SUM(amount_sold),
 '9,999,999,999')
 SALES$, NTILE(4) OVER (ORDER BY SUM(amount_sold)) AS TILE4
FROM sales, products, customers, times, channels
WHERE sales.prod_id=products.prod_id AND sales.cust_id=customers.cust_id
  AND sales.time_id=times.time_id AND sales.channel_id=channels.channel_id
  AND times.calendar_year=2000 AND prod_category= 'Electronics'
GROUP BY calendar_month_desc;

MONTH    SALES$              TILE4
-------- -------------- ----------
2000-02         242,416          1
2000-01         257,286          1
2000-03         280,011          1
2000-06         315,951          2
2000-05         316,824          2
2000-04         318,106          2
2000-07         433,824          3
2000-08         477,833          3
2000-12         553,534          3
2000-10         652,225          4
2000-11         661,147          4
2000-09         691,449          4

再生可能な結果を得るには、NTILE ORDER BY文を完全に指定する必要があります。同等の値は、隣接バケット間で分散されます。確定的な結果を確実に得るには、一意キーで順序付けを行う必要があります。

ROW_NUMBER関数

ROW_NUMBER関数では、(ORDER BYで定義されたとおり、1から順番に)一意の番号がパーティション内の各行に割り当てられます。構文は次のとおりです。

ROW_NUMBER ( ) OVER ( [query_partition_clause] order_by_clause )

例21-6 ROW_NUMBER

SELECT channel_desc, calendar_month_desc,
   TO_CHAR(TRUNC(SUM(amount_sold), -5), '9,999,999,999') SALES$,
   ROW_NUMBER() OVER (ORDER BY TRUNC(SUM(amount_sold), -6) DESC) AS ROW_NUMBER
FROM sales, products, customers, times, channels
WHERE sales.prod_id=products.prod_id AND sales.cust_id=customers.cust_id
  AND sales.time_id=times.time_id AND sales.channel_id=channels.channel_id
  AND times.calendar_month_desc IN ('2001-09', '2001-10')
GROUP BY channel_desc, calendar_month_desc;

CHANNEL_DESC         CALENDAR SALES$         ROW_NUMBER
-------------------- -------- -------------- ----------
Direct Sales         2001-09       1,100,000          1
Direct Sales         2001-10       1,000,000          2
Internet             2001-09         500,000          3
Internet             2001-10         700,000          4
Partners             2001-09         600,000          5
Partners             2001-10         600,000          6

上述の結果には同等の値のペアが3組あります。NTILEと同様、ROW_NUMBERも不確定的な関数なので、同じ内容の値のそれぞれにおいて行番号が変わる場合があります。確定的な結果を確実に得るには、一意キーで順序付けを行う必要があります。ほとんどの場合、同じ内容の値を解決する列を新しく問合せに追加し、それをORDER BYの指定で使用する必要があります。

集計ウィンドウ関数

一連のウィンドウ関数を使用すると、累積集計、移動集計および集中集計を計算できます。この種類の関数では、対応するウィンドウ内にある他の行に基づいて、表内の各行に対する値が戻されます。集計ウィンドウ関数群では、SUMAVERAGECOUNTMAXMINを始めとする多数の関数の移動および累積バージョンの計算が可能です。この種の関数は、問合せのSELECT句およびORDER BY句でのみ使用できます。集計ウィンドウ関数群には、ウィンドウ内の最初の値が戻されるFIRST_VALUE、ウィンドウ内の最後の値が戻されるLAST_VALUEといった便利な関数があります。これらの関数を使用すると、自己結合なしで表の複数の行にアクセスできます。ウィンドウ関数の構文は次のとおりです。

analytic_function([ arguments ])
   OVER (analytic_clause)

 where analytic_clause =
     [ query_partition_clause ]
     [ order_by_clause [ windowing_clause ] ]

and query_partition_clause =
    PARTITION BY
      { value_expr[, value_expr ]...
      | ( value_expr[, value_expr ]... )
      }
 and windowing_clause =
     { ROWS | RANGE }
     { BETWEEN
       { UNBOUNDED PRECEDING
     | CURRENT ROW
     | value_expr { PRECEDING | FOLLOWING }
     }
     AND
     { UNBOUNDED FOLLOWING
     | CURRENT ROW
     | value_expr { PRECEDING | FOLLOWING }
     }
   | { UNBOUNDED PRECEDING
     | CURRENT ROW
     | value_expr PRECEDING
     }
   }

DISTINCTキーワードはMAXおよびMINを除くウィンドウ関数ではサポートされません。


関連項目:

構文および制限の詳細は、『Oracle Database SQL言語リファレンス』を参照してください。

ウィンドウ関数に入力したNULLの取扱

ウィンドウ関数のNULLの処理方法は、SQL集計関数のNULLの処理方法と同じです。その他の処理方法は、ユーザー定義の関数によって、またはウィンドウ関数でDECODECASE式を使用することによって取得できます。

論理オフセットを指定したウィンドウ関数

論理オフセットは、RANGE 10 PRECEDINGなどの定数または定数を求める式で指定するか、RANGE INTERVAL N DAY/MONTH/YEAR PRECEDINGなどのインターバル指定またはインターバルを求める式によって指定できます。

論理オフセットでは、NUMERIC(オフセットが数値の場合)またはDATE(インターバルが指定される場合)と互換性のある型の式を1つのみ、関数のORDER BY式リストに指定できます。

RANGEキーワードを使用する分析関数で、ウィンドウとして次の2つのいずれかを指定する場合は、ORDER BY句に複数のソート・キーを指定できます。

  • RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW。このウィンドウの短縮形はRANGE UNBOUNDED PRECEDINGです。この短縮形を使用することもできます。

  • RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING

ウィンドウの境界がこれらの条件に合致しない場合、分析関数のORDER BY句に指定できるソート・キーは1つのみとなります。

例21-7 累積集計関数

1999年の四半期別、顧客ID別の累積amount_soldの例を次に示します。

SELECT c.cust_id, t.calendar_quarter_desc, TO_CHAR (SUM(amount_sold),
  '9,999,999,999.99') AS Q_SALES, TO_CHAR(SUM(SUM(amount_sold))

OVER (PARTITION BY c.cust_id ORDER BY c.cust_id, t.calendar_quarter_desc
ROWS UNBOUNDED
PRECEDING), '9,999,999,999.99') AS CUM_SALES
  FROM sales s, times t, customers c
  WHERE s.time_id=t.time_id AND s.cust_id=c.cust_id AND t.calendar_year=2000
    AND c.cust_id IN (2595, 9646, 11111)
  GROUP BY c.cust_id, t.calendar_quarter_desc
  ORDER BY c.cust_id, t.calendar_quarter_desc;

   CUST_ID CALENDA Q_SALES           CUM_SALES
---------- ------- ----------------- -----------------
      2595 2000-01            659.92            659.92
      2595 2000-02            224.79            884.71
      2595 2000-03            313.90          1,198.61
      2595 2000-04          6,015.08          7,213.69
      9646 2000-01          1,337.09          1,337.09
      9646 2000-02            185.67          1,522.76
      9646 2000-03            203.86          1,726.62
      9646 2000-04            458.29          2,184.91
     11111 2000-01             43.18             43.18
     11111 2000-02             33.33             76.51
     11111 2000-03            579.73            656.24
     11111 2000-04            307.58            963.82

この例では、分析関数SUMによって各行のウィンドウが定義されます。ウィンドウはパーティションの先頭から開始され(UNBOUNDED PRECEDING)、デフォルトではカレント行で終了します。

この例では、それ自体がSUMである値に対してSUMを実行しているため、ネストされたSUMが必要です。ネストされた集計は、分析集計関数できわめて頻繁に使用されます。

例21-8 移動集計関数

次に、時間ベースのウィンドウの例を示します。このウィンドウは、特定の顧客について、今月と過去2か月間の売上の移動平均を示すものです。

SELECT c.cust_id, t.calendar_month_desc, TO_CHAR (SUM(amount_sold),
      '9,999,999,999') AS SALES, TO_CHAR(AVG(SUM(amount_sold))
OVER (ORDER BY c.cust_id, t.calendar_month_desc ROWS 2 PRECEDING),
      '9,999,999,999') AS MOVING_3_MONTH_AVG
FROM sales s, times t, customers c
WHERE s.time_id=t.time_id AND s.cust_id=c.cust_id
   AND t.calendar_year=1999 AND c.cust_id IN (6510)
GROUP BY c.cust_id, t.calendar_month_desc
ORDER BY c.cust_id, t.calendar_month_desc;

   CUST_ID CALENDAR SALES          MOVING_3_MONTH
---------- -------- -------------- --------------
      6510 1999-04             125            125
      6510 1999-05           3,395          1,760
      6510 1999-06           4,080          2,533
      6510 1999-07           6,435          4,637
      6510 1999-08           5,105          5,207
      6510 1999-09           4,676          5,405
      6510 1999-10           5,109          4,963
      6510 1999-11             802          3,529

ウィンドウ計算は問合せにより取り出されたデータの境界を越えることはないため、出力データの3か月の移動平均計算のうち、最初の2行は指定したよりも小さいインターバルに基づいていることに注意してください。結果セットの境界で見られるこのような異なるウィンドウ・サイズを考慮する必要があります。つまり、必要な内容のみが含まれるように問合せの変更が必要になることがあります。

集中集計関数

カレント行の前後に集中する集計ウィンドウ関数の計算は簡単です。次の例では、すべての顧客について、1999年12月末の1週間における売上の集中移動平均を計算します。ここで、カレント行の前後1日(カレント行も含む)の合計売上の平均がわかります。

例21-9 集中集計

SELECT t.time_id, TO_CHAR (SUM(amount_sold), '9,999,999,999')
AS SALES, TO_CHAR(AVG(SUM(amount_sold)) OVER
  (ORDER BY t.time_id
   RANGE BETWEEN INTERVAL '1' DAY PRECEDING AND
   INTERVAL '1' DAY FOLLOWING), '9,999,999,999') AS CENTERED_3_DAY_AVG
   FROM sales s, times t
   WHERE s.time_id=t.time_id AND t.calendar_week_number IN (51)
     AND calendar_year=1999
   GROUP BY t.time_id
   ORDER BY t.time_id;

TIME_ID   SALES          CENTERED_3_DAY
--------- -------------- --------------
20-DEC-99        134,337        106,676
21-DEC-99         79,015        102,539
22-DEC-99         94,264         85,342
23-DEC-99         82,746         93,322
24-DEC-99        102,957         82,937
25-DEC-99         63,107         87,062
26-DEC-99         95,123         79,115

ウィンドウ計算は問合せにより取り出されたデータの境界は越えないため、出力データの各製品の集中移動平均計算のうち、最初の行と最後の行は2日にのみ基づいています。ユーザーは、結果セットの境界で見られるこのような異なるウィンドウ・サイズを考慮する必要があります。場合によっては、問合せの調整が必要となります。

重複がある場合の集計ウィンドウ関数

次の例では、同じ内容の値のデータが存在する場合、つまり単一の順序値に対して複数の行が戻される場合に、集計ウィンドウ関数で値がどのように計算されるかを示します。次の問合せでは、特定の期間における複数の顧客に対する売上高が取得されます(実データセットを定義するためにインライン・ビューを使用していますが、特に意味はないので無視してかまいません)。この問合せには、カレント行の日付から10日前まで実行される変動ウィンドウが定義されています。この例のウィンドウ句の定義にRANGEキーワードが使用されていることに注意してください。これは、このウィンドウが、範囲内にある各値に対して多くの行を保持できることを意味しています。この場合、重複するデータ値を持つ行が3組あります。

例21-10 論理オフセットを指定した集計ウィンドウ関数

SELECT time_id, daily_sum, SUM(daily_sum) OVER (ORDER BY time_id
RANGE BETWEEN INTERVAL '10' DAY PRECEDING AND CURRENT ROW)
AS current_group_sum
FROM (SELECT time_id, channel_id, SUM(s.quantity_sold)
AS daily_sum
FROM customers c, sales s, countries
WHERE c.cust_id=s.cust_id
  AND c.country_id = countries.country_id
  AND s.cust_id IN (638, 634, 753, 440 ) AND s.time_id BETWEEN '01-MAY-00'
  AND '13-MAY-00' GROUP BY time_id, channel_id);

TIME_ID    DAILY_SUM CURRENT_GROUP_SUM
--------- ---------- -----------------
06-MAY-00          7                 7   /* 7 */
10-MAY-00          1                 9   /* 7 + (1+1) */
10-MAY-00          1                 9   /* 7 + (1+1) */
11-MAY-00          2                15   /* 7 + (1+1) + (2+4) */
11-MAY-00          4                15   /* 7 + (1+1) + (2+4) */
12-MAY-00          1                16   /* 7 + (1+1) + (2+4) + 1 */
13-MAY-00          2                23   /* 7 + (1+1) + (2+4) + 1 + (5+2) */
13-MAY-00          5                23   /* 7 + (1+1) + (2+4) + 1 + (5+2) */

この例の出力では、5月6日と5月12日を除くすべての日付について2つの行が戻されています。出力の右側のコメントにある数字をチェックして、値がどのように計算されているかを確認します。カッコ内の各グループは、単一の日について戻された値を表します。

この例は、ROWSキーワードではなくRANGEキーワードを使用した場合にのみ当てはまることに注意してください。RANGEを使用すると、分析関数のORDER BY句でORDER BY式を1つしか使用できないことにも注意が必要です。ROWSキーワードを使用した場合は、分析関数のORDER BY句で複数のORDER BY式を使用できます。

行ごとに変動するウィンドウ・サイズ

特定の条件に応じて、行ごとにウィンドウのサイズを変えると便利な場合があります。特定の日付に対してウィンドウを大きくし、その他の日付には小さくする必要がある場合などです。たとえば、3営業日にわたる株価の移動平均を計算するとします。すべての営業日で毎日の行数が同じで、非営業日については格納されていない場合は、物理ウィンドウ関数を使用できます。この条件が満たされない場合に移動平均を計算するには、ウィンドウ・サイズ・パラメータで式を使用します。

ウィンドウ・サイズ指定における式は、様々なソースで作成できます。式には、時間表などの表の列を使用できます。また、カレント行の値に基づいてウィンドウの適切な境界を戻す関数も使用できます。仮想の株価データベースに関する次の文では、RANGE句にユーザー定義関数を使用してウィンドウ・サイズが設定されています。

SELECT t_timekey, AVG(stock_price)
        OVER (ORDER BY t_timekey RANGE fn(t_timekey) PRECEDING) av_price
FROM stock, time WHERE st_timekey = t_timekey
ORDER BY t_timekey;

この文では、t_timekeyが日付フィールドです。fnは、次の仕様を持つPL/SQLファンクションとします。

fn(t_timekey)は次の値を戻します。

  • t_timekeyが月曜日または火曜日の場合は4。

  • それ以外の場合は2。

  • 前に休日があっても、カウントは正しく調整されます。

日付列に関して、ウィンドウ関数でORDER BY句に数値が使用される形でウィンドウが指定されている場合、この数値は日数に変換されます。単にfn(t_timekey)とするのではなく、インターバル・リテラル変換関数をNUMTODSINTERVAL(fn(t_timekey), 'DAY')のように使用しても、同じ処理を行えます。また、INTERVAL型の値を戻すPL/SQLファンクションを記述することもできます。

物理オフセットを指定した集計ウィンドウ関数の例

行単位で表されるウィンドウでは、順序付け式は、結果を確定的にするために一意である必要があります。たとえば、次の問合せは、この結果セット内でtime_idが一意でないため、確定的ではありません。

例21-11 物理オフセットを指定した集計ウィンドウ関数

SELECT t.time_id, TO_CHAR(amount_sold, '9,999,999,999') AS INDIV_SALE,
       TO_CHAR(SUM(amount_sold) OVER (PARTITION BY t.time_id ORDER BY t.time_id
ROWS UNBOUNDED PRECEDING), '9,999,999,999') AS CUM_SALES
FROM sales s, times t, customers c
WHERE s.time_id=t.time_id AND s.cust_id=c.cust_id
  AND t.time_id IN
   (TO_DATE('11-DEC-1999'), TO_DATE('12-DEC-1999'))
  AND c.cust_id
BETWEEN 6500 AND 6600
ORDER BY t.time_id;

TIME_ID   INDIV_SALE    CUM_SALES
--------- ----------    ---------
12-DEC-99         23           23
12-DEC-99          9           32
12-DEC-99         14           46
12-DEC-99         24           70
12-DEC-99         19           89

この問題に対処するには、結果セットにprod_id列を追加してtime_idおよびprod_idの両方を順序付けするという方法があります。

FIRST_VALUE関数およびLAST_VALUE関数

FIRST_VALUE関数やLAST_VALUE関数を使用すると、ウィンドウの最初や最後の行を選択できます。これらの行は、計算上の基準行として使用されるので特に重要です。これらの行を使用する例としては、たとえば、日付で順序付けされた売上データが保持されるパーティションについて、「その期間の最初の販売日(FIRST_VALUE)と比較した各日の売上はどの程度か」といった質問をする場合があります。また、売上高が昇順で順序付けされている行集合について「その範囲で最大の売上(LAST_VALUE)と比較した場合の、範囲内の各売上の割合はどの程度か」という質問をする場合なども当てはまります。

FIRST_VALUEIGNORE NULLSオプションを使用すると、セット内で最初のNULL以外の値が戻されます。すべての値がNULLの場合は、NULLが戻されます。LAST_VALUEIGNORE NULLSを使用すると、セット内で最後のNULL以外の値が戻されます。すべての値がNULLの場合は、NULLが戻されます。IGNORE NULLSオプションは、在庫表を適切に移入する場合に特に役立ちます。

集計レポート関数

問合せの処理後に、結果の行数や列の平均値といった集計値をパーティション内で簡単に計算でき、他のレポート関数でも使用可能にできます。集計レポート関数では、パーティション内のすべての行に対して同一の集計値が戻されます。この種の関数におけるNULLへの対応は、SQL集計関数と同じです。構文は次のとおりです。

{SUM | AVG | MAX | MIN | COUNT | STDDEV | VARIANCE  ... }
  ([ALL | DISTINCT] {value expression1 | *})
   OVER ([PARTITION BY value expression2[,...]])

さらに、次の条件が適用されます。

レポート関数は、SELECT句またはORDER BY句でのみ使用できます。レポート関数の主なメリットは、単一の問合せブロックでデータの複数のパスを実行し、問合せパフォーマンスをスピードアップできることです。「売上が市全体の売上の10%以上である販売員の数をカウントする」といった問合せで、別々の問合せブロック間の結合が必要ありません。

たとえば、「各製品カテゴリについて、最大の売上を記録した地域を検索する」という質問について考えてみます。MAX集計レポート関数を使用する同等のSQL問合せは、次のようになります。

SELECT prod_category, country_region, sales
FROM (SELECT SUBSTR(p.prod_category,1,8) AS prod_category, co.country_region,
 SUM(amount_sold) AS sales,
MAX(SUM(amount_sold)) OVER (PARTITION BY prod_category) AS MAX_REG_SALES
FROM sales s, customers c, countries co, products p
WHERE s.cust_id=c.cust_id AND c.country_id=co.country_id
   AND s.prod_id =p.prod_id AND s.time_id = TO_DATE('11-OCT-2001')
GROUP BY prod_category, country_region)
WHERE sales = MAX_REG_SALES;

集計レポート関数MAX(SUM(amount_sold))を含む内部問合せでは、次の内容が戻されます。

PROD_CAT COUNTRY_REGION            SALES MAX_REG_SALES
-------- -------------------- ---------- -------------
Electron Americas                 581.92        581.92
Hardware Americas                 925.93        925.93
Peripher Americas                3084.48       4290.38
Peripher Asia                    2616.51       4290.38
Peripher Europe                  4290.38       4290.38
Peripher Oceania                  940.43       4290.38
Software Americas                 4445.7        4445.7
Software Asia                    1408.19        4445.7
Software Europe                  3288.83        4445.7
Software Oceania                  890.25        4445.7

問合せ結果の全体は、次のようになります。

PROD_CAT COUNTRY_REGION            SALES
-------- -------------------- ----------
Electron Americas                 581.92
Hardware Americas                 925.93
Peripher Europe                  4290.38
Software Americas                 4445.7

例21-12 集計レポートの例

集計レポートをネストされた問合せと組み合せると、複雑な問合せに対する応答が効率化されます。たとえば、最も重要な製品サブカテゴリで最も売れ行きがよい製品を知りたいとします。製品カテゴリ内の売上の20%以上を占める製品サブカテゴリについて、サブカテゴリごとに売上上位5位までの製品を検索する問合せは、次のようになります。

SELECT SUBSTR(prod_category,1,8) AS CATEG, prod_subcategory, prod_id, SALES
FROM (SELECT p.prod_category, p.prod_subcategory, p.prod_id,
      SUM(amount_sold) AS SALES,
      SUM(SUM(amount_sold)) OVER (PARTITION BY p.prod_category) AS CAT_SALES,
      SUM(SUM(amount_sold)) OVER
         (PARTITION BY p.prod_subcategory) AS SUBCAT_SALES,
      RANK() OVER  (PARTITION BY p.prod_subcategory
         ORDER BY SUM(amount_sold) ) AS RANK_IN_LINE
     FROM sales s, customers c, countries co, products p
     WHERE s.cust_id=c.cust_id
       AND c.country_id=co.country_id AND  s.prod_id=p.prod_id
       AND s.time_id=to_DATE('11-OCT-2000')
     GROUP BY p.prod_category, p.prod_subcategory, p.prod_id
     ORDER BY prod_category, prod_subcategory)
  WHERE SUBCAT_SALES>0.2*CAT_SALES AND RANK_IN_LINE<=5;

RATIO_TO_REPORT関数

RATIO_TO_REPORT関数では、値の集合の合計に対して、ある値の割合が計算されます。式value expressionNULLと評価される場合、RATIO_TO_REPORTNULLと評価されますが、分母の値の合計を計算する際には0(ゼロ)として扱われます。構文は次のとおりです。

RATIO_TO_REPORT ( expr ) OVER ( [query_partition_clause] )

ここでは、次の事項が適用されます。

  • exprには、列参照または集計を含む任意の有効な式を指定できます。

  • PARTITION BY句には、RATIO_TO_REPORT関数の計算対象とするグループを定義します。PARTITION BY句がない場合、この関数は問合せ結果セット全体に対して計算されます。

例21-13 RATIO_TO_REPORT

チャネル別売上のRATIO_TO_REPORTを計算するには、次のような構文を使用できます。

SELECT ch.channel_desc, TO_CHAR(SUM(amount_sold),'9,999,999') AS SALES,
     TO_CHAR(SUM(SUM(amount_sold)) OVER (), '9,999,999') AS TOTAL_SALES,
     TO_CHAR(RATIO_TO_REPORT(SUM(amount_sold)) OVER (), '9.999')
     AS RATIO_TO_REPORT
FROM sales s, channels ch
WHERE s.channel_id=ch.channel_id  AND s.time_id=to_DATE('11-OCT-2000')
GROUP BY ch.channel_desc;

CHANNEL_DESC         SALES      TOTAL_SALE RATIO_
-------------------- ---------- ---------- ------
Direct Sales             14,447     23,183   .623
Internet                    345     23,183   .015
Partners                  8,391     23,183   .362

LAG/LEAD関数

LAGおよびLEAD関数は、行の相対位置を確実に認識できる場合の、値の比較に役立ちます。この2つの関数は、カレント行からターゲット行までの行数を指定する形で使用します。これらの関数では、表の複数の行に対して自己結合せずに同時にアクセスできるため、処理速度が向上します。LAG関数では、現在位置から指定したオフセット分のみ前の行がアクセスされ、LEAD関数では、現在位置から指定したオフセット分のみ後の行がアクセスされます。

LAGおよびLEAD関数の構文

これらの関数の構文は次のとおりです。

{LAG | LEAD} ( value_expr [, offset] [, default] )
   OVER ( [query_partition_clause] order_by_clause )

offsetはオプションのパラメータで、デフォルトは1です。defaultはオプションのパラメータで、offsetが表またはパーティションの境界外となる場合に戻される値です。

例21-14 LAGおよびLEAD

SELECT time_id, TO_CHAR(SUM(amount_sold),'9,999,999') AS SALES,
  TO_CHAR(LAG(SUM(amount_sold),1) OVER (ORDER BY time_id),'9,999,999') AS LAG1,
  TO_CHAR(LEAD(SUM(amount_sold),1) OVER (ORDER BY time_id),'9,999,999') AS LEAD1
FROM sales
WHERE time_id>=TO_DATE('10-OCT-2000') AND time_id<=TO_DATE('14-OCT-2000')
GROUP BY time_id;

TIME_ID   SALES      LAG1       LEAD1
--------- ---------- ---------- ----------
10-OCT-00    238,479                23,183
11-OCT-00     23,183    238,479     24,616
12-OCT-00     24,616     23,183     76,516
13-OCT-00     76,516     24,616     29,795
14-OCT-00     29,795     76,516

LAG/LEAD関数を使用して、スパースなデータに対する周期ごとの比較問合せを実行する方法は、「レポート用のデータの稠密化」を参照してください。

FIRST/LAST関数

FIRST/LAST集計関数を使用すると、データセットをランク付けした上で、最上位または最下位の行に対する処理ができます。最上位または最下位の行が発見された後には、目的とする任意の列に対して集計関数が適用されます。つまり、FIRST/LASTでは、列Aを対象としてランク付けした結果、最上位または最下位となった行の、列Bにおける値に対する集計の結果が戻されます。この場合、自己結合や副問合せが不要になり、より高いパフォーマンスが得られるので便利です。FIRST関数およびLAST関数の構文としては、各グループに対し1つの戻り値を生成する通常の集計関数(MINMAXSUMAVGCOUNTVARIANCESTDDEV)を最初に記述します。その上で、FIRST/LAST関数で使用するランキングを指定するため、KEEPで始まる新しい句を追加します。

FIRSTおよびLASTの構文

これらの関数の構文は次のとおりです。

aggregate_function KEEP ( DENSE_RANK LAST ORDER BY
  expr [ DESC | ASC ] [NULLS { FIRST | LAST }]
  [, expr [ DESC | ASC ] [NULLS { FIRST | LAST }]]...)
[OVER query_partitioning_clause]

ORDER BY句には、複数の式を指定できます。

通常の集計としてのFIRSTおよびLAST

FIRST/LAST集計ファミリは、通常の集計関数として使用できます。

例21-15 FIRSTおよびLASTの例1

次に示す問合せでは、製品の最低価格と定価を比較できます。メンズ・ウェア・カテゴリにおける製品サブカテゴリごとに、次の内容が戻されます。

  • 最低価格が最も低い製品の定価

  • 最も低い最低価格

  • 最低価格が最も高い製品の定価

  • 最も高い最低価格

SELECT prod_subcategory, MIN(prod_list_price)
  KEEP (DENSE_RANK FIRST ORDER BY (prod_min_price)) AS LP_OF_LO_MINP,
MIN(prod_min_price) AS LO_MINP,
MAX(prod_list_price) KEEP (DENSE_RANK LAST ORDER BY (prod_min_price))
 AS LP_OF_HI_MINP,
MAX(prod_min_price) AS HI_MINP
FROM products WHERE prod_category='Electronics'
GROUP BY prod_subcategory;

PROD_SUBCATEGORY LP_OF_LO_MINP LO_MINP  LP_OF_HI_MINP  HI_MINP
---------------- ------------- -------  ------------- ----------
Game Consoles          299.99  299.99          299.99     299.99
Home Audio             499.99  499.99          599.99     599.99
Y Box Accessories        7.99    7.99           20.99      20.99
Y Box Games              7.99   7.99            29.99      29.99

集計レポートとしてのFIRSTおよびLAST

FIRST/LAST集計ファミリは、集計レポート関数としても使用できます。たとえば、人数の増加が年間で最大の月や最小の月の計算などができます。これらの関数の構文は、他の集計レポートの構文と似ています。

例21-15FIRST/LASTに関する例で考えてみます。この場合、「個々の製品の定価を検索し、それをサブカテゴリ内で最低価格が最も高い製品および最も低い製品の定価と比較するとどうなるか」という問題になります。

FIRST/LASTを集計レポートとして使用し、こうした情報をドキュメンテーション(Documentation)というサブカテゴリに関して検索する問合せを次に示します。

例21-16 FIRSTおよびLASTの例2

SELECT prod_id, prod_list_price,
    MIN(prod_list_price) KEEP (DENSE_RANK FIRST ORDER BY (prod_min_price))
       OVER(PARTITION BY (prod_subcategory)) AS LP_OF_LO_MINP,
    MAX(prod_list_price) KEEP (DENSE_RANK LAST ORDER BY (prod_min_price))
       OVER(PARTITION BY (prod_subcategory)) AS LP_OF_HI_MINP
FROM products WHERE prod_subcategory = 'Documentation';

   PROD_ID PROD_LIST_PRICE LP_OF_LO_MINP LP_OF_HI_MINP
---------- --------------- ------------- -------------
        40           44.99         44.99         44.99
        41           44.99         44.99         44.99
        42           44.99         44.99         44.99
        43           44.99         44.99         44.99
        44           44.99         44.99         44.99
        45           44.99         44.99         44.99

FIRSTおよびLAST関数を集計レポートとして使用すると、その結果を簡単に「最高給与に対する給与の割合」などの計算に簡単に組み込むことができます。

逆パーセンタイル関数

値の集合の累積分布(パーセンタイル)を求めるには、CUME_DIST関数を使用できます。ただし、逆の操作(特定のパーセンタイルを計算するための値の検索)は、簡単でも効率的でもありません。これを簡単に操作できるように、PERCENTILE_CONT関数とPERCENTILE_DISC関数が導入されました。この2つの関数は、通常の集計関数としてのみでなく、ウィンドウおよびレポート関数としても使用できます。

これらの関数では、ソートの指定と、0〜1のパーセンタイル値をとるパラメータが必要とされます。ソート指定の処理には、ORDER BY句で1つの式を使用します。通常の集計関数として使用すると、ソートされた集合ごとに単一の値が戻されます。

PERCENTILE_CONTは内挿法により計算される連続関数で、PERCENTILE_DISCは不連続値を想定するステップ関数です。PERCENTILE_CONTおよびPERCENTILE_DISCは、他の集計と同様、グルーピングされた問合せの行グループを対象として操作する関数ですが、次のような違いがあります。

通常の集計の構文

[PERCENTILE_CONT | PERCENTILE_DISC]( constant expression )
    WITHIN GROUP ( ORDER BY single order by expression
[ASC|DESC] [NULLS FIRST| NULLS LAST])

逆パーセンタイルの例

次の問合せを使用して、この項の例で使用したデータのうち17行を戻すとします。

SELECT cust_id, cust_credit_limit, CUME_DIST()
   OVER (ORDER BY cust_credit_limit) AS CUME_DIST
FROM customers WHERE cust_city='Marshal';

   CUST_ID CUST_CREDIT_LIMIT  CUME_DIST
---------- ----------------- ----------
     28344              1500 .173913043
      8962              1500 .173913043
     36651              1500 .173913043
     32497              1500 .173913043
     15192              3000 .347826087
    102077              3000 .347826087
    102343              3000 .347826087
      8270              3000 .347826087
     21380              5000  .52173913
     13808              5000  .52173913
    101784              5000  .52173913
     30420              5000  .52173913
     10346              7000 .652173913
     31112              7000 .652173913
     35266              7000 .652173913
      3424              9000 .739130435
    100977              9000 .739130435
    103066             10000 .782608696
     35225             11000 .956521739
     14459             11000 .956521739
     17268             11000 .956521739
    100421             11000 .956521739
     41496             15000          1

PERCENTILE_DISC(x)の計算では、x以上の値が最初に見つかるまで、各グループ内のCUME_DIST値がスキャンされます。xは、指定した百分位数です。このサンプルの問合せではPERCENTILE_DISC(0.5)となり、結果は次のように5,000となります。

SELECT PERCENTILE_DISC(0.5) WITHIN GROUP
  (ORDER BY cust_credit_limit) AS perc_disc, PERCENTILE_CONT(0.5) WITHIN GROUP
  (ORDER BY cust_credit_limit) AS perc_cont
 FROM customers WHERE cust_city='Marshal';

PERC_DISC   PERC_CONT
---------   ---------
     5000        5000

PERCENTILE_CONTの計算では、順序付けを行った後に、行と行の線形内挿法によって結果が導かれます。PERCENTILE_CONT(x)を計算するため、まず行番号=RN= (1+x* (n-1))が計算されます。nはグループ内の行数、xは指定した百分位数です。さらに、行番号CRN = CEIL(RN)およびFRN = FLOOR(RN)の行の値の線形内挿法により、この集計関数の最終結果が計算されます。

最終結果は、PERCENTILE_CONT(X) =(CRN = FRN = RN)の場合は(行RNの式の値)、それ以外の場合は(CRN - RN)*(行FRNの式の値)+(RN -FRN)*(行CRNの式の値)となります。

前述の問合せの例で、PERCENTILE_CONT(0.5)を計算する場合を考えてみます。この場合、nは17で、どちらのグループも行番号はRN = (1 + 0.5*(n-1))= 9となります。これを式に当てはめると(FRN=CRN=9)となり、結果として行9からの値が戻されます。

別の例として、PERCENTILE_CONT(0.66)を計算する場合を考えてみます。行番号は、RN=(1 + 0.66*(n-1))= (1 + 0.66*16)= 11.67と計算されます。その結果、PERCENTILE_CONT(0.66) = (12-11.67)*(行11の値)+(11.67-11)*(行12の値)となります。結果は次のようになります。

SELECT PERCENTILE_DISC(0.66) WITHIN GROUP
  (ORDER BY cust_credit_limit) AS perc_disc, PERCENTILE_CONT(0.66) WITHIN GROUP
  (ORDER BY cust_credit_limit) AS perc_cont
FROM customers WHERE cust_city='Marshal';

 PERC_DISC  PERC_CONT
---------- ----------
      9000       8040

逆パーセンタイル関数は、他の既存の集計関数と同様に問合せのHAVING句に使用できます。

集計レポートとしての使用

集計関数PERCENTILE_CONTおよびPERCENTILE_DISCは、集計レポート関数としても使用できます。その場合の構文は、他の集計レポートの場合と同様です。

[PERCENTILE_CONT | PERCENTILE_DISC](constant expression)
WITHIN GROUP ( ORDER BY single order by expression
[ASC|DESC] [NULLS FIRST| NULLS LAST])
OVER ( [PARTITION BY value expression [,...]] )

次の問合せでは同じ内容(この結果セットに含まれる顧客の与信限度額の中央値)が計算されますが、後述の出力のように結果セットの各行について結果がレポートされます。

SELECT cust_id, cust_credit_limit, PERCENTILE_DISC(0.5) WITHIN GROUP
     (ORDER BY cust_credit_limit) OVER () AS perc_disc,
  PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY cust_credit_limit)
  OVER () AS perc_cont
FROM customers WHERE cust_city='Marshal';

   CUST_ID CUST_CREDIT_LIMIT  PERC_DISC  PERC_CONT
---------- ----------------- ---------- ----------
     28344              1500       5000       5000
      8962              1500       5000       5000
     36651              1500       5000       5000
     32497              1500       5000       5000
     15192              3000       5000       5000
    102077              3000       5000       5000
    102343              3000       5000       5000
      8270              3000       5000       5000
     21380              5000       5000       5000
     13808              5000       5000       5000
    101784              5000       5000       5000
     30420              5000       5000       5000
     10346              7000       5000       5000
     31112              7000       5000       5000
     35266              7000       5000       5000
      3424              9000       5000       5000
    100977              9000       5000       5000
    103066             10000       5000       5000
     35225             11000       5000       5000
     14459             11000       5000       5000
     17268             11000       5000       5000
    100421             11000       5000       5000
     41496             15000       5000       5000

逆パーセンタイルの制限

PERCENTILE_DISCの場合は、ORDER BY句の式に、ソート可能なデータ型(数値、文字列、日付など)を使用できます。一方、PERCENTILE_CONTの評価には線形内挿法が使用されるため、ORDER BY句の式には数値型または日付/時刻型(インターバルを含む)を指定する必要があります。式がDATE型の場合、内挿の結果はその型の最小単位に丸められます。DATE型の場合、内挿された値は最も近い秒に丸められ、インターバル型の場合は最も近い秒(INTERVAL DAY TO SECOND)または月(INTERVAL YEAR TO MONTH)に丸められます。

他の集計と同様に、逆パーセンタイル関数でも結果の評価時にNULLは無視されます。たとえば、集合内の中央値を求める場合、Oracle DatabaseではNULLが無視され、NULL以外の値から中央値が求められます。ORDER BY句にはNULLS FIRST/NULLS LASTオプションも使用できますが、NULLが無視されるので、こうしたオプションも結果的には無視されます。

仮説ランク関数および仮説分布関数

この種の関数では、what-if分析に役立つ機能が提供されます。たとえば、行を他の行集合に仮に挿入すると、行のランクがどうなるかという問題があるとします。

この集計ファミリでは、仮に挿入する行と順序付けられた行のグループを引数として1つ以上取り、対象とするグループに行を仮に挿入した場合の、その行のRANKDENSE_RANKPERCENT_RANKまたはCUME_DISTが戻されます。

仮説ランク関数および仮説分布関数の構文

[RANK | DENSE_RANK | PERCENT_RANK | CUME_DIST]( constant expression [, ...] )
WITHIN GROUP ( ORDER BY order by expression [ASC|DESC] [NULLS FIRST|NULLS LAST][, ...] )

ここで、constant expressionは定数に評価される式です。このような式を引数として複数個、関数に渡す場合もあります。ORDER BY句には、ランキングの基準となるソート順を定義する式を1つ以上含めることができます。ORDER BY句内の各式には、ASCDESCNULLS FIRSTNULLS LASTオプションを使用できます。

例21-17 仮説ランク関数および仮説分布関数の例1

この項で使用されているproducts表の定価データを使用して、価格50ドルのセーターの仮のRANKPERCENT_RANKおよびCUME_DISTを計算し、各セーター・サブカテゴリに当てはまるかどうかを調べる場合を考えます。この場合の問合せと結果は次のようになります。

SELECT cust_city,
  RANK(6000) WITHIN GROUP (ORDER BY CUST_CREDIT_LIMIT DESC) AS HRANK,
  TO_CHAR(PERCENT_RANK(6000) WITHIN GROUP
  (ORDER BY cust_credit_limit),'9.999') AS HPERC_RANK,
  TO_CHAR(CUME_DIST (6000) WITHIN GROUP
  (ORDER BY cust_credit_limit),'9.999') AS HCUME_DIST
FROM customers
WHERE cust_city LIKE 'Fo%'
GROUP BY cust_city;

CUST_CITY                           HRANK HPERC_ HCUME_
------------------------------ ---------- ------ ------
Fondettes                              13   .455   .478
Fords Prairie                          18   .320   .346
Forest City                            47   .370   .378
Forest Heights                         38   .456   .464
Forestville                            58   .412   .418
Forrestcity                            51   .438   .444
Fort Klamath                           59   .356   .363
Fort William                           30   .500   .508
Foxborough                             52   .414   .420

逆パーセンタイル集計とは異なり、仮説ランク関数および仮説分布関数の場合、ソート指定のORDER BY句には複数の式を使用できます。ORDER BY句の式の数と引数の数は同じにする必要があり、引数は、対応するORDER BY句の式と同じ型または互換性のある型の定数式にする必要があります。次の例では、複数の仮説ランク関数において2つの引数が使用されています。

例21-18 仮説ランク関数および仮説分布関数の例2

SELECT prod_subcategory,
  RANK(10,8) WITHIN GROUP (ORDER BY prod_list_price DESC,prod_min_price)
  AS HRANK, TO_CHAR(PERCENT_RANK(10,8) WITHIN GROUP
  (ORDER BY prod_list_price, prod_min_price),'9.999') AS HPERC_RANK,
  TO_CHAR(CUME_DIST (10,8) WITHIN GROUP
  (ORDER BY prod_list_price, prod_min_price),'9.999') AS HCUME_DIST
FROM products WHERE prod_subcategory LIKE 'Recordable%'
GROUP BY prod_subcategory;

PROD_SUBCATEGORY          HRANK   HPERC_   HCUME_
--------------------      -----   ------   ------
Recordable CDs                4     .571     .625
Recordable DVD Discs          5     .200     .333

これらの関数は、他の集計関数と同様に問合せのHAVING句に使用できます。集計レポート関数や集計ウィンドウ関数としては使用できません。

線形回帰関数

この回帰関数では、数値の組の集合に対する微分最小2乗法で求めた回帰直線の適合がサポートされます。この関数は、集計関数としても、ウィンドウ関数やレポート関数としても使用できます。

線形回帰関数には次のようなものがあります。

Oracleではこの種の関数は、(e1, e2)の組の集合に対して、e1またはe2のいずれかがNULLである組をすべて排除した後に適用されます。e1は従属変数の値(y)として、e2は独立変数(x)として解釈されます。どちらの式も、数値である必要があります。

すべての回帰関数は、データからの単一パスの間に同時に計算されます。こうした回帰関数は、多くの場合COVAR_POPCOVAR_SAMPおよびCORR関数と組み合せて使用されます。

REGR_COUNT関数

REGR_COUNTでは、回帰直線の適合に使用されるNULL以外の数値の組の数が戻されます。この関数を空の集合に適用した場合(または、e1およびe2の両方がNULLでない(e1, e2)の組が存在しない場合)、0(ゼロ)が戻ります。

REGR_AVGY関数およびREGR_AVGX関数

REGR_AVGYでは回帰直線の従属変数の平均、REGR_AVGXでは回帰直線の独立変数の平均が計算されます。REGR_AVGYでは、(e1, e2)の組のうちe1またはe2のいずれかがNULLであるものが排除され、その後で第1の引数(e1)の平均が計算されます。同様に、REGR_AVGXでは、NULLが排除された後に第2の引数(e2)の平均が計算されます。どちらの関数も、空の集合に適用した場合にはNULLが戻ります。

REGR_SLOPE関数およびREGR_INTERCEPT関数

REGR_SLOPE関数では、NULL以外の(e1, e2)の組に適合する回帰直線の傾きが計算されます。

REGR_INTERCEPT関数では、回帰直線のy切片が計算されます。傾きまたは回帰平均がNULLの場合、REGR_INTERCEPTからはNULLが戻ります。

REGR_R2関数

REGR_R2関数では、回帰直線の確定係数(通常は「Rの2乗」または「適合度」)が計算されます。

回帰直線が定義される場合(線の傾きがNULLではない場合)、REGR_R2からは0(ゼロ)〜1の値が戻ります。それ以外の場合はNULLが戻ります。値が1に近づくほど、回帰直線がデータに適合します。

REGR_SXX関数、REGR_SYY関数およびREGR_SXY関数

REGR_SXX関数、REGR_SYY関数およびREGR_SXY関数は、回帰分析用の様々な診断統計情報の計算に使用されます。これらの関数では、(e1, e2)の組のうちe1またはe2のいずれかがNULLであるものが排除された後で、次の計算が実行されます。

REGR_SXX:    REGR_COUNT(e1,e2) * VAR_POP(e2)
REGR_SYY:    REGR_COUNT(e1,e2) * VAR_POP(e1)
REGR_SXY:    REGR_COUNT(e1,e2) * COVAR_POP(e1, e2)

線形回帰統計の例

表21-2「一般的な診断統計情報およびその式」に、線形回帰分析で使用される一般的な診断統計情報の一部を示します。今回のリリースの新しい関数を使用すると、これらの情報をすべて計算できます。

表21-2 一般的な診断統計情報およびその式

統計情報のタイプ

調整R2

1-((1 - REGR_R2)*((REGR_COUNT-1)/(REGR_COUNT-2)))

標準誤差

SQRT((REGR_SYY-(POWER(REGR_SXY,2)/REGR_SXX))/(REGR_COUNT-2))

2乗の総計

REGR_SYY

2乗の回帰合計

POWER(REGR_SXY,2) / REGR_SXX

2乗の残差合計

REGR_SYY - (POWER(REGR_SXY,2)/REGR_SXX)

傾きのt統計

REGR_SLOPE * SQRT(REGR_SXX) /(標準誤差)

y切片のt統計量

REGR_INTERCEPT / ((標準誤差)* SQRT((1/REGR_COUNT)+(POWER(REGR_AVGX,2)/REGR_SXX))


線形回帰計算のサンプル

この例では、製品の販売数量をその製品の定価の線形関数として表す、微分最小2乗法で求めた回帰直線を計算します。計算は、販売チャネル別にグルーピングされます。SLOPEINTCPTおよびRSQRの値は、それぞれ、回帰直線の傾き、切片および確定係数です。(整数)値COUNTは、販売数量データと定価データの両方を使用できる各チャネルの製品の数量です。

SELECT s.channel_id, REGR_SLOPE(s.quantity_sold, p.prod_list_price) SLOPE,
  REGR_INTERCEPT(s.quantity_sold, p.prod_list_price) INTCPT,
  REGR_R2(s.quantity_sold, p.prod_list_price) RSQR,
  REGR_COUNT(s.quantity_sold, p.prod_list_price) COUNT,
  REGR_AVGX(s.quantity_sold, p.prod_list_price) AVGLISTP,
  REGR_AVGY(s.quantity_sold, p.prod_list_price) AVGQSOLD
FROM sales s, products p WHERE s.prod_id=p.prod_id
  AND p.prod_category='Electronics' AND s.time_id=to_DATE('10-OCT-2000')
GROUP BY s.channel_id;

CHANNEL_ID      SLOPE     INTCPT       RSQR      COUNT   AVGLISTP   AVGQSOLD
---------- ---------- ---------- ---------- ---------- ---------- ----------
         2          0          1          1         39 466.656667          1
         3          0          1          1         60     459.99          1
         4          0          1          1         19 526.305789          1

ピボット操作

ビジネス・インテリジェンス問合せから戻されたデータは、多くの場合、クロス集計形式で表されていると最も便利に使用できます。SELECT文においてpivot_clauseを使用すると、行を回転して列とするクロス集計の問合せを記述し、回転のプロセスでデータを集計できます。ピボット操作はデータ・ウェアハウスにおける重要なテクニックです。この操作では、入力する複数行がデータ・ウェアハウス内でより少数の行や、より多数の行(通常の場合)に変換されます。ピボット操作時には、ピボット列の値リストにある各項目に集計演算子が適用されます。ピボット列には不定的な式を含めることはできません。式に対するピボットが必要な場合、ビュー内でその式に別名を与えてからPIVOT操作を行います。基本的な構文は次のようになります。

SELECT ....
FROM <table-expr>
   PIVOT
     (
      aggregate-function(<column>)
      FOR <pivot-column> IN (<value1>, <value2>,..., <valuen>)
        ) AS <alias>
WHERE .....

pivot_clause構文については、『Oracle Database SQL言語リファレンス』を参照してください。

ピボット操作の使用方法を示すため、後述の例の基準として次のビューを作成します。

CREATE VIEW sales_view AS
SELECT
 prod_name product, country_name country, channel_id channel,
 SUBSTR(calendar_quarter_desc, 6,2) quarter,
 SUM(amount_sold) amount_sold, SUM(quantity_sold) quantity_sold
FROM sales, times, customers, countries, products
WHERE sales.time_id = times.time_id AND
  sales.prod_id = products.prod_id AND
  sales.cust_id = customers.cust_id AND
  customers.country_id = countries.country_id
GROUP BY prod_name, country_name, channel_id,
 SUBSTR(calendar_quarter_desc, 6, 2);

例: ピボット操作

次の文では、channel列を対象として典型的なピボットを解説します。

SELECT * FROM
  (SELECT product, channel, amount_sold
   FROM sales_view
   ) S PIVOT (SUM(amount_sold)
   FOR CHANNEL IN (3 AS DIRECT_SALES, 4 AS INTERNET_SALES,
                   5 AS CATALOG_SALES, 9 AS TELESALES))
ORDER BY product;

PRODUCT                  DIRECT­_SALES  INTERNET_SALES  CATALOG_SALES  TELESALES
----------------------   ------------  --------------  -------------  ---------
...
Internal 6X CD-ROM          229512.97        26249.55
Internal 8X CD-ROM          286291.49        42809.44
Keyboard Wrist Rest         200959.84        38695.36                   1522.73
...

出力では、ピボット値それぞれについて、DIRECT_SALESINTERNET_SALESCATALOG_SALESおよびTELESALESという別名の付いた4つの列が新しく作成されています。この出力には合計が表示されます。別名を付けない場合、列ヘッダーはINリストの値となります。

複数列に対するピボット操作

ピボットは複数の列に対して実行できます。次の文で、典型的な複数列のピボットを解説します。

SELECT *
FROM
     (SELECT product, channel, quarter, quantity_sold
      FROM sales_view
     ) PIVOT (SUM(quantity_sold)
                FOR (channel, quarter) IN
                  ((5, '02') AS CATALOG_Q2,
                   (4, '01') AS INTERNET_Q1,
                   (4, '04') AS INTERNET_Q4,
                   (2, '02') AS PARTNERS_Q2,
                   (9, '03') AS TELE_Q3
                  )
                );

PRODUCT              CATALOG_Q2  INTERNET_Q1  INTERNET_Q4  PARTNERS_Q2   TELE_Q3
-------              ----------  -----------  -----------  -----------   -------
...
Bounce                                  347           632          954
...
Smash Up Boxing                         129           280          560
...
Comic Book Heroes                        47           155          275
...

この例では、複数列のINリストが指定され、そのINリストのメンバーに一致するような列ヘッダーが与えられています。

ピボット操作: 複数の集計

ピボット操作では複数の集計を併用できます。この例を次に示します。

SELECT *
FROM
     (SELECT product, channel, amount_sold, quantity_sold
      FROM sales_view
     ) PIVOT (SUM(amount_sold) AS sums,
              SUM(quantity_sold) AS sumq
              FOR channel IN (5, 4, 2, 9)
               )
ORDER BY product;
PRODUCT                5_SUMS  5_SUMQ    4_SUMS   4_SUMQ      2_SUMS   2_SUMQ    9_SUMS   9_SUMQ
-------------          ------  ------    ------   ------      ------   ------    ------   ------
O/S Doc Set English                   142780.36     3081   381397.99     8044   6028.66      134
O/S Doc Set French                     55503.58     1192   132000.77     2782
...

この問合せでは、ピボットの値(または別名)と集計関数の別名をアンダースコアで連結することにより、列ヘッダーが作成されます。

ソース・データ内のNULLとPIVOTで生成されたNULLとの識別

ソース・データ内に存在するNULL値と、PIVOTを使用することで生成されたNULL値は、識別できます。次の例では、PIVOTによって生成されるNULLについて解説します。

次の問合せでは5列分の行が戻されます。列prod_idと、ピボット操作結果の列Q1Q1_COUNT_TOTALQ2Q2_COUNT_TOTALの5列です。prod_idの一意の各値について、Q1_COUNT_TOTALには行のqtrの値がQ1である行の合計数が戻されます。Q2_COUNT_TOTALには行qtrの値がQ2である行の合計数が戻されます。

ここでは、次の構造からなるsales2という表があると仮定します。

PROD_ID   QTR  AMOUNT_SOLD
-------   ---  -----------
100        Q1           10
100        Q1           20
100        Q2         NULL
200        Q1           50

SELECT *
FROM sales2
        PIVOT
         ( SUM(amount_sold), COUNT(*) AS count_total
           FOR qtr IN ('Q1', 'Q2')
         );

PROD_ID    "Q1"   "Q1_COUNT_TOTAL"  "Q2"       "Q2_COUNT_TOTAL"
-------    ----   ----------------  ---------  ----------------
    100      20                  2   NULL <1>                 1
    200      50                  1   NULL <2>                 0

この結果から、prod_idが100の場合、四半期(quarter)がQ1の売上行が2行、四半期がQ2の売上行が1行あること、prod_idが200の場合、四半期がQ1の売上行が1行、四半期がQ2の売上行はないことがわかります。したがって、Q2_COUNT_TOTALの内容から、NULL<1>は元の表内の行のうちメジャーがNULL値のものに由来し、NULL<2>は元の表内でprod_idが200の行が四半期Q2にはなかったために表示されていると識別できます。

アンピボット操作

アンピボット操作は、PIVOT操作を元に戻す操作ではなく、データを列から行に回転する操作です。ピボットされたデータを操作している場合にUNPIVOT操作を行っても、PIVOTまたはその他の手段で作成された集計は元に戻せません。

アンピボットを解説するため、まず、所定の年の四半期を示す4列からなる、ピボットされた表を作成します。

CREATE TABLE pivotedTable AS
SELECT *
FROM (SELECT product, quarter, quantity_sold
      FROM sales_view
     )
    PIVOT
    (
       SUM(quantity_sold)
       FOR quarter IN ('01' AS Q1, '02' AS Q2, '03' AS Q3, '04' AS Q4));

この表の内容は次のとおりです。

SELECT *
FROM pivotedTable
ORDER BY product;

PRODUCT              Q1      Q2      Q3     Q4
-------            ----    ----    ----   ----
Finding Fido       1274    1671    1618   1605
Fly Fishing         716     918    1209   1248
...

次のUNPIVOT操作では、一連の四半期(quarter)の列が回転されて行になります。各製品について、各四半期を表す4つの行が作成されます。

SELECT *
FROM  pivotedTable
   UNPIVOT INCLUDE NULLS
       (
        quantity_sold
        FOR quarter IN (Q1, Q2, Q3, Q4))
ORDER BY product, quarter;

PRODUCT              QU     QUANTITY_SOLD
-------              --     -------------
256MB Memory Card    Q1              1179
256MB Memory Card    Q2              1533
256MB Memory Card    Q3              1455
256MB Memory Card    Q4              1374
...
64MB Memory Card     Q1               414
64MB Memory Card     Q2               215
64MB Memory Card     Q3
64MB Memory Card     Q4                81
...

この例ではINCLUDE NULLSが使用されています。デフォルト設定のEXCLUDE NULLSを使用することもできます。

ワイルド・カードおよび副問合せのXML操作によるピボット操作

ピボットの対象とする列でワイルド・カード引数または副問合せを使用するには、PIVOT XML構文を利用する方法があります。PIVOT XMLを使用した場合、操作の出力は適切に整形されたXMLになります。

次の例に、ワイルド・カード・キーワードANYを使用した場合を示します。この例では、sales_view内のチャネルの値をすべて含むXMLが出力されます。

SELECT *
FROM
   (SELECT product, channel, quantity_sold
    FROM sales_view
    ) PIVOT XML(SUM(quantity_sold)
                FOR channel IN (ANY)
               );

キーワードANYは、PIVOT操作においてはXML操作の一部としてのみ利用できます。この出力には、データセット内にチャネルが存在する場合のデータが含まれます。また、複数の値を戻すには集計関数ではGROUP BY句を指定する必要がありますが、pivot_clauseでは明示的なGROUP BY句は含まれていません。かわりに、pivot_clauseでは暗黙的なGROUP BYを実行します。

次の例では、副問合せを使用する場合を示します。この例では、すべてのチャネルの値と、各チャネルに対応する売上データを含むXMLが出力されます。

SELECT *
FROM
     (SELECT product, channel, quantity_sold
      FROM sales_view
     ) PIVOT XML(SUM(quantity_sold)
                FOR channel IN (SELECT DISTINCT channel_id FROM CHANNELS)
             );

この出力ではデータが稠密化され、各製品についてあり得るチャネルがすべて含まれる状態となります。

その他の分析機能

Oracleには、一連のSQL統計関数と統計パッケージDBMS_STAT_FUNCSが用意されています。この項では、新しい関数の一部を基本的な構文とともに示します。

DBMS_STAT_FUNCSパッケージの詳細は『Oracle Database PL/SQLパッケージ・プロシージャおよびタイプ・リファレンス』を、構文およびセマンティックについては『Oracle Database SQL言語リファレンス』を参照してください。

線形代数

線形代数は数学の一分野であり、実践的にも幅広く応用されています。線形代数を使用して表現できる事柄を研究や分析の対象として含む領域は多く、その一部として例をあげれば、統計学(多重線形回帰分析や主成分分析)、データ・マイニング(クラスタリングや分類)、バイオインフォマティクス(マイクロアレイ・データの分析)、オペレーションズ・リサーチ(サプライ・チェーンなどの最適化問題)、経済学(消費者需要データの分析)、金融(資産分配問題)など様々です。無償で利用できる線形代数用ライブラリも、様々な種類のものが提供されています。OracleのUTL_NLAパッケージでは、強力な線形代数ライブラリとして定評のあるBLASとLAPACKのためのPL/SQL行列データ型やPL/SQLラッパー・サブプログラムを利用できます。

線形代数の土台となっているのは行列演算です。これまで、PL/SQLで行列演算を実行するためには、PL/SQLのネイティブ・データ型を基にした行列の表現を考案した上で、行列演算のルーチンを一から記述する必要がありました。そのためには、きわめて手間のかかるプログラミングが必要となり、それを実装した後のパフォーマンスにも限界がありました。一方、開発者が独自のルーチンを作成せず、データを外部パッケージに送って処理しようとすると、データのやり取りに時間がかかる場合があります。UTL_NLAパッケージを使用すれば、データをOracle内部で処理できるのみでなく、手間のかかるプログラミングも不要になり、高速な実装が実現されます。

例21-19 線形代数

ビジネス分析にOracleの線形代数サポートをどのように活用するかについて、1つの例を紹介します。UTL_NLAパッケージを使用して作成された多重線形回帰アプリケーションを起動します。この多重線形回帰アプリケーションは、OLS_Regressionというオブジェクトに実装されます。OLS Regressionオブジェクトのサンプル・ファイルは、$ORACLE_HOME/plsql/demoにあります。

小売業者がそのマーケティング・プログラムの有効性を分析する場合を考えます。店舗ごとにそのマーケティング費用が、メディア広告(media)、販促活動(promo)、割引券(disct)およびダイレクト・メール(dmail)の各プログラムに割り当てられます。回帰分析を行って、平均的な店舗におけるある年の売上高(sales)と4つのマーケティング・プログラムにかかる費用との間に線形関係を見出します。マーケティング・データが次の表に格納されているとします。

sales_marketing_data (
  /* Store information*/
  store_no   NUMBER,
  year       NUMBER,
  /* Sales revenue (in dollars)*/
  sales      NUMBER,   /* sales amount*/
  /* Marketing expenses (in dollars)*/
  media      NUMBER,   /*media advertisements*/
  promo      NUMBER,   /*promotions*/
  disct      NUMBER,   /*dicount coupons*/
  dmail      NUMBER,   /*direct mailers*/

ここで係数を基にして、次のような売上高とマーケティングの線形モデルを作成できます。

Sales Revenue = a  + b Media Advisements
                   + c Promotions
                   + d Discount Coupons
                   + e Direct Mailer

このモデルは、OLS Regressionオブジェクトを参照する次のようなビューとして実装できます。

CREATE OR REPLACE VIEW sales_marketing_model (year, ols)
   AS SELECT year,
        OLS_Regression(
        /* mean_y => */
        AVG(sales),
        /* variance_y => */
        var_pop(sales),
        /* MV mean vector => */
        UTL_NLA_ARRAY_DBL (AVG(media),AVG(promo),
                           AVG(disct),AVG(dmail)),
        /* VCM variance covariance matrix => */
        UTL_NLA_ARRAY_DBL (var_pop(media),covar_pop(media,promo),
                           covar_pop(media,disct),covar_pop(media,dmail),
                           var_pop(promo),covar_pop(promo,disct),
                           covar_pop(promo,dmail),var_pop(disct),
                           covar_pop(disct,dmail),var_pop(dmail)),
        /* CV covariance vector => */
  UTL_NLA_ARRAY_DBL (covar_pop(sales,media),covar_pop(sales,promo),
                           covar_pop(sales,disct),covar_pop(sales,dmail)))
 FROM sales_marketing_data
 GROUP BY year;

マーケティング・プログラム・マネージャはこのビューを使用することで、「売上高とマーケティングに関するこのモデルは2004年のデータに適合しているか、つまり、この重相関はある許容値(たとえば0.9)よりも大きいか」などの分析を実行できます。これに対応する問合せをSQLで記述するとすれば、次のようになります。

SELECT model.ols.getCorrelation(1)
       AS "Applicability of Linear Model"
FROM sales_marketing_model model
WHERE year = 2004;

また、「2003年にマーケティング・プログラムをまったく実施しなかった店舗の予想基準売上高はいくらか」あるいは「2004年のマーケティング・プログラムではどの要素が最も効果的であったか、つまり、費用増分に対する売上高増分の割合が最大であったプログラムはどれか」などの分析を行い、その結果を得ることが可能です。

UTL_NLAパッケージおよび線形代数の使用方法の詳細は、『Oracle Database PL/SQLパッケージ・プロシージャおよびタイプ・リファレンス』を参照してください。

高頻度項目セット

一定のイベントがどのくらいの頻度で発生するか(たとえば、ある顧客が食料品店でどのくらいの頻度でミルクを購入するか)をカウントするかわりに、複数のイベントがどのくらいの頻度で同時に発生するか(たとえば、ある顧客が食料品店でどのくらいの頻度でミルクとシリアルを同時に購入するか)をカウントできます。こうした複数のイベントをカウントするには、高頻度項目セット(名前のとおり複数項目のセット)というものを使用します。項目セットの例としては、特定の顧客が食料品店での1回の買い物で購入したすべての製品(通常はマーケット・バスケットと呼ばれる)、1回のセッションでユーザーがアクセスしたWebページ、特定の顧客が利用する金融サービスなどがあります。

高頻度項目セットを使用する実際の目的は、最も頻繁に発生する項目セットを見つけることにあります。食料品店のPOSデータを分析する場合であれば、たとえば、同時に購入される項目の組として最も多いものはミルクとバナナであるということがわかるわけです。このため、高頻度項目セットは長い間、小売業界のビジネス・インテリジェンス環境で最も一般的なマーケット・バスケット分析用ツールとして使用されてきました。高頻度項目セットの計算機能はデータベースに統合されています。操作はリレーショナル表の上で行われ、SQLを介してアクセスします。データベースと統合することで、次のような大きな利点があります。

  • これまで高頻度項目セット操作に依存していたアプリケーションの場合は、より単純な実装が可能になり、パフォーマンスが大幅に向上します。

  • これまで高頻度項目セットを使用していなかったSQLベース・アプリケーションの場合は、簡単に拡張してこの機能を利用できるようになります。

高頻度項目セットの分析は、PL/SQLパッケージDBMS_FREQUENT_ITEMSETSで実行されます。詳細は、『Oracle Database PL/SQLパッケージ・プロシージャおよびタイプ・リファレンス』を参照してください。また、高頻度項目セットの使用例は「高頻度項目セット」を参照してください。

記述統計情報

次の記述統計情報を計算できます。

  • データセットの中央値

    Median (expr) [OVER (query_partition_clause)]
    



  • データセットのモード

    STATS_MODE (expr)
    

仮説の検証 - パラメトリック検定

次の記述統計情報を計算できます。

  • 1標本のT-検定

    STATS_T_TEST_ONE (expr1, expr2 (a constant) [, return_value])
    
  • 対標本のT-検定

    STATS_T_TEST_PAIRED (expr1, expr2 [, return_value])
    
  • 独立標本のT-検定。併合分散

    STATS_T_TEST_INDEP (expr1, expr2 [, return_value])
    
  • 独立標本のt-検定。非併合分散

    STATS_T_TEST_INDEPU (expr1, expr2 [, return_value])
    
  • F-検定

    STATS_F_TEST (expr1, expr2 [, return_value])
    
  • 1方向ANOVA

    STATS_ONE_WAY_ANOVA (expr1, expr2 [, return_value])
    

クロス集計統計情報

次の構文を使用して、クロス集計統計情報を計算できます。

STATS_CROSSTAB (expr1, expr2 [, return_value])

次のいずれかの値が戻されます。

  • カイ2乗の測定値

  • カイ2乗の測定値の有意性

  • カイ2乗の自由度

  • ファイ係数、クラメールのV統計

  • コンティンジェンシー係数

  • コーエンのカッパ

仮説の検証 - ノンパラメトリック検定

次の構文を使用して、仮説統計情報を計算できます。

STATS_BINOMIAL_TEST (expr1, expr2, p [, return_value])
  • 2項検定/ウィルコクソンの符号付き順位検定

    STATS_WSR_TEST (expr1, expr2 [, return_value])
    
  • マン・ホイットニー検定

    STATS_MW_TEST (expr1, expr2 [, return_value])
    
  • コルモゴロフ・スミルノフ検定

    STATS_KS_TEST (expr1, expr2 [, return_value])
    

ノンパラメトリック相関

次のパラメトリック統計情報を計算できます。

  • スピアマンのロー係数

    CORR_S (expr1, expr2 [, return_value])
    
  • ケンドールのタウb係数

    CORR_K (expr1, expr2 [, return_value])
    

これらの関数に加え、今回のリリースにはPL/SQLパッケージDBMS_STAT_FUNCSも用意されています。このパッケージには、分布適合をサポートする関数とともに、記述統計関数SUMMARYが含まれています。SUMMARY関数は、様々な記述統計情報を含む表の数値列を集計します。分布適合関数は5つあり、それぞれ正規分布、一様分布、ワイブル分布、ポアソン分布、指数分布をサポートします。

WIDTH_BUCKET関数

WIDTH_BUCKET関数は、特定の式について、この式の結果に対して評価後に割り当てられるバケット番号を戻します。この関数を使用すると、ヒストグラムを生成できます。ヒストグラムでは、データ集合はインターバル・サイズ(最大値から最小値まで)が等しい等幅バケットに分割されます。各バケットに保持される行数は変動します。関連する関数NTILEでは、等度数バケットが作成されます。

ヒストグラムを生成できるのは、数値または日付のデータ型の場合のみです。したがって、最初の3つのパラメータは、すべて数値型またはすべて日付型の式にする必要があります。他の型の式は使用できません。最初のパラメータがNULLの場合、エラー・メッセージが戻されます。2番目または3番目のパラメータがNULLの場合、NULL値は日付または数値のディメンションの範囲について終了点(または点)を示すことができないため、エラー・メッセージが戻されます。最後のパラメータ(バケット数)は、正の整数値に評価される数値型の式にする必要があります。0(ゼロ)、NULLまたは負の値の場合はエラーになります。

バケットには、0〜(n+1)の番号が付いています。バケット0には最小値未満の値のカウントが保持され、バケット(n+1)には指定した最大値以上の値のカウントが保持されます。

WIDTH_BUCKETの構文

WIDTH_BUCKETは、パラメータとして4つの式をとります。最初のパラメータは、ヒストグラムの対象となる式です。2番目と3番目のパラメータは、最初のパラメータについて許容範囲の端点を示す式です。4番目のパラメータは、バケット数を示します。

WIDTH_BUCKET(expression, minval expression, maxval expression, num buckets)

customersからの次のデータを考えてみます。これは、17人の顧客の与信限度額を示しています。このデータは、例21-20の問合せで収集されます。

CUST_ID    CUST_CREDIT_LIMIT
---------  -----------------
    10346               7000
    35266               7000
    41496              15000
    35225              11000
     3424               9000
    28344               1500
    31112               7000
     8962               1500
    15192               3000
    21380               5000
    36651               1500
    30420               5000
     8270               3000
    17268              11000
    14459              11000
    13808               5000
    32497               1500
   100977               9000
   102077               3000
   103066              10000
   101784               5000
   100421              11000
   102343               3000

このcustomers表には、cust_credit_limit列に1500〜15000までの値があり、WIDTH_BUCKET (cust_credit_limit, 0, 20000, 4)を使用すると、これらの値を1〜4の番号が付いた4つの等幅バケットに割り当てることができます。理想的には、各バケットは実際の数値直線のクローズ/オープン・インターバルです。たとえば、バケット番号2は5000.0000〜9999.9999...のスコアに割り当てられており、[5000, 10000)は5,000がインターバルに含まれ、10,000は除外されることを示す場合があります。範囲[0, 20,000)外の他の値に対処するために、0未満の値は番号0で指定されたアンダーフロー・バケットに割り当てられ、20,000以上の値は番号5(通常はバケット数+1)で指定されたオーバーフロー・バケットに割り当てられます。バケットの割当て方法については、図21-3を参照してください。

図21-3 バケットの割当て

図21-3の説明は図の下のリンクをクリックしてください。
「図21-3 バケットの割当て」の説明

バケットの境界は、WIDTH_BUCKET (cust_credit_limit, 20000, 0, 4)のように逆順で指定することもできます。境界が逆順になっている場合、バケットはオープン/クローズ・インターバルとなります。この例で、バケット番号1は(15000,20000]、バケット番号2は(10000,15000]、バケット番号4は(0,5000]となります。オーバーフロー・バケットの番号は0 (20000, +infinity)、アンダーフロー・バケットの番号は5 (-infinity, 0)です。

バケット・カウント・パラメータが0または負の値の場合は、エラーになります。

例21-20 WIDTH_BUCKET

次の問合せは、customers表の与信限度額のバケット番号を示しています。境界は、それぞれ通常の順序および逆順で指定されています。範囲は0〜20,000です。

SELECT cust_id, cust_credit_limit,
 WIDTH_BUCKET(cust_credit_limit,0,20000,4) AS WIDTH_BUCKET_UP,
 WIDTH_BUCKET(cust_credit_limit,20000, 0, 4) AS WIDTH_BUCKET_DOWN
FROM customers WHERE cust_city = 'Marshal';

   CUST_ID CUST_CREDIT_LIMIT WIDTH_BUCKET_UP WIDTH_BUCKET_DOWN
---------- ----------------- --------------- -----------------
     10346              7000               2                 3
     35266              7000               2                 3
     41496             15000               4                 2
     35225             11000               3                 2
      3424              9000               2                 3
     28344              1500               1                 4
     31112              7000               2                 3
      8962              1500               1                 4
     15192              3000               1                 4
     21380              5000               2                 4
     36651              1500               1                 4
     30420              5000               2                 4
      8270              3000               1                 4
     17268             11000               3                 2
     14459             11000               3                 2
     13808              5000               2                 4
     32497              1500               1                 4
    100977              9000               2                 3
    102077              3000               1                 4
    103066             10000               3                 3
    101784              5000               2                 4
    100421             11000               3                 2
    102343              3000               1                 4

ユーザー定義集計関数

Oracleでは、ユーザー定義集計関数と呼ばれる独自の関数を作成できます。この種の関数は、PL/SQL、JavaおよびCなどのプログラミング言語で記述し、マテリアライズド・ビューで分析関数または集計関数として使用できます。構文および制限の詳細は、『Oracle Databaseデータ・カートリッジ開発者ガイド』を参照してください。

この種の関数のメリットを次に示します。

ユーザー定義集計関数の単純な例として、偏りの統計を考えてみます。この計算では、データセットの分散が平均に対して一方に偏っているかどうかが計算され、分散の一方の最後尾が他方より極端に大きい場合が示されます。ユーザー定義集計関数udskewを作成し、前述の例の与信限度額データに適用した場合、SQL文とその結果は次のようになります。

SELECT USERDEF_SKEW(cust_credit_limit) FROM customers
WHERE cust_city='Marshal';

USERDEF_SKEW
============
0.583891

ユーザー定義集計関数を作成する前に、ニーズを通常のSQLで満たすことができるかどうかを考慮する必要があります。SQLでは、特にCASE式を使用すれば、多数の複雑な計算を直接行うことができます。

通常のSQLを使用しても開発を簡素化することは可能です。また、SQLでは多くの問合せ操作がすでに適切にパラレル化されています。前述の例でも、偏りの統計は、長くはなりますが標準的なSQLを使用して作成できます。

CASE式

Oracleでは、現在、単純CASE文と検索CASE文をサポートしています。CASE文は、目的がDECODE文に似ていますが、DECODE文以上の柔軟性および機能性が提供されます。従来のDECODE文より簡単に理解でき、パフォーマンスも向上します。CASE文は、一般に、カテゴリを年齢などのバケット(たとえば20〜29、30〜39など)に分割する場合に使用します。

単純CASE文の構文は、次のとおりです。

CASE expr WHEN comparison_expr THEN return_expr
 [, WHEN comparison_expr THEN return_expr]... [ELSE else_expr] END

単純CASE式は、expr値がcomparison_exprと等価かどうかを調べます。

検索CASE文の構文は、次のとおりです。

CASE WHEN condition THEN return_expr [, WHEN condition THEN return_expr]
   ... ELSE else_expr] END

検索CASE式では、等価性のみではなく、あらゆる種類の条件を指定できます。

指定できる引数の最大数は255です。WHEN ... THENの各組は、2つの引数として数えられます。この制限を超えないようにするには、return_expr自体がCASE式になるようにCASE式をネストします。

例21-21 CASE

ある会社のすべての従業員の平均給与を検索するとします。従業員の給与が2000ドル未満の場合、問合せにはかわりに2000ドルを使用します。CASE文を使用しない場合、この問合せは次のようになります。

SELECT AVG(foo(e.salary)) FROM employees e;

これは、hrサンプル・スキーマに対して実行する問合せです。この場合のfooは、入力が2001以上の場合はその入力値を、それ以外の場合は2000を戻す関数です。この問合せは、各行で関数を起動する必要があるため、パフォーマンスを考慮する必要があります。また、独自関数を記述すると、開発の負荷も大きくなることがあります。

データベースでPL/SQLを使用せずにCASE式を使用すると、この問合せは次のように記述できます。

SELECT AVG(CASE when e.salary > 2000 THEN e.salary ELSE 2000 end)
  AS avg_sal_2k_floor
FROM employees e;

CASE式を使用すると、独自関数を開発する必要がなく、高速で実行できます。

例21-22 独立したサブセットを集計するCASE

データの複数のサブセットに対して集計を実行する際、通常のGROUP BYで対応できない場合は、CASEを集計関数の内部で使用できます。たとえば、前述の例において、それぞれ独自のCASE式を持つ複数のAVG列をSELECT構文のリストに含めることができます。このようにすると、給与が0〜2000または2000〜5000の範囲にある全従業員の平均給与を求める次のような問合せを作成できます。

SELECT AVG(CASE WHEN e.sal BETWEEN 0 AND 2000 THEN e.sal ELSE null END) avg2000,
    AVG(CASE WHEN e.sal BETWEEN 2001 AND 5000 THEN e.sal ELSE null END) avg5000
 FROM emps e;

この問合せでは、独立したサブセット・データの集計結果を別々の列に表示していますが、CASE式をGROUP BY句に追加すると、集計結果を単一の列の複数の行に表示できます。次の項では、CASEを使用してヒストグラムを作成する2つのアプローチとともに、このアプローチの柔軟性を示します。

ユーザー定義のバケットを使用したヒストグラムの作成

ユーザー定義バケット(バケット数および各バケットの幅の両方)を含むヒストグラムを作成する場合は、CASE文を使用します。次に、CASE文で作成されたヒストグラムの例を2つ示します。最初の例では、ヒストグラムの合計が複数の列に示され、単一の行が戻されます。2番目の例では、ヒストグラムはラベル列および単一の合計列で示され、複数の行が戻されます。

例21-23 ヒストグラムの例1

SELECT SUM(CASE WHEN cust_credit_limit BETWEEN  0 AND 3999 THEN 1 ELSE 0 END)
  AS "0-3999",
SUM(CASE WHEN cust_credit_limit BETWEEN  4000 AND 7999 THEN 1 ELSE 0 END)
  AS "4000-7999",
SUM(CASE WHEN cust_credit_limit BETWEEN  8000 AND 11999 THEN 1 ELSE 0 END)
  AS "8000-11999",
SUM(CASE WHEN cust_credit_limit BETWEEN  12000 AND 16000 THEN 1 ELSE 0 END)
  AS "12000-16000"
FROM customers WHERE cust_city = 'Marshal';

    0-3999  4000-7999 8000-11999 12000-16000
---------- ---------- ---------- -----------
         8          7          7           1

例21-24 ヒストグラムの例2

SELECT (CASE WHEN cust_credit_limit BETWEEN  0 AND 3999 THEN  ' 0 - 3999'
   WHEN cust_credit_limit BETWEEN  4000 AND 7999 THEN ' 4000 - 7999'
   WHEN cust_credit_limit BETWEEN  8000 AND 11999 THEN  ' 8000 - 11999'
   WHEN cust_credit_limit BETWEEN  12000 AND 16000 THEN '12000 - 16000' END)
  AS BUCKET, COUNT(*) AS Count_in_Group
FROM customers WHERE cust_city = 'Marshal' GROUP BY
 (CASE WHEN cust_credit_limit BETWEEN  0 AND 3999 THEN ' 0 - 3999'
 WHEN cust_credit_limit BETWEEN  4000 AND 7999 THEN ' 4000 - 7999'
 WHEN cust_credit_limit BETWEEN  8000 AND 11999 THEN  ' 8000 - 11999'
 WHEN cust_credit_limit BETWEEN  12000 AND 16000 THEN '12000 - 16000' END);

BUCKET        COUNT_IN_GROUP
------------- --------------
 0 - 3999                  8
 4000 - 7999               7
 8000 - 11999              7
12000 - 16000              1

レポート用のデータの稠密化

データは通常、スパースな形式で格納されています。つまり、ディメンション値の特定の組合せで値が存在しない場合、ファクト表には行が存在していません。しかし、ファクト・データが存在しない場合でも、ディメンション値のすべての組合せの行を表示し、データを稠密な形式で表示する必要がある場合があります。たとえば、特定の期間において製品が販売されていない場合でも、その期間の売上値をゼロとして製品の横に表示する場合などです。さらに、データが時間ディメンションに沿って稠密であれば、時系列の計算を簡単に実行できます。これは、稠密なデータが期間ごとに一定数の行を占めているため、物理オフセットを指定した分析ウィンドウ関数の使用が単純化されるためです。データの稠密化は、スパース・データを稠密な形式に変換するプロセスです。スパース性の問題を解決するために、パーティション外部結合を使用して時系列間または他のディメンションとの間のギャップを埋めることができます。この結合は、問合せで定義した各論理パーティションに外部結合を適用することで、従来の外部結合の構文を拡張したものです。Oracleでは、PARTITION BY句で指定した式に基づいて、問合せの行を論理的にパーティション化します。パーティション外部結合の結果は、論理的にパーティション化された表にある各パーティションの外部結合と、結合のもう一方の表のUNIONです。

このタイプの結合を使用すると、時間ディメンションに限らず、あらゆるディメンションのギャップを埋めることができます。比較の基準として最も頻繁に使用されるのは時間ディメンションであるため、ここにあげた例のほとんどでは、時間のディメンションを対象としています。

パーティション結合の構文

パーティション外部結合の構文は、ANSI SQLのJOIN句にPARTITION BY句と式のリストを追加したものですリストの式は、外部結合が適用されるグループを指定します。次に、パーティション外部結合で通常使用する2種類の構文を示します。

SELECT .....
FROM table_reference
PARTITION BY (expr [, expr ]... )
RIGHT OUTER JOIN table_reference

SELECT .....
FROM table_reference
LEFT OUTER JOIN table_reference
PARTITION BY {expr [,expr ]...)

FULL OUTER JOINは、パーティション外部結合ではサポートされないので注意してください。

スパースなデータの例

標準的なスパース・ディメンションの状態を次の例に示します。2000〜2001年の20〜30週における製品Bounceの1週間の売上と年度累計の売上が計算されます。

SELECT SUBSTR(p.Prod_Name,1,15) Product_Name, t.Calendar_Year Year,
 t.Calendar_Week_Number Week, SUM(Amount_Sold) Sales
FROM Sales s, Times t, Products p
WHERE s.Time_id = t.Time_id AND s.Prod_id = p.Prod_id AND
 p.Prod_name IN ('Bounce') AND t.Calendar_Year IN (2000,2001) AND
 t.Calendar_Week_Number BETWEEN 20 AND 30
GROUP BY p.Prod_Name, t.Calendar_Year, t.Calendar_Week_Number;

PRODUCT_NAME          YEAR       WEEK      SALES
--------------- ---------- ---------- ----------
Bounce                2000         20        801
Bounce                2000         21    4062.24
Bounce                2000         22    2043.16
Bounce                2000         23    2731.14
Bounce                2000         24    4419.36
Bounce                2000         27    2297.29
Bounce                2000         28    1443.13
Bounce                2000         29    1927.38
Bounce                2000         30    1927.38
Bounce                2001         20     1483.3
Bounce                2001         21    4184.49
Bounce                2001         22    2609.19
Bounce                2001         23    1416.95
Bounce                2001         24    3149.62
Bounce                2001         25    2645.98
Bounce                2001         27    2125.12
Bounce                2001         29    2467.92
Bounce                2001         30    2620.17

この例では、データが稠密化されていれば22行(1年11週間の2年分)になるはずですが、2000年の25週と26週、2001年の26週と28週がないので、実際には18行のみです。

データのギャップを埋める

前の問合せのスパース・データを取り、時間データの稠密なセットでパーティション外部結合を実行できます。次の問合せでは、元の問合せをvという別名にし、tという別名にしたtimes表からデータを選択します。ここでは、時系列にギャップはないので、22行を取得します。追加された4行には、NVL関数で設定されたSales値0があります。

SELECT Product_Name, t.Year, t.Week, NVL(Sales,0) dense_sales
FROM
 (SELECT SUBSTR(p.Prod_Name,1,15) Product_Name,
  t.Calendar_Year Year, t.Calendar_Week_Number Week, SUM(Amount_Sold) Sales
  FROM Sales s, Times t, Products p
  WHERE s.Time_id = t.Time_id AND s.Prod_id = p.Prod_id AND
   p.Prod_name IN ('Bounce') AND t.Calendar_Year IN (2000,2001) AND
   t.Calendar_Week_Number BETWEEN 20 AND 30
  GROUP BY p.Prod_Name, t.Calendar_Year, t.Calendar_Week_Number) v
PARTITION BY (v.Product_Name)
RIGHT OUTER JOIN
 (SELECT DISTINCT Calendar_Week_Number Week, Calendar_Year Year
  FROM Times
  WHERE Calendar_Year IN (2000, 2001)
  AND Calendar_Week_Number BETWEEN 20 AND 30) t
ON (v.week = t.week AND v.Year = t.Year)
ORDER BY t.year, t.week;
PRODUCT_NAME          YEAR       WEEK DENSE_SALES
--------------- ---------- ---------- -----------
Bounce                2000         20         801
Bounce                2000         21     4062.24
Bounce                2000         22     2043.16
Bounce                2000         23     2731.14
Bounce                2000         24     4419.36
Bounce                2000         25           0
Bounce                2000         26           0
Bounce                2000         27     2297.29
Bounce                2000         28     1443.13
Bounce                2000         29     1927.38
Bounce                2000         30     1927.38
Bounce                2001         20      1483.3
Bounce                2001         21     4184.49
Bounce                2001         22     2609.19
Bounce                2001         23     1416.95
Bounce                2001         24     3149.62
Bounce                2001         25     2645.98
Bounce                2001         26           0
Bounce                2001         27     2125.12
Bounce                2001         28           0
Bounce                2001         29     2467.92
Bounce                2001         30     2620.17

この問合せでは、時間ディメンションのインライン・ビューに、20〜30の週のWHERE条件が置かれています。これは、結果セットのサイズを大きくしないために導入されたものです。

2つのディメンションのギャップを埋める

n次元のデータは通常、(n-2)ページのディメンションの稠密な2次元のクロス集計として表示されます。これには、クロス集計内にある2つのディメンションのディメンション値をすべて埋める必要があります。次に示す例では、2つのディメンションのギャップを埋めるために、パーティション外部結合機能が使用されています。

WITH v1 AS
 (SELECT p.prod_id, country_id, calendar_year,
    SUM(quantity_sold) units, SUM(amount_sold) sales
  FROM sales s, products p, customers c, times t
  WHERE s.prod_id in (147, 148) AND t.time_id = s.time_id AND
    c.cust_id = s.cust_id AND p.prod_id = s.prod_id
  GROUP BY p.prod_id, country_id, calendar_year),
v2 AS                                  --countries to use for densifications
  (SELECT DISTINCT country_id
  FROM customers
  WHERE country_id IN (52782, 52785, 52786, 52787, 52788)),
v3 AS                                   --years to use for densifications
  (SELECT DISTINCT calendar_year FROM times)
SELECT v4.prod_id, v4.country_id, v3.calendar_year, units, sales
FROM
  (SELECT prod_id, v2.country_id, calendar_year, units, sales
   FROM v1 PARTITION BY (prod_id)
   RIGHT OUTER JOIN v2                  --densifies on country
   ON (v1.country_id = v2.country_id)) v4
PARTITION BY (prod_id,country_id)
RIGHT OUTER JOIN v3                     --densifies on year
ON (v4.calendar_year = v3.calendar_year)
ORDER BY 1, 2, 3;

この問合せでは、WITH副問合せのファクタリング句v1で、製品、国、年レベルの売上データを集計します。この結果はスパースですが、製品ごとのすべての国、年の組合せを表示する場合もあります。このために、製品の値に基づいたv1の各パーティションを取り、最初に国ディメンションでそれを外部結合します。これによって、製品ごとの国の値がすべて提供されます。次に、結果を取り、それを製品と国の値でパーティション化し、さらに時間ディメンションで外部結合します。これで、製品と国の組合せごとに時間の値がすべて提供されます。

   PROD_ID COUNTRY_ID CALENDAR_YEAR      UNITS      SALES
---------- ---------- ------------- ---------- ----------
       147      52782          1998
       147      52782          1999         29     209.82
       147      52782          2000         71     594.36
       147      52782          2001        345    2754.42
       147      52782          2002
       147      52785          1998          1       7.99
       147      52785          1999
       147      52785          2000
       147      52785          2001
       147      52785          2002
       147      52786          1998          1       7.99
       147      52786          1999
       147      52786          2000          2      15.98
       147      52786          2001
       147      52786          2002
       147      52787          1998
       147      52787          1999
       147      52787          2000
       147      52787          2001
       147      52787          2002
       147      52788          1998
       147      52788          1999
       147      52788          2000          1       7.99
       147      52788          2001
       147      52788          2002
       148      52782          1998        139    4046.67
       148      52782          1999        228    5362.57
       148      52782          2000        251    5629.47
       148      52782          2001        308    7138.98
       148      52782          2002
       148      52785          1998
       148      52785          1999
       148      52785          2000
       148      52785          2001
       148      52785          2002
       148      52786          1998
       148      52786          1999
       148      52786          2000
       148      52786          2001
       148      52786          2002
       148      52787          1998
       148      52787          1999
       148      52787          2000
       148      52787          2001
       148      52787          2002
       148      52788          1998          4     117.23
       148      52788          1999
       148      52788          2000
       148      52788          2001
       148      52788          2002

在庫表のギャップを埋める

在庫表は通常、様々な製品の有効単位数を追跡します。この表は、イベントが発生した場合にのみ製品の行を格納するため、スパースです。sales表の場合、イベントは売上であり、在庫表の場合、イベントは製品の有効数量の変化です。たとえば、次の在庫表を考えてみます。

CREATE TABLE invent_table (
product VARCHAR2(10),
time_id DATE,
quant NUMBER);

INSERT INTO invent_table VALUES
 ('bottle', TO_DATE('01/04/01', 'DD/MM/YY'), 10);
INSERT INTO invent_table VALUES
 ('bottle', TO_DATE('06/04/01', 'DD/MM/YY'), 8);
INSERT INTO invent_table VALUES
 ('can', TO_DATE('01/04/01', 'DD/MM/YY'), 15);
INSERT INTO invent_table VALUES
 ('can', TO_DATE('04/04/01', 'DD/MM/YY'), 11);

在庫表には、次の行があります。

PRODUCT    TIME_ID   QUANT
---------- --------- -----
bottle     01-APR-01    10
bottle     06-APR-01     8
can        01-APR-01    15
can        04-APR-01    11

レポートの目的で、この在庫データを異なる表示にする場合があります。たとえば、各製品の時間の値をすべて表示する必要があるような場合です。これには、パーティション外部結合を使用します。さらに、存在しない期間で新たに挿入された行については、最も近い既存の期間から持ち越された数量列の値を表示する場合があります。これには、分析ウィンドウ関数LAST_VALUEの値を使用します。次に、問合せとその目的とする出力を示します。

WITH v1 AS
 (SELECT time_id
  FROM times
  WHERE times.time_id BETWEEN
   TO_DATE('01/04/01', 'DD/MM/YY')
   AND TO_DATE('07/04/01', 'DD/MM/YY'))
SELECT product, time_id, quant quantity,
  LAST_VALUE(quant IGNORE NULLS)
    OVER (PARTITION BY product ORDER BY time_id)
    repeated_quantity
FROM
 (SELECT product, v1.time_id, quant
  FROM invent_table PARTITION BY (product)
  RIGHT OUTER JOIN v1
  ON (v1.time_id = invent_table.time_id))
ORDER BY 1, 2;

内部問合せは、各製品内の時間でパーティション外部結合を計算します。内部問合せは、時間ディメンションでデータを稠密化します(つまり、時間ディメンションは、週の各曜日の行を持つことになります)。ただし、メジャー列quantityは、新たに追加された行に対してNULLとなります(次の結果のquantity列の出力を参照してください)。

外部問合せでは、分析関数LAST_VALUEを使用しています。この関数を適用すると、製品単位でデータがパーティション化され、時間ディメンション列(time_id)でデータが順序付けられます。行ごとに、この関数は、IGNORE NULLSオプションを基に、ウィンドウ内で最後の非NULL値を検索します。このオプションは、LAST_VALUEFIRST_VALUEの両方で使用できます。次の結果では、repeated_quantity列に目的の出力が表示されます。

PRODUCT    TIME_ID   QUANTITY REPEATED_QUANTITY
---------- --------- -------- -----------------
bottle     01-APR-01       10                10
bottle     02-APR-01                         10
bottle     03-APR-01                         10
bottle     04-APR-01                         10
bottle     05-APR-01                         10
bottle     06-APR-01        8                 8
bottle     07-APR-01                          8
can        01-APR-01       15                15
can        02-APR-01                         15
can        03-APR-01                         15
can        04-APR-01       11                11
can        05-APR-01                         11
can        06-APR-01                         11
can        07-APR-01                         11

ギャップを埋めるデータ値の計算

前の項の例では、パーティション外部結合を使用して1つ以上のディメンションのギャップを埋める方法を示しています。ただし、パーティション外部結合で生成された結果セットでは、PARTITION BYリストに含まれない列にNULL値があります。通常、これらはメジャー列です。分析SQL関数を使用すると、こうしたNULL値を非NULL値に置き換えることができます。

たとえば、次の問合せは、2000年の64MBメモリー・カードおよびDVD-Rディスク(製品IDは122と136)の月の売上合計を計算します。この問合せでは、すべての月のデータを稠密化するために、パーティション外部結合を使用します。売上が存在しない月に対しては、分析SQL関数AVGを使用して、製品の売上が存在する各月の平均売上と平均数量を計算します。

SQL*Plusで作業している場合は、次の2つのコマンドを実行すると、列ヘッダーが折り返されて結果が読みやすくなります。

col computed_units  heading  'Computed|_units'
col computed_sales  heading  'Computed|_sales'

WITH V AS
 (SELECT substr(p.prod_name,1,12) prod_name, calendar_month_desc,
     SUM(quantity_sold) units, SUM(amount_sold) sales
   FROM sales s, products p, times t
   WHERE s.prod_id IN (122,136) AND calendar_year = 2000
     AND t.time_id = s.time_id
     AND p.prod_id = s.prod_id
   GROUP BY p.prod_name, calendar_month_desc)
SELECT v.prod_name, calendar_month_desc, units, sales,
  NVL(units, AVG(units) OVER (partition by v.prod_name)) computed_units,
  NVL(sales, AVG(sales) OVER (partition by v.prod_name)) computed_sales
FROM
  (SELECT DISTINCT calendar_month_desc
   FROM times
   WHERE calendar_year = 2000) t
   LEFT OUTER JOIN V
   PARTITION BY (prod_name)
   USING (calendar_month_desc);
                                              computed   computed
PROD_NAME    CALENDAR      UNITS      SALES     _units     _sales
------------ -------- ---------- ---------- ---------- ----------
64MB Memory  2000-01         112    4129.72        112    4129.72
64MB Memory  2000-02         190       7049        190       7049
64MB Memory  2000-03          47    1724.98         47    1724.98
64MB Memory  2000-04          20      739.4         20      739.4
64MB Memory  2000-05          47    1738.24         47    1738.24
64MB Memory  2000-06          20      739.4         20      739.4
64MB Memory  2000-07                        72.6666667    2686.79
64MB Memory  2000-08                        72.6666667    2686.79
64MB Memory  2000-09                        72.6666667    2686.79
64MB Memory  2000-10                        72.6666667    2686.79
64MB Memory  2000-11                        72.6666667    2686.79
64MB Memory  2000-12                        72.6666667    2686.79
DVD-R Discs, 2000-01         167     3683.5        167     3683.5
DVD-R Discs, 2000-02         152    3362.24        152    3362.24
DVD-R Discs, 2000-03         188    4148.02        188    4148.02
DVD-R Discs, 2000-04         144    3170.09        144    3170.09
DVD-R Discs, 2000-05         189    4164.87        189    4164.87
DVD-R Discs, 2000-06         145    3192.21        145    3192.21
DVD-R Discs, 2000-07                            124.25    2737.71
DVD-R Discs, 2000-08                            124.25    2737.71
DVD-R Discs, 2000-09           1      18.91          1      18.91
DVD-R Discs, 2000-10                            124.25    2737.71
DVD-R Discs, 2000-11                            124.25    2737.71
DVD-R Discs, 2000-12           8     161.84          8     161.84

稠密化したデータに対する時系列の計算

稠密化は、レポートのみを目的としたものではありません。稠密化によって、一部の計算、特に時系列の計算が可能となります。時系列の計算は、データが時間ディメンションに沿って稠密であれば、より簡単になります。稠密なデータは、期間ごとに一定数の行を占めているため、物理オフセットを指定した分析ウィンドウ関数の使用が単純化されます。

実例として、「データのギャップを埋める」に示す例を使用し、その問合せに分析関数を追加します。次の機能強化した例では、週の売上とともに、週次年度累計売上を計算します。時系列の稠密化においてパーティション外部結合により挿入されたNULL値は、通常どおりに処理されます。つまり、SUM関数はこの値を0として処理します。

SELECT Product_Name, t.Year, t.Week, NVL(Sales,0) Current_sales,
 SUM(Sales)
   OVER (PARTITION BY Product_Name, t.year ORDER BY t.week) Cumulative_sales
FROM
 (SELECT SUBSTR(p.Prod_Name,1,15) Product_Name, t.Calendar_Year Year,
   t.Calendar_Week_Number Week, SUM(Amount_Sold) Sales
  FROM Sales s, Times t, Products p
  WHERE s.Time_id = t.Time_id AND
   s.Prod_id = p.Prod_id AND p.Prod_name IN ('Bounce') AND
   t.Calendar_Year IN (2000,2001) AND
   t.Calendar_Week_Number BETWEEN 20 AND 30
  GROUP BY p.Prod_Name, t.Calendar_Year, t.Calendar_Week_Number) v
PARTITION BY (v.Product_Name)
RIGHT OUTER JOIN
(SELECT DISTINCT
 Calendar_Week_Number Week, Calendar_Year Year
 FROM Times
 WHERE Calendar_Year in (2000, 2001)
 AND Calendar_Week_Number BETWEEN 20 AND 30) t
ON (v.week = t.week AND v.Year = t.Year)
ORDER BY t.year, t.week;

PRODUCT_NAME          YEAR       WEEK CURRENT_SALES CUMULATIVE_SALES
--------------- ---------- ---------- ------------- ----------------
Bounce                2000         20           801              801
Bounce                2000         21       4062.24          4863.24
Bounce                2000         22       2043.16           6906.4
Bounce                2000         23       2731.14          9637.54
Bounce                2000         24       4419.36          14056.9
Bounce                2000         25             0          14056.9
Bounce                2000         26             0          14056.9
Bounce                2000         27       2297.29         16354.19
Bounce                2000         28       1443.13         17797.32
Bounce                2000         29       1927.38          19724.7
Bounce                2000         30       1927.38         21652.08
Bounce                2001         20        1483.3           1483.3
Bounce                2001         21       4184.49          5667.79
Bounce                2001         22       2609.19          8276.98
Bounce                2001         23       1416.95          9693.93
Bounce                2001         24       3149.62         12843.55
Bounce                2001         25       2645.98         15489.53
Bounce                2001         26             0         15489.53
Bounce                2001         27       2125.12         17614.65
Bounce                2001         28             0         17614.65
Bounce                2001         29       2467.92         20082.57
Bounce                2001         30       2620.17         22702.74

1つの時間レベルでの周期ごとの比較: 例

ここでは、この機能を使用して、複数の期間にわたって値を比較する方法、特に、週レベルで複数年度にわたる売上の比較を計算する方法について説明します。次の問合せは、同じ行で、2000年と2001年の各製品の週次年度累計売上を戻します。

この例では、はじめにWITH句がきています。これによって、問合せの読みやすさが向上し、パーティション外部結合に集中できます。SQL*Plusで作業している場合は、次のコマンドを実行すると、列ヘッダーが折り返されて結果が読みやすくなります。

col Weekly_ytd_sales_prior_year  heading  'Weekly_ytd|_sales_|prior_year'

WITH v AS
  (SELECT SUBSTR(p.Prod_Name,1,6) Prod, t.Calendar_Year Year,
     t.Calendar_Week_Number Week, SUM(Amount_Sold) Sales
   FROM Sales s, Times t, Products p
   WHERE s.Time_id = t.Time_id AND
     s.Prod_id = p.Prod_id AND p.Prod_name in ('Y Box') AND
     t.Calendar_Year in (2000,2001) AND
     t.Calendar_Week_Number BETWEEN 30 AND 40
   GROUP BY p.Prod_Name, t.Calendar_Year, t.Calendar_Week_Number)
SELECT Prod , Year, Week, Sales,
  Weekly_ytd_sales, Weekly_ytd_sales_prior_year
FROM
  (SELECT Prod, Year, Week, Sales, Weekly_ytd_sales,
     LAG(Weekly_ytd_sales, 1) OVER
       (PARTITION BY Prod , Week ORDER BY Year) Weekly_ytd_sales_prior_year
   FROM
     (SELECT v.Prod  Prod , t.Year Year, t.Week Week,
        NVL(v.Sales,0) Sales, SUM(NVL(v.Sales,0)) OVER
          (PARTITION BY v.Prod , t.Year ORDER BY t.week) weekly_ytd_sales
      FROM v
      PARTITION BY (v.Prod )
      RIGHT OUTER JOIN
        (SELECT DISTINCT Calendar_Week_Number Week, Calendar_Year Year
         FROM Times
         WHERE Calendar_Year IN (2000, 2001)) t
      ON (v.week = t.week AND v.Year = t.Year)
     ) dense_sales
  ) year_over_year_salesWHERE Year = 2001 AND Week BETWEEN 30 AND 40
ORDER BY 1, 2, 3;
                                                         Weekly_ytd
                                                            _sales_
PROD         YEAR       WEEK      SALES WEEKLY_YTD_SALES prior_year
------ ---------- ---------- ---------- ---------------- ----------
Y Box        2001         30    7877.45          7877.45          0
Y Box        2001         31   13082.46         20959.91    1537.35
Y Box        2001         32   11569.02         32528.93    9531.57
Y Box        2001         33   38081.97          70610.9   39048.69
Y Box        2001         34   33109.65        103720.55   69100.79
Y Box        2001         35          0        103720.55   71265.35
Y Box        2001         36     4169.3        107889.85   81156.29
Y Box        2001         37   24616.85         132506.7   95433.09
Y Box        2001         38   37739.65        170246.35  107726.96
Y Box        2001         39     284.95         170531.3   118817.4
Y Box        2001         40   10868.44        181399.74  120969.69

インライン・ビューdense_salesFROM句では、集計ビューvと時間ビューtのパーティション外部結合を使用して、時間ディメンションに沿って売上データのギャップを埋めます。パーティション外部結合の結果が、分析関数SUM ... OVERで処理され、週次年度累計売上(weekly_ytd_sales列)が計算されます。したがって、dense_salesビューは、集計ビューにないものも含めて、週次年度累計売上データを計算します。次に、インライン・ビューyear_over_year_salesは、LAG関数を使用して、1年前の週次年度累計売上を計算します。weekly_ytd_sales_prior_yearというラベルが付けられたLAG関数には、2000年と2001年の同じ週の行を単一のパーティションに組み合せるPARTITION BY句を指定します。1のオフセットをLAG関数に渡し、前年の週次年度累計売上を取得します。最も外側の問合せブロックでは、条件yr = 2001year_over_year_salesのデータを選択します。したがって、この問合せでは各製品について、2001年と2000年の指定した週の週次年度累計売上が戻されます。

複数の時間レベルでの周期ごとの比較: 例

前の例では、単一の時間レベルでの比較を作成する方法を示しましたが、1回の問合せで複数の時間レベルを処理できればより便利となります。たとえば、年、四半期、月、曜日レベルで前期との売上を比較できます。ここでは、時間階層のすべてのレベルで、年度累計売上の年度ごとの比較を実行する問合せの作成方法について説明します。

このタスクを実行するには、いくつかの手順を踏みます。目標は、1回の問合せで日、週、月、四半期、年レベルでの比較を実行することです。手順は次のとおりです。

  1. cube_prod_timeというビューを作成します。このビューは、timesproductsにわたって集計した売上の階層的キューブを保持します。

  2. 次に、キューブのエッジとして使用する時間ディメンションのビューを作成します。完全な日付セットを保持する時間エッジは、cube_prod_timeビューでスパース・データにパーティション外部結合されます。

  3. 最後に、パフォーマンスを最大にするために、マテリアライズド・ビューmv_prod_timeを作成します。これにはcube_prod_timeと同じディメンションを使用します。

階層的キューブの詳細は、第20章「データ・ウェアハウスにおける集計のためのSQL」を参照してください。マテリアライズド・ビューは、次の項の手順1で定義します。


手順1   階層的キューブ・ビューの作成

次に示すマテリアライズド・ビューは、すでにシステムに存在している場合もあります。存在していない場合は、ここで作成します。作成する場合は、処理時間をかけないように、問合せでは2つの製品に制限するので注意してください。

CREATE OR REPLACE VIEW cube_prod_time AS
SELECT
  (CASE
     WHEN ((GROUPING(calendar_year)=0 )
       AND (GROUPING(calendar_quarter_desc)=1 ))
       THEN (TO_CHAR(calendar_year) || '_0')
     WHEN ((GROUPING(calendar_quarter_desc)=0 )
       AND (GROUPING(calendar_month_desc)=1 ))
       THEN (TO_CHAR(calendar_quarter_desc) || '_1')
     WHEN ((GROUPING(calendar_month_desc)=0 )
       AND (GROUPING(t.time_id)=1 ))
       THEN (TO_CHAR(calendar_month_desc) || '_2')
     ELSE (TO_CHAR(t.time_id) || '_3')
  END) Hierarchical_Time,
  calendar_year year, calendar_quarter_desc quarter,
  calendar_month_desc month, t.time_id day,
  prod_category cat, prod_subcategory subcat, p.prod_id prod,
  GROUPING_ID(prod_category, prod_subcategory, p.prod_id,
    calendar_year, calendar_quarter_desc, calendar_month_desc,t.time_id) gid,
  GROUPING_ID(prod_category, prod_subcategory, p.prod_id) gid_p,
  GROUPING_ID(calendar_year, calendar_quarter_desc,
    calendar_month_desc, t.time_id) gid_t,
  SUM(amount_sold) s_sold, COUNT(amount_sold) c_sold, COUNT(*) cnt
FROM SALES s, TIMES t, PRODUCTS p
WHERE s.time_id = t.time_id AND
  p.prod_name IN ('Bounce', 'Y Box') AND s.prod_id = p.prod_id
GROUP BY
  ROLLUP(calendar_year, calendar_quarter_desc, calendar_month_desc, t.time_id),
  ROLLUP(prod_category, prod_subcategory, p.prod_id);

このビューは2つの製品に限定されるため、2200強の行のみが戻されます。Hierarchical_Time列には、時間階層の全レベルの文字列表現があるので注意が必要です。Hierarchical_Time列で使用されるCASE式は、マーカー(_0, _1, ...)を各日付文字列に付加して、値の時間レベルを示します。_0は年レベルを表し、_1は四半期レベル、_2は月レベル、_3は日レベルを表します。GROUP BY句は、時間ディメンションおよび製品ディメンションのロールアップ階層を指定する、連結されたROLLUPです。GROUP BY句によって、階層的キューブの内容が決まります。

手順2   日付値の完全なセットであるビューedge_timeの作成

edge_timeを使用して、パーティション外部結合による階層的キューブの時間のギャップを埋めます。edge_timeHierarchical_Time列が、ビューcube_prod_timeHierarchical_Time列とのパーティション結合に使用されます。edge_timeは次の文で定義します。

CREATE OR REPLACE VIEW edge_time AS
SELECT
  (CASE
     WHEN ((GROUPING(calendar_year)=0 )
       AND (GROUPING(calendar_quarter_desc)=1 ))
       THEN (TO_CHAR(calendar_year) || '_0')
     WHEN ((GROUPING(calendar_quarter_desc)=0 )
       AND (GROUPING(calendar_month_desc)=1 ))
       THEN (TO_CHAR(calendar_quarter_desc) || '_1')
     WHEN ((GROUPING(calendar_month_desc)=0 )
       AND (GROUPING(time_id)=1 ))
       THEN (TO_CHAR(calendar_month_desc) || '_2')
     ELSE (TO_CHAR(time_id) || '_3')
   END) Hierarchical_Time,
   calendar_year yr, calendar_quarter_number qtr_num,
   calendar_quarter_desc qtr, calendar_month_number mon_num,
   calendar_month_desc mon, time_id - TRUNC(time_id, 'YEAR') + 1 day_num,
   time_id day,
GROUPING_ID(calendar_year, calendar_quarter_desc,
  calendar_month_desc, time_id) gid_t
FROM TIMES
GROUP BY ROLLUP
 (calendar_year, (calendar_quarter_desc, calendar_quarter_number),
 (calendar_month_desc, calendar_month_number), time_id);

手順3   パフォーマンス向上をサポートするマテリアライズド・ビューmv_prod_timeの作成

このマテリアライズド・ビューの定義は、これまでに定義したビューcube_prod_timeの定義と同じです。同じ問合せとなるので、cube_prod_timeへの参照はマテリアライズド・ビューmv_prod_timeを使用するように書き換えられます。次のマテリアライズド・ビューは、すでにシステムに存在している場合もあります。存在していない場合は、ここで作成します。作成が必要な場合は、処理時間をかけないように、問合せでは2つの製品に制限するので注意してください。

CREATE MATERIALIZED VIEW mv_prod_time
REFRESH COMPLETE ON DEMAND AS
SELECT
  (CASE
     WHEN ((GROUPING(calendar_year)=0 )
       AND (GROUPING(calendar_quarter_desc)=1 ))
       THEN (TO_CHAR(calendar_year) || '_0')
     WHEN ((GROUPING(calendar_quarter_desc)=0 )
       AND (GROUPING(calendar_month_desc)=1 ))
       THEN (TO_CHAR(calendar_quarter_desc) || '_1')
     WHEN ((GROUPING(calendar_month_desc)=0 )
       AND (GROUPING(t.time_id)=1 ))
       THEN (TO_CHAR(calendar_month_desc) || '_2')
     ELSE (TO_CHAR(t.time_id) || '_3')
  END) Hierarchical_Time,
  calendar_year year, calendar_quarter_desc quarter,
  calendar_month_desc month, t.time_id day,
  prod_category cat, prod_subcategory subcat, p.prod_id prod,
  GROUPING_ID(prod_category, prod_subcategory, p.prod_id,
    calendar_year, calendar_quarter_desc, calendar_month_desc,t.time_id) gid,
  GROUPING_ID(prod_category, prod_subcategory, p.prod_id) gid_p,
  GROUPING_ID(calendar_year, calendar_quarter_desc,
    calendar_month_desc, t.time_id) gid_t,
  SUM(amount_sold) s_sold, COUNT(amount_sold) c_sold, COUNT(*) cnt
FROM SALES s, TIMES t, PRODUCTS p
WHERE s.time_id = t.time_id AND
  p.prod_name IN ('Bounce', 'Y Box') AND s.prod_id = p.prod_id
GROUP BY
  ROLLUP(calendar_year, calendar_quarter_desc, calendar_month_desc, t.time_id),
  ROLLUP(prod_category, prod_subcategory, p.prod_id);

手順4   比較問合せの作成

ここまでで比較問合せの段階が設定されました。すべての時間レベルでの周期ごとの比較計算を取得できます。それには、時間ディメンションに沿った稠密なデータで、分析関数を階層的キューブに適用する必要があります。

次に、各時間レベルで実行可能な計算の一部を示します。

  • すべての時間レベルでの前期の売上の合計

  • 前の期間からの売上の変化

  • すべての時間レベルでの1年前の同じ期間の売上の合計

  • 昨年の同じ期間からの売上の変化

次の例では、この4つの計算をすべて実行します。ビューcube_prod_timeおよびedge_timeのパーティション外部結合を使用して、dense_cube_prod_timeという稠密なデータのインライン・ビューを作成します。続いて、前の単一レベルの例と同様に、問合せでLAG関数を使用します。外部WHERE句は、2001年8月の各日、8月全体、2001年の第3四半期の3つのレベルで時間を指定します。結果の最後の2行には、月レベルと四半期レベルの集計が含まれます。SQL*Plusを使用している場合は、次のコマンドで列ヘッダーを調整すると、結果が読みやすくなります。このコマンドを実行すると、列ヘッダーが折り返されて行が短くなります。

col sales_prior_period heading 'sales_prior|_period'
col variance_prior_period heading 'variance|_prior|_period'
col sales_same_period_prior_year heading 'sales_same|_period_prior|_year'
col variance_same_period_p_year heading 'variance|_same_period|_prior_year'

次に、現在の売上を前期の売上および1年前の売上と比較する問合せを示します。

SELECT SUBSTR(prod,1,4) prod, SUBSTR(Hierarchical_Time,1,12) ht,
  sales, sales_prior_period,
  sales - sales_prior_period variance_prior_period,
  sales_same_period_prior_year,
  sales - sales_same_period_prior_year variance_same_period_p_year
FROM
 (SELECT cat, subcat, prod, gid_p, gid_t,
    Hierarchical_Time, yr, qtr, mon, day, sales,
    LAG(sales, 1) OVER (PARTITION BY gid_p, cat, subcat, prod,
      gid_t ORDER BY yr, qtr, mon, day)
      sales_prior_period,
    LAG(sales, 1) OVER (PARTITION BY gid_p, cat, subcat, prod,
      gid_t, qtr_num, mon_num, day_num ORDER BY yr)
      sales_same_period_prior_year
  FROM
   (SELECT c.gid, c.cat, c.subcat, c.prod, c.gid_p,
      t.gid_t, t.yr, t.qtr, t.qtr_num, t.mon, t.mon_num,
      t.day, t.day_num, t.Hierarchical_Time, NVL(s_sold,0) sales
    FROM cube_prod_time c
    PARTITION BY (gid_p, cat, subcat, prod)
    RIGHT OUTER JOIN edge_time t
    ON ( c.gid_t = t.gid_t AND
      c.Hierarchical_Time = t.Hierarchical_Time)
   ) dense_cube_prod_time
 )                        --side by side current and prior year sales
WHERE prod IN (139) AND gid_p=0 AND    --1 product and product level data
  ( (mon IN ('2001-08' ) AND gid_t IN (0, 1)) OR --day and month data
  (qtr IN ('2001-03' ) AND gid_t IN (3)))        --quarter level data
ORDER BY day;

                                           variance    sales_same     variance
                             sales_prior     _prior _period_prior _same_period
PROD HT                SALES     _period    _period         _year  _prior_year
---- ------------ ---------- ----------- ---------- ------------- ------------
139  01-AUG-01_3           0           0          0             0            0
139  02-AUG-01_3     1347.53           0    1347.53             0      1347.53
139  03-AUG-01_3           0     1347.53   -1347.53         42.36       -42.36
139  04-AUG-01_3       57.83           0      57.83        995.75      -937.92
139  05-AUG-01_3           0       57.83     -57.83             0            0
139  06-AUG-01_3           0           0          0             0            0
139  07-AUG-01_3      134.81           0     134.81        880.27      -745.46
139  08-AUG-01_3     1289.89      134.81    1155.08             0      1289.89
139  09-AUG-01_3           0     1289.89   -1289.89             0            0
139  10-AUG-01_3           0           0          0             0            0
139  11-AUG-01_3           0           0          0             0            0
139  12-AUG-01_3           0           0          0             0            0
139  13-AUG-01_3           0           0          0             0            0
139  14-AUG-01_3           0           0          0             0            0
139  15-AUG-01_3       38.49           0      38.49       1104.55     -1066.06
139  16-AUG-01_3           0       38.49     -38.49             0            0
139  17-AUG-01_3       77.17           0      77.17       1052.03      -974.86
139  18-AUG-01_3     2467.54       77.17    2390.37             0      2467.54
139  19-AUG-01_3           0     2467.54   -2467.54        127.08      -127.08
139  20-AUG-01_3           0           0          0             0            0
139  21-AUG-01_3           0           0          0             0            0
139  22-AUG-01_3           0           0          0             0            0
139  23-AUG-01_3     1371.43           0    1371.43             0      1371.43
139  24-AUG-01_3      153.96     1371.43   -1217.47        2091.3     -1937.34
139  25-AUG-01_3           0      153.96    -153.96             0            0
139  26-AUG-01_3           0           0          0             0            0
139  27-AUG-01_3     1235.48           0    1235.48             0      1235.48
139  28-AUG-01_3       173.3     1235.48   -1062.18       2075.64     -1902.34
139  29-AUG-01_3           0       173.3     -173.3             0            0
139  30-AUG-01_3           0           0          0             0            0
139  31-AUG-01_3           0           0          0             0            0
139  2001-08_2       8347.43     7213.21    1134.22       8368.98       -21.55
139  2001-03_1       24356.8    28862.14   -4505.34      24168.99       187.81

最初のLAG関数(sales_prior_period)により、gid_pcatsubcatprodおよびgid_tのデータがパーティション化され、すべての時間ディメンション列で行が順序付けされます。この関数ではオフセット1を渡すことで前期の売上値が取得されます。2番目のLAG関数(sales_same_period_prior_year)では、この他にqtr_nummon_numおよびday_numの各列でもデータがパーティション化され、yrで順序付けされます。その結果、オフセット1によって1年前の同じ期間の売上が計算されます。最も外側のSELECT句は、分散を計算します。

ディメンションのカスタム・メンバーの作成: 例

多くの分析SQLタスクでは、ディメンションでカスタム・メンバーを定義すると役立ちます。たとえば、分析用に特定の期間を定義するとします。パーティション外部結合を使用して、メンバーを一時的にディメンションに追加できます。新しくSQLに導入されたMODEL句は、ディメンション内の新しいメンバーが関与する複雑なシナリオの作成に適しています。詳細は、第22章「モデリングのSQL」を参照してください。

タスクの例として、timeディメンションの新しいメンバーを定義する場合はどうするかを示します。ここでは、timeディメンションで月レベルの13番目のメンバーを作成します。この13番目の月は、2001年の各四半期の最初の月における各製品の売上の合計として定義します。

これを行うには、2つの手順があります。ここでは、前の例で作成したビューと表を使用します。まず、適切なディメンションに追加される新しいメンバーを持つビューを作成します。ビューでは、UNION ALL演算子を使用して新しいメンバーが追加されます。カスタム・メンバーを使用して問合せを実行するには、CASE式およびパーティション外部結合を使用します。

timeディメンションの新しいメンバーは、次のビューで作成されます。

CREATE OR REPLACE VIEW time_c AS
(SELECT * FROM edge_time
UNION ALL
SELECT '2001-13_2', 2001, 5, '2001-05', 13, '2001-13', null, null,
8 -- <gid_of_mon>
FROM DUAL);

この文では、(前の例で定義した)edge_timeビューとユーザー定義の13番目の月のUNION ALLを実行することで、ビューtime_cが定義されます。標準メンバーからカスタム・メンバーを区別するために、gid_t値として8が選択されています。UNION ALLでは、DUAL表からSELECTを実行することで、13番目の月のメンバーの属性が指定されます。グルーピングid(列gid_t)は8に設定され、四半期の番号は5に設定されます。

したがって、2番目の手順は、問合せでインライン・ビューを使用して、cube_prod_timetime_cのパーティション外部結合を実行することです。この手順では、製品集計の各レベルで13番目の月の売上データを作成します。メインの問合せでは、分析関数SUMCASE式とともに使用され、各四半期の最初の月における売上合計として定義された13番目の月が計算されます。

SELECT * FROM  (SELECT SUBSTR(cat,1,12) cat, SUBSTR(subcat,1,12) subcat,
     prod, mon, mon_num,
     SUM(CASE WHEN mon_num IN (1, 4, 7, 10)
           THEN s_sold
           ELSE NULL
         END)
       OVER (PARTITION BY gid_p, prod, subcat, cat, yr) sales_month_13
   FROM
     (SELECT c.gid, c.prod, c.subcat, c.cat, gid_p,
        t.gid_t, t.day, t.mon, t.mon_num,
        t.qtr, t.yr, NVL(s_sold,0) s_sold
      FROM cube_prod_time c
      PARTITION BY (gid_p, prod, subcat, cat)
      RIGHT OUTER JOIN time_c t
      ON (c.gid_t = t.gid_t AND
        c.Hierarchical_Time = t.Hierarchical_Time)
     )
  )
WHERE mon_num=13;

CAT          SUBCAT             PROD MON         MON_NUM SALES_MONTH_13
------------ ------------ ---------- -------- ---------- --------------
Electronics  Game Console         16 2001-13          13      762334.34
Electronics  Y Box Games         139 2001-13          13       75650.22
Electronics  Game Console            2001-13          13      762334.34
Electronics  Y Box Games             2001-13          13       75650.22
Electronics                          2001-13          13      837984.56
                                     2001-13          13      837984.56

SUM関数では、CASEが使用され、データが各年の1、4、7、10月に制限されます。データセットが2製品のみと小さいため、結果のロールアップ値は、必然的により低いレベルの集計の繰り返しになります。ロールアップ値のより現実的なセットでは、Game ConsoleおよびY Box Gamesサブカテゴリからより多くの製品を、基底のマテリアライズド・ビューに含めることができます。