7.22.12 PERCENTILE_CONT

(1) Specification format

inverse-distribution-function-PERCENTILE_CONT ::= PERCENTILE_CONT(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_CONT.

   Table 7‒25: 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_CONT

   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_CONT
   1	INTEGER	DOUBLE PRECISION
   2	SMALLINT
   3	DECIMAL(m,n)
   4	DOUBLE PRECISION

4. PERCENTILE_CONT is calculated by linear interpolation with respect to an ordered set of values. Letting N be the number of input rows and P be the value of the specified argument, it first calculates the row number RN = {1 + P × (N - 1)}. Then, by linear interpolation between the values of the rows of row numbers CRN = CEIL(RN) and FRN = FLOOR(RN), the execution result of PERCENTILE_CONT is calculated. The result of the calculation is as follows:

   • When CRN=FRN=RN: the value of row RN

   • Otherwise: (CRN - RN) × (value of row FRN) + (RN - FRN) × (value of row CRN)

To Page Top

(4) Example

Example 1

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).

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

Example 2

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

SELECT PERCENTILE_CONT(0.5) WITHIN GROUP(ORDER BY "SALARY") AS "PERCENTILE_CONT"
    FROM "SALARYLIST"

To Page Top