Excel percentile in Oracle
725400Sep 14 2011 — edited Sep 14 2011Hi
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