日本語PDF
  1. SQL言語リファレンス
  2. ファンクション
  3. APPROX_PERCENTILE_DETAIL

APPROX_PERCENTILE_DETAIL

構文

approx_percentile_detail.epsの説明が続きます
図approx_percentile_detail.epsの説明

目的

APPROX_PERCENTILE_DETAILは、exprの値について近似パーセンタイル情報を計算して、詳細と呼ばれるBLOB値を戻し、ここにはその情報が特殊な形式で含まれます。

exprで許容されるデータ型は、DETERMINISTIC句で指定するアルゴリズムによって異なります。詳細は、「DETERMINISTIC」句を参照してください。

通常、このファンクションはGROUP BY句とともにSELECT文で使用されます。これにより、行の各グループ内のexprに対する近似パーセンタイル情報が計算され、グループごとに単一の詳細が戻されます。

APPROX_PERCENTILE_DETAILによって戻される詳細は、APPROX_PERCENTILE_AGGファンクションへの入力として使用して詳細の集計を実行したり、TO_APPROX_PERCENTILEファンクションへの入力として使用して詳細を指定されたパーセンタイル値に変換できます。これらの3つのファンクションを一緒に使用すると、リソースを大量に使用する近似パーセンタイル計算を1回実行し、生成された詳細を格納した後、これらの詳細に対して効率的に集計と問合せを実行できます。たとえば:

  1. APPROX_PERCENTILE_DETAILファンクションを使用して、近似パーセンタイル計算を実行し、生成される詳細を表またはマテリアライズド・ビューに格納します。これらは、市区町村の収入パーセンタイル情報など、粒度の高い詳細にできます。

  2. APPROX_PERCENTILE_AGGファンクションを使用して、前のステップで取得した詳細を集計し、生成される詳細を表またはマテリアライズド・ビューに格納します。これらは、都道府県の収入パーセンタイル情報など、粒度のより低い詳細にできます。

  3. TO_APPROX_PERCENTILEファンクションを使用して、格納した詳細値をパーセンタイル値に変換します。TO_APPROX_PERCENTILEファンクションを使用すると、APPROX_PERCENTILE_DETAILファンクションまたはAPPROX_PERCENTILE_AGGファンクションによって作成された詳細値を問い合せることができます。

DETERMINISTIC

この句を使用すると、近似パーセンタイル値を計算するために使用するアルゴリズムのタイプを制御できます。

  • DETERMINISTICを指定すると、このファンクションによって、決定的な近似パーセンタイル情報が計算されます。この場合、exprは数値または数値に暗黙的に変換可能な値に評価される必要があります。

  • DETERMINSTICを指定しないと、このファンクションによって、非決定的な近似パーセンタイル情報が計算されます。この場合、exprは、数値または日時値、あるいは数値または日時値に暗黙的に変換可能な値に評価される必要があります。

関連項目:

例

この項の例では、APPROX_PERCENTILE_DETAIL、APPROX_PERCENTILE_AGGおよびTO_APPROX_PERCENTILEファンクションを一緒に使用して、リソースを大量に使用する近似パーセンタイル計算を1回実行し、生成された詳細を格納した後、これらの詳細に対して効率的に集計と問合せを実行する方法を示します。

APPROX_PERCENTILE_DETAIL: 例

次の文は、表sh.customersおよびsh.salesに対して、各顧客に販売された製品の金額を問い合せます。APPROX_PERCENTILE_DETAIL関数は、顧客が居住する各都市のcity_detailと呼ばれる詳細の情報を戻します。戻された詳細は、amt_sold_by_city_mvというマテリアライズド・ビューに格納されます。 

CREATE MATERIALIZED VIEW amt_sold_by_city_mv
ENABLE QUERY REWRITE AS
SELECT c.country_id country,
       c.cust_state_province state,
       c.cust_city city,
       APPROX_PERCENTILE_DETAIL(s.amount_sold) city_detail
FROM customers c, sales s
WHERE c.cust_id = s.cust_id
GROUP BY c.country_id, c.cust_state_province, c.cust_city;

APPROX_PERCENTILE_AGG: 例

次の文は、APPROX_PERCENTILE_AGGファンクションを使用して、amt_sold_by_city_mvに格納されている詳細を読み取り、各都道府県の顧客に販売された製品の金額を含む集計された詳細を作成します。これらの集計された詳細は、amt_sold_by_state_mvというマテリアライズド・ビューに格納されます。 

CREATE MATERIALIZED VIEW amt_sold_by_state_mv AS
SELECT country,
       state,
       APPROX_PERCENTILE_AGG(city_detail) state_detail
FROM amt_sold_by_city_mv
GROUP BY country, state;

次の文は、各国の顧客に販売された製品のおおよその金額を含む、集計された詳細を作成することを除き、前の文と似ています。これらの集計された詳細は、amt_sold_by_country_mvというマテリアライズド・ビューに格納されます。 

CREATE MATERIALIZED VIEW amt_sold_by_country_mv AS
  SELECT country,
         APPROX_PERCENTILE_AGG(city_detail) country_detail
  FROM amt_sold_by_city_mv
  GROUP BY country;

TO_APPROX_PERCENTILE: 例

次の文は、TO_APPROX_PERCENTILEファンクションを使用して、amt_sold_by_city_mvに格納されている詳細を問い合せ、各市区町村の顧客に販売された製品の金額について25パーセンタイル、50パーセンタイルおよび75パーセンタイルの近似値を戻します。 

SELECT country,
       state,
       city,
       TO_APPROX_PERCENTILE(city_detail, .25, 'NUMBER') "25th Percentile",
       TO_APPROX_PERCENTILE(city_detail, .50, 'NUMBER') "50th Percentile",
       TO_APPROX_PERCENTILE(city_detail, .75, 'NUMBER') "75th Percentile"
FROM amt_sold_by_city_mv
ORDER BY country, state, city;

COUNTRY STATE        CITY           25th Percentile 50th Percentile 75th Percentile
------- ------------ -------------- --------------- --------------- ---------------
  52769 Kuala Lumpur Kuala Lumpur             19.29            38.1           53.84
  52769 Penang       Batu Ferringhi           21.51           42.09           57.26
  52769 Penang       Georgetown               19.15           33.25           56.12
  52769 Selangor     Klang                    18.08           32.06           51.29
  52769 Selangor     Petaling Jaya            19.29           35.43            60.2
. . .

次の文は、TO_APPROX_PERCENTILEファンクションを使用して、amt_sold_by_state_mvに格納されている詳細を問い合せ、各都道府県の顧客に販売された製品の金額について25パーセンタイル、50パーセンタイルおよび75パーセンタイルの近似値を戻します。 

SELECT country,
       state,
       TO_APPROX_PERCENTILE(state_detail, .25, 'NUMBER') "25th Percentile",
       TO_APPROX_PERCENTILE(state_detail, .50, 'NUMBER') "50th Percentile",
       TO_APPROX_PERCENTILE(state_detail, .75, 'NUMBER') "75th Percentile"
FROM amt_sold_by_state_mv
ORDER BY country, state;

COUNTRY STATE        25th Percentile 50th Percentile 75th Percentile
------- ------------ --------------- --------------- ---------------
  52769 Kuala Lumpur           19.29            38.1           53.84
  52769 Penang                 20.19           36.84           56.12
  52769 Selangor               16.97           32.41           52.69
  52770 Drenthe                16.76            31.7           53.89
  52770 Flevopolder            20.38           39.73           61.81
. . .

次の文は、TO_APPROX_PERCENTILEファンクションを使用して、amt_sold_by_country_mvに格納されている詳細を問い合せ、各国の顧客に販売された製品の金額について25パーセンタイル、50パーセンタイルおよび75パーセンタイルの近似値を戻します。 

SELECT country,
       TO_APPROX_PERCENTILE(country_detail, .25, 'NUMBER') "25th Percentile",
       TO_APPROX_PERCENTILE(country_detail, .50, 'NUMBER') "50th Percentile",
       TO_APPROX_PERCENTILE(country_detail, .75, 'NUMBER') "75th Percentile"
FROM amt_sold_by_country_mv
ORDER BY country;

  COUNTRY 25th Percentile 50th Percentile 75th Percentile
--------- --------------- --------------- ---------------
    52769            19.1           35.43           52.78
    52770           19.29           38.99           59.58
    52771           11.99           44.99          561.47
    52772           18.08           33.72           54.16
    52773           15.67           29.61           50.65
. . .

APPROX_PERCENTILE_AGGでは、その入力として、近似パーセンタイル情報を含む詳細の列を取得し、該当する情報の集計を実行できます。次の文は、近似パーセンタイル詳細をAPPROX_PERCENTILE_AGGによって解釈して、TO_APPROX_PERCENTILEファンクションへの入力を指定する方法を示します。前の例と同様、この問合せは、各国の顧客に販売された製品の金額について25パーセンタイル近似値を戻します。この結果は、前の例で25パーセンタイルに対して戻された結果と同じであることに注意してください。 

SELECT country,
       TO_APPROX_PERCENTILE(APPROX_PERCENTILE_AGG(city_detail), .25, 'NUMBER') "25th Percentile"
FROM amt_sold_by_city_mv
GROUP BY country
ORDER BY country;

  COUNTRY 25th Percentile
---------- ---------------
     52769            19.1
     52770           19.29
     52771           11.99
     52772           18.08
     52773           15.67
. . .

近似問合せに基づくクエリー・リライトおよびマテリアライズド・ビュー: 例

「APPROX_PERCENTILE_DETAIL: 例」では、ENABLE QUERY REWRITE句は、マテリアライズド・ビューamt_sold_by_city_mvの作成時に指定されます。これにより、マテリアライズド・ビューを使用して、APPROX_MEDIANやAPPROX_PERCENTILEなどの近似処理ファンクションを含む問合せをリライトできます。

たとえば、データベース・レベルか現行のセッションで、クエリー・リライトが有効になっていることを確認し、次の問合せを実行します。

SELECT c.country_id country,
       APPROX_MEDIAN(s.amount_sold) amount_median
FROM customers c, sales s
WHERE c.cust_id = s.cust_id
GROUP BY c.country_id;

DBMS_XPLANを問い合せて計画をEXPLAINします。 

SET LINESIZE 300
SET PAGESIZE 0
COLUMN plan_table_output FORMAT A150

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(format=>'BASIC'));

次の計画に示すように、オプティマイザでは、問合せにマテリアライズド・ビューamt_sold_by_city_mvが使用されました。 

EXPLAINED SQL STATEMENT:
------------------------
SELECT c.country_id country, APPROX_MEDIAN(s.amount_sold)
amount_median FROM customers c, sales s WHERE c.cust_id = s.cust_id
GROUP BY c.country_id

Plan hash value: 2232676046

-------------------------------------------------------------
| Id  | Operation                     | Name                |
-------------------------------------------------------------
|   0 | SELECT STATEMENT              |                     |
|   1 |  HASH GROUP BY APPROX         |                     |
|   2 |   MAT_VIEW REWRITE ACCESS FULL| AMT_SOLD_BY_CITY_MV |
-------------------------------------------------------------