René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback -
 

percent_rank [Oracle SQL analytic function]

percent_rank returns a number between 0 and 1. The first returned number in a partition is always 0. The following returned numbers are increased in equal steps to reach 1 for the last returned row in a partition. With other words, percent_rank returns (for the nth row in a partition of m records): (n-1)/(m-1). 
create table pr_tab (
  n number
);

insert into pr_tab values (null);
insert into pr_tab values (   1);
insert into pr_tab values (   2);
insert into pr_tab values (   3);
insert into pr_tab values (   4);
insert into pr_tab values (   4);
insert into pr_tab values (   4);
insert into pr_tab values (   7);
insert into pr_tab values (   8);
insert into pr_tab values (   9);
insert into pr_tab values (  10);

select n, 
       percent_rank( ) over (order by n)        percent_rank,
      (rank        ( ) over (order by n) -1 ) /
      (count       (*) over ()           -1 )   calculated
 from pr_tab;

         N PERCENT_RANK CALCULATED
---------- ------------ ----------
         1            0          0
         2           .1         .1
         3           .2         .2
         4           .3         .3
         4           .3         .3
         4           .3         .3
         7           .6         .6
         8           .7         .7
         9           .8         .8
        10           .9         .9
                      1          1
With the following syntax, it returns the percent_rank as though the value within the parenthesis were part of the data in the table: 
delete from pr_tab where n = 3;

select percent_rank( 3) within group (order by n) percent_rank
  from pr_tab;

PERCENT_RANK
------------
          .2