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

SELECT 
    department, 
   MEDIAN(age) MEDIAN_AGE 
FROM employee_table GROUP BY department;
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