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

次のトピックでは、Oracleにおける分析SQLの機能および手法について説明します。これらのトピックは、データ・ウェアハウスの観点から表されていますが、分析およびレポートを必要とするすべてのアクティビティに適用可能です。

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

Oracle Databaseでは、大規模な分析SQL関数のファミリを用意しています。このような分析用の関数を使用すると、次の計算が可能です。

  • ランキングとパーセンタイル(百分位数)

  • 変動ウィンドウの計算

  • LAG/LEAD分析

  • FIRST/LAST分析

  • 線形回帰統計

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

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

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

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

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

タイプ 使用目的

ランキング

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

ウィンドウ

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

レポート

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

LAG/LEAD

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

FIRST/LAST

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

線形回帰

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

逆パーセンタイル

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

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

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

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

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

  • 処理順序

    分析関数を使用した問合せ処理は、3つのステップで実行されます。第1に、すべての結合、WHEREGROUP BYおよびHAVING句が実行されます。第2に、結果セットを分析関数で使用できるようになり、その計算がすべて実行されます。第3に、問合せの最後にORDER BY句がある場合、ORDER BYが処理され、正確な出力順序付けが可能になります。処理順序は、図19-1のとおりです。

  • 結果セット・パーティション

    分析関数を使用すると、問合せの結果セットをパーティションと呼ばれる行グループに分割できます。分析関数で使用されるパーティションという用語は、表パーティション機能とは無関係です。この章でパーティションという用語が使用される場合、分析関数に関する意味のみを示します。パーティションはGROUP BY句で定義されているグループの後に作成されるため、SUMやAVGなどのすべての集計結果で使用できます。パーティションは、必要な列または式に基づいて分割することもできます。問合せ結果セットは、すべての行を持つ1つのパーティション、少数の大きなパーティション、またはそれぞれが数行しか持たない多数の小さなパーティションに分割可能です。

  • ウィンドウ

    パーティションの各行に対して、スライドするデータ・ウィンドウを定義できます。このウィンドウで、カレント行の計算に使用される行の範囲が決まります。ウィンドウの大きさは、行の物理数値または時間などのロジカル・インターバルに基づきます。ウィンドウには、開始行および終了行があります。ウィンドウの定義によっては、ウィンドウの片方または両方の末端を移動できます。たとえば、累積合計関数として定義されているウィンドウでは、開始行がパーティションの最初の行に固定されますが、終了行はパーティションの開始点から最後の行までスライドします。一方、移動平均として定義されているウィンドウでは、開始点および終了点の両方がスライドし、一定の物理範囲または論理範囲が維持されます。

    ウィンドウのサイズは、パーティション内の行全体と同じ大きさからパーティション内の1行のスライド・ウィンドウとしてまで様々に設定できます。ウィンドウがパーティションの境界に近い場合、関数からは使用可能な行の結果のみが戻されます。結果がユーザーの必要とするものではないことは警告されません。

    ウィンドウ関数を使用するとカレント行が計算に含まれるため、n個の項目を処理する場合は(n-1)を指定します。

  • カレント行

    分析関数を使用して実行される各計算は、パーティション内のカレント行に基づいて行われます。カレント行は、ウィンドウの開始および終了を判断する際の参照点として機能します。たとえば、中央の移動平均計算は、カレント行とその前の6行およびその後の6行にわたるウィンドウのように定義できます。この場合、図19-2のように、13行の大きさのスライド・ウィンドウが作成されます。

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

図19-2の説明が続きます
「図19-2 スライド・ウィンドウの例」の説明

19.2 ランキング関数、ウィンドウ関数およびレポート関数

この項では、ランキング、ウィンドウおよびレポート用の基本的な分析関数について説明します。次の項目が含まれます。

19.2.1 ランキング関数

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

19.2.1.1 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が表示されている順序は確定的ではありません。

19.2.1.1.1 RANK関数およびDENSE_RANK関数でのランキング順序

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

例19-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句で明示的にソートを指定する必要があります。

19.2.1.1.2 複数の式でのランキング

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

例19-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組の同じ内容の値が解決されています。

この問合せの上位5つの結果のみを表示したい場合は、ORDER BY COL_RANK FETCH FIRST 5 ROWS ONLY文を追加します。詳細は、SQLの行の制限を参照してください。

19.2.1.1.3 例: RANKとDENSE_RANKの違い

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

例19-3 RANKとDENSE_RANK

SELECT channel_desc, calendar_month_desc,
   TO_CHAR(TRUNC(SUM(amount_sold),-5), '9,999,999,999') SALES$,
      RANK() OVER (ORDER BY TRUNC(SUM(amount_sold),-5) DESC) AS RANK,
DENSE_RANK() OVER (ORDER BY TRUNC(SUM(amount_sold),-5) 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では、最大のランク値がデータセット内の個別値の数を示すことに注意します。

19.2.1.1.4 グループ内でのランキング: 例

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

例19-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;

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

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

例19-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
19.2.1.1.5 例: キューブおよびロールアップのグループごとのランキング

RANKなどの分析関数は、CUBEROLLUPまたはGROUPING SETS演算子によるグルーピングに基づいて再設定されます。これは、CUBEROLLUPおよびGROUPING SETS問合せで作成されたグループにランクを割り当てる場合に有効です。GROUPING関数の詳細は、データ・ウェアハウスにおける集計のための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
19.2.1.1.6 例: ランキング関数での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
19.2.1.2 ボトムNランキング関数

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

19.2.1.3 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
19.2.1.4 PERCENT_RANK関数

PERCENT_RANKCUME_DISTと似ていますが、分子に行カウントではなくランク値が使用されます。したがって、値グループに対する値の相対的なパーセント・ランクが戻されます。この関数は、一般的なスプレッドシートで使用できます。ある行の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)
19.2.1.5 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文を完全に指定する必要があります。同等の値は、隣接バケット間で分散されます。確定的な結果を確実に得るには、一意キーで順序付けを行う必要があります。

19.2.1.6 ROW_NUMBER関数

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

ROW_NUMBER ( ) OVER ( [query_partition_clause] order_by_clause )

例19-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-10       1,000,000          1
Direct Sales         2001-09       1,100,000          2
Internet             2001-09         500,000          3
Partners             2001-09         600,000          4
Partners             2001-10         600,000          5
Internet             2001-10         700,000          6

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

19.2.2 ウィンドウ関数

一連のウィンドウ関数を使用すると、累積集計、移動集計および集中集計を計算できます。この種類の関数では、対応するウィンドウ内にある他の行に基づいて、表内の各行に対する値が戻されます。集計ウィンドウ関数群では、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 ]...
      }

 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言語リファレンス』を参照してください。

この項では、次の項目について説明します。

19.2.2.1 ウィンドウ関数に入力したNULLの取扱いについて

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

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

論理オフセットは、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つのみとなります。

例19-7 累積集計関数

2000年の四半期別、顧客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が必要です。ネストされた集計は、分析集計関数できわめて頻繁に使用されます。

例19-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行は指定したよりも小さいインターバルに基づいていることに注意してください。結果セットの境界で見られるこのような異なるウィンドウ・サイズを考慮する必要があります。つまり、必要な内容のみが含まれるように問合せの変更が必要になることがあります。

19.2.2.3 集中集計関数

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

例19-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日にのみ基づいています。前述の例のように、結果セットの境界で見られるこのような異なるウィンドウ・サイズを考慮する必要があります。場合によっては、問合せの調整が必要となります。

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

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

例19-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式を使用できます。

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

特定の条件に応じて、行ごとにウィンドウのサイズを変えると便利な場合があります。特定の日付に対してウィンドウを大きくし、その他の日付には小さくする必要がある場合などです。たとえば、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ファンクションを記述することもできます。

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

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

例19-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の両方を順序付けするという方法があります。

19.2.3 レポート関数

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

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

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

  • アスタリスク(*)は、COUNT(*)でのみ使用できます。

  • DISTINCTは、対応する集計関数で許可されている場合にのみサポートされます。

  • value expression1およびvalue expression2には、列参照または集計を含む有効な式を指定できます。

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

関連項目:

RATIO_TO_REPORT関数

レポート関数は、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

例19-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;
19.2.3.1 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句がない場合、この関数は問合せ結果セット全体に対して計算されます。

例19-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

19.2.4 LAG/LEAD関数

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

NTH_VALUE関数と関連があり、これを単純化したものであると考えられるのがLAG関数とLEAD関数です。LAGLEADでは、指定した物理オフセットの行の値を取得できるだけです。これでは不十分な場合は、NTH_VALUEを使用して、いわゆる論理オフセットまたは相対位置に基づく行の値を取得できます。NTH_VALUE関数ではIGNORE NULLSオプションを指定できます。つまり、条件を指定したり、特定の条件に基づき行を除外できるという意味で、その機能を強化できます。例19-17では、数量が8未満の行はフィルタにより除外されています。これはLAGLEADではできません。目的の行までのオフセットがわからないためです。

詳細は、NTH_VALUE関数およびOracle Database SQL言語リファレンスを参照してください。

19.2.4.1 LAG/LEADの構文

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

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

offsetはオプションのパラメータで、デフォルトは1です。defaultはオプションのパラメータで、offsetが表またはパーティションの境界外となる場合に戻される値です。IGNORE NULLSを指定した場合、戻される値は、NULLの行を無視した上で、指定されたLAGまたはLEADのオフセットの行から取得されます。

例19-14 LAG/LEAD

この例は、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関数を使用して、スパースなデータに対する周期ごとの比較問合せを実行する方法は、レポート用のデータの稠密化を参照してください。

例19-15 IGNORE NULLSを使用したLAG/LEAD

この例は、LAGおよびLEADIGNORE NULLSオプションを指定する一般的な使用例を示しています。

SELECT prod_id, channel_id, SUM(quantity_sold) quantity,
 CASE WHEN SUM(quantity_sold) < 5000 THEN SUM(amount_sold) ELSE NULL END amount,
 LAG(CASE WHEN SUM(quantity_sold) < 5000 THEN SUM(amount_sold) ELSE NULL END)
 IGNORE NULLS OVER (PARTITION BY prod_id ORDER BY channel_id) lag
FROM sales
WHERE prod_id IN (18,127,138)
GROUP BY prod_id, channel_id;

 PROD_ID  CHANNEL_ID  QUANTITY   AMOUNT          LAG
--------  ----------  --------   -----------     ----------
      18           2      2888    4420923.94
      18           3      5615                   4420923.94
      18           4      1088    1545729.81     4420923.94
     127           2      4508     274088.08  
     127           3      9626                    274088.08
     127           4      1850     173682.67      274088.08
     138           2      1120      127390.3       
     138           3      3878     393111.15       127390.3
     138           4       543      71203.21      393111.15

9 rows selected.

19.2.5 FIRST_VALUE関数、LAST_VALUE関数およびNTH_VALUE関数

この項では、次のトピックで説明するFIRST_VALUELAST_VALUEおよびNTH_VALUE関数について説明します。

19.2.5.1 FIRST_VALUE関数およびLAST_VALUE関数

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

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

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

FIRST_VALUE|LAST_VALUE ( <expr> ) [RESPECT NULLS|IGNORE NULLS] OVER (analytic clause );

例19-16 FIRST_VALUE

この例では、FIRST_VALUEIGNORE NULLSオプションを使用しています。

SELECT prod_id, channel_id, time_id,
  CASE WHEN MIN(amount_sold) > 9.5 
    THEN MIN(amount_sold) ELSE NULL END amount_sold, 
  FIRST_VALUE(CASE WHEN MIN(amount_sold) > 9.5 
    THEN min(amount_sold) ELSE NULL END) 
    IGNORE NULLS OVER (PARTITION BY prod_id 
                      ORDER BY channel_id DESC, time_id 
                      ROWS BETWEEN UNBOUNDED PRECEDING 
                      AND UNBOUNDED FOLLOWING) nv FROM sales 
                      WHERE prod_id = 115 AND time_id BETWEEN '18-DEC-01' 
                      AND '22-DEC-01' GROUP BY prod_id, channel_id, time_id 
                      ORDER BY prod_id;

  PROD_ID CHANNEL_ID TIME_ID    AMOUNT_SOLD         NV
---------- ---------- --------- ----------- ----------
       115          4 18-DEC-01                   9.66
       115          4 19-DEC-01                   9.66
       115          4 20-DEC-01                   9.66
       115          4 22-DEC-01                   9.66
       115          3 18-DEC-01        9.66       9.66
       115          3 19-DEC-01        9.66       9.66
       115          3 20-DEC-01        9.66       9.66
       115          3 21-DEC-01        9.66       9.66
       115          3 22-DEC-01        9.66       9.66
       115          2 18-DEC-01        9.67       9.66
       115          2 19-DEC-01        9.67       9.66
       115          2 21-DEC-01        9.67       9.66
       115          2 22-DEC-01        9.67       9.66
 
13 rows selected.
19.2.5.2 NTH_VALUE関数

NTH_VALUE関数では、ウィンドウ内の任意の行から列値を見つけることができます。たとえば、ある企業の株価について、ある年で5番目に高かった終値を検索する場合に使用できます。

NTH_VALUE関数と関連があり、これを単純化したものであると考えられるのがLAG関数とLEAD関数です。LAGLEADでは、指定した物理オフセットの行の値を取得できるだけです。これでは不十分な場合は、NTH_VALUEを使用して、いわゆる論理オフセットまたは相対位置に基づく行の値を取得できます。NTH_VALUEFIRST_VALUEおよびLAST_VALUE関数では、IGNORE NULLSオプションを使用して、条件を指定したり、特定の条件に基づいて行を除外できるという意味で、その機能を強化できます。例19-17では、数量が8未満の行はフィルタにより除外されています。これはLAGLEADではできません。目的の行までのオフセットがわからないためです。

詳細は、『Oracle Database SQL言語リファレンス』を参照してください。

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

NTH_VALUE (<expr>, <n expr>) [FROM FIRST | FROM LAST]
[RESPECT NULLS | IGNORE NULLS] OVER (<window specification>)
  • exprには、列、定数、バインド変数、またはこれらを含む式を指定できます。

  • nには、列、定数、バインド変数、またはこれらを含む式を指定できます。

  • RESPECT NULLSは、NULLを処理するデフォルトのメカニズムです。exprのNULL値を計算に含めるか、計算から除外するかを決定します。デフォルトはRESPECT NULLSです。

  • FROM FIRSTおよびFROM LASTオプションでは、オフセットnが最初の行からか最後の行からかを指定できます。デフォルトはFROM FIRSTです。

  • IGNORE NULLSを指定すると、メジャー値内のNULLをスキップできます。

例19-17 NTH_VALUE

次の例は、prod_idが10から20の範囲の各製品について、昇順で2番目のchannel_idamount_sold値を戻します。

SELECT prod_id, channel_id, MIN(amount_sold), 
  NTH_VALUE(MIN(amount_sold), 2) OVER (PARTITION BY prod_id ORDER BY channel_id
  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) NV 
FROM sales
WHERE prod_id BETWEEN 10 AND 20 GROUP BY prod_id, channel_id;

   PROD_ID CHANNEL_ID MIN(AMOUNT_SOLD)         NV
---------- ---------- ---------------- ----------
        13          2           907.34      906.2
        13          3            906.2      906.2
        13          4           842.21      906.2
        14          2          1015.94    1036.72
        14          3          1036.72    1036.72
        14          4           935.79    1036.72
        15          2           871.19     871.19
        15          3           871.19     871.19
        15          4           871.19     871.19
        16          2           266.84     266.84
        16          3           266.84     266.84
        16          4           266.84     266.84
        16          9            11.99     266.84
...

19.3 分析用の高度な集計

Oracle Databaseは、高度な集計を実行するための複数のSQL関数を提供しています。また、特定の正確な関数には、近似結果を返す対応する関数が提供されています。

この項では、次に示す高度な分析集計関数について説明します。

19.3.1 近似集計について

近似集計は、近似結果を返すSQL関数を使用して計算されます。これらは、正確な値を必要とせず、近似処理が許容されるデータ探索問合せで主に使用されます。

APPROX_COUNT_DISTINCT関数は、指定された式に対して個別値を含む行の概数を返します。APPROX_COUNT_DISTINCT_DETAILおよびAPPROX_COUNT_DISTINCT_AGG関数を使用すると、指定されたグループ内の近似個別値カウントの変動する集計レベルを計算できます。これらの集計結果は、その後の分析またはユーザー問合せに応答するために表またはマテリアライズド・ビューに格納できます。

APPROX_COUNT_DISTINCT_DETAIL関数は、WHERE句にリストされるすべてのディメンションのタプルを含むベースレベルのサマリーをバイナリ形式で作成します。APPROX_COUNT_DISTINCT_AGG関数は、APPROX_COUNT_DISTINCT_DETAIL関数によって生成されたデータを使用して、高レベルのタプルをバイナリ形式で抽出します。これにより、元の計算(この場合はAPPROX_COUNT_DISTINCTを使用した計算)を再実行する必要がなくなります。バイナリ形式を使用する集計データは、TO_APPROX_COUNT_DISTINCTを使用して判読可能な形式に変換されます。

図19-3は、APPROX_COUNT_DISTINCT_DETAILを使用して、各月に販売された各製品の概数を取得する例を示しています。次のような問合せを使用して、my_sales表から選択された販売データは年および月ごとに集計され、SALES_APPROX_MONTH表に格納されます。

INSERT INTO sales_approx_month
   (SELECT year, month, APPROX_COUNT_DISTINCT_DETAIL(prod_id) approx_month
      FROM my_sales
      GROUP BY year, month);

approx_monthに格納される値はバイナリ値であることに注意してください。TO_APPROX_COUNT_DISTINCT関数を使用して、これらのバイナリ値を判読可能な形式で表示します。年および月ごとに集計された各製品の販売数を表示するには、approx_month列に対してTO_APPROX_COUNT_DISTINCT関数を使用します。年ごとに集計されたデータを表示するには、approx_month列に格納されたデータに対してTO_APPROX_COUNT_DISTINCT関数およびAPPROX_COUNT_DISTINCT_AGG関数を使用します。

図19-3 SQL関数を使用した近似集計の表示

図19-3の説明が続きます
「図19-3 SQL関数を使用した近似集計の表示」の説明

各年に販売された各製品の概数を別の方法で計算するには、APPROX_COUNT_DISTINCT_AGGを使用して、SALES_APPROX_MONTH表に格納された月次詳細を集計し、この結果を表またはマテリアライズド・ビューに格納します。

近似パーセンタイル結果を返すSQL関数のプロパティ

近似パーセンタイル結果を提供するSQL関数には、APPROX_PERCENTILEAPPROX_PERCENTILE_DETAILおよびAPPROX_PERCENTILE_AGGがあります。これらの関数には、次の追加プロパティがあります。

  • ERROR_RATE

    近似計算のエラー率を計算することにより、補間されたパーセンタイル値の精度を示します。

  • CONFIDENCE

    エラー率の精度の信頼度を示します(エラー率が指定されている場合)。

  • DETERMINISTIC

    近似処理の計算に使用されるアルゴリズムを制御します。

    一貫性のある繰返し可能な結果が必要な場合は、DETERMINISTICを使用します。通常、これは結果を他のユーザーと共有する必要がある場合です。

関連項目:

19.3.2 LISTAGG関数

LISTAGG関数は、ORDER BY句に基づいて各グループ内のデータを順序付けてから、メジャー列の値を連結します。

Oracle Database 12cリリース2 (12.2)より前のリリースでは、LISTAGG関数によって返された連結値が、戻り値のデータ型でサポートされる最大長を超える場合、次のエラーが返されます。

ORA-01489: 文字列を連結した結果、長さが最大長を超えました

Oracle Database 12cリリース2 (12.2)以降では、戻り値のデータ型でサポートされる最大長内に収まるように戻り文字列を切り捨てて、戻り値が切り捨てられたことを示す切捨てリテラルを表示できます。切捨ては、最後の完全なデータ値の後に実行されるため、不完全なデータ値が表示されることはありません。

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

LISTAGG ( [ALL] <measure_column> [,<delimiter>] [ON OVERFLOW TRUNCATE [truncate_literal] | ON OVERFLOW ERROR  [WITH | WITHOUT COUNT]]) 
	WITHIN GROUP (ORDER BY <oby_expression_list>)

measure_columnには、列、定数、バインド変数、またはこれらを含む式を指定できます。

データ型でサポートされる最大長内に戻り文字列が収まっていない場合、エラーを表示することも、戻り文字列を切り捨てて、切捨てリテラルを表示することもできます。デフォルトはON OVERFLOW ERRORで、切捨てが発生したときにエラーを表示します。

truncate_literalには、NULL、文字列リテラルまたは定数式を指定できます。戻り値のデータ型でサポートされる最大長より長い値をLISTAGGが返した場合、最後のデリミタの後、値のリストの末尾にこれが追加されます。デフォルト値は省略記号(...)です。

WITH COUNTは、戻り値のデータ型でサポートされる最大長を超えたためにLISTAGGの出力から切り捨てられたデータ値の件数を表示します。このオプションがデフォルトです。文字列が切り捨てられたときにLISTAGG関数の末尾に件数を表示しない場合は、WITHOUT COUNTを使用します。

delimiterには、NULL (デフォルト値)、文字列リテラル、バインド変数または定数式を指定できます。これは必須パラメータです。デリミタを指定しない場合は、NULLがデリミタとして使用されます。

oby_expression_listには、任意の順序付けオプションを含む式のリストを指定できます。昇順または降順でソートしたり(ASCまたはDESC)、NULLのソート順を制御したり(NULLS FIRSTまたはNULLS LAST)するときに使用します。デフォルトは、ASCENDINGおよびNULLS LASTです。

関連項目:

VARCHAR2データ型でサポートされる最大長の詳細は、Oracle Database SQL言語リファレンスを参照してください。

19.3.2.1 集計として使用するLISTAGG

LISTAGG関数を集計として使用できます。

例19-18 集計として使用するLISTAGG

次の例では、LISTAGGを集計として使用しています。

SELECT prod_id, LISTAGG(cust_first_name||' '||cust_last_name, '; ') 
  WITHIN GROUP (ORDER BY amount_sold DESC) cust_list
FROM sales, customers
WHERE sales.cust_id = customers.cust_id AND cust_gender = 'M' 
  AND cust_credit_limit = 15000 AND prod_id BETWEEN 15 AND 18 
  AND channel_id = 2 AND time_id > '01-JAN-01'
GROUP BY prod_id;

PROD_ID  CUST_LIST
-------  -----------------------------------------------
     15  Hope Haarper; Roxanne Crocker; ... Mason Murray
     16  Manvil Austin; Bud Pinkston; ... Helga Nickols
     17  Opal Aaron; Thacher Rudder; ... Roxanne Crocker
     18  Boyd Lin; Bud Pinkston; ... Erik Ready

この出力は、読みやすく変更されています。この場合、省略記号は、最後の顧客名の前のいくつかの値が出力から省略されていることを示します。

例19-19 戻り文字列が最大許容長を超えるLISTAGG

この例では、GROUP BY句で指定した各グループ内のデータが順序付けられ、cust_first_nameおよびcust_last_name列の値が連結されます。連結された名前のリストが、VARCHAR2データ型でサポートされる最大長を超える場合、リストは最後の完全な名前まで切り捨てられます。リストの末尾に、「...」というオーバーフロー・リテラルが追加され、その後に、切り捨てられた値の件数が表示されます。

SELECT country_region,
  LISTAGG(s.CUST_FIRST_NAME||' '|| s.CUST_LAST_NAME, ';' ON OVERFLOW TRUNCATE WITH COUNT) WITHIN GROUP (ORDER BY s.cust_id) AS customer_names
FROM countries c, customers s
WHERE c.country_id = s.country_id
GROUP BY c.country_region
ORDER BY c.country_region;

COUNTRY_REGION
--------------------
CUSTOMER_NAMES
--------------------------------------------------------------------------------
Africa
Laurice Lincoln;Kirsten Newkirk;Verna Yarborough;Chloe Dwyer;Betty Sampler;Terry
 Hole;Waren Parkburg;Uwe Feldman;Douglas Hanson;Woodrow Lazar;Alfred Doctor;Stac
.
.
Zwolinsky;Buzz Milenova;Abbie Venkayala

COUNTRY_REGION
--------------------
CUSTOMER_NAMES
--------------------------------------------------------------------------------
Americas
Linette Ingram;Vida Puleo;Gertrude Atkins;Sibil Haul;Raina Cassidy;Kaula Daley;G
abriela Sean;Dolores Moore;Erica Vandermark;Madallyn Ladd;Carolyn Hinkle;Leonora
.
.
emphill;Urban Smyth;Murry Ivy;Steven Lauers;...(21482)

COUNTRY_REGION
--------------------
CUSTOMER_NAMES
--------------------------------------------------------------------------------
Asia
Harriett Charles;Willa Fitz;Faith Fischer;Gay Nance;Maggie Cain;Neda Clatterbuck
;Justa Killman;Penelope Oliver;Mandisa Grandy;Marette Overton;Astrid Rice;Poppy
.
.
ob Gentile;Lynn Hardesty;Mabel Barajas;...(1648)

COUNTRY_REGION
--------------------
CUSTOMER_NAMES
--------------------------------------------------------------------------------
Europe
Abigail Kessel;Anne Koch;Buick Emmerson;Frank Hardy;Macklin Gowen;Rosamond Kride
r;Raina Silverberg;Gloria Saintclair;Macy Littlefield;Yuri Finch;Bertilde Sexton
.
.
el Floyd;Lincoln Sean;Morel Gregory;Kane Speer;...(30284)

COUNTRY_REGION
--------------------
CUSTOMER_NAMES
--------------------------------------------------------------------------------
Middle East
Dalila Rockwell;Alma Elliott;Cara Jeffreys;Joy Sandstrum;Elizabeth Barone;Whitby
 Burnns;Geoffrey Door;Austin Dutton;Tobin Newcomer;Blake Overton;Lona Kimball;Lo
.
.
edy;Brandon Moy;Sydney Fenton

COUNTRY_REGION
--------------------
CUSTOMER_NAMES
--------------------------------------------------------------------------------
Oceania
Fredericka Umstatt;Viola Nettles;Alyce Reagan;Catherine Odenwalld;Mauritia Linde
green;Heidi Schmidt;Ray Wade;Cicily Graham;Myrtle Joseph;Joan Morales;Brenda Obr
.
.
;Fredie Elgin;Gilchrist Lease;Guthrey Cain;...(793)

6 rows selected.
19.3.2.2 集計レポートとして使用するLISTAGG

LISTAGG関数を集計レポートとして使用できます。

例19-20 集計レポートとして使用するLISTAGG

この例では、LISTAGGを集計レポートとして使用しています。各期間内の各製品の最も低い原価を抽出します。

connect sh/sh
set lines 120 pages 20000
column list format A40

SELECT time_id, prod_id, LISTAGG(MIN(unit_cost),';') 
      WITHIN GROUP (ORDER BY prod_id) OVER (PARTITION BY time_id) lowest_unit_cost
  FROM sh.sales_transactions_ext
  WHERE time_id BETWEEN '20-DEC-01' AND '22-DEC-01' AND prod_id BETWEEN 120 AND 125
  GROUP BY time_id, prod_id;

    TIME_ID      PROD_ID   LOWEST_UNIT_COST    
    --------- ----------   -----------------------------------
    20-DEC-01        121    9.11;9.27;15.84;43.95
    20-DEC-01        122    9.11;9.27;15.84;43.95
    20-DEC-01        123    9.11;9.27;15.84;43.95
    21-DEC-01        120    9.11;9.27
    21-DEC-01        121    9.11;9.27
    22-DEC-01        120    9.11;9.27;15.84;43.95;16.06;12.66
    22-DEC-01        121    9.11;9.27;15.84;43.95;16.06;12.66
    22-DEC-01        122    9.11;9.27;15.84;43.95;16.06;12.66
    22-DEC-01        123    9.11;9.27;15.84;43.95;16.06;12.66
    22-DEC-01        124    9.11;9.27;15.84;43.95;16.06;12.66
    22-DEC-01        125    9.11;9.27;15.84;43.95;16.06;12.66

19.3.3 FIRST/LAST関数

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

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

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

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

この項では、次の項目について説明します。

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

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

例19-21 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
19.3.3.2 集計レポートとしてのFIRSTおよびLAST

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

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

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

例19-22 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関数を集計レポートとして使用すると、その結果を簡単に「最高給与に対する給与の割合」などの計算に簡単に組み込むことができます。

19.3.4 逆パーセンタイル関数

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

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

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

  • 0から1までの値をとるパラメータが必要です。パラメータにこの範囲外の値を指定した場合、エラーとなります。このパラメータには、定数に評価される式を指定する必要があります。

  • ソート指定が必要です。ソートは、ORDER BY句に単一の式を指定する形で指定します。複数の式は使用できません。

Oracle Database 12cリリース2 (12.2)以降では、近似逆分布関数APPROX_PERCENTILEは、そのソート指定に従ってそのパーセンタイル値に該当する近似の補間された値を戻します。

19.3.4.1 通常の集計の構文
[PERCENTILE_CONT | PERCENTILE_DISC]( constant expression ) 
    WITHIN GROUP ( ORDER BY single order by expression
[ASC|DESC] [NULLS FIRST| NULLS LAST])
19.3.4.2 逆パーセンタイルの例

次の問合せを使用して、この項の例で使用したデータのうち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句に使用できます。

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

集計関数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
19.3.4.4 逆パーセンタイル関数の制限

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が無視されるので、こうしたオプションも結果的には無視されます。

19.3.4.5 近似結果を返すパーセンタイル関数の使用

Oracle Databaseには、近似パーセンタイル結果を返すSQL関数のセットが用意されています。これらの関数を使用して、品質の監視、ソーシャル・メディア・アクティビティの追跡、パフォーマンスの監視およびデータ・セット内の外れ値の検索を行うことができます。

次のSQL関数は、近似パーセンタイル結果を計算および表示します。

  • APPROX_PERCENTILE

    ソート指定に従ってそのパーセンタイル値に該当する補間された近似値を返します。これは、正確な結果とわずかに誤差がありますが、PERCENTILE_CONTよりはるかに高速に大量のデータを処理できます。

  • APPROX_PERCENTILE_DETAIL

    GROUP BY句を使用して指定された一連のデータ内の、詳細と呼ばれる近似パーセンタイル情報を計算します。この関数で作成された詳細情報はバイナリ形式で格納されており、TO_APPROX_PERCENTILEAPPROX_PERCENT_DETAIL_AGGの両方の関数で使用されることを想定しています。

  • APPROX_PERCENTILE_AGG

    APPROX_PERCENTILE_DETAIL関数を使用して作成された詳細の集計を実行します。

  • TO_APPROX_PECENTILE

    詳細または集計の結果(BLOB値として格納されている)を判読可能な形式で表示します。

詳細および高レベルの集計データは、その後の分析のために表またはマテリアライズド・ビューに格納できます。

例: 国または州内の近似パーセンタイル販売データの表示

この例では、APPROX_PERCENTILE_DETAILを使用して、パーセンタイル計算を1回実行し、この結果を表に格納し、格納されたデータに基づいて近似集計を実行します。TO_APPROX_PERCENTILE関数は、パーセンタイル計算の結果を判読可能な形式で表示するために使用されます。

  1. APPROX_PERCENTILE_DETAILを使用して、各州の販売額の近似パーセンタイルを計算し、approx_sales_percentile_detailと呼ばれる表にこの結果を格納します。
    CREATE TABLE approx_sales_percentile_detail AS
    SELECT c.country_id country, c.cust_state_province state, approx_percentile_detail(amount_sold) detail
    FROM sales s, customers c
    WHERE s.cust_id = c.cust_id
    GROUP BY c.country_id, c.cust_state_province;
  2. TO_APPROX_PERCENTILEを使用して、詳細を問い合せ、表に格納されている値を集計してこれらの値を判読可能な形式で表示します。

    次の文は、APPROX_PERCENTILE_AGG関数を使用して、approx_sales_percentile_detail表に格納されている詳細データをさらに集計します。TO_APPROX_PERCENTILE関数は、集計結果を判読可能な形式で表示します。

    SELECT country, to_approx_percentile(approx_percentile_agg(detail),0.5) median_amt_sold
    FROM approx_sales_percentile_detail 
    GROUP BY country
    ORDER BY country;
    
       COUNTRY   MEDIAN_AMT_SOLD
    ----------    ---------------
         52769	      33.5
         52770	     35.92
         52771	     44.99
         52772	     35.55
         52773	     29.61
         52774	     35.55
         52775	     42.09
         52776	     34.67
         52777	      38.1
         52778	     38.35
         52779	     38.67
         52782	     36.89
         52785	     22.99
         52786	     44.99
         52787	     27.99
         52788	     27.13
         52789	     37.79
         52790	     33.69
    
    18 rows selected.
    
    

    関連項目:

    Oracle Database SQL言語リファレンスAPROX_PERCENTILEAPPROX_PERCENTILE_DETAILAPPROX_PERCENTILE_AGGおよびTO_APPROX_PERCENTILE

19.3.5 仮説ランク関数

この種の関数では、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オプションを使用できます。

例19-23 仮説ランク関数および仮説分布関数の例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つの引数が使用されています。

例19-24 仮説ランク関数および仮説分布関数の例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句に使用できます。集計レポート関数や集計ウィンドウ関数としては使用できません。

19.3.6 線形回帰関数

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

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

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

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

19.3.6.1 REGR_COUNT関数

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

19.3.6.2 REGR_AVGY関数およびREGR_AVGX関数

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

19.3.6.3 REGR_SLOPE関数およびREGR_INTERCEPT関数

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

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

19.3.6.4 REGR_R2関数

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

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

19.3.6.5 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)
19.3.6.6 線形回帰統計の例

線形回帰分析を伴う一般的な診断統計の一部を表19-2に示します。Oracleにより、これらすべてを計算できます。

表19-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))

19.3.6.7 線形回帰計算の例

この例では、製品の販売数量をその製品の定価の線形関数として表す、微分最小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

19.3.7 統計集計について

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

関連項目:

この項では、次の項目について説明します。

19.3.7.1 記述統計情報

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

  • データセットの中央値

    Median (expr) [OVER (query_partition_clause)]
  • データセットのモード

    STATS_MODE (expr)

Oracle Database 12cリリース2 (12.2)以降では、近似逆分布関数APPROX_MEDIANは、指定した式の近似中央値を返します。

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

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

  • 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])
19.3.7.3 クロス集計統計情報

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

STATS_CROSSTAB (expr1, expr2 [, return_value])

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

  • カイ2乗の測定値

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

  • カイ2乗の自由度

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

  • 一致係数

  • コーエンのカッパ

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

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

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])
19.3.7.5 ノンパラメトリック相関

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

  • スピアマンのロー係数

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

    CORR_K (expr1, expr2 [, return_value])
    

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

19.3.8 ユーザー定義集計について

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を使用して作成できます。

19.4 ピボット操作

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

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

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

この項では、次の項目について説明します。

19.4.1 ピボットの例に使用するビューの作成

ピボットとアンピボットの例は、sales_viewビューに基づいています。

例19-25 ピボットの例のためのSALES_VIEWビューの作成

次の例は、ピボットの使用を示すためのベースとして使用するsales_viewビューを作成します。

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);

19.4.2 ピボットの例

次の文は、例19-25の説明に従って作成したビューsales_viewchannel列に対する一般的なピボットを示しています。

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リストの値となります。

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

ピボットは複数の列に対して実行できます。次の文は、例19-25の説明に従って作成したビューsales_viewに対する一般的な複数列のピボットを示しています。

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リストのメンバーに一致するような列ヘッダーが与えられています。

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

次の例に示すように、複数の集計を使用してピボットを実行できます。この例では、例19-25で作成したsales_viewから複数の集計に対するピボットを実行しています。

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   
... 
 

この問合せでは、ピボットの値、アンダースコア文字および集計列の別名を連結することにより、列ヘッダーが作成されます。生成された列見出しの長さが列名の最大長より長い場合、ORA-00918エラーが戻されます。このエラーを回避するには、AS aliasを使用して、ピボット列見出しや集計値列の名前、またはその両方のためのより短い列の別名を指定します。ピボット値への別名使用については、複数列に対するピボット操作で説明しています。

関連項目:

列名の最大長に関する詳細は、『Oracle Database SQL言語リファレンス』を参照してください。

19.4.5 ソース・データ内の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にはなかったために表示されていると識別できます。

19.4.6 ワイルド・カードおよび副問合せの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)
               );

ビューsales_viewを作成する構文については、例19-25を参照してください。

キーワード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)
             );

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

19.5 アンピボット操作

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

アンピボットを解説するため、まず、所定の年の四半期を示す4列からなる、ピボットされた表を作成します。次のコマンドは、例19-25で作成したビューsales_viewに基づく表を作成します。

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

表の内容は次のようになります。

SELECT *
FROM pivotedTable
ORDER BY product;

PRODUCT         Q1_SUMQ  Q1_SUMA  Q2_SUMQ  Q2_SUMA   Q3_SUMQ  Q3_SUMA   Q4_SUMQ    Q4_SUMA
--------------- -------  -------  -------  --------  -------  --------  -------    ---------
1.44MB External   6098   58301.33    5112   49001.56    6050   56974.3     5848     55341.28
128MB Memory      1963  110763.63    2361  132123.12    3069  170710.4     2832    157736.6
17" LCD           1492 1812786.94    1387 1672389.06    1591 1859987.66    1540   1844008.11
...

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

SELECT product, DECODE(quarter, 'Q1_SUMQ', 'Q1', 'Q2_SUMQ', 'Q2', 'Q3_SUMQ', 'Q3',
   'Q4_SUMQ', 'Q4') AS quarter, quantity_sold
FROM  pivotedTable
   UNPIVOT INCLUDE NULLS
       (quantity_sold
        FOR quarter IN (Q1_SUMQ, Q2_SUMQ, Q3_SUMQ, Q4_SUMQ))
ORDER BY product, quarter;

PRODUCT                          QU    QUANTITY_SOLD
-------                          --    -------------
1.44MB External 3.5" Diskette    Q1             6098
1.44MB External 3.5" Diskette    Q2             5112
1.44MB External 3.5" Diskette    Q3             6050
1.44MB External 3.5" Diskette    Q4             5848
128MB Memory Card                Q1             1963
128MB Memory Card                Q2             2361
128MB Memory Card                Q3             3069
128MB Memory Card                Q4             2832
...

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

さらに、次のように、2つの列を使用してアンピボット操作を行うことができます。

SELECT product, quarter, quantity_sold, amount_sold
FROM  pivotedTable
   UNPIVOT INCLUDE NULLS
       (
        (quantity_sold, amount_sold)
        FOR quarter IN ((Q1_SUMQ, Q1_SUMA) AS 'Q1', (Q2_SUMQ, Q2_SUMA) AS 'Q2', (Q3_SUMQ, Q3_SUMA) AS 'Q3', (Q4_SUMQ, Q4_SUMA) AS 'Q4'))
ORDER BY product, quarter;
 
PRODUCT                          QU   QUANTITY_SOLD   AMOUNT_SOLD
-----------------------------    --   -------------   ------------
1.44MB External 3.5" Diskette    Q1            6098       58301.33
1.44MB External 3.5" Diskette    Q2            5112       49001.56
1.44MB External 3.5" Diskette    Q3            6050       56974.3
1.44MB External 3.5" Diskette    Q4            5848       55341.28
128MB Memory Card                Q1            1963      110763.63
128MB Memory Card                Q2            2361      132123.12
128MB Memory Card                Q3            3069      170710.4
128MB Memory Card                Q4            2832      157736.6

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

データは通常、スパースな形式で格納されています。つまり、ディメンション値の特定の組合せで値が存在しない場合、ファクト表には行が存在していません。しかし、ファクト・データが存在しない場合でも、ディメンション値のすべての組合せの行を表示し、データを稠密な形式で表示する必要がある場合があります。たとえば、特定の期間において製品が販売されていない場合でも、その期間の売上値をゼロとして製品の横に表示する場合などです。さらに、データが時間ディメンションに沿って稠密であれば、時系列の計算を簡単に実行できます。これは、稠密なデータが期間ごとに一定数の行を占めているため、物理オフセットを指定した分析ウィンドウ関数の使用が単純化されるためです。データの稠密化は、スパース・データを稠密な形式に変換するプロセスです。

スパース性の問題を解決するために、パーティション外部結合を使用して時系列または他のディメンションとのギャップを埋めることができます。この結合は、問合せで定義した各論理パーティションに外部結合を適用することで、従来の外部結合の構文を拡張したものです。Oracleでは、PARTITION BY句で指定した式に基づいて、問合せの行を論理的にパーティション化します。パーティション外部結合の結果は、論理的にパーティション化された表にある各パーティションの外部結合と、結合のもう一方の表のUNIONです。

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

この項では、次の項目について説明します。

19.6.1 パーティション結合の構文について

パーティション外部結合の構文は、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は、パーティション外部結合ではサポートされないので注意してください。

19.6.2 スパースなデータの例

標準的なスパース・ディメンションの状態を次の例に示します。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行のみです。

19.6.3 データのギャップ補完

スパースなデータの例に示す問合せのスパース・データを取り、時間データの稠密なセットでパーティション外部結合を実行できます。次の問合せでは、元の問合せを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条件が置かれています。これは、結果セットのサイズを大きくしないために導入されたものです。

19.6.4 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

19.6.5 在庫表のギャップ補完

在庫表は通常、様々な製品の有効単位数を追跡します。この表は、イベントが発生した場合にのみ製品の行を格納するため、スパースです。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

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

データのギャップ補完2つのディメンションのギャップ補完在庫表のギャップ補完の各項の例は、パーティション外部結合を使用して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

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

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

実例として、データのギャップ補完に示す例を使用し、その問合せに分析関数を追加します。次の機能強化した例では、週の売上とともに、週次年度累計売上を計算します。時系列の稠密化においてパーティション外部結合により挿入された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

この項では、次の項目について説明します。

19.7.1 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_sales
WHERE 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年の指定した週の週次年度累計売上が戻されます。

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

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

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

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

    階層的キューブ・ビューの作成を参照してください。

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

    日付値の完全なセットであるビューedge_timeの作成を参照してください。

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

    パフォーマンス向上をサポートするマテリアライズド・ビューmv_prod_timeの作成を参照してください。

  4. 比較問合せを作成します。

    比較問合せの作成を参照してください。

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

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

次に示すマテリアライズド・ビューは、すでにシステムに存在している場合もあります。存在していない場合は、ここで作成します。作成が必要な場合は、処理時間をかけないように、問合せでは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句によって、階層的キューブの内容が決まります。

日付値の完全なセットであるビュー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);

パフォーマンス向上をサポートするマテリアライズド・ビュー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);

比較問合せの作成

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

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

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

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

  • すべての時間レベルでの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句は、分散を計算します。

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

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

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

これを行うには、2つのステップがあります。ここでは、前の例で作成したビューと表を使用します。この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サブカテゴリからより多くの製品を、基底のマテリアライズド・ビューに含めることができます。

19.8 その他の分析およびレポートの機能

この項では、次に示す追加の分析機能について説明します。

19.8.1 WIDTH_BUCKET関数

WIDTH_BUCKET関数は、特定の式について、この式の結果に対して評価後に割り当てられるバケット番号を戻します。WIDTH_BUCKETの構文では、WIDTH_BUCKETの構文について説明します。

この関数を使用すると、ヒストグラムを生成できます。ヒストグラムでは、データ集合はインターバル・サイズ(最大値から最小値まで)が等しい等幅バケットに分割されます。各バケットに保持される行数は変動します。関連する関数NTILEでは、等度数バケットが作成されます。

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

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

19.8.1.1 WIDTH_BUCKETの構文

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

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

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

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)で指定されたオーバーフロー・バケットに割り当てられます。バケットの割当て方法については、図19-4を参照してください。

図19-4 バケットの割当て

図19-4の説明が続きます
「図19-4 バケットの割当て」の説明

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

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

例19-26 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

19.8.2 線形代数

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

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

関連項目:

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

例19-27 線形代数

ビジネス分析に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,   /*discount 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)よりも大きいか」などの分析を実行できます。では、相関が複数の場合は、許容値(0.9など)よりも大きくなりますか。これに対応する問合せをSQLで記述するとすれば、次のようになります。

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

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

19.8.3 CASE式

Oracleでは、現在、単純および検索CASE文をサポートしています。CASE文は、目的がDECODE文に似ていますが、DECODE文以上の柔軟性および機能性が提供されます。従来のDECODE文より簡単に理解でき、パフォーマンスも向上します。一般に、カテゴリを年齢などのバケット(たとえば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式では、等価性のみではなく、あらゆる種類の条件を指定できます。

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

関連項目:

CASEを使用してヒストグラムを作成する方法については、CASE文を使用したヒストグラムの作成を参照してください。

例19-28 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式を使用すると、独自関数を開発する必要がなく、高速で実行できます。

例19-29 独立したサブセットを集計する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つのアプローチとともに、このアプローチの柔軟性を示します。

19.8.3.1 CASE文を使用したヒストグラムの作成

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

例19-30 ヒストグラムの例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

例19-31 ヒストグラムの例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

19.8.4 SQL分析の高頻度項目セット

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

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

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

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

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

19.9 SQLの行の制限

特定の行数または行の割合によって、SQL問合せから戻される行を制限できます。場合によっては、問合せ結果を順序付けてから戻される行数を制限する必要が生じることもあります。最初に行をソートしてから戻す行数を制限する問合せは、通常、上位N問合せと呼ばれます。これにより、「給料の高い従業員の上位10人はだれか」などのような基本的な質問のレポートまたは単純なビューを簡単に作成できます。これは、参照用にデータセットの最初の数行を示すユーザー・インタフェースにも役立ちます。上位N問合せを発行する際に、オフセットを指定することもできます。オフセットにより、問合せ結果セットの先頭の数行が除外されます。すると問合せは、オフセットの後の最初の行から、指定の数または割合の行を戻します。オフセットにより、一般的な質問を変更し、最も給料の高い従業員に関する質問で上位10人の従業員をスキップして給料のランキングが11位から20位までの従業員のみが戻されるようにすることができます。同様に、給料ごとに従業員の問合せを行い、上位10人の従業員をスキップして残りの従業員のうち上位10%を戻すこともできます。

戻される行を制限する問合せは、かねてより、ROW_NUMBERウィンドウ関数、ROWNUM疑似列およびその他の手法を使用して実現可能でしたが、row_limiting_clauseのANSI SQL標準構文を使用して、より簡単に記述できるようになりました。この句を使用する際、問合せにORDER BY句を含めることによって、上位N問合せで必要となる確定的なソート順を保証できます。row_limiting_clause句は、SELECTの最後の部分として、ORDER BY句の後に置かれ、FETCHまたはOFFSETのいずれかで開始されます。基本的な構文は次のようになります。

[ OFFSET offset { ROW | ROWS } ]
[ FETCH { FIRST | NEXT } [ { rowcount | percent PERCENT } ]
    { ROW | ROWS } { ONLY | WITH TIES } ]

この構文については、次の項で例示します。

OFFSET

これは、行の制限が開始される前にスキップする行数を指定します。offsetは数値である必要があります。負の数を指定すると、オフセットは0と見なされます。NULLを指定したり、問合せで戻される行数以上の数を指定すると、戻される行数は0行となります。offsetに少数が含まれている場合、その少数部分は切り捨てられます。この句を指定しない場合、offsetは0で、行の制限は最初の行から開始されます。読みやすくするため、Oracleでは、ROWまたはROWS(これらは同等です)を使用するオプションを提供しています。

FETCH

これは、戻す行数または行の割合を指定します。この句を指定しない場合、offset + 1行目からすべての行が戻されます。WITH TIESキーワードを使用すると、問合せには、限定された最後の行のソート・キーと一致するすべての行も含まれます。

問合せで戻される行数の制限方法について説明するため、次の文について考えてみましょう。

SELECT employee_id, last_name
FROM employees
ORDER BY employee_id
FETCH FIRST 5 ROWS ONLY;

EMPLOYEE_ID LAST_NAME
----------- ----------
        100 King
        101 Kochhar
        102 De Haan
        103 Hunold
        104 Ernst

この文では、employee_idの値が小さい方から5人の従業員が戻されます。

次の5人を戻すには、この文にOFFSETを追加します。

SELECT employee_id, last_name
FROM employees
ORDER BY employee_id
OFFSET 5 ROWS FETCH NEXT 5 ROWS ONLY;

EMPLOYEE_ID LAST_NAME
----------- ----------
        105 Austin
        106 Pataballa
        107 Lorentz
        108 Greenberg
        109 Faviet

この文で、FETCH FIRSTFETCH NEXTは同等ですが、OFFSETが使用されている場合には、FETCH NEXTの方が明確です。

オフセットは、次の文のように、10などの大きな値にできます。

SELECT employee_id, last_name
FROM employees
ORDER BY employee_id
OFFSET 10 ROWS FETCH NEXT 5 ROWS ONLY;

EMPLOYEE_ID LAST_NAME
----------- ----------
        110 Chen
        111 Sciarra
        112 Urman
        113 Popp
        114 Raphaely

固定の数ではなく割合で値を戻すようにすることもできます。これを示すため、次の文では、給料が下位5%の従業員を戻します。

SELECT employee_id, last_name, salary
FROM employees
ORDER BY salary
FETCH FIRST 5 PERCENT ROWS ONLY;

EMPLOYEE_ID LAST_NAME             SALARY
----------- --------------------- ------
        132 Olson                   2100
        128 Markle                  2200
        136 Philtanker              2200
        127 Landry                  2400
        135 Gee                     2400
        119 Colmenares              2500

この結果セットでは、5%は6行です。OFFSETを使用する場合、割合の計算はオフセットが適用される前の結果セット全体に基づくため、これが重要となります。次の文は、OFFSETの使用例です。

SELECT employee_id, last_name, salary
FROM employees
ORDER BY salary, employee_id
OFFSET 6 ROWS FETCH FIRST 5 PERCENT ROWS ONLY;

EMPLOYEE_ID LAST_NAME             SALARY
----------- --------------------- ------
        131 Marlow                  2500
        140 Patel                   2500
        144 Vargas                  2500
        182 Sullivan                2500
        191 Perkins                 2500
        118 Himuro                  2500

この文では依然として6行が戻されますが、結果セットの7番目の行から開始されます。ORDER BY句に追加される追加のemployee_idは、確定的なソートを保証するためのものでした。

WITH TIESを使用して、同等の値を戻すオプションもあります。これによって、給料が下位5%の従業員に加え、最後にフェッチされた行と同じ給料のすべての従業員も戻されます。

SELECT employee_id, last_name, salary
FROM employees
ORDER BY salary
FETCH FIRST 5 PERCENT ROWS WITH TIES;

EMPLOYEE_ID LAST_NAME             SALARY
----------- --------------------- ------
        132 Olson                   2100
        128 Markle                  2200
        136 Philtanker              2200
        127 Landry                  2400
        135 Gee                     2400
        119 Colmenares              2500
        131 Marlow                  2500
        140 Patel                   2500
        144 Vargas                  2500
        182 Sullivan                2500
        191 Perkins                 2500

同じ問合せを発行することもできますが、次の文を使用すると、最初の5つの値がスキップされます。

SELECT employee_id, last_name, salary
FROM employees
ORDER BY salary
OFFSET 5 ROWS FETCH FIRST 5 PERCENT ROWS WITH TIES;

EMPLOYEE_ID LAST_NAME             SALARY
----------- --------------------- ------
        119 Colmenares              2500
        131 Marlow                  2500
        140 Patel                   2500
        144 Vargas                  2500
        182 Sullivan                2500
        191 Perkins                 2500

19.9.1 SQLの行の制限における制限事項および考慮事項

row_limiting_clause句には、次の制限事項があります。

  • この句は、for_update_clauseと同時には指定できません。

  • この句を指定すると、順序疑似列のCURRVALまたはNEXTVALを、選択リストに含めることができなくなります。

  • 定義する問合せにこの句が含まれている場合、マテリアライズド・ビューは増分リフレッシュに適応しません。

関連項目:

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