freq_agg() and mcv_agg() functions

freq_agg(
  min_freq DOUBLE PRECISION,
  value AnyElement
) RETURNS SpaceSavingAggregate

Aggregate data into a space-saving aggregate object, 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.

Examples

Create a space-saving aggregate over a field ZIP in a HomeSales table. This aggregate tracks any ZIP value that occurs in at least 5% of rows:

SELECT toolkit_experimental.freq_agg(0.05, ZIP) FROM HomeSales;

Copy

mcv_agg (
    n INTEGER,
    value AnyElement
    [, skew DOUBLE PRECISION]
) RETURNS SpaceSavingAggregate

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 to keep, rather than a frequency cutoff.

Examples

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;

Copy

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;

Copy

into_values(
    agg SpaceSavingAggregate
) RETURNS (AnyElement, DOUBLE PRECISION, DOUBLE PRECISION)

Returns the data from a space-saving aggregate as a table. The table lists the stored values with the minimum and maximum bounds for their estimated frequencies.

max_frequency (
    agg SpaceSavingAggregate,
    value AnyElement
) RETURNS DOUBLE PRECISION

Get the maximum bound of the estimated frequency for a given value in a space-saving aggregate

Examples

Find the maximum frequency of the value 3 in a column named value within the table value_test:

SELECT max_frequency(
    (SELECT mcv_agg(20, value) FROM value_test),
    3
);

Copy

min_frequency (
    agg SpaceSavingAggregate,
    value AnyElement
) RETURNS DOUBLE PRECISION

Get the minimum bound of the estimated frequency for a given value in a space-saving aggregate

Examples

Find the minimum frequency of the value 3 in a column named value within the table value_test:

SELECT min_frequency(
    (SELECT mcv_agg(20, value) FROM value_test),
    3
);

Copy

topn (
    agg SpaceSavingAggregate,
    n INTEGER
) RETURNS AnyElement

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.

Examples

Get the 20 most frequent zip_codes from an employees table:

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

Copy

rollup(
    agg SpaceSavingAggregate
) RETURNS SpaceSavingAggregate

This will combine multiple aggregates created with freq_agg or mcv_agg functions. This function does require that the source aggregates have been created with the same parameters (same min_freq for freq_agg, same n-factor and skew, if used, for a mcv_agg).

This will produce a very similar aggregate to running the same aggregate function over all the source data. In most cases, any difference will be no more than what you might get from simply reordering the input. However, if the source data for the different aggregates is very differently distributed, the rollup result may have looser frequency bounds.