MEDIAN is an inverse distribution function. In contrast to the average function, the median function returns the middle value or an interpolated value from a sorted window or group of rows. NULL values are ignored.
The following formula is evaluated:
- MEDIAN(expr) is an alias for PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY expr).
- For inverse distribution functions, refer to PERCENTILE_CONT and PERCENTILE_DISC.
- MEDIAN does not allow the use of a window_frame_clause. It uses the entire partition as window for the computation of the result.
Specifying ALL has no effect since it is already used by default.
- The over_clause must not contain an order_clause.
- For information about the over_clause and analytic functions in general, refer to the Analytic Functions section.
id, department, age,
MEDIAN(age) OVER (PARTITION BY department) MEDIAN_AGE
FROM employee_table ORDER BY department;