MEDIAN
The MEDIAN function returns the median of a set of numbers. The MEDIAN function can run only on an accelerator server.
The schema is SYSIBM.
- numeric-expression
- An expression that returns a built-in numeric data type. numeric-expression must not include a scalar fullselect, or invoke a function that is non-deterministic or has an external action.
If the argument is DECFLOAT(n), the result of the function is DECFLOAT(34). Otherwise, the result of the function is a double precision floating-point number.
The function is applied to the set of values that are derived from the argument values by the elimination of null values.
The result can be null. If numeric-expression is null or if the function is applied to the empty set, the result is the null value.
Example 1: Calculate the median salary of the employees in department D11 from the EMPLOYEE table.
SELECT MEDIAN(SALARY)
FROM EMPLOYEE
WHERE WORKDEPT = ‘D11’;The result is 24680.00. Department D11 has 11 employees. The middle row of a group of 11 values is the sixth row. The result of MEDIAN over that group is the value of the sixth row, which is 24680.00.
Example 2: Calculate the median salary of the employees in department E21 from the EMPLOYEE table.
SELECT MEDIAN(SALARY)
FROM EMPLOYEE
WHERE WORKDEPT = ‘E21’;The result is 24605.00. Department E21 has six employees. Because there are an even number of rows, the MEDIAN is computed by interpolating a value between the middle two rows. The middle two rows are the third row with the value 23840.00 and the fourth row with the value 25370.00. MEDIAN is computed by averaging those two values, which is 24605.00.
When EXPLAIN is issued against an SQL statement that references the MEDIAN function, but the statement cannot be run on the accelerator server for any reason, the following values are populated in the PLAN_TABLE table:
- For a SELECT statement, column QBLOCK_TYPE would have the value 'PRUNED', and column ACCESSTYPE would have the value ' ' (blank).
- For an INSERT statement with a subselect, the PLAN_TABLE rows for the subselect would have column QBLOCK_TYPE with the value 'NCOSUB', and column ACCESSTYPE with the value ' ' (blank).
Also, when a view is referenced in an SQL statement that uses the MEDIAN function, the tables referred to in the view must be enabled for acceleration. Those tables referred to in the view do not need to be enabled for acceleration when the view is created.
