APPROXIMATE_MEDIAN [Aggregate]

Computes the approximate median of an expression over a group of rows. The function returns a FLOAT value.

APPROXIMATE_MEDIAN is an alias of APPROXIMATE_PERCENTILE [Aggregate] with a parameter of 0.5.

Note: This function is best suited for large groups of data. If you have a small group of data, use the exact MEDIAN [Analytic] function.

Behavior Type

Immutable

Syntax

APPROXIMATE_MEDIAN ( expression )

Parameters

expression

Any FLOAT or INTEGER data type. The function returns the approximate middle value or an interpolated value that would be the approximate middle value once the values are sorted. Null values are ignored in the calculation.

Examples

For optimal performance when using GROUP BY in your query, verify that your table is sorted on the GROUP BY column.

The following examples uses this table:

CREATE TABLE allsales(state VARCHAR(20), name VARCHAR(20), sales INT) ORDER BY state;
INSERT INTO allsales VALUES('MA', 'A', 60);
INSERT INTO allsales VALUES('NY', 'B', 20);
INSERT INTO allsales VALUES('NY', 'C', 15);
INSERT INTO allsales VALUES('MA', 'D', 20);
INSERT INTO allsales VALUES('MA', 'E', 50);
INSERT INTO allsales VALUES('NY', 'F', 40);
INSERT INTO allsales VALUES('MA', 'G', 10);
COMMIT;

Calculate the approximate median of all sales in this table: 

=> SELECT APPROXIMATE_MEDIAN (sales) FROM allsales; 
APROXIMATE_MEDIAN
--------------------
                 20
(1 row)

Modify the query to group sales by state, and obtain the approximate median for each one: 

=> SELECT state, APPROXIMATE_MEDIAN(sales) FROM allsales GROUP BY state;
 state | APPROXIMATE_MEDIAN
-------+--------------------
 MA    |                 35
 NY    |                 20
(2 rows)

See Also