7.22.13 PERCENTILE_DISC

(1) Specification format

inverse-distribution-function-PERCENTILE_DISC ::= PERCENTILE_DISC(value-specification) WITHIN-group-specification
 
  WITHIN-group-specification ::= WITHIN GROUP(ORDER BY sort-specification-list)

To Page Top

(2) Explanation of specification format

value-specification:

The value for which the percentile is to be determined, expressed in the form of a value specification. For details about value specifications, see 7.21 Value specification.

The following rules apply:

• The specified value must be between 0 and 1 (data type INTEGER, SMALLINT, or DECIMAL).

• If the null value is specified, the execution result will be a null value.

• If a dynamic parameter is specified by itself, the assumed data type of the dynamic parameter is DECIMAL(3,2).

WITHIN-group-specification:

WITHIN-group-specification ::= WITHIN GROUP(ORDER BY sort-specification-list)

The WITHIN group specification specifies the data for which the percentile is to be determined and the order of the data. In sort-specification-list, specify the data for which the percentile is to be determined as the sort key, and the ordering of the data (ascending or descending) as the order specification. For details about sort specification lists, see 7.24 Sort specification list.

The following rules apply:

• Specification of the null-value sort order is not permitted in the sort specification list in the WITHIN group specification.

• No more than one sort specification is permitted in the sort specification list in the WITHIN group specification.

To Page Top

(3) Rules

1. Null values are not included in the calculation.

2. If the number of input rows is 0, the execution result will be a null value.

3. The following table shows the data types that can be specified in the sort key of the sort specification list and the data type of the execution result of the inverse distribution function PERCENTILE_DISC.

   Table 7‒26: Data types that can be specified in the sort key of the sort specification list and data type of the execution result of the inverse distribution function PERCENTILE_DISC

   No.	Data types that can be specified in the sort key of the sort specification list	Data type of the execution result of the inverse distribution function PERCENTILE_DISC
   1	INTEGER	INTEGER
   2	SMALLINT	SMALLINT
   3	DECIMAL(m,n)	DECIMAL(m,n)
   4	DOUBLE PRECISION	DOUBLE PRECISION
   5	CHAR(n)	CHAR(n)
   6	VARCHAR(n)	VARCHAR(n)
   7	DATE	DATE
   8	TIME(p)	TIME(p)
   9	TIMESTAMP(p)	TIMESTAMP(p)

4. PERCENTILE_DISC returns a result from an ordered set of values. If P is the value of the specified argument, it sorts the values in the value expression specified in the sort specification list, and then, from among those values, returns the value that is greater than or equal to P with the smallest CUME_DIST value with respect to the same sort specification list.

To Page Top

(4) Example

Example

Using the data in the salary table (SALARYLIST), this example determines the median value (50^th percentile) of the employee salaries (SALARY) by job class (POSITION).

Both PERCENTILE_CONT and PERCENTILE_DISC can be used to determine the median value.

SELECT "POSITION",
        PERCENTILE_CONT(0.5) WITHIN GROUP(ORDER BY "SALARY" ASC) AS "PERCENTILE_CONT",
        PERCENTILE_DISC(0.5) WITHIN GROUP(ORDER BY "SALARY" ASC) AS "PERCENTILE_DISC"
    FROM "SALARYLIST"
    GROUP BY "POSITION"
    ORDER BY "POSITION"

As shown above, the results of PERCENTILE_CONT and PERCENTILE_DISC can differ. This is because PERCENTILE_CONT returns results that are linearly interpolated, while PERCENTILE_DISC returns results only from the set of values upon which calculations are being performed.

To Page Top