7.22.11 MEDIAN

MEDIAN determines the median of an ordered set of values. The value might be linearly interpolated.

Note

MEDIAN is an inverse distribution function that gives the same result as specifying the median value (0.5) as the argument (percentile specification) to PERCENTILE_CONT.

Letting ARG1 be the aggregated argument to MEDIAN, MEDIAN is equivalent to the following PERCENTILE_CONT function.

PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY ARG1 ASC)

Organization of this subsection

(1) Specification format
(2) Explanation of specification format
(3) Rules
(4) Example

(1) Specification format

inverse-distribution-function-MEDIAN ::= MEDIAN(value-expression)

To Page Top

(2) Explanation of specification format

value-expression:: Specify the input values for obtaining the median in the form of a value expression. For details about value expressions, see 7.20 Value expression.

To Page Top

(3) Rules

Null values are not included in the calculation.
If the number of input rows is 0, the execution result will be a null value.

The following table shows the data type that can be specified in the value expression and the data type of the execution result of the inverse distribution function MEDIAN.

Table 7‒24: Data type that can be specified in the value expression and data type of the execution result of the inverse distribution function MEDIAN
No.	Data type that can be specified in the value expression	Data type of the execution result of the inverse distribution function MEDIAN
1	`INTEGER`	`DOUBLE` `PRECISION`
2	`SMALLINT`
3	`DECIMAL(m,n)`
4	`DOUBLE` `PRECISION`

MEDIAN is calculated by linear interpolation with respect to an ordered set of values. Letting N be the number of input rows, it first calculates the row number RN = {1 + 0.5 × (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 MEDIAN is calculated. The result of the calculation is as follows:
- When CRN=FRN=RN: 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",MEDIAN("SALARY") AS "MEDIAN"
    FROM "SALARYLIST"
    GROUP BY "POSITION"
    ORDER BY "POSITION"

Example of execution results

[Figure]

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 MEDIAN("SALARY") AS "MEDIAN"
    FROM "SALARYLIST"

[Figure]

To Page Top