|Oracle® Database SQL Reference
10g Release 1 (10.1)
Part Number B10759-01
See Also:"Analytic Functions " for information on syntax, semantics, and restrictions
MEDIAN is an inverse distribution function that assumes a continuous distribution model. It takes a numeric or datetime value and returns the middle value or an interpolated value that would be the middle value once the values are sorted. Nulls are ignored in the calculation.
This function takes as arguments any numeric datatype or any nonnumeric datatype that can be implicitly converted to a numeric datatype. If you specify only
expr, then the function returns the same datatype as the numeric datatype of the argument. if you specify the
OVER clause, then Oracle Database determines the argument with the highest numeric precedence, implicitly converts the remaining arguments to that datatype, and returns that datatype.
The result of
MEDIAN is computed by first ordering the rows. Using
N as the number of rows in the group, Oracle calculates the row number (
RN) of interest with the formula
RN = (
1 + (0.
1)). The final result of the aggregate function is computed by linear interpolation between the values from rows at row numbers
The final result will be:
if (CRN = FRN = RN) then (value of expression from row at RN) else (CRN - RN) * (value of expression for row at FRN) + (RN - FRN) * (value of expression for row at CRN)
You can use
MEDIAN as an analytic function. You can specify only the
query_partition_clause in its
OVER clause. It returns, for each row, the value that would fall in the middle among a set of values within each partition.
Compare this function with these functions:
PERCENTILE_CONT , which returns, for a given percentile, the value that corresponds to that percentile by way of interpolation.
MEDIAN is the specific case of
PERCENTILE_CONT where the percentile value defaults to 0.5.
PERCENTILE_DISC , which is useful for finding values for a given percentile without interpolation.
The following query returns the median salary for each department in the
SELECT department_id, MEDIAN(salary) FROM employees GROUP BY department_id; DEPARTMENT_ID MEDIAN(SALARY) ------------- -------------- 10 4400 20 9500 30 2850 40 6500 50 3100 60 4800 70 10000 80 8900 90 17000 100 8000 110 10150 7000