[오라클 레퍼런스 함수] APPROX_PERCENTILE_DETAIL - 근사 백분위수 정보 계산

★

APPROX_PERCENTILE_DETAIL

 

구문

APPROX_PERCENTILE_DETAIL( expr [ DETERMINISTIC ] )

 

목적

APPROX_PERCENTILE_DETAIL 함수는 expr의 값에 대한 근사 백분위수 정보를 계산하고, 이 정보를 특별한 형식으로 담은 BLOB 형태의 디테일(detail) 값을 반환합니다.

expr에 대해 허용되는 데이터 타입은 DETERMINISTIC 절에서 지정한 알고리즘에 따라 다릅니다. 자세한 내용은 DETERMINISTIC 절을 참조하시기 바랍니다.

이 함수는 주로 SELECT 문의 GROUP BY 절과 함께 사용됩니다. 그룹 단위로 expr에 대한 근사 백분위수 정보를 계산하고, 각 그룹마다 단일 디테일 값을 반환합니다.

APPROX_PERCENTILE_DETAIL 함수가 반환한 디테일 값은 APPROX_PERCENTILE_AGG 함수의 입력으로 사용할 수 있으며, 이를 통해 디테일을 집계할 수 있습니다. 또한 TO_APPROX_PERCENTILE 함수를 사용하여 디테일 값을 지정된 백분위수 값으로 변환할 수 있습니다. 이 세 가지 함수를 함께 사용하여 자원을 효율적으로 활용하여 근사 백분위수 계산을 한 번 수행한 다음, 결과 디테일을 저장하고 해당 디테일을 기반으로 효과적인 집계 및 조회를 수행할 수 있습니다. 예를 들어:

 

1. APPROX_PERCENTILE_DETAIL 함수를 사용하여 근사 백분위수 계산을 수행하고, 결과 디테일을 테이블이나 머티리얼라이즈드 뷰에 저장하세요. 이러한 디테일은 수입 백분위수 정보와 같이 높은 정밀도를 가지는 백분위수 디테일일 수 있습니다.
2. APPROX_PERCENTILE_AGG 함수를 사용하여 이전 단계에서 얻은 디테일을 집계하고, 그 결과를 테이블이나 머티리얼라이즈드 뷰에 저장하세요. 이러한 디테일은 주로 주(State)별 수입 백분위수 정보와 같이 상대적으로 낮은 정밀도를 가지는 백분위수 디테일일 수 있습니다.
3. TO_APPROX_PERCENTILE 함수를 사용하여 저장된 디테일 값을 백분위수 값으로 변환하세요. TO_APPROX_PERCENTILE 함수를 사용하여 APPROX_PERCENTILE_DETAIL 함수나 APPROX_PERCENTILE_AGG 함수로 생성된 디테일 값을 질의할 수 있습니다.

 

DETERMINISTIC

이 절은 근사 백분위수 값을 계산하는 데 사용되는 알고리즘 유형을 제어할 수 있습니다.

 

• DETERMINISTIC을 지정하면 이 함수는 결정론적인 근사 백분위수 정보를 계산합니다. 이 경우, expr은 숫자 값이거나 숫자 값으로 암묵적으로 변환될 수 있는 값을 평가해야 합니다.
• DETERMINSTIC을 생략하면 이 함수는 비결정론적인 근사 백분위수 정보를 계산합니다. 이 경우, expr은 숫자 또는 날짜/시간 값이거나 숫자 또는 날짜/시간 값으로 암묵적으로 변환될 수 있는 값을 평가해야 합니다.

 

참고:

• APPROX_PERCENTILE_AGG
• TO_APPROX_PERCENTILE

 

예제

이 섹션의 예제들은 APPROX_PERCENTILE_DETAIL, APPROX_PERCENTILE_AGG, 그리고 TO_APPROX_PERCENTILE 함수들을 함께 사용하여 자원 집약적인 근사 백분위수 계산을 한 번 수행하고, 결과적인 디테일을 저장한 후에 해당 디테일들을 효율적으로 집계하고 조회하는 방법을 보여줍니다.

 

APPROX_PERCENTILE_DETAIL: 예제

다음 쿼리는 테이블 sh.customers와 sh.sales를 조회하여 각 고객이 구매한 제품의 금액을 가져옵니다. APPROX_PERCENTILE_DETAIL 함수는 각 도시별로 고객이 거주하는 도시별로 디테일(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에 저장된 디테일들을 읽고, 각 주(state)별로 고객이 구매한 제품의 금액을 담은 집계된 디테일을 생성합니다. 이 집계된 디테일은 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;

 

다음 쿼리는 이전 쿼리와 유사하지만, 각 국가(country)별로 고객이 구매한 제품의 근사 금액을 담은 집계된 디테일을 생성합니다. 이 집계된 디테일은 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: 예제

다음 쿼리는 amt_sold_by_city_mv에 저장된 디테일을 조회하고, 각 도시별로 고객이 구매한 제품의 근사 25번째 백분위수, 50번째 백분위수, 그리고 75번째 백분위수 값을 반환하는 TO_APPROX_PERCENTILE 함수를 사용합니다.

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

 

다음 쿼리는 amt_sold_by_state_mv에 저장된 디테일을 조회하고, 각 주별로 고객이 구매한 제품의 근사 25번째 백분위수, 50번째 백분위수, 그리고 75번째 백분위수 값을 반환하는 TO_APPROX_PERCENTILE 함수를 사용합니다.

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

다음 쿼리는 amt_sold_by_country_mv에 저장된 디테일을 조회하고, 각 국가별로 고객이 구매한 제품의 근사 25번째 백분위수, 50번째 백분위수, 그리고 75번째 백분위수 값을 반환하는 TO_APPROX_PERCENTILE 함수를 사용합니다.

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 함수는 근사 백분위수 정보가 포함된 디테일(detail) 컬럼을 입력으로 받아, 해당 정보를 집계하는 기능을 제공합니다. 다음 쿼리는 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
. . .

 

근사 쿼리를 기반으로 쿼리 리라이트(Query Rewrite)와 자재화된 뷰(Materialized Views) 예제:

APPROX_PERCENTILE_DETAIL: 예제에서, materialized view amt_sold_by_city_mv를 생성할 때 ENABLE QUERY REWRITE 절이 지정됩니다. 이는 APPROX_MEDIAN 또는 APPROX_PERCENTILE과 같은 근사 함수를 포함하는 쿼리가 자재화된 뷰를 사용하여 다시 작성(rewrite)되도록 허용합니다.

예를 들어, 데이터베이스 수준이나 현재 세션 수준에서 쿼리 리라이트가 활성화되어 있는지 확인하고 다음 쿼리를 실행하세요:

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을 쿼리하여 실행 계획을 설명합니다:

SET LINESIZE 300
SET PAGESIZE 0
COLUMN plan_table_output FORMAT A150

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

 

다음과 같은 실행 계획에 따라서 옵티마이저가 쿼리에 대해 materialized view인 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 |
-------------------------------------------------------------

 

---

출처: 오라클 레퍼런스

원문 링크: Oracle APPROX_PERCENTILE_DETAIL 함수 문서