Returns the middle value in a given column. If number of values are even, MEDIAN returns the average of the two middle values.
Syntax
MEDIAN(<value>) [FILTER ([WHERE] <condition>)]
Parameters
| Parameter | Description | Supported input types |
|---|
<value> | The expression used to calculate the median value. | DOUBLE PRECISION, REAL, BIGINT, INT |
<condition> | An optional boolean expression to filter rows used in aggregation | BOOL |
Return Type
MEDIAN returns a value of type DOUBLE PRECISION.
- This function ignores
NULL values.
- If the input is empty, the function returns
NULL.
Examples
The following example calculates the median of three values. Since the number of elements is odd, the middle element is returned directly:
SELECT MEDIAN(a) AS result FROM UNNEST(array[1,2,5]) AS a;
Rows: 1Execution time: 6.04ms
NULL elements is even, MEDIAN returns the average of the two middle values. NULL values are ignored before the median is calculated:
SELECT MEDIAN(a) AS result FROM UNNEST(array[100, NULL, 1, 2, 5]) AS a;
Rows: 1Execution time: 6.46ms
NULL, the non-null values are [1, 2, 5, 100]. The two middle elements are 2 and 5, so the result is calculated as: ((2+5)/2). 
