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

percentile_disc [Oracle SQL analytic function]

percentile_disc returns a number between 0 and 1. 
create table pd_tab (n number);

insert into pd_tab values (null);
insert into pd_tab values (null);
insert into pd_tab values (   1);
insert into pd_tab values (   2);
insert into pd_tab values (   3);
insert into pd_tab values (   4);
insert into pd_tab values (  10);
insert into pd_tab values (  10);
insert into pd_tab values (  10);
insert into pd_tab values ( 100);
insert into pd_tab values ( 100);
insert into pd_tab values (1000);
percentile_desc ignores null values. 
create view pd_view as
  select n,
         cume_dist() over (order by n) cume_dist_
    from pd_tab
  where  n is not null;


select * from pd_view;

         N CUME_DIST_
---------- ----------
         1         .1
         2         .2
         3         .3
         4         .4
        10         .7
        10         .7
        10         .7
       100         .9
       100         .9
      1000          1

define cd=.7

select
  percentile_disc(&cd) within group (order by n         ) percentile_disc,
  min(case when cume_dist_ >= &cd then n else null end)   calculated
from pd_view;

PERCENTILE_DISC CALCULATED
--------------- ----------
             10         10

define cd=.25

select
  percentile_disc(&cd) within group (order by n         ) percentile_disc,
  min(case when cume_dist_ >= &cd then n else null end)   calculated
from pd_view;

PERCENTILE_DISC CALCULATED
--------------- ----------
              3          3