Skip to main content

AggregateFunction

Aggregate functions can have an implementation-defined intermediate state that can be serialized to an AggregateFunction(...) data type and stored in a table, usually, by means of a materialized view. The common way to produce an aggregate function state is by calling the aggregate function with the -State suffix. To get the final result of aggregation in the future, you must use the same aggregate function with the -Mergesuffix.

AggregateFunction(name, types_of_arguments...) — parametric data type.

Parameters

  • Name of the aggregate function. If the function is parametric, specify its parameters too.

  • Types of the aggregate function arguments.

Example

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

uniq, anyIf (any+If) and quantiles are the aggregate functions supported in ClickHouse.

Usage​

Data Insertion​

To insert data, use INSERT SELECT with aggregate -State- functions.

Function examples

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

In contrast to the corresponding functions uniq and quantiles, -State- functions return the state, instead of the final value. In other words, they return a value of AggregateFunction type.

In the results of SELECT query, the values of AggregateFunction type have implementation-specific binary representation for all of the ClickHouse output formats. If dump data into, for example, TabSeparated format with SELECT query, then this dump can be loaded back using INSERT query.

Data Selection​

When selecting data from AggregatingMergeTree table, use GROUP BY clause and the same aggregate functions as when inserting data, but using -Mergesuffix.

An aggregate function with -Merge suffix takes a set of states, combines them, and returns the result of 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​