)*20=18

percentile_disc()

PERCENTILE_DISC

这里就不做详细分析。

Syntax

Purpose

PERCENTILE_DISC is an inverse distribution function that assumes a discrete distribution model. It takes a percentile value and a sort specification and returns an element from the set. Nulls are ignored in the calculation.

This function takes as an argument any numeric data type or any nonnumeric data type that can be implicitly converted to a numeric data type. The function returns the same data type as the numeric data type of the argument.

The first expr must evaluate to a numeric value between 0 and 1, because it is a percentile value. This expression must be constant within each aggregate group. The ORDER BY clause takes a single expression that can be of any type that can be sorted.

For a given percentile value P, PERCENTILE_DISC sorts the values of the expression in the ORDER BY clause and returns the value with the smallest CUME_DIST value (with respect to the same sort specification) that is greater than or equal to P.

Aggregate Example

See aggregate example for .

Analytic Example

The following example calculates the median discrete percentile of the salary of each employee in the sample table hr.employees:

SELECT last_name, salary, department_id,

PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY salary DESC)

OVER (PARTITION BY department_id) "Percentile_Disc",

CUME_DIST() OVER (PARTITION BY department_id

ORDER BY salary DESC) "Cume_Dist"

FROM employees

WHERE department_id in (30, 60)

ORDER BY last_name, salary, department_id;

LAST_NAME SALARY DEPARTMENT_ID Percentile_Disc Cume_Dist

------------------------- ---------- ------------- --------------- ----------

Austin 4800 60 4800 .8

Baida 2900 30 2900 .5

Colmenares 2500 30 2900 1

Ernst 6000 60 4800 .4

Himuro 2600 30 2900 .833333333

Hunold 9000 60 4800 .2

Khoo 3100 30 2900 .333333333

Lorentz 4200 60 4800 1

Pataballa 4800 60 4800 .8

Raphaely 11000 30 2900 .166666667

Tobias 2800 30 2900 .666666667

The median value for Department 30 is 2900, which is the value whose corresponding percentile (Cume_Dist) is the smallest value greater than or equal to 0.5. The median value for Department 60 is 4800, which is the value whose corresponding percentile is the smallest value greater than or equal to 0.5.

percentile_disc:percentile_disc函数在功能上类似于percentile_cont函数，只是percentile_cont函数使用了连续分布模型，而percentile_disc函数使用了离期 分布模型。当没有值与指定的percent_rank精确匹配的时候，percentile_cont(0.5)会计算两个离得最近的值的平均值。相反，在升序排列的情况下，percentile_disc函数只取比所传递的参数percent_rank值更大的值。在降序排列的时候，percentile_disc函数只取比所传递的参数percent_rank值更小的值。