APPROXIMATE_PERCENTILE [Aggregate]

Computes the approximate percentile of an expression over a group of rows. This function returns a FLOAT value. Nulls are ignored.

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

Behavior Type

Immutable

Syntax

APPROXIMATE_PERCENTILE ( expression USING PARAMETERS percentile = number )

Parameters

expression

Any FLOAT or INTEGER data type. Null values are ignored.

number

Percentile value, which must be a FLOAT constant ranging from 0 to 1 (inclusive).

 

Examples

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

The following example 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 percentile for sales in each state: 

=> SELECT state, APPROXIMATE_PERCENTILE(sales USING PARAMETERS percentile=0.5) AS median FROM allsales GROUP BY state;
 state | median
-------+--------
 MA    |     35
 NY    |     20
(2 rows)

See Also