Oracle SQL - Analytic Functions PERCENT

Introduction

The syntax for the PERCENT_RANK function:

PERCENT_RANK() OVER ([PARTITION clause] ORDER clause)

The PARTITION clause is optional.

PERCENT_RANK will compute the cumulative fraction of the ranking that exists for a particular ranking value.

PERCENT_RANK is set to compute so that the first row is zero, and the other values in this column are computed based on the formula:

Percent_rank (PR) = (Rank-1)/(Number of rows-1)

SELECT ename, orig_salary, 
  RANK() OVER(ORDER BY orig_salary) RANK, 
  PERCENT_RANK() OVER(ORDER BY orig_salary) PR, 
  CUME_DIST() OVER(ORDER BY orig_salary) CD 
FROM emp 
ORDER BY orig_salary;

Demo

SQL>
SQL> drop table emp;

Table dropped.-- from  ww w.j a  v a2s  . com

SQL> create table emp(
  2  empno        Number(3)  NOT NULL,
  3  ename        VARCHAR2(10 BYTE),
  4  hireDate     DATE,
  5  orig_salary  Number(8,2),
  6  curr_salary  Number(8,2),
  7  region       VARCHAR2(1 BYTE)
  8  );
SQL>
SQL> insert into emp values(1,'Alison',DATE '1996-03-21', 45000, NULL,  'E');
SQL> insert into emp values(2,'James',DATE  '1978-12-12', 23000, 32000, 'W');
SQL> insert into emp values(3,'Celia',DATE  '1982-10-24', NULL,  58000, 'E');
SQL> insert into emp values(4,'Robert',DATE '1984-01-15', 31000, NULL,  'W');
SQL> insert into emp values(5,'Linda',DATE  '1987-07-30', NULL,  53000, 'E');
SQL> insert into emp values(6,'David',DATE  '1990-12-31', 78000, NULL,  'W');
SQL> insert into emp values(7,'Jode',DATE  '1996-09-17', 21000, 29000, 'E');
SQL>
SQL> SELECT ename, orig_salary,
  2    RANK() OVER(ORDER BY orig_salary) RANK,
  3    PERCENT_RANK() OVER(ORDER BY orig_salary) PR,
  4    CUME_DIST() OVER(ORDER BY orig_salary) CD
  5  FROM emp
  6  ORDER BY orig_salary;

ENAME      ORIG_SALARY       RANK         PR         CD
---------- ----------- ---------- ---------- ----------
Jode             21000          1          0 .142857143
James            23000          2 .166666667 .285714286
Robert           31000          3 .333333333 .428571429
Alison           45000          4         .5 .571428571
David            78000          5 .666666667 .714285714
Linda                           6 .833333333          1
Celia                           6 .833333333          1

7 rows selected.

SQL>

Oracle SQL - Analytic Functions PERCENT_RANK

Introduction

Demo