topn()

Get the top N most common values from a space-saving aggregate

Since 1.16.0

Get the top N most common values from a space-saving aggregate. The space-saving aggregate can be created from either freq_agg or mcv_agg.

Samples

Get the 20 most frequent zip_codes from an employees table:

SELECT topn(mcv_agg(20, zip_code)) FROM employees;

Arguments

The syntax is:

topn (
    agg SpaceSavingAggregate,
    n INTEGER
) RETURNS SETOF AnyElement

Name	Type	Default	Required	Description
agg	SpaceSavingAggregate	-	✔	A space-saving aggregate created using either freq_agg or mcv_agg
n	INTEGER	-	✔	The number of values to return. Required only for frequency aggregates. For top N aggregates, defaults to target N of the aggregate itself, and requests for a higher N return an error. In some cases, the function might return fewer than N values. This might happen if a frequency aggregate doesn’t contain N values above the minimum frequency, or if the data isn’t skewed enough to support N values from a top N aggregate.

Returns

Column	Type	Description
topn	SETOF AnyElement	The N most-frequent values in the aggregate