Ordered-set aggregate functions supported by Hologres - Hologres

Hologres is compatible with PostgreSQL and supports standard PostgreSQL syntax for data development. The ordered-set aggregate functions available in Hologres are a subset of those in PostgreSQL.

These functions use the WITHIN GROUP (ORDER BY ...) syntax, where the aggregated input is sorted before the function is applied.

When percentile_cont or percentile_disc is used in a WITHIN GROUP (ORDER BY col) clause, the clause only supports fields of the FLOAT4 or FLOAT8 type. Cast the column explicitly: WITHIN GROUP (ORDER BY col::float8).

Supported functions

Function	Description	Direct argument type	Aggregated argument type	Return type
`mode() WITHIN GROUP (ORDER BY sort_expression)`	Returns the most frequent input value. If multiple values are equally frequent, returns the first one.	None	A sortable type	Same as the sort expression
`percentile_cont(fraction) WITHIN GROUP (ORDER BY sort_expression)`	Returns a value corresponding to the specified fraction in the ordering. If the exact percentile falls between two values, the function interpolates between them.	`double precision`	`double precision` or `interval`	Same as the sort expression
`percentile_cont(fractions) WITHIN GROUP (ORDER BY sort_expression)`	Returns an array of results matching the shape of the `fractions` parameter. Each non-null element is replaced by the value at the corresponding percentile, with interpolation applied as needed.	`double precision[]`	`double precision` or `interval`	Array of the same type as the sort expression
`percentile_disc(fraction) WITHIN GROUP (ORDER BY sort_expression)`	Returns the first input value whose position in the ordering equals or exceeds the specified percentile. Unlike `percentile_cont`, this function always returns an actual value from the input — no interpolation.	`double precision`	A sortable type	Same as the sort expression
`percentile_disc(fractions) WITHIN GROUP (ORDER BY sort_expression)`	Returns an array of results matching the shape of the `fractions` parameter. Each non-null element is replaced by the input value at the corresponding percentile, with no interpolation.	`double precision[]`	A sortable type	Array of the same type as the sort expression

Examples

mode()

SELECT mode() WITHIN GROUP (ORDER BY user_id) FROM testtable;
-- Result: 293890

percentile_cont(fraction) — returns a single continuous percentile, interpolating if needed

SELECT percentile_cont(0.5) WITHIN GROUP (ORDER BY cust_id) FROM testtable;
-- Result: 1105639996.5

percentile_cont(fractions) — returns an array of continuous percentiles

SELECT percentile_cont(0.5) WITHIN GROUP (ORDER BY member_id) FROM testtable;
-- Result: 96727903.5

percentile_disc(fraction) — returns a single discrete percentile (actual value, no interpolation)

SELECT percentile_disc(0.6) WITHIN GROUP (ORDER BY (impression_id) FROM testtable;
-- Result: 0.0

percentile_disc(fractions) — returns an array of discrete percentiles

SELECT percentile_disc(0.6) WITHIN GROUP (ORDER BY () FROM testtable;
-- Result: 0.0

Usage notes

Choosing between percentile_cont and percentile_disc

percentile_cont interpolates between the two nearest values, while percentile_disc returns the nearest actual value from the input without interpolation.

Use percentile_cont when a smoothed estimate is acceptable, such as for statistical analysis.
Use percentile_disc when the result must be a value that exists in the dataset, such as for reporting or ranking.

Type casting requirement

When using percentile_cont or percentile_disc with WITHIN GROUP (ORDER BY col), the ORDER BY column must be of type FLOAT4 or FLOAT8. If the column is a different numeric type, cast it explicitly:

SELECT percentile_cont(0.5) WITHIN GROUP (ORDER BY col::float8) FROM testtable;