Results 1 to 13 of 13
  1. #1
    Join Date
    Feb 2004
    Posts
    3

    Unanswered: Percentile Computation

    I need help in calculation the Fifth Percentile and the 95th Percentile from the Price/Unit data I have.

    The table has two columns.

    Price Units.

    2.99 45
    1.99 32
    2.49 66
    1.49 77
    1.09 78
    3.29 22

    With this data, I'd like to calculate teh 5th and the 95th Percentile (for PRICE) in Oracle SQL. The Percentile Price is weighted by Units.

    Any suggestions?

    Thanks.

  2. #2
    Join Date
    Jan 2004
    Location
    North Haven, CT
    Posts
    110

    Re: Percentile Computation

    Originally posted by sunny99
    I need help in calculation the Fifth Percentile and the 95th Percentile from the Price/Unit data I have.

    The table has two columns.

    Price Units.

    2.99 45
    1.99 32
    2.49 66
    1.49 77
    1.09 78
    3.29 22

    With this data, I'd like to calculate teh 5th and the 95th Percentile (for PRICE) in Oracle SQL. The Percentile Price is weighted by Units.

    Any suggestions?

    Thanks.
    check out percentile_dis.
    This aggregation function might be helpful.

  3. #3
    Join Date
    Oct 2002
    Location
    Plymouth UK
    Posts
    116
    I think that should be PERCENTILE_DISC. This is one of the many analytical functions now available, you may what to check what is available.

    However, analytical functions are only available from version 8.1.6. You don't mention which version of Oracle you are running.

  4. #4
    Join Date
    Dec 2003
    Location
    Noida, India.
    Posts
    171
    Refer

    http://www-db.stanford.edu/dbseminar...upta/paper.pdf

    for exact syntax and difference berween percentile_disc and percentile_cont.
    Oracle can do wonders !

  5. #5
    Join Date
    Feb 2004
    Posts
    3
    Originally posted by cmasharma
    Refer

    http://www-db.stanford.edu/dbseminar...upta/paper.pdf

    for exact syntax and difference berween percentile_disc and percentile_cont.
    PERCENTILE_DISC will do the trick when the data is in one column. However, I have 2 columns that contain the data. (Price and Units). I need to find the 95th Percentile of the Prices weighted by Units. Units have to be incorporated in the computation.

    Please let me know if you have any suggestions.

    Thanks.

  6. #6
    Join Date
    Jan 2004
    Location
    North Haven, CT
    Posts
    110
    You basicly got a distribution of units across price ranges. It sounds like a simple problem of probability. If i cannot find a solution based on a single select statement, i will look a book on statistics. I believe this kind of thing has already been written in Fortran or C. You might be able to find the logic from Numerical Recipes.

    Originally posted by sunny99
    PERCENTILE_DISC will do the trick when the data is in one column. However, I have 2 columns that contain the data. (Price and Units). I need to find the 95th Percentile of the Prices weighted by Units. Units have to be incorporated in the computation.

    Please let me know if you have any suggestions.

    Thanks.

  7. #7
    Join Date
    Dec 2003
    Location
    Noida, India.
    Posts
    171
    Am not very good in statictics.. but from what I gather, you want to know the price for item that has the unit at 95th position? i.e. u arrange units in order, get unit at 95th position and for that position find the price.

    Do i understand ur requirement correctly? If no, can you pls explain ...

    Originally posted by sunny99
    PERCENTILE_DISC will do the trick when the data is in one column. However, I have 2 columns that contain the data. (Price and Units). I need to find the 95th Percentile of the Prices weighted by Units. Units have to be incorporated in the computation.

    Please let me know if you have any suggestions.

    Thanks.
    Oracle can do wonders !

  8. #8
    Join Date
    Feb 2004
    Posts
    3
    Originally posted by cmasharma
    Am not very good in statictics.. but from what I gather, you want to know the price for item that has the unit at 95th position? i.e. u arrange units in order, get unit at 95th position and for that position find the price.

    Do i understand ur requirement correctly? If no, can you pls explain ...
    Let me explain the problem differently. If I had one column and 1000 rows of price data, I can get the 95th Percentile by using PERCENTIL_DIS function. (I would not have to arrange/sort the 1000 rows).

    However, I do NOT have 1000 rows of price data. Instead I have 6 rows of price data with 2 COLUMNS. For each of the 6 prices, I have a 2nd column which gives the count. Here is a sample data

    PRICE COUNT
    1.59 250
    1.99 230
    2.19 200
    2.49 170
    2.79 100
    2.99 50

    Sharma Ji, Please let me know if this is not clear.
    Thanks.

  9. #9
    Join Date
    Dec 2003
    Location
    Noida, India.
    Posts
    171
    This would require you to build an algorith (a procedure) that ungroups the price.. then use the percentile_disc function on this list of price.

    If i get a better solution, will post it here.



    Originally posted by sunny99
    Let me explain the problem differently. If I had one column and 1000 rows of price data, I can get the 95th Percentile by using PERCENTIL_DIS function. (I would not have to arrange/sort the 1000 rows).

    However, I do NOT have 1000 rows of price data. Instead I have 6 rows of price data with 2 COLUMNS. For each of the 6 prices, I have a 2nd column which gives the count. Here is a sample data

    PRICE COUNT
    1.59 250
    1.99 230
    2.19 200
    2.49 170
    2.79 100
    2.99 50

    Sharma Ji, Please let me know if this is not clear.
    Thanks.
    Oracle can do wonders !

  10. #10
    Join Date
    Dec 2003
    Location
    Noida, India.
    Posts
    171
    I have tried to implement it using a temporary table. Check out if this serves your purpose.

    Code:
    SQL> create table s (price number, cnt number);
    
    Table created.
    
    SQL> insert into s values (12,3);
    
    1 row created.
    
    SQL> insert into s values (13,4);
    
    1 row created.
    
    SQL> insert into s values (20,8);
    
    1 row created.
    
    SQL> commit;
    
    Commit complete.
    
    SQL> select * from s;
    
         PRICE        CNT
    ---------- ----------
            12          3
            13          4
            20          8
    
    SQL> CREATE GLOBAL TEMPORARY TABLE temp_ss (price number, cnt number) on commit delete rows;
    
    Table created.
    
    SQL> select * from temp_s;
    
    no rows selected
    
    SQL> declare
      2   cursor c1 is select price, cnt from s;
      3   n_price number;
      4  begin
      5   for l1 in c1 loop
      6    for i in 1..l1.cnt loop
      7     insert into temp_ss (price) values (l1.price);
      8    end loop;
      9   end loop;
     10   
     11   select percentile_disc(0.95) within group (order by price) into n_price from temp_s;
     12   commit;
     13   dbms_output.put_line(n_price);
     14  end;
     15  /
    20
    
    PL/SQL procedure successfully completed.
    
    SQL> select * from temp_s;
    
    no rows selected
    
    SQL>
    Oracle can do wonders !

  11. #11
    Join Date
    Jan 2004
    Location
    North Haven, CT
    Posts
    110
    Let us do it by using pivot table, assuming value of units is less than 1000 for every record in the table, otherwise, change the query accordingly.

    SQL> create table x(price number, units number);

    Table created.

    SQL>
    SQL> insert into x values (1.59, 250);

    1 row created.

    SQL> insert into x values (1.99, 230);

    1 row created.

    SQL> insert into x values (2.19, 200);

    1 row created.

    SQL> insert into x values (2.49, 170);

    1 row created.

    SQL> insert into x values (2.79, 100);

    1 row created.

    SQL> insert into x values (2.99, 50);

    1 row created.

    SQL>
    SQL> select percentile_disc(0.95) within group (order by price) from
    2 (select price from x, (select rownum seq from all_objects, all_objects where rownum < 1000) pivot
    3 where pivot.seq < x.units + 1);

    PERCENTILE_DISC(.95)WITHINGROU
    ------------------------------
    2.79

    SQL>
    SQL> select percentile_disc(0.96) within group (order by price) from
    2 (select price from x, (select rownum seq from all_objects where rownum < 1000) pivot
    3 where pivot.seq < x.units + 1);

    PERCENTILE_DISC(.96)WITHINGROU
    ------------------------------
    2.99

  12. #12
    Join Date
    Dec 2003
    Location
    Noida, India.
    Posts
    171
    This is indeed a better solution. But does it not have the limitation of having units of price less than 1000 (or number of records on all_objects)???
    Oracle can do wonders !

  13. #13
    Join Date
    Jan 2004
    Location
    North Haven, CT
    Posts
    110
    Originally posted by cmasharma
    This is indeed a better solution. But does it not have the limitation of having units of price less than 1000 (or number of records on all_objects)???
    There are plenty ways to create a pivot view. select from all objects is the one with least coding. If you worry about number of rows, you can have something like

    select rownum from all_objects, all_objects.... but it will be slower, or you can try some more elegant ways.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •