MEDIAN
Purpose
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:
Syntax
median::=
Usage Notes
- 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.
Examples
Aggregate Function
Result
DEPARTMENT | MEDIAN_AGE |
ACCOUNTS | 32 |
HR | 32 |
Analytic Function
SELECT
id, department, age,
MEDIAN(age) OVER (PARTITION BY department) MEDIAN_AGE
FROM employee_table ORDER BY department;
Result
ID | DEPARTMENT | AGE | MEDIAN_AGE |
2001 | ACCOUNTS | 30 | 32 |
2002 | ACCOUNTS | 32 | 32 |
2003 | ACCOUNTS | 27 | 32 |
2004 | ACCOUNTS | 42 | 32 |
2005 | ACCOUNTS | 42 | 32 |
1001 | HR | 30 | 32 |
1002 | HR | 34 | 32 |
1003 | HR | 40 | 32 |
1004 | HR | 30 | 32 |