Oracle APPROX_PERCENTILE_DETAIL, APPROX_PERCENTILE_AGG, TO_APPROX_PERCENTILE 설명 : 오라클 함수

* 주의 : 본 함수는 Oracle Database 12C R2 이상 사용가능함

 

1. 함수의 목적
   

    Oracle APPROX_PERCENTILE_DETAIL는 Group by의 모든 디멘젼(all dimension)의 개략적인 백분위수 집계를 BLOB로 반환한다.

    APPROX_PERCENTILE_AGG는 APPROX_PERCENTILE_DETAIL을 통해 생성된 상세 집계를 이용하여 source 테이블을 다시 한번 읽지 않고도 상위레벨 집계를 생성한다.

   두 함수의 리턴값은 BLOB로 반환하기 때문에 사람이 인식가능한 형태로 변환한기 위해서는 TO_APPROX_PERCENTILE 함수로 변환과정을 거쳐야 한다. (개략적이라고는 하나 거의 100% 맞다)

 

 

   * approximate query를 위한 함수로, 읽는 범위가 넓은 대용량 데이터베이스를 정확도보다는 속도를 중요시하는 상황에 쓰이도록 개발되었다(속도 매우 빠름, 오차허용 범위내의 정확도). 예를 들어 BI/DW등 대용량 데이터베이스에서 집계 테이블을 별도로 만들지 않고, 바로 소스 테이블을 approximate query를 사용할 수 있다. 오라클 내장 함수 중 접두어로 [APPROX_]가 붙은 함수는 approximate query를 위한 함수이다. 

 

  * 백분위수란? 

    백분위수(Percentile. 百分位數)는 크기가 있는 값들로 이뤄진 자료를 순서대로 나열했을 때 백분율로 나타낸 특정 위치의 값을 이르는 용어

  
2. 샘플을 통한 개념 퀵뷰

 

1) customers 테이블과 sales 테이블을 조인하여 amount_sold 컬럼을 대상으로 ountry_id, cust_state_province, cust_city를 디멘젼(dimension)하는 개략적인 백분위수 deatil 집계를 생성한다.

 

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;

 

1) amt_sold_by_city_mv를 사용하여 원 소스테이블인 customer와 sales 테이블을 읽지 않고 country, state의 디멘전으로 구성된 상위 집계를 생성한다.

 

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;

 

2) 생성된 상위집계인 amt_sold_by_state_mv을 사람이 인식가능한 형태로 조회하기 위해 TO_APPROX_PERCENTILE로 state_detail 컬럼을 한번 더 감싼다. 

3. 사용방법 

4. 함수 PARAMETER 설명
 
APPROX_PERCENTILE_DETAIL [expr]

expr의 경우 BFILE, BLOB, CLOB, LONG, LONG RAW 또는 NCLOB 이외의 스칼라 데이터 유형의 컬럼을 지정할 수 있다.

 

TO_APPROX_PERCENTILE [expr]

null 혹은 0~1사이의 백분위수 값을 지정한다. 지정형식이 맞지 않으면 입력된 값이 무시된다.

 

[DETERMINSTIC]

DETERMINISTIC을 지정하면 이 함수는 deterministic approximate percentile 정보를 리턴한다. 이 경우 expr은 숫자 값 또는 숫자 값으로 변환할 수 있는 값이여야 한다.

생략하면 nondeterministic approximate percentile 정보를 리턴한다. 이 경우 expr은 숫자 값 또는 datetime 혹은 숫자나 datetime으로 변환할 수 있는 값이여야 한다.

 

[detail]

APPROX_PERCENTILE_DETAIL로 생성된 BLOB 컬럼을 지정한다.

 

[datatype]

변환될 값의 형식으로 NUMBER, BINARY_FLOAT, BINARY_DOUBLE, DATE, TIMESTAMP, INTERVAL YEAR TO MONTH, and INTERVAL DAY TO SECOND 중 하나를 지정한다.  

 

[DESC, ASC]

순서대로 혹은 역순으로 할 것이냐를 지정

 

[ERROR_RATE, CONFIDENCE]

생성된 DETAIL 정보의 백분위수 정확도를 알 수 있다. 지정하면 0에서 1까지의 십진수 값을 반환합니다. 

ERRER_RATE를 지정하면 평가된 백분위수의 오류율을 알 수 있다.

CONFIDENCE를 지정하면 ERROR_RATE를 사용했을 반환하는 오류율의 신뢰도를 알 수 있다.  

 

5. 다양한 샘플표현

 

끝.