PERCENT_RANK

PERCENT_RANK returns the relative rank of a row within its partition as a value between 0.0 and 1.0.

Limits

Window functions are subject to the following limits:

Window functions are supported only in SELECT statements.
A window function cannot contain nested window functions or nested aggregate functions.
Window functions cannot be used together with aggregate functions at the same query level.

Syntax

DOUBLE PERCENT_RANK() OVER ([partition_clause] [orderby_clause])

Return value

Returns a DOUBLE value calculated as:

(r - 1) / (n - 1)

where r is the RANK() of the current row and n is the total number of rows in the partition.

If the partition contains only one row, 0.0 is returned.

Rows with identical ORDER BY values receive the same percentile rank. For example, if two rows tie at rank 1 in a partition of 6, both receive 0.0.

Parameters

partition_clause and orderby_clause: See Window specification for syntax details.

Sample data

The examples in this topic use an emp table. Run the following statements to create the table and load the sample data:

CREATE TABLE IF NOT EXISTS emp
   (empno    BIGINT,
    ename    STRING,
    job      STRING,
    mgr      BIGINT,
    hiredate DATETIME,
    sal      BIGINT,
    comm     BIGINT,
    deptno   BIGINT);

tunnel upload emp.txt emp;

The emp.txt file contains the following data:

7369,SMITH,CLERK,7902,1980-12-17 00:00:00,800,,20
7499,ALLEN,SALESMAN,7698,1981-02-20 00:00:00,1600,300,30
7521,WARD,SALESMAN,7698,1981-02-22 00:00:00,1250,500,30
7566,JONES,MANAGER,7839,1981-04-02 00:00:00,2975,,20
7654,MARTIN,SALESMAN,7698,1981-09-28 00:00:00,1250,1400,30
7698,BLAKE,MANAGER,7839,1981-05-01 00:00:00,2850,,30
7782,CLARK,MANAGER,7839,1981-06-09 00:00:00,2450,,10
7788,SCOTT,ANALYST,7566,1987-04-19 00:00:00,3000,,20
7839,KING,PRESIDENT,,1981-11-17 00:00:00,5000,,10
7844,TURNER,SALESMAN,7698,1981-09-08 00:00:00,1500,0,30
7876,ADAMS,CLERK,7788,1987-05-23 00:00:00,1100,,20
7900,JAMES,CLERK,7698,1981-12-03 00:00:00,950,,30
7902,FORD,ANALYST,7566,1981-12-03 00:00:00,3000,,20
7934,MILLER,CLERK,7782,1982-01-23 00:00:00,1300,,10
7948,JACCKA,CLERK,7782,1981-04-12 00:00:00,5000,,10
7956,WELAN,CLERK,7649,1982-07-20 00:00:00,2450,,10
7956,TEBAGE,CLERK,7748,1982-12-30 00:00:00,1300,,10

Examples

Rank each employee within their department by salary in descending order and return a percentile rank score for each row:

SELECT deptno, ename, sal,
       PERCENT_RANK() OVER (PARTITION BY deptno ORDER BY sal DESC) AS sal_new
FROM emp;

Result:

+------------+------------+------------+------------+
| deptno     | ename      | sal        | sal_new    |
+------------+------------+------------+------------+
| 10         | JACCKA     | 5000       | 0.0        |
| 10         | KING       | 5000       | 0.0        |
| 10         | CLARK      | 2450       | 0.4        |
| 10         | WELAN      | 2450       | 0.4        |
| 10         | TEBAGE     | 1300       | 0.8        |
| 10         | MILLER     | 1300       | 0.8        |
| 20         | SCOTT      | 3000       | 0.0        |
| 20         | FORD       | 3000       | 0.0        |
| 20         | JONES      | 2975       | 0.5        |
| 20         | ADAMS      | 1100       | 0.75       |
| 20         | SMITH      | 800        | 1.0        |
| 30         | BLAKE      | 2850       | 0.0        |
| 30         | ALLEN      | 1600       | 0.2        |
| 30         | TURNER     | 1500       | 0.4        |
| 30         | MARTIN     | 1250       | 0.6        |
| 30         | WARD       | 1250       | 0.6        |
| 30         | JAMES      | 950        | 1.0        |
+------------+------------+------------+------------+

Employees with the same sal value receive the same percentile rank. In department 10, both employees with sal = 5000 receive 0.0, and both with sal = 1300 receive 0.8.

Related functions

PERCENT_RANK is a window function. For the full list of window functions, see Window functions.