What is the mathematical way of calculating PERCENTILE_DISC() in oracle

Question

I have a series of numbers as follows: 2, 3, 5, 1, 9, 10, 20, 34, 77, 55, 11, 13, 56, 99 I write a query to calculate PERCENTILE of 25th as follows:

SELECT
  PERCENTILE_DISC(0.25) WITHIN GROUP (ORDER BY VALUE_DATA) AS percent_disc,
  PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY VALUE_DATA) AS percent_cont
FROM
  tmp_percentile

The result is: PERCENTILE_CONT(0.25) = 6 and PERCENTILE_DISC(0.25) = 5. I learned how to calculate PERCENTILE_CONT through this link: https://docs.oracle.com/cd/B13789_01/server.101/b10759/functions100.htm#i1000909. But I still haven't found the answer for PERCENTILE_DISC. Why PERCENTILE_DISC(0.25) = 5

Answer 1

PERCENTILE_DISC returns an actual value from your data, whereas PERCENTILE_CONT can return a value that does not exist in your data. e.g.

The PERCENTILE_DISC(0.25) function will sort your numbers in ascending order and then select the value at the 25th percentile.

• Order the numbers: 1, 2, 3, 5, 9, 10, 11, 13, 20, 34, 55, 56, 77, 99
• Find the position that corresponds to the 25th percentile. Position (P) is calculated as: P = N * percentile, where N is the total number of items in the data (i.e. there are 14 items), so, P = 14 * 0.25 = 3.5.
• Since 3.5 is not a whole number, the PERCENTILE_DISC function rounds to the nearest integer (4 in this case) returning the data value at that position. Hence, the result is 5.

see: https://docs.oracle.com/en/database/oracle/oracle-database/23/sqlrf/PERCENTILE_DISC.html#GUID-7C34FDDA-C241-474F-8C5C-50CC0182E005