How to calculate Quantile 95 in Oracle SQL

Question

How to calculate Quantile 95 value, using SQL or PL/SQL?

Answer 1

With the SQL aggregate or analytic function PERCENTILE_DISC() (or perhaps PERCENTILE_CONT()). Google for your version of Oracle and the function name to find the Oracle documentation for these functions.

Here is how I can find the 95th percentile salary in the HR.EMPLOYEES table (HR is the standard HR schema that is installed with many Oracle databases):

select percentile_disc(0.95) within group (order by salary) as sal_95th_pctile
from   hr.employees
;

SAL_95TH_PCTILE
---------------
          13000

If instead I wanted to find the 95th percentile salary in each department, I would use the analytic version:

select percentile_disc(0.95) within group (order by salary) 
                             over (partition by department_id) as sal_95th_pctile
from   hr.employees
;

For the HR.EMPLOYEES table this makes little sense, since each department has only a few employees, so "95th percentile" is meaningless; but that's how you would do it when all "departments" had many values from which to compute the 95th percentile.