MEDIAN

Syntax

MEDIAN
(
  expression  IN { smallint, int, bigint,
                    real, double precision,
                    timestamp, timestamptz, time, timetz, date }
)
RETURNS median;

Overview

The MEDIAN aggregate function is a statistical function that calculates and returns the median of the values in a group. It is inspired by Oracle's MEDIAN function and implemented in PostgreSQL.

It is available for integer, real, and date/time data types. Internally, the MEDIAN function works in two steps.

Aggregation step

• This step collects the values in the group into a type-correct vector (list data structure).

• NULL values are ignored in the aggregation.

Final calculation step

• When all the values in the group have been collected, the median value is finally calculated.

• First, we sort the values in the aggregated vector with a quick sort algorithm.

• Returns the median value if the number of aggregated values is odd.

• If the number of aggregated values is even, compute and return the average of the two values in the center. For date and time-related data with time zones, calculate the average value after timezoning the two center values to UTC.

Calculate the average value in UTC and adopt the timezone of the larger of the two center values.

Parameters

Parameter	Description
expression	The value to be aggregated, which supports the following data types. Integer types: smallint, int, bigint

• Real number type: real(float4), double precision

• Date/Time types: timestamp, timestamptz, time, timetz, date NULL values are ignored when aggregating, and values are stored in internal vectors for each type.

Caution

• If an even number of values are aggregated, the average of the two values in the center is calculated and returned.

• Internally, the C code uses the qsort function to sort the values.

• For date and time related data, additional timezone and type conversion processing is performed according to Oracle's processing logic.

Example

# Test table
create table employees (name varchar, salary integer, hire_date timestamptz);
INSERT INTO employees (name, salary, hire_date) VALUES
('John Doe', 55000, '2020-03-15 09:00:00'),
('Jane Smith', 62000, '2019-07-22 10:30:00'),
('Michael Johnson', 72000, '2018-11-10 08:45:00'),
('Emily Davis', 48000, '2021-05-01 12:00:00'),
('David Wilson', 53000, '2017-09-17 14:20:00'),
('Sarah Brown', 60000, '2016-12-05 09:15:00'),
('James Taylor', 75000, '2015-06-30 16:45:00'),
('Jessica Martinez', 68000, '2022-01-25 11:10:00'),
('Daniel Anderson', 58000, '2020-10-05 13:35:00'),
('Laura Thomas', 49500, '2023-08-12 08:00:00');


-- Example of calculating the median for integer data
SELECT oracle.MEDIAN(salary) FROM employees;

 median 
--------
  59000
(1 row)


-- Example of calculating the median for datatype data
SELECT oracle.MEDIAN(hire_date) FROM employees;

         median         
------------------------
 2019-11-17 21:45:00+09