[Oracle] PERCENT_RANK()

오라클 PERCENT_RANK()

 

PERCENT_RANK()

   -

 

PERCENT_RANK(expr) WITHIN GROUP(ORDER BY expr DESC|ASC NULLS FIRST|LAST)

PERCENT_RANK() OVER (query_partition_clause order_by_clause) 

의 형태

 

PERCENT_RANK is similar to the CUME_DIST(cumulative distribution) function. The range of values returned by PERCENT_RANK is 0 to 1. inclusive. The first row in any set has a PERCENT_RANK of 0. The return value is NUMBER.

 

• As an aggregate function, PERCENT_RANK calculates, for a hypothetical row r identified by the arguments of the function and a corresponding sort specification, the rank of row r minus 1 divided by the number of rows in the aggregate group. This calculation is made as if the hypothetical row r were inserted into the group of rows over which Oracle Database is to aggregate. 
  
  The arguments of the function identify a single hypothetical row within each aggregate group. Therefore, they must all evaluate to constant expression within each aggregate group. The constant argument expressions and the expression in the ORDER BY caluse of the aggregate match by position. Therefore the number of agruments must be the same and their types must be compatible.
• As an analytic function, for a row r, PERCENT_RANK calculates the rank of r minus 1, divided by 1 less than the number of rows begin evaluated(the entire result set or a partition).

Aggregate Example

 

오라클 oe 계정, hr 계정으로 테스트 가능

The following example calculates the percent rank of a hypothetical employee in the samle table hr.employees with a salary of $15,000 and a commission of 5%:

 

SQL>

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

 

--> 0.97196261682243

 

SQL>

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

 

--> 0.97196261682243

 

107명 중에 salaryrk 15000이면 104번째

따라서 104/107을 하면 0.9719626168224299‬ 이렇게 나온다.

 

Analytic Example

The following example calculates, for each employee, the percent rank of the employee's salary within the department :

 

SQL>

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, last_name
;