Purpose of the Oracle MEDIAN Function
The MEDIAN function returns the median of the set of provided values.
The MEDIAN is the middle value in a set of values.
So, for example, you had these five values:
10, 15, 18, 23, 40.
The median of these would be the middle value after they have been ordered in ascending order. So, the median would be 18.
I’ve written an article on finding the median, mean, and mode in Oracle if you’re looking for different ways of finding a mid-point.
The syntax of the MEDIAN function is:
MEDIAN ( expr ) OVER (query_partition_clause).
The parameters of the MEDIAN function are:
- expr (mandatory): The expression to calculate a median for. This can be a set of numbers, or a column.
- query_partition_clause (optional): The clause that is used to partition the data when using MEDIAN as an analytic query.
The expr value can be any numeric data type. The MEDIAN function returns the same data type as the expr value.
If you specify the OVER clause, Oracle will work out the data type with the highest precedence and return that type.
Examples of the MEDIAN Function
Here are some examples of the MEDIAN function. I find that examples are the best way for me to learn about code, even with the explanation above.
Example 1 – aggregate
This example finds the median using it as an aggregate function.
SELECT MEDIAN(fees_required) AS median_fees_req FROM student;
It shows the value of 300 because this is the middle value in the column after it has been ordered.
Example 2 – analytic
This example uses the MEDIAN function as an analytic function
SELECT first_name, last_name, address_state, fees_required, MEDIAN(fees_required) OVER (PARTITION BY address_state) AS median_fees_req FROM student;
This example shows the median fees_required when grouped by the home_state for each record in the table.
Some functions which are similar to the MEDIAN function are:
- AVG: Returns the average of the set of values.
- PERCENTILE_CONT: This function finds the value for a specified percentile. If you specify the percentile as 0.5, it should return the median.
If you want to know more about SQL functions, you can find a full list of Oracle SQL functions here.
Lastly, if you enjoy the information and career advice I’ve been providing, sign up to my newsletter below to stay up-to-date on my articles. You’ll also receive a fantastic bonus. Thanks!