【오라클(Oracle) SQL 함수】 PERCENTILE_CONT 함수

o PERCENTILE_CONT

 

---

문 법

percentile_cont::=

 

---

목 적

PERCENTILE_CONT 함수는 연속된 분포 모델을 가정하여 인수로 지정한 백분위 값에 해당하는 값을 계산하는 역 분포 함수(inverse distribution function)이다.

이 함수는 백분위수 값과 소트 지정을 취하고, 소트 지정에 해당하는 백분위수 값에 해당하는 보간 된 값을 반환한다. 계산 시 NULL은 무시된다.

이 함수는 인수로써 수치 데이터형 또는 암묵적으로 수치 데이터형으로 변환 가능한 비수치 데이터형을 취한다. 함수는 인수의 수치 데이터형과 같은 데이터형을 반환한다.

처음 expr은 백분위수 값이기 때문에 0과 1 사이의 수치 값으로 평가되어야 한다. 이 expr은 각 집계 그룹 내에서 상수일 필요가 있다. ORDER BY구문은 오라클이 보간을 실행할 수 있는 형태인 수치 또는 datetime 값을 단일 식으로 취한다.

PERCENTILE_CONT의 결과는 순서화된 후에 값 사이에 선형 보간에 의해 계산된다. 집계 그룹 안에서 백분위 값(P)과 행의 수(N)를 이용해, 소트 지정에 대해서 행을 순서화한 후에 관심 있는 행의 수를 계산한다. 이 행의 수(RN)는 계산식 RN=(1+(P*(N-1))에 따라서 계산한다. 집계 함수의 마지막 결과는 행 번호 CRN=CEILING(RN)과 FRN=FLOOR(RN)의 행으로부터 값 사이의 선형 보간에 의해 계산된다.

최종 결과는 다음과 같다. If (CRN = FRN = RN) then the result is (value of expression from row at RN) Otherwise the result is (CRN - RN) * (value of expression for row at FRN) + (RN - FRN) * (value of expression for row at CRN)

분석 함수로써 PERCENTILE_CONT함수를 이용할 수 있다. OVER구문에서는 단지 query_partitioning_clause를 지정할 수 있다.

각 행에 대해서 각 파티션 내의 값의 세트 사이에서 지정된 백분위에 해당하는 값을 반환한다.

MEDIAN함수는 백분위 값이 기본값 0.5로 지정한 PERCENTILE_CONT의 특별한 경우이다. MEDIAN 참조.

 

---

함수 비교

• PERCENTILE_CONT는 데이터 세트에 있거나 없을 수 있는 적절한 값을 보간법을 사용하여서 계산하는 반면, PERCENTILE_DISC는 항상 해당 세트에서 백분위수 바로 앞에 실제로 존재하는 값을 반환합니다. 데이터 계수가 짝수인 경우 반환하는 값이 차이가 날 수 있다.

 

---

패키지 함수 비교 (Oracle, Pandas, R Prog, Dplyr, Sqldf, Pandasql, Data.Table)

• PERCENTILE_CONT

 

---

관련 함수

함수명	함수내용
MEDIAN	연속된 분산 모델을 가정한 역 분포 함수이다. 이 함수는 수치형 데이터 또는 일자(Date)에 대한 중앙값 또는 값의 정렬 후에 보간법을 사용하여 중앙값을 계산하여 반환한다.
PERCENT_RANK	인수로 지정한 값의 그룹 내의 위치를 나타내는 백분위 순위(순위 퍼센트, Percent rank)를 반환한다.
PERCENTILE_CONT	연속된 분포 모델을 가정하여 인수로 지정한 백분위 값에 해당하는 값을 계산하는 역 분포 함수(inverse distribution function)이다.
PERCENTILE_DISC	이산 분포 모형을 가정하는 역 분포 함수이다. 사용자가 지정한 백분위수에 해당하는 데이터 값을 검색하고, 정확하게 일치하는 데이터 값이 없으면 지정한 백분위 값의 주변 데이터 정렬 옵션에 따라 우선순위가 빠른 바로 앞의 관측치 값을 반환한다.

 

---

예제

 

집계함수 예제

다음 예제는 각 부서 내에서 중앙 급여를 계산한다.

Oracle Program

SELECT department_id,
      PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary DESC) "Median cont",
      PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY salary DESC) "Median disc"
FROM  employees
GROUP BY department_id;

 

Results

DEPARTMENT_ID Median-cont Median-disc
------------- ----------- -----------
           10        4400        4400
           20        9500       13000
           30        2850        2900
           40        6500        6500
           50        3100        3100
           60        4800        4800
           70       10000       10000
           80        8800        8800
           90       17000       17000
          100        8000        8200
          110       10150       12000

 

PERCENTILE_CONT와 PERCENTILE_DISC는 다른 결과를 반환할 경우기 있다. PERCENTILE_CONT는 직선 보간 후의 결과를 계산한다.

PERCENTILE_DISC는 집계된 값의 세트로부터 값을 반환한다.

이 예제처럼 백분위 값이 0.5일 때, PERCENTILE_CONT는 요소의 짝수를 가지는 그룹에 대해서 2개의 중앙값의 평균을 반환한다. 반면에 PERCENTILE_DISC함수는 2개의 중앙값 사이의 첫 번째 값을 반환한다. 

요소의 홀수 원소를 가지는 집계 그룹에 대하여 양쪽 함수 모두 중앙 원소의 값을 반환한다.

 

---

분석함수 예제

다음 예제에서, 0.5의 백분위(percent_Rank)에 상응하는 부서 60의 중앙값은 4800이다. 부서 30에서 급여의 0.5 백분위가 없기 때문에, 중앙값은 2900(백분위 0.4)과 2800(백분위 0.6)의 사이에서 보간 되어서 2850으로 평가된다.

Oracle Program

SELECT last_name, salary, department_id,
       PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary DESC) 
                            OVER (PARTITION BY department_id) "Percentile_Cont",
       PERCENT_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) "Percent_Rank"
FROM   employees WHERE department_id IN (30, 60);

 

Results

LAST_NAME         SALARY DEPARTMENT_ID Percentile_Cont Percent_Rank
------------- ---------- ------------- --------------- ------------
Raphaely           11000            30            2850            0
Khoo                3100            30            2850           .2
Baida               2900            30            2850           .4
Tobias              2800            30            2850           .6
Himuro              2600            30            2850           .8
Colmenares          2500            30            2850            1
Hunold              9000            60            4800            0
Ernst               6000            60            4800          .25
Austin              4800            60            4800           .5
Pataballa           4800            60            4800           .5
Lorentz             4200            60            4800            1

 

---

오라클 SQL 함수(Oracle SQL Function) 목록 리스트 링크

 

[SQL, Pandas, R Prog, Dplyr, SQLDF, PANDASQL, DATA.TABLE] SQL EMP 예제로 만나는 테이블 데이터 처리 방법 리스트 링크