Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Excel percentile in Oracle

725400Sep 14 2011 — edited Sep 14 2011
Hi
I am converting an excel solution with percentile calculation to an solution in our Oracle Datawarehouse.
I have some problem to convert the windowing excel function to an Oracle analytic function. I have tried the percentile_count and percent_rank functions, but they do not give the correct result and they seems not to be windowing.

Here is the a case:

From excel:
test_id test_value excel_percentile calculation of excel_percentile
1 1 1 =PERCENTILE(B$2:B2;0,95)
2 2 1.95 =PERCENTILE(B$2:B3;0,95)
3 3 2.9 =PERCENTILE(B$2:B4;0,95)
4 4 3.85 =PERCENTILE(B$2:B5;0,95)

Oracle testdata
CREATE TABLE percentile_test (
test_id number,
test_value number
);
INSERT INTO percentile_test VALUES (1,1);
INSERT INTO percentile_test VALUES (2,2);
INSERT INTO percentile_test VALUES (3,3);
INSERT INTO percentile_test VALUES (4,4);
COMMIT;

Any ideas ?

Best regards
Klaus
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Oct 12 2011
Added on Sep 14 2011
2 comments
1,266 views