[오라클(Oracle) SQL 함수] PERCENT_RANK 함수

o PERCENT_RANK (백분위수 순위)

 

---

문 법

 

• 집계 문법

percent_rank_aggregate::=

 

• 분석 문법

 

percent_rank_analytic::=

 

---

목 적 

PERCENT_RANK함수는 인수로 지정한 값의 그룹 내의 상대적 위치를 나타내는 백분위수 순위(순위 퍼센트, Percent rank)를 반환한다.

PERCENT_RANK 함수는 CUME_DIST(누적 분포) 함수와 유사하다. PERCENT_RANK 함수에 의해 반환되는 값의 범위는 0~1(경계값 포함)이다. 모든 집합의 최초 첫 번째 행은 PERCENT_RANK 함수의 값은 0이다. 반환되는 값의 데이터 타입은 NUMBER이다.

• 집계 함수로써, PERCENT_RANK 함수는 함수의 인수와 대응하는 정렬 지정 방식에 의해서 선택된 불확실한(특정) 행 r 을 계산하여서, 해당 행 r 의 RANK(순위)로부터 1을 뺀 값을 집계 그룹 내의 행의 수로 나눈다. 이 계산은 불확실한(특정) 행 r 이 오라클 데이터베이스가 집계하는 행의 그룹에 삽입되어 있는 것 처럼 계산한다. 함수의 인수는 각 집계 그룹 내에서 하나의 불확실한 행을 식별한다. 그러므로, 각 집계 그룹 내에서 상수 표현식으로 평가될 필요가 있다. 상수 인수 표현식과 집계 ORDER BY 절에 있는 표현식은 위치는 위치별로 일치해야 한다. 그러므로 인수의 개수는 같아야 하고, 그 형태는 호환되어야 한다.

(가상 행의 RANK - 1)/(집계 그룹 행의 개수)

 

• 분석 함수로써 PERCENT_RANK 함수는 파티션 그룹 내에서 r 행의 백분위수 순위는 r 행의 그룹 내 Rank(순위) 에서 1을 뺀 수를 평가 중인 행 수(전체 쿼리 결과 집합 또는 파티션) 에서 1을 뺀 수로 나눈 값이다.

 (그룹별 RANK - 1)/(그룹 행의 개수 - 1)

 

CUME_DIST 함수와 유사한 함수이나 PARTITION 별 각 row 의 순위 -1 / PARTITION 내의 ROW 의 수 -1를 결괏값으로 하며, 결괏값의 범위는 0 <=결괏값 <=1이고 집합의 첫 번째 row 의 PERCENT_RANK 는 항상 0이 된다.

 

[ CUME_DIST 함수 와 PERCENT_RNAK 함수 비교]

데이터	RANK 함수	DENSE_RANK 함수	CUME_DIST 함수	PERCENT_RANK 함수
10	1	1	0.25	0           -> (1-1) / (4-1)
20	2	2	0.75	0.33       -> (2-1) / (4-1)
20	2	2	0.75	0.33       -> (2-1) / (4-1)
30	4	3	1	1            -> (4-1) / (4-1)

 

---

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

• PERCENT_RANK Oracle Function 프로그래밍 비교

 

---

관련 함수

함수명	함수 내용
CUME_DIST	그룹에 존재하는 관측치의 누적 분포 값을 계산한다.
DENSE_RANK	Rank()와 유사한 함수로 각 그룹별로 ORDER BY절에 사용된 칼럼이나 표현식을 기준으로 정렬하고, 그룹 내의 각 열에 대한 순위를 부여한다.
LAG	이 함수는 Self 조인 작업 수행하지 않고, 하나의 테이블을 접근하면서 여러 개의 행에 동시에 접근(Access)하여 작업한다. LAG 함수는 현재 위치를 기준으로 이전에 지정한 행에 접근하여 작업을 수행한다.
LEAD	이 함수는 Self 조인 작업을 수행하지 않고, 하나의 테이블을 접근하면서 여러 개의 행에 동시에 접근(Access)하여 작업한다. 쿼리에서 수행되는 행의 Series나 커서의 위치를 지정하면, LEAD 함수는 해당 위치 이후에 존재하는 물리적 Offset의 행에 ACCESS 한다.
NTILE	그룹 내에서 정렬된 데이터를 expr에 의해 지정된 bucket의 수로 분할하여, 각 행을 적절한 bucket의 번호를 부여하여 할당한다.
PERCENT_RANK	인수로 지정한 값의 그룹 내의 위치를 나타내는 백분위 순위(순위 퍼센트, Percent rank)를 반환한다.
PERCENTILE_CONT	연속된 분포 모델을 가정하여 인수로 지정한 백분위 값에 해당하는 값을 계산하는 역 분포 함수(inverse distribution function)이다.
PERCENTILE_DISC	이산 분포 모형을 가정하는 역 분포 함수이다. 사용자가 지정한 백분위수에 해당하는 데이터 값을 검색하고, 정확하게 일치하는 데이터 값이 없으면 지정한 백분위 값의 주변 데이터 정렬 옵션에 따라 우선순위가 빠른 바로 앞의 관측치 값을 반환한다.
RANK	값의 그룹에서 값의 순위를 계산한다. 반환되는 데이터형은 NUMBER이다.
ROW_NUMBER	이 함수는 파티션 그룹별 또는 전체 데이터에 대하여 order_by_clause에서 지정된 행의 정렬 순위대로 1로 시작하는 중복을 허용하지 않는 유일한(unique) 순서 값을 할당한다. (파티션에서 각 행 또는 쿼리에 의해서 반환되는 각 행에 대하여)

 

---

예 제

 

집계 예제

다음 예제는 hr.employees테이블에서 급여가 $15,500이고, 수수료가 5%인 불확실한 종업원의 Percent Rank를 계산한다.

Oracle Programming

SELECT PERCENT_RANK(15000, .05) WITHIN GROUP (ORDER BY salary, commission_pct) "Percent-Rank" 
FROM   employees;

 

Results

Percent-Rank
------------
  .971962617

 

---

분석 예제

다음 예제는 종업원마다 부서 내에서 종업원 급여의 Percent Rank를 계산한다.

Oracle Programming

SELECT department_id, last_name, salary, 
       PERCENT_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS pr
FROM   employees
ORDER BY 
       pr, salary;

 

Results

DEPARTMENT_ID LAST_NAME                     SALARY         PR
------------- ------------------------- ---------- ----------
           10 Whalen                          4400          0
           40 Marvis                          6500          0
. . .
           80 Vishney                        10500 .176470588
           50 Everett                         3900 .181818182
           30 Khoo                            3100         .2
. . .
           80 Johnson                         6200 .941176471
           50 Markle                          2200 .954545455
           50 Philtanker                      2200 .954545455
           50 Olson                           2100          1
. . .

 

---

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

 

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

2023.12 수정