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.
Behavior Type
Syntax
APPROXIMATE_MEDIAN ( expression )
Parameters
expression |
Any |
Examples
Tip: 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)