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 - 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 expects a single value of the argument of the aggregate function, or in the case of multiple arguments, a tuple of them; the value is aggregated to form the state.
• array - the format expects an Array of values, as described in the value option above; all the elements of the array are aggregated to form the state.

In the value and array modes, the column is read as if it had the type of the values (T, Tuple(T1, T2), or an Array of them), in the representation the input format uses for that type. This works the same way in every input format, from CSV and JSONEachRow to RowBinary and Parquet.

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.