AggregateFunction Type

Documentation for the AggregateFunction data type in ClickHouse, which stores intermediate states of aggregate functions

Description

All Aggregate functions in ClickHouse have an implementation-specific intermediate state that can be serialized to an AggregateFunction data type and stored in a table. This is usually done by means of a materialized view.

There are two aggregate function combinators commonly used with the AggregateFunction type:

• The -State aggregate function combinator, which when appended to an aggregate function name, produces AggregateFunction intermediate states.
• The -Merge aggregate function combinator, which is used to get the final result of an aggregation from the intermediate states.

Syntax

AggregateFunction(aggregate_function_name, types_of_arguments...)

Parameters

• aggregate_function_name - The name of an aggregate function. If the function is parametric, then its parameters should be specified too.
• types_of_arguments - The types of the aggregate function arguments.

for example:

CREATE TABLE t
(
    column1 AggregateFunction(uniq, UInt64),
    column2 AggregateFunction(anyIf, String, UInt8),
    column3 AggregateFunction(quantiles(0.5, 0.9), UInt64)
) ENGINE = ...

Usage

Data Insertion

To insert data into a table with columns of type AggregateFunction, you can use INSERT SELECT with aggregate functions and the -State aggregate function combinator.

For example, to insert into columns of type AggregateFunction(uniq, UInt64) and AggregateFunction(quantiles(0.5, 0.9), UInt64) you would use the following aggregate functions with combinators.

uniqState(UserID)
quantilesState(0.5, 0.9)(SendTiming)

In contrast to functions uniq and quantiles, uniqState and quantilesState (with -State combinator appended) return the state, rather than the final value. In other words, they return a value of AggregateFunction type.

In the results of the SELECT query, values of type AggregateFunction have implementation-specific binary representations for all of the ClickHouse output formats.

There is a special Session level setting aggregate_function_input_format that allows to build state from the input values. It supports the following formats:

• state - binary string with the serialized state (the default). If you dump data into, for example, the TabSeparated format with a SELECT query, then this dump can be loaded back using the INSERT query.
• value - the format will expect a single value of the argument of the aggregate function, or in the case of multiple arguments, a tuple of them; that will be deserialized to form the relevant state
• array - the format will expect an Array of values, as described in the values option above; all the elements of the array will be aggregated to form the state

Data Selection

When selecting data from AggregatingMergeTree table, use the GROUP BY clause and the same aggregate functions as for when you inserted the data, but use the -Merge combinator.

An aggregate function with the -Merge combinator appended to it takes a set of states, combines them, and returns the result of the complete data aggregation.

For example, the following two queries return the same result:

SELECT uniq(UserID) FROM table

SELECT uniqMerge(state) FROM (SELECT uniqState(UserID) AS state FROM table GROUP BY RegionID)

Usage Example

See AggregatingMergeTree engine description.

Related Content

• Blog: Using Aggregate Combinators in ClickHouse
• MergeState combinator.
• State combinator.