Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.tools -> Re: Anyway to calculate a percentile?

Re: Anyway to calculate a percentile?

From: Hakan <heren_at_home.com>
Date: 2000/07/12
Message-ID: <396BFF15.29520213@home.com> 






hi,



i wrote the following in a very short period of time. You might like to
spend


more time for the performance.



SQLWKS> select * from v$version

     2> /
BANNER                                                          
----------------------------------------------------------------
Oracle7 Server Release 7.3.4.5.0 - Production                   
PL/SQL Release 2.3.4.5.0 - Production                           
CORE Version 3.5.4.0.0 - Production                             
TNS for 32-bit Windows: Version 2.3.4.0.0 - Production          
NLSRTL Version 3.2.4.0.1 - Production                           


5 rows selected.

SQLWKS> create table percentile_table
     2> (score number(10))
     3> /
Statement processed.
SQLWKS> insert into percentile_table values(	18	);	
1 row processed.
SQLWKS> insert into percentile_table values(	21	);	
1 row processed.
SQLWKS> insert into percentile_table values(	21	);	
1 row processed.
SQLWKS> insert into percentile_table values(	27	);	
1 row processed.
SQLWKS> insert into percentile_table values(	29	);	
1 row processed.
SQLWKS> insert into percentile_table values(	31	);	
1 row processed.
SQLWKS> insert into percentile_table values(	32	);	
1 row processed.
SQLWKS> insert into percentile_table values(	33	);	
1 row processed.
SQLWKS> insert into percentile_table values(	35	);	
1 row processed.
SQLWKS> insert into percentile_table values(	41	);	
1 row processed.
SQLWKS> insert into percentile_table values(	43	);	
1 row processed.
SQLWKS> insert into percentile_table values(	44	);	
1 row processed.
SQLWKS> insert into percentile_table values(	45	);	
1 row processed.
SQLWKS> insert into percentile_table values(	45	);	
1 row processed.
SQLWKS> insert into percentile_table values(	46	);	
1 row processed.
SQLWKS> insert into percentile_table values(	47	);	
1 row processed.
SQLWKS> insert into percentile_table values(	48	);	
1 row processed.
SQLWKS> insert into percentile_table values(	49	);	
1 row processed.
SQLWKS> insert into percentile_table values(	52	);	
1 row processed.
SQLWKS> insert into percentile_table values(	54	);	
1 row processed.
SQLWKS> insert into percentile_table values(	54	);	
1 row processed.
SQLWKS> insert into percentile_table values(	55	);	
1 row processed.
SQLWKS> insert into percentile_table values(	55	);	
1 row processed.
SQLWKS> insert into percentile_table values(	56	);	
1 row processed.
SQLWKS> insert into percentile_table values(	57	);	
1 row processed.
SQLWKS> insert into percentile_table values(	58	);	
1 row processed.
SQLWKS> insert into percentile_table values(	61	);	
1 row processed.
SQLWKS> insert into percentile_table values(	62	);	
1 row processed.
SQLWKS> insert into percentile_table values(	63	);	
1 row processed.
SQLWKS> insert into percentile_table values(	64	);	
1 row processed.
SQLWKS> insert into percentile_table values(	66	);	
1 row processed.
SQLWKS> insert into percentile_table values(	67	);	
1 row processed.
SQLWKS> insert into percentile_table values(	69	);	
1 row processed.
SQLWKS> insert into percentile_table values(	70	);	
1 row processed.
SQLWKS> insert into percentile_table values(	71	);	
1 row processed.
SQLWKS> insert into percentile_table values(	73	);	
1 row processed.
SQLWKS> insert into percentile_table values(	77	);	
1 row processed.
SQLWKS> insert into percentile_table values(	80	);	
1 row processed.
SQLWKS> insert into percentile_table values(	81	);	
1 row processed.
SQLWKS> insert into percentile_table values(	82	);	


1 row processed.


SQLWKS> commit;


Statement processed.


SQLWKS> create or replace function calc_percentile(x number) return
number as

     2>   percentile number;
     3> begin
     4>   select sum(decode(sign(x-score), -1, 0, 1))/count(score)*100
     5>     into percentile
     6>     from percentile_table;
     7>   return percentile;
     8> end;
     9> /




Statement processed.


SQLWKS> select score, to_char(calc_percentile(score), '999.99') || ' %'
from percentile_table

     2>  order by 1
     3> /
SCORE      TO_CHAR(C
---------- ---------
        18    2.50 %
        21    7.50 %
        21    7.50 %
        27   10.00 %
        29   12.50 %
        31   15.00 %
        32   17.50 %
        33   20.00 %
        35   22.50 %
        41   25.00 %
        43   27.50 %
        44   30.00 %
        45   35.00 %
        45   35.00 %
        46   37.50 %
        47   40.00 %
        48   42.50 %
        49   45.00 %
        52   47.50 %
        54   52.50 %
        54   52.50 %
        55   57.50 %
        55   57.50 %
        56   60.00 %
        57   62.50 %
        58   65.00 %
        61   67.50 %
        62   70.00 %
        63   72.50 %
        64   75.00 %
        66   77.50 %
        67   80.00 %
        69   82.50 %
        70   85.00 %
        71   87.50 %
        73   90.00 %
        77   92.50 %
        80   95.00 %
        81   97.50 %
        82  100.00 %




40 rows selected.



clean code:


select * from v$version


/


create table percentile_table


(score number(10))


/

insert into percentile_table values(	18	);	
insert into percentile_table values(	21	);	
insert into percentile_table values(	21	);	
insert into percentile_table values(	27	);	
insert into percentile_table values(	29	);	
insert into percentile_table values(	31	);	
insert into percentile_table values(	32	);	
insert into percentile_table values(	33	);	
insert into percentile_table values(	35	);	
insert into percentile_table values(	41	);	
insert into percentile_table values(	43	);	
insert into percentile_table values(	44	);	
insert into percentile_table values(	45	);	
insert into percentile_table values(	45	);	
insert into percentile_table values(	46	);	
insert into percentile_table values(	47	);	
insert into percentile_table values(	48	);	
insert into percentile_table values(	49	);	
insert into percentile_table values(	52	);	
insert into percentile_table values(	54	);	
insert into percentile_table values(	54	);	
insert into percentile_table values(	55	);	
insert into percentile_table values(	55	);	
insert into percentile_table values(	56	);	
insert into percentile_table values(	57	);	
insert into percentile_table values(	58	);	
insert into percentile_table values(	61	);	
insert into percentile_table values(	62	);	
insert into percentile_table values(	63	);	
insert into percentile_table values(	64	);	
insert into percentile_table values(	66	);	
insert into percentile_table values(	67	);	
insert into percentile_table values(	69	);	
insert into percentile_table values(	70	);	
insert into percentile_table values(	71	);	
insert into percentile_table values(	73	);	
insert into percentile_table values(	77	);	
insert into percentile_table values(	80	);	
insert into percentile_table values(	81	);	
insert into percentile_table values(	82	);	


commit;


create or replace function calc_percentile(x number) return number as
  percentile number;


begin


  select sum(decode(sign(x-score), -1, 0, 1))/count(score)*100
    into percentile


    from percentile_table;


  return percentile;


end;


/


select score, to_char(calc_percentile(score), '999.99') || ' %' from
percentile_table


 order by 1


/




Hakan



rob.blank_at_lighting.ge.com wrote:


> 

> Has anyone had the need to calculate a percentile in Oracle 7.3.x?  For

> example if I had a table with a numerical column how would I find the

> value at the 10th percentile?  I can think of several ways I could try

> this, none of which I like, so I thought I might throw it up for grabs

> here.

> 

> The first idea I had involved ordering the list and dividing the rownum

> by the row count to derive a percentile.  But there's apparently a

> problem with doing that.  This approach would be based in part on the

> following SQL:

> 

> select rownum, col1

> from

>         (select col1

>         from table1

>         order by col1) ordered_list

> 

> When I add the order by clause to the subselect I get a compilation

> error.  ORA-00907: missing right parenthesis.  I can almost understand

> that it makes no sense to to order a subselect like that (except that

> that's key to my solution), but why would I get a compilation error?

> 

> Anyway, if anyone's had any experience doing something like this, or

> has any ideas your help would be greatly appreciated.

> 

> Thanks,

> Rob

> 

> Sent via Deja.com http://www.deja.com/

> Before you buy.

Received on Wed Jul 12 2000 - 00:00:00 CDT












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US