Skip to Main Content
  • Questions

  • Customized version of PERCENTILE_CONT aggregated function

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Antonio.

Asked: September 02, 2015 - 7:21 am UTC

Last updated: September 18, 2015 - 3:24 am UTC

Version: 11.2g

Viewed 1000+ times

You Asked

Hi,

I would like to implement, in pl/sql, a customized version of PERCENTILE_CONT aggregated function. The following code snippet shows the definition of percentile that I would like to use:

/**
Percentile function

vec: array of profit&loss
cl : confidence level (e.g. 97%)
**/
FUNCTION percentile(vec IN t_collection, cl NUMBER) RETURN NUMBER IS
n NUMBER; -- array length
num NUMBER; -- number corresponding to quantile qd
qd NUMBER; -- quantile given as input
k NUMBER; -- 1 <= k <= vec.length
pth NUMBER; -- k-th percentile
q1 NUMBER;
q2 NUMBER;
BEGIN
n := vec.count; -- number of elements within array of p&l
qd := 100 - cl; -- input quantile related to confidence interval
num := (n * qd) / 100 + 0.5; -- q(i) = [(i - 0.5) / n] * 100, therefore i = (n * qd) / 100 + 0.5
k := trunc(num,0); -- k-th integer index element corresponding to k-th quantile

IF qd < (50/n) THEN -- if qd is lower than first quantile
pth := vec(vec.first);
ELSIF qd > (100 - 50/n) THEN -- if qd is greater than last quantile
pth := vec(vec.last);
ELSE -- if qd is between first and last quantile values
BEGIN
q1 := 100 * (k - 0.5) / n; -- q(k)
q2 := 100 * (k + 0.5) / n; -- q(k+1)
pth := (((q2 - qd) / (100 / n)) * vec(k) + ((qd - q1) / (100 / n)) * vec(k+1)) * (-1);
END;
END IF;

RETURN pth;

EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20001, 'An error was encountered - ' || SQLCODE || ' -ERROR- ' ||SQLERRM);
END;

I haven't be able to find any source code of Oracle PERCENTILE_CONT to analyze it. Could you please let me know how to implement such customized PERCENTILE_CONT function based on function I've provided?

Thanks in advance.
Tony.

and Chris said...

Oracle doesn't publish its source code, so you're not going to find anything to tell you about it there.

The docs provide the following definition for percentile_cont:

http://docs.oracle.com/database/121/SQLRF/functions141.htm#SQLRF00687 


The result of PERCENTILE_CONT is computed by linear interpolation between values after ordering them. Using the percentile value (P) and the number of rows (N) in the aggregation group, you can compute the row number you are interested in after ordering the rows with respect to the sort specification. This row number (RN) is computed according to the formula RN = (1+(P*(N-1)). The final result of the aggregate function is computed by linear interpolation between the values from rows at row numbers CRN = CEILING(RN) and FRN = FLOOR(RN).

The final result will be:

  If (CRN = FRN = RN) then the result is
    (value of expression from row at RN)
  Otherwise the result is
    (CRN - RN) * (value of expression for row at FRN) +
    (RN - FRN) * (value of expression for row at CRN)


Why are you trying to implement your own version?

For us to provide a description of how to do this, please supply:

* An explanation of why the existing percentile_cont doesn't provide the results you want
* A description of how your modified version should work with sample inputs and outputs

Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Customized version of PERCENTILE_CONT

Antonio Savoldi, September 02, 2015 - 9:19 am UTC

>> An explanation of why the existing percentile_cont doesn't provide the results you want

The existing version of PERCENTILE_CONT cannot be used for calculating VaR (Value at Risk), as required by PRISMA margining methodology. As specified in the documentation of the methodology, provided by EUREX (European Derivative Market), the Var figures are quantiles of profit and loss distributions.


The profit and loss distribution can be mathematically expressed as a vector, in which each element represents the profit and loss calculated froma given set of scenario pricesand current prices. Each component of the profit and loss vector being sorted in ascending order represents a specific quantile of the empirical distribution. The quantile qi represented by the i-th component of the sorted profit and loss vector can be calculated according to qi = 100%(i - 0.5) / L where L denotes the length ofthe profit and loss vector.

In case the (100% − cl) quantile is exactly represented by a single component of the subsample profit and loss vector, this component will be the VaR figure.

In case the (100% − cl) quantile lies between two quantiles of the profit and loss distribution, it will be interpolated linearly using the next highest(q+) and next lowest quantile(q-) of the profit and loss distributionand the associated components of the profit and loss vector,denotesas PL(q+) qnd PL(q-) accordingto the formula:

VaR(cl) = {[(q+ - (100% - cl)) / (q+ - q-)] * PL(q-) + [((100% - cl) - q-) / (q+ - q-)] * PL(q+)} * (-1)

In case (100 − cl) is smaller than the quantile associated with the first component, the VaR will be given as thefirst component. In case it is bigger than the quantile associated with the last component, the VaR will be given asthe last component.

A positive VaR figure indicates a loss. The VaR figures utilized for the calculation of the initial margin will always be bounded below by zero. Every negative VaR, representing again, will be set to zero.

Example

i PL Components Quantile qi
1 -4000 100% * (1 - 0.5) = 10%
2 -3800 30%
3 -3700 50%
4 -3500 70%
5 -3200 90%

For instance VaR(10%) = -4000 whereas Var(80%) = 3900 (by appling the above formula). Oracle, with the built-in PERCENTILE_CONT, provides 3840.



Chris Saxon
September 18, 2015 - 3:23 am UTC

Thanks for the clarification

Aggregate Functions

David, September 02, 2015 - 1:04 pm UTC

Hi Antonio,

You can review the documentation on aggregate functions here:
http://docs.oracle.com/cd/E11882_01/appdev.112/e10765/aggr_functions.htm#ADDCI4623

A different approach might be to use the function you wrote in conjunction with COLLECT. Something like
SELECT t.column1
      ,percentile(CAST(COLLECT(t.column2) AS t_collection), 97) percentile
FROM my_table t
GROUP  BY t.column1;


quantile rebasement

Antonio Savoldi, September 02, 2015 - 2:05 pm UTC

There's no reason to re-do the wheel, at least in this case. I sorted out that I can rebase the quantile and convert it to row-numbwer, by obtaining, as a result, the confidence level that I need for Oracle PERCENTILE_COUNT function.

For instance:
RN = 1 + P(N - 1)

but
RN = (N x qd) / 100 + 0.5

therefore
P = (N x qd / 100 - 0.5) / (N - 1)

if qd = 3, N = 188, P will be equal to 0,0274866, which is exactly the confidence level that placed in the function gives the same results of the other function (based on quantile).

Thanks again,
Regards.

Tony
Chris Saxon
September 02, 2015 - 4:57 pm UTC

Great to hear! I (Chris) am not familiar with these calculations so was struggling to figure out what exactly to do here.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.