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タイルの値を計算する。 |
|
ウィンドウ |
累積集計および変動集計を計算する。 |
|
レポート |
市場占有率などのシェアを計算する。 |
|
|
現在行から指定した行数を移動した行の値を検索する。 |
|
|
順序付けされたグループ内の最初または最後の値。 |
|
線形回帰 |
線形回帰およびその他の統計情報(傾き、切片など)を計算する。 |
|
逆パーセンタイル |
データセット内で指定されたパーセンタイルと一致する値。 |
|
仮説ランクおよび仮説分布 |
行が指定されたデータセットに挿入された場合に与えられるランクまたはパーセンタイル。 |
こうした処理を行うため、分析関数ではSQL処理に新しい要素がいくつか追加されています。これらの要素は、既存のSQL上に作成され、柔軟で強力な計算式を可能にします。分析関数には、いくつかの例外を除きこの追加要素群が含まれます。図19-1に、処理フローを表します。
-
処理順序
分析関数を使用した問合せ処理は、3つのステップで実行されます。第1に、すべての結合、
WHERE、GROUPBYおよびHAVING句が実行されます。第2に、結果セットを分析関数で使用できるようになり、その計算がすべて実行されます。第3に、問合せの最後にORDERBY句がある場合、ORDERBYが処理され、正確な出力順序付けが可能になります。処理順序は、図19-1のとおりです。 -
結果セット・パーティション
分析関数を使用すると、問合せの結果セットをパーティションと呼ばれる行グループに分割できます。分析関数で使用されるパーティションという用語は、表パーティション機能とは無関係です。この章でパーティションという用語が使用される場合、分析関数に関する意味のみを示します。パーティションは
GROUPBY句で定義されているグループの後に作成されるため、SUMやAVGなどのすべての集計結果で使用できます。パーティションは、必要な列または式に基づいて分割することもできます。問合せ結果セットは、すべての行を持つ1つのパーティション、少数の大きなパーティション、またはそれぞれが数行しか持たない多数の小さなパーティションに分割可能です。 -
ウィンドウ
パーティションの各行に対して、スライドするデータ・ウィンドウを定義できます。このウィンドウで、カレント行の計算に使用される行の範囲が決まります。ウィンドウの大きさは、行の物理数値または時間などのロジカル・インターバルに基づきます。ウィンドウには、開始行および終了行があります。ウィンドウの定義によっては、ウィンドウの片方または両方の末端を移動できます。たとえば、累積合計関数として定義されているウィンドウでは、開始行がパーティションの最初の行に固定されますが、終了行はパーティションの開始点から最後の行までスライドします。一方、移動平均として定義されているウィンドウでは、開始点および終了点の両方がスライドし、一定の物理範囲または論理範囲が維持されます。
ウィンドウのサイズは、パーティション内の行全体と同じ大きさからパーティション内の1行のスライド・ウィンドウとしてまで様々に設定できます。ウィンドウがパーティションの境界に近い場合、関数からは使用可能な行の結果のみが戻されます。結果がユーザーの必要とするものではないことは警告されません。
ウィンドウ関数を使用するとカレント行が計算に含まれるため、n個の項目を処理する場合は(n-1)を指定します。
-
カレント行
分析関数を使用して実行される各計算は、パーティション内のカレント行に基づいて行われます。カレント行は、ウィンドウの開始および終了を判断する際の参照点として機能します。たとえば、中央の移動平均計算は、カレント行とその前の6行およびその後の6行にわたるウィンドウのように定義できます。この場合、図19-2のように、13行の大きさのスライド・ウィンドウが作成されます。
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 )
RANKとDENSE_RANKの違いは、同じ値の項目がある場合、DENSE_RANKではランキングの順序に抜けができないという点です。つまり、ある競争についてDENSE_RANKを使用してランキングした結果、3人が同点で第2位であった場合、3人全員が第2位となり、次点の人が第3位になります。RANK関数でも3人全員が第2位になりますが、次点の人は第5位になります。
次に、RANKに関する注意点を示します。
-
デフォルトのソート順は昇順です。必要に応じて降順に変更できます。
-
オプションの
PARTITIONBY句の式を使用すると、問合せ結果セットがRANK関数の適用範囲となるグループ群に分割されます。つまり、グループが変更されるたびに、RANKがリセットされます。実際には、PARTITIONBY句の値式でリセットの境界が定義されます。 -
PARTITIONBY句がない場合、ランクは問合せ結果セット全体にわたって計算されます。 -
ORDER>BY句によって、ランキングが実行されるメジャー(<valueexpression>)が指定され、各グループ(またはパーティション)でソートされる行の順序が定義されます。各パーティション内でデータがソートされると、各行が1からランク付けされます。 -
NULLSFIRST|NULLSLAST句によって、順序付けされた行セットでNULLの位置が最初になるか最後になるかが示されます。順序付けによって、NULLが、NULL以外の値より高いか低いかが比較されます。順序が昇順であった場合、NULLSFIRSTはNULLが他のどのNULL以外の値よりも小さいことを示し、NULLSLASTはNULL以外の値よりも大きいことを示します。降順では、その逆になります。例: ランキング関数でのNULLの処理の例を参照してください。 -
NULLSFIRST|NULLSLAST句が省略されている場合、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 119.2.1.1.5 例: キューブおよびロールアップのグループごとのランキング
RANKなどの分析関数は、CUBE、ROLLUPまたはGROUPING SETS演算子によるグルーピングに基づいて再設定されます。これは、CUBE、ROLLUPおよびGROUPING SETS問合せで作成されたグループにランクを割り当てる場合に有効です。GROUPING関数の詳細は、データ・ウェアハウスにおける集計のためのSQLを参照してください。
CUBEおよびROLLUP問合せの例を次に示します。
SELECT channel_desc, country_iso_code, SUM(amount_sold) 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 countries.country_id = customers.country_id AND sales.channel_id = channels.channel_id
AND channels.channel_desc IN ('Direct Sales', 'Internet') AND times.calendar_month_desc='2000-07'
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 US 616539.04 1
Direct Sales GB 83869.96 2
Internet US 82595.71 3
Direct Sales JP 79047.78 4
Internet JP 7103.39 5
Internet GB 6477.98 6
Direct Sales 779456.78 1
Internet 96177.08 2
US 699134.75 1
GB 90347.94 2
JP 86151.17 3
875633.86 119.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 1519.2.1.2 APPROX_RANK関数
APPROX_RANK関数は、値のグループにおける近似値を返します。
この関数では、オプションのPARTITION BY句の後に必須のORDER BY ... DESC句が必要です。PARTITION BYキーはGROUP BYキーのサブセットである必要があります。ORDER BY句にはAPPROX_COUNTまたはAPPROX_SUMのいずれかを含める必要があります。
APPROX_RANK関数の構文は次のとおりです。
SELECT expr_1[, expr_2, … expr_j], APPROX_*(expr_k) agg_1[, APPROX_*(expr_l) agg_2…]
FROM table_name
WHERE …
GROUP BY expr_1[, expr_2, …expr_j]
HAVING APPROX_RANK(PARTITION BY partition_by_clause ORDER BY APPROX_*(expr_k) DESC) <= N1
[AND APPROX_RANK(PARTITION BY partition_by_clause ORDER BY APPROX_*(expr_l) DESC) <= N2…)];次の例では、部門ごとの給与合計上位10位のジョブを返します。各ジョブについて、給与合計とランキングも表示されます。
SELECT deptno, job, APPROX_SUM(sal), APPROX_RANK(PARTITION BY deptno ORDER BY APPROX_SUM(sal) DESC) rk
FROM emp
GROUP BY deptno, job
HAVING APPROX_RANK(PARTITION BY deptno ORDER BY APPROX_SUM(sal) DESC) <= 10;
DEPTNO JOB APPROX_SUM(SAL) RK
---------- --------- --------------- ----------
10 CLERK 1300 3
10 MANAGER 2450 2
10 PRESIDENT 5000 1
20 CLERK 1900 3
20 MANAGER 2975 2
20 ANALYST 6000 1
30 CLERK 950 3
30 MANAGER 2850 2
30 SALESMAN 5600 1
次の例では、合計給与が上位2位、その職種の従業員数が上位3位に入るジョブを部門ごとに返します。
SELECT deptno, job, APPROX_SUM(sal), APPROX_COUNT(*)
FROM emp
GROUP BY deptno, job
HAVING APPROX_RANK(PARTITION BY deptno ORDER BY APPROX_SUM(sal) DESC) <= 2
AND APPROX_RANK(PARTITION BY deptno ORDER BY APPROX_COUNT(*) DESC) <= 3;
DEPTNO JOB APPROX_SUM(SAL) APPROX_COUNT(*)
---------- --------- --------------- ---------------
10 MANAGER 2450 1
10 PRESIDENT 5000 1
20 MANAGER 2975 1
20 ANALYST 6000 2
30 MANAGER 2850 1
30 SALESMAN 5600 4次の例では、MAX_ERROR属性を使用して近似集計の精度をレポートします。
SELECT deptno, job, APPROX_SUM(sal) sum_sal, APPROX_SUM(sal,'MAX_ERROR') sum_sal_err
FROM emp
GROUP BY deptno, job
HAVING APPROX_RANK(PARTITION BY deptno ORDER BY APPROX_SUM(sal) DESC) <= 2;
DEPTNO JOB SUM_SAL SUM_SAL_ERR
---------- --------- ---------- -----------
10 MANAGER 2450 0
10 PRESIDENT 5000 0
20 MANAGER 2975 0
20 ANALYST 6000 0
30 MANAGER 2850 0
30 SALESMAN 5600 0
19.2.1.3 ボトムNランキング関数
ボトムNはトップNに似ていますが、ランク式内の順序付け順序が異なります。前述の例の場合でいうと、降順のかわりに昇順でSUM(s_amount)を順序付けできます。
19.2.1.4 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 119.2.1.5 PERCENT_RANK関数
PERCENT_RANKはCUME_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.6 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など)または式になります。
この関数では、RANKやCUME_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.7 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 ウィンドウ関数
一連のウィンドウ関数を使用すると、累積集計、移動集計および集中集計を計算できます。この種類の関数では、対応するウィンドウ内にある他の行に基づいて、表内の各行に対する値が戻されます。集計ウィンドウ関数群では、SUM、AVERAGE、COUNT、MAX、MINを始めとする多数の関数の移動および累積バージョンの計算が可能です。この種の関数は、問合せの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の処理方法と同じです。その他の処理方法は、ユーザー定義の関数によって、またはウィンドウ関数でDECODEかCASE式を使用することによって取得できます。
19.2.2.2 論理オフセットを指定したウィンドウ関数
論理オフセットは、RANGE 10 PRECEDINGなどの定数または定数を求める式で指定するか、RANGE INTERVAL N DAY/MONTH/YEAR PRECEDINGなどのインターバル指定またはインターバルを求める式によって指定できます。
論理オフセットでは、NUMERIC(オフセットが数値の場合)またはDATE(インターバルが指定される場合)と互換性のある型の式を1つのみ、関数のORDER BY式リストに指定できます。
RANGEキーワードを使用する分析関数で、ウィンドウとして次の2つのいずれかを指定する場合は、ORDER BY句に複数のソート・キーを指定できます。
-
RANGEBETWEENUNBOUNDEDPRECEDINGANDCURRENTROW。このウィンドウの短縮形はRANGEUNBOUNDEDPRECEDINGです。この短縮形を使用することもできます。 -
RANGEBETWEENCURRENTROWANDUNBOUNDEDFOLLOWING。
ウィンドウの境界がこれらの条件に合致しない場合、分析関数の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には、列参照または集計を含む有効な式を指定できます。 -
PARTITIONBY句には、ウィンドウ関数の計算対象とするグループを定義します。PARTITIONBY句がない場合、この関数は問合せ結果セット全体に対して計算されます。
関連項目:
レポート関数は、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 expressionがNULLと評価される場合、RATIO_TO_REPORTもNULLと評価されますが、分母の値の合計を計算する際には0(ゼロ)として扱われます。構文は次のとおりです。
RATIO_TO_REPORT ( expr ) OVER ( [query_partition_clause] )
ここでは、次の事項が適用されます。
-
exprには、列参照または集計を含む任意の有効な式を指定できます。 -
PARTITIONBY句には、RATIO_TO_REPORT関数の計算対象とするグループを定義します。PARTITIONBY句がない場合、この関数は問合せ結果セット全体に対して計算されます。
例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 .36219.2.4 LAG/LEAD関数
LAGおよびLEAD関数は、行の相対位置を確実に認識できる場合の、値の比較に役立ちます。この2つの関数は、カレント行からターゲット行までの行数を指定する形で使用します。これらの関数では、表の複数の行に対して自己結合せずに同時にアクセスできるため、処理速度が向上します。LAG関数では、現在位置から指定したオフセット分のみ前の行がアクセスされ、LEAD関数では、現在位置から指定したオフセット分のみ後の行がアクセスされます。LAGおよびLEAD関数の構文ではこうした関数の構文について説明します。
NTH_VALUE関数と関連があり、これを単純化したものであると考えられるのがLAG関数とLEAD関数です。LAGとLEADでは、指定した物理オフセットの行の値を取得できるだけです。これでは不十分な場合は、NTH_VALUEを使用して、いわゆる論理オフセットまたは相対位置に基づく行の値を取得できます。NTH_VALUE関数ではIGNORE NULLSオプションを指定できます。つまり、条件を指定したり、特定の条件に基づき行を除外できるという意味で、その機能を強化できます。例19-17では、数量が8未満の行はフィルタにより除外されています。これはLAGやLEADではできません。目的の行までのオフセットがわからないためです。
詳細は、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およびLEADでIGNORE 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_VALUE、LAST_VALUEおよびNTH_VALUE関数について説明します。
19.2.5.1 FIRST_VALUE関数およびLAST_VALUE関数
FIRST_VALUE関数やLAST_VALUE関数を使用すると、ウィンドウの最初や最後の行を選択できます。これらの行は、計算上の基準行として使用されるので特に重要です。これらの行を使用する例としては、たとえば、日付で順序付けされた売上データが保持されるパーティションについて、「その期間の最初の販売日(FIRST_VALUE)と比較した各日の売上はどの程度か」といった質問をする場合があります。
FIRST_VALUEでIGNORE NULLSオプションを使用すると、セット内で最初のNULL以外の値が戻されます。すべての値がNULLの場合は、NULLが戻されます。LAST_VALUEでIGNORE NULLSを使用すると、セット内で最後のNULL以外の値が戻されます。すべての値がNULLの場合は、NULLが戻されます。IGNORE NULLSオプションは、在庫表を適切に移入する場合に特に役立ちます。
これらの関数の構文は次のとおりです。
FIRST_VALUE|LAST_VALUE ( <expr> ) [RESPECT NULLS|IGNORE NULLS] OVER (analytic clause );
例19-16 FIRST_VALUE
この例では、FIRST_VALUEでIGNORE 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関数です。LAGとLEADでは、指定した物理オフセットの行の値を取得できるだけです。これでは不十分な場合は、NTH_VALUEを使用して、いわゆる論理オフセットまたは相対位置に基づく行の値を取得できます。NTH_VALUE、FIRST_VALUEおよびLAST_VALUE関数では、IGNORE NULLSオプションを使用して、条件を指定したり、特定の条件に基づいて行を除外できるという意味で、その機能を強化できます。例19-17では、数量が8未満の行はフィルタにより除外されています。これはLAGやLEADではできません。目的の行までのオフセットがわからないためです。
詳細は、『Oracle Database SQL言語リファレンス』を参照してください。
この関数の構文は次のとおりです。
NTH_VALUE (<expr>, <n expr>) [FROM FIRST | FROM LAST] [RESPECT NULLS | IGNORE NULLS] OVER (<window specification>)
-
exprには、列、定数、バインド変数、またはこれらを含む式を指定できます。 -
nには、列、定数、バインド変数、またはこれらを含む式を指定できます。 -
RESPECTNULLSは、NULLを処理するデフォルトのメカニズムです。exprのNULL値を計算に含めるか、計算から除外するかを決定します。デフォルトはRESPECTNULLSです。 -
FROMFIRSTおよびFROMLASTオプションでは、オフセットnが最初の行からか最後の行からかを指定できます。デフォルトはFROMFIRSTです。 -
IGNORENULLSを指定すると、メジャー値内のNULLをスキップできます。
例19-17 NTH_VALUE
次の例は、prod_idが10から20の範囲の各製品について、昇順で2番目のchannel_idのamount_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関数を使用します。
各年に販売された各製品の概数を別の方法で計算するには、APPROX_COUNT_DISTINCT_AGGを使用して、SALES_APPROX_MONTH表に格納された月次詳細を集計し、この結果を表またはマテリアライズド・ビューに格納します。
近似パーセンタイル結果を返すSQL関数のプロパティ
近似パーセンタイル結果を提供するSQL関数には、APPROX_PERCENTILE、APPROX_PERCENTILE_DETAILおよびAPPROX_PERCENTILE_AGGがあります。これらの関数には、次の追加プロパティがあります。
-
ERROR_RATE近似計算のエラー率を計算することにより、補間されたパーセンタイル値の精度を示します。
-
CONFIDENCEエラー率の精度の信頼度を示します(エラー率が指定されている場合)。
-
DETERMINISTIC近似処理の計算に使用されるアルゴリズムを制御します。
一貫性のある繰返し可能な結果が必要な場合は、
DETERMINISTICを使用します。通常、これは結果を他のユーザーと共有する必要がある場合です。
関連項目:
-
『Oracle Database SQL言語リファレンス』の
APPROX_COUNT_DISTINCTに関する項 -
関数と
ERROR_RATE、CONFIDENCEおよびDETERMINISTICプロパティの詳細は、Oracle Database SQL言語リファレンスのAPPROX_COUNT_DISTINCT_DETAILに関する項を参照してください。 -
『Oracle Database SQL言語リファレンス』の
APPROX_COUNT_DISTINCT_AGGに関する項 -
『Oracle Database SQL言語リファレンス』の
TO_APPROX_COUNT_DISTINCTに関する項
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] [DISTINCT] <measure_column> [,<delimiter>] [ON OVERFLOW TRUNCATE [truncate_literal] | ON OVERFLOW ERROR [WITH | WITHOUT COUNT]]) WITHIN GROUP (ORDER BY <oby_expression_list>)
DISTINCTを指定すると、重複する値がリストから削除されます。
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-20 DISTINCTを使用して繰返し値が削除されたLISTAGG
この例では、GROUP BY句で指定された各グループ内のデータを順序付け、prod_cateogry列およびprod_desc列の値を連結します。連結された名前のリストが、VARCHAR2データ型でサポートされる最大長を超える場合、リストは最後の完全な文字列まで切り捨てられます。DISTINCTキーワードは、指定したメジャー列の重複値を削除する必要があることを指定します。
SELECT cust_id, LISTAGG( DISTINCT prod_category||':'||prod_desc,' ; ' ON OVERFLOW TRUNCATE WITH COUNT)
WITHIN GROUP (ORDER BY amount_sold)
FROM sh.sales, sh.products
WHERE sales.prod_id=products.prod_id
AND amount_sold > 200 AND products.prod_id BETWEEN 10 and 15
AND time_id > '01-JAN-01'
GROUP BY cust_id;
19.3.2.2 集計レポートとして使用するLISTAGG
LISTAGG関数を集計レポートとして使用できます。
例19-21 集計レポートとして使用する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つの戻り値を生成する通常の集計関数(MIN、MAX、SUM、AVG、COUNT、VARIANCE、STDDEV)を最初に記述します。その上で、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-22 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-22のFIRST/LASTの例で考えてみます。この場合、「個々の製品の定価を検索し、それをサブカテゴリ内で最低価格が最も高い製品および最も低い製品の定価と比較するとどうなるか」という問題になります。
FIRST/LASTを集計レポートとして使用し、こうした情報をドキュメンテーション(Documentation)というサブカテゴリに関して検索する問合せを次に示します。
例19-23 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までの値をとるパラメータが必要です。パラメータにこの範囲外の値を指定した場合、エラーとなります。このパラメータには、定数に評価される式を指定する必要があります。
-
ソート指定が必要です。ソートは、
ORDERBY句に単一の式を指定する形で指定します。複数の式は使用できません。
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 500019.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_DETAILGROUP BY句を使用して指定された一連のデータ内の、詳細と呼ばれる近似パーセンタイル情報を計算します。この関数で作成された詳細情報はバイナリ形式で格納されており、TO_APPROX_PERCENTILEとAPPROX_PERCENT_DETAIL_AGGの両方の関数で使用されることを想定しています。 -
APPROX_PERCENTILE_AGGAPPROX_PERCENTILE_DETAIL関数を使用して作成された詳細の集計を実行します。 -
TO_APPROX_PECENTILE詳細または集計の結果(BLOB値として格納されている)を判読可能な形式で表示します。
詳細および高レベルの集計データは、その後の分析のために表またはマテリアライズド・ビューに格納できます。
例: 国または州内の近似パーセンタイル販売データの表示
この例では、APPROX_PERCENTILE_DETAILを使用して、パーセンタイル計算を1回実行し、この結果を表に格納し、格納されたデータに基づいて近似集計を実行します。TO_APPROX_PERCENTILE関数は、パーセンタイル計算の結果を判読可能な形式で表示するために使用されます。
19.3.5 仮説ランク関数
この種の関数では、what-if分析に役立つ機能が提供されます。たとえば、行を他の行集合に仮に挿入すると、行のランクがどうなるかという問題があるとします。
この集計ファミリでは、仮に挿入する行と順序付けられた行のグループを引数として1つ以上取り、対象とするグループに行を仮に挿入した場合の、その行のRANK、DENSE_RANK、PERCENT_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句内の各式には、ASC、DESC、NULLS FIRST、NULLS LASTオプションを使用できます。
例19-24 仮説ランク関数および仮説分布関数の例1
この項で使用されているproducts表の定価データを使用して、価格50ドルのセーターの仮のRANK、PERCENT_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-25 仮説ランク関数および仮説分布関数の例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_POP、COVAR_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.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 |
|
|
標準誤差 |
|
|
2乗の総計 |
|
|
2乗の回帰合計 |
|
|
2乗の残差合計 |
|
|
傾きのt統計 |
|
|
y切片のt統計量 |
|
19.3.6.7 線形回帰計算の例
この例では、製品の販売数量をその製品の定価の線形関数として表す、微分最小2乗法で求めた回帰直線を計算します。計算は、販売チャネル別にグルーピングされます。SLOPE、INTCPTおよび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 119.3.7 統計集計について
Oracle Databaseには、一連のSQL統計関数と統計パッケージDBMS_STAT_FUNCSが用意されています。この項では、新しい関数の一部を基本的な構文とともに示します。
DBMS_STAT_FUNCSパッケージの詳細は『Oracle Database PL/SQLパッケージ・プロシージャおよびタイプ・リファレンス』を、構文およびセマンティックについては『Oracle Database SQL言語リファレンス』を参照してください。
この項では、次の項目について説明します。
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-26 ピボットの例のための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-26の説明に従って作成したビューsales_viewのchannel列に対する一般的なピボットを示しています。
SELECT * FROM
(SELECT product, channel, amount_sold
FROM sales_view
) S PIVOT (SUM(amount_sold)
FOR CHANNEL IN (3 AS DIRECT_SALES, 4 AS INTERNET_SALES,
5 AS CATALOG_SALES, 9 AS TELESALES))
ORDER BY product;
PRODUCT DIRECT_SALES INTERNET_SALES CATALOG_SALES TELESALES
---------------------- ------------ -------------- ------------- ---------
...
Internal 6X CD-ROM 229512.97 26249.55
Internal 8X CD-ROM 286291.49 42809.44
Keyboard Wrist Rest 200959.84 38695.36 1522.73
...
出力では、ピボット値それぞれについて、DIRECT_SALES、INTERNET_SALES、CATALOG_SALESおよびTELESALESという別名の付いた4つの列が新しく作成されています。この出力には合計が表示されます。別名を付けない場合、列ヘッダーはINリストの値となります。
19.4.3 複数列に対するピボット操作
ピボットは複数の列に対して実行できます。次の文は、例19-26の説明に従って作成したビュー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-26で作成した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と、ピボット操作結果の列Q1、Q1_COUNT_TOTAL、Q2、Q2_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-26を参照してください。
キーワード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-26で作成したビュー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.619.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 200219.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_VALUEとFIRST_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.8419.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_salesのFROM句では、集計ビュー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 = 2001でyear_over_year_salesのデータを選択します。したがって、この問合せでは各製品について、2001年と2000年の指定した週の週次年度累計売上が戻されます。
19.7.2 複数の時間レベルでの周期ごとの比較: 例
前の例では、単一の時間レベルでの比較を作成する方法を示しましたが、1回の問合せで複数の時間レベルを処理できればより便利となります。たとえば、年、四半期、月、曜日レベルで前期との売上を比較できます。ここでは、時間階層のすべてのレベルで、年度累計売上の年度ごとの比較を実行する問合せの作成方法について説明します。
このタスクを実行するには、いくつかのステップを踏みます。目標は、1回の問合せで日、週、月、四半期、年レベルでの比較を実行することです。ステップは次のとおりです。
-
cube_prod_timeというビューを作成します。このビューは、timesとproductsにわたって集計した売上の階層的キューブを保持します。階層的キューブ・ビューの作成を参照してください。
-
キューブのエッジとして使用する時間ディメンションのビューを作成します。完全な日付セットを保持する時間エッジは、
cube_prod_timeビューでスパース・データにパーティション外部結合されます。日付値の完全なセットであるビューedge_timeの作成を参照してください。
-
最後に、パフォーマンスを最大にするために、マテリアライズド・ビュー
mv_prod_timeを作成します。これにはcube_prod_timeと同じディメンションを使用します。 -
比較問合せを作成します。
比較問合せの作成を参照してください。
階層的キューブの詳細は、データ・ウェアハウスにおける集計のための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_timeのHierarchical_Time列が、ビューcube_prod_timeのHierarchical_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_p、cat、subcat、prodおよびgid_tのデータがパーティション化され、すべての時間ディメンション列で行が順序付けされます。この関数ではオフセット1を渡すことで前期の売上値が取得されます。2番目のLAG関数(sales_same_period_prior_year)では、この他にqtr_num、mon_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_timeとtime_cのパーティション外部結合を実行することです。このステップでは、製品集計の各レベルで13番目の月の売上データを作成します。メインの問合せでは、分析関数SUMがCASE式とともに使用され、各四半期の最初の月における売上合計として定義された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-27の問合せで収集されます。
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を参照してください。
バケットの境界は、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-27 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 419.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-28 線形代数
ビジネス分析に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-29 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-30 独立したサブセットを集計する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-31 ヒストグラムの例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-32 ヒストグラムの例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 FIRSTとFETCH 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 250019.9.1 SQLの行の制限における制限事項および考慮事項
row_limiting_clause句には、次の制限事項があります。
-
この句は、
for_update_clauseと同時には指定できません。 -
この句を指定すると、順序疑似列の
CURRVALまたはNEXTVALを、選択リストに含めることができなくなります。 -
定義する問合せにこの句が含まれている場合、マテリアライズド・ビューは増分リフレッシュに適応しません。
関連項目:
構文および制限の詳細は、『Oracle Database SQL言語リファレンス』を参照してください。