Skip to content

mcv_agg()

Aggregate data into a space-saving aggregate for further calculation of most-frequent values

Since 1.16.0

Aggregate data into a space-saving aggregate, which stores frequency information in an intermediate form. You can then use any of the accessors in this group to return estimated frequencies or the most common elements.

This differs from freq_agg in that you can specify a target number of values for topn() to return, rather than a frequency cutoff.

Create a topN aggregate over the country column of the users table. Targets the top 10 most-frequent values:

SELECT mcv_agg(10, country) FROM users;

Create a topN aggregate over the type column of the devices table. Estimates the skew of the data to be 1.05, and targets the 5 most-frequent values:

SELECT mcv_agg(5, 1.05, type) FROM devices;

The syntax is:

mcv_agg (
count INTEGER,
[skew DOUBLE PRECISION,]
value BIGINT | TEXT
) RETURNS SpaceSavingAggregate
NameTypeDefaultRequiredDescription
countINTEGER-✔The target number of most-frequent values. Must be greater than 0.
valueBIGINT | TEXT-✔The column to store frequencies for
skewDOUBLE PRECISION1.1The estimated skew of the data, defined as the s parameter of a zeta distribution. Must be greater than 1.0. Defaults to 1.1. For more information, see the section on skew.
ColumnTypeDescription
mcv_aggSpaceSavingAggregateAn object storing the most common elements of the given table and their estimated frequency. The exact type is SpaceSavingBigIntAggregate or SpaceSavingTextAggregate depending on the input type. You can pass this object to any of the accessor functions to get a final result.