PERCENTILE_CONT
Returns a percentile, the value below which the given share of the group values lies; between neighboring values it computes an intermediate one.
PostgreSQL 17.5
PERCENTILE_CONT(fraction) WITHIN GROUP (ORDER BY expression)Parameters
- fractiondouble precision
- Share from 0 to 1, for example 0.5 for the median; an array of shares is allowed
- expressiondouble precision | interval
- Numeric expression or interval by which the values are ordered
Return value
A number (double precision) or an interval; an array if fraction is an array; NULL for an empty set
Examples
Median and average price
PostgreSQL 17.5
SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (
ORDER BY unit_price
) AS median,
AVG(unit_price) AS average
FROM Payments;Median price for each family member
PostgreSQL 17.5
SELECT family_member,
PERCENTILE_CONT(0.5) WITHIN GROUP (
ORDER BY unit_price
) AS median_price
FROM Payments
GROUP BY family_member
ORDER BY family_member;Quartiles in one call
PostgreSQL 17.5
SELECT PERCENTILE_CONT(ARRAY [0.25, 0.5, 0.75]) WITHIN GROUP (
ORDER BY unit_price
) AS quartiles
FROM Payments;Details
The function has a special syntax: the expression goes not in the parentheses but in WITHIN GROUP (ORDER BY ...). NULL values are ignored.
If the position falls between two values, the result is computed between them: for 1, 2, 3 and 10 the median is 2.5. To get one of the existing values, use PERCENTILE_DISC. Unlike AVG, the median hardly depends on rare very large values.