PERCENTILECONT

PERCENTILECONT refers to the PostgreSQL percentile_cont function, a window and aggregate function used to compute a percentile for a set of values by linearly interpolating between ordered values. It provides a continuous percentile, in contrast to discrete percentiles returned by certain other functions.

The typical usage is percentile_cont(percentile) WITHIN GROUP (ORDER BY sort_expression), where percentile is a number between 0 and 1 inclusive and sort_expression determines the data to be ranked. The function can also be used as a window function with OVER, for example across partitions or within partitions of a result set.

Behavior and return type: The percentile parameter must lie in the range [0, 1]. NULL values are ignored in the calculation. If there are no non-null input values, the result is NULL. The function returns a value whose data type matches the sort_expression; for numeric inputs it yields a numeric result, and for other types (such as timestamps) it can interpolate to produce a value of the same type.

How it works: percentile_cont computes the percentile by linearly interpolating between adjacent values in the ordered input. If the exact percentile corresponds to a data point, that point is returned; otherwise, a weighted interpolation between the two surrounding values is performed to produce the result.

Examples:

- Median salary: SELECT percentile_cont(0.5) WITHIN GROUP (ORDER BY salary) FROM employees;

- 90th percentile by department: SELECT dept, percentile_cont(0.9) WITHIN GROUP (ORDER BY salary) OVER (PARTITION BY dept) AS p90_salary FROM employees;

See also: percentile_disc, which computes discrete percentiles without interpolation, and other standard SQL percentile functions.