Mega Code Archive

 

Categories / Oracle PLSQL / Analytical Functions

 

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

SQL> SQL> -- create demo table SQL> create table Employee(   2    empno              Number(3)  NOT NULL, -- Employee ID   3    ename              VARCHAR2(10 BYTE),   -- Employee Name   4    hireDate          DATE,                -- Date Employee Hired   5    orig_salary        Number(8,2),         -- Orignal Salary   6    curr_salary        Number(8,2),         -- Current Salary   7    region             VARCHAR2(1 BYTE)     -- Region where employeed   8  )   9  / Table created. SQL> SQL> SQL> -- prepare data for employee table SQL> insert into Employee(empno,  ename,  hireDate,                   orig_salary, curr_salary, region)   2                values(122,'Alison',to_date('19960321','YYYYMMDD'), 45000,       NULL,       'E')   3  / 1 row created. SQL> insert into Employee(empno,  ename,  hireDate,                       orig_salary, curr_salary, region)   2                values(123, 'James',to_date('19781212','YYYYMMDD'), 23000,       32000,       'W')   3  / 1 row created. SQL> insert into Employee(empno,  ename,  hireDate,                       orig_salary, curr_salary, region)   2                values(104,'Celia',to_date('19821024','YYYYMMDD'), NULL,       58000,        'E')   3  / 1 row created. SQL> insert into Employee(empno,  ename,  hireDate,                       orig_salary, curr_salary, region)   2                values(105,'Robert',to_date('19840115','YYYYMMDD'), 31000,      NULL,        'W')   3  / 1 row created. SQL> insert into Employee(empno,  ename,  hireDate,                       orig_salary, curr_salary, region)   2                values(116,'Linda', to_date('19870730','YYYYMMDD'), NULL,       53000,       'E')   3  / 1 row created. SQL> insert into Employee(empno,  ename,  hireDate,                       orig_salary, curr_salary, region)   2                values(117,'David', to_date('19901231','YYYYMMDD'), 78000,       NULL,       'W')   3  / 1 row created. SQL> insert into Employee(empno,  ename,  hireDate,                       orig_salary, curr_salary, region)   2                values(108,'Jode',  to_date('19960917','YYYYMMDD'), 21000,       29000,       'E')   3  / 1 row created. SQL> SQL> -- display data in the table SQL> select * from Employee   2  /      EMPNO ENAME      HIREDATE  ORIG_SALARY CURR_SALARY R ---------- ---------- --------- ----------- ----------- -        122 Alison     21-MAR-96       45000             E        123 James      12-DEC-78       23000       32000 W        104 Celia      24-OCT-82                   58000 E        105 Robert     15-JAN-84       31000             W        116 Linda      30-JUL-87                   53000 E        117 David      31-DEC-90       78000             W        108 Jode       17-SEP-96       21000       29000 E 7 rows selected. SQL> SQL> SQL> SQL> SQL> --PERCENT_RANK will compute the cumulative fraction of the ranking that exists for a particular ranking value. This calculation and the one for CUME_DIST are like the values one would see in a histogram. 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: SQL> SQL> --Percent_rank (PR) = (Rank-1)/(Number of rows-1) SQL> SQL> --For example, using our Employee table with PERCENT_RANK and CUME_DIST: SQL> SQL> SELECT empno, ename, region,   2    RANK() OVER(PARTITION BY region ORDER BY curr_salary)   3        RANK,   4    PERCENT_RANK() OVER(PARTITION BY region ORDER BY   5         curr_salary) PR,   6    CUME_DIST() OVER(PARTITION BY region ORDER BY curr_salary)   7        CD   8  FROM employee;      EMPNO ENAME      R       RANK         PR         CD ---------- ---------- - ---------- ---------- ----------        108 Jode       E          1          0        .25        116 Linda      E          2 .333333333         .5        104 Celia      E          3 .666666667        .75        122 Alison     E          4          1          1        123 James      W          1          0 .333333333        117 David      W          2         .5          1        105 Robert     W          2         .5          1 7 rows selected. SQL> SQL> SQL> SQL> SQL> SQL> -- clean the table SQL> drop table Employee; Table dropped. SQL> SQL>