SimpleAggregateFunction Type

Documentation for the SimpleAggregateFunction data type

Description

The SimpleAggregateFunction data type stores the intermediate state of an aggregate function, but not its full state as the AggregateFunction type does.

This optimization can be applied to functions for which the following property holds:

the result of applying a function f to a row set S1 UNION ALL S2 can be obtained by applying f to parts of the row set separately, and then again applying f to the results: f(S1 UNION ALL S2) = f(f(S1) UNION ALL f(S2)).

This property guarantees that partial aggregation results are enough to compute the combined one, so we do not have to store and process any extra data. For example, the result of the min or max functions require no extra steps to calculate the final result from the intermediate steps, whereas the avg function requires keeping track of a sum and a count, which will be divided to get the average in a final Merge step which combines the intermediate states.

Aggregate function values are commonly produced by calling an aggregate function with the -SimpleState combinator appended to the function name.

Syntax

SimpleAggregateFunction(aggregate_function_name, types_of_arguments...)

Parameters

• aggregate_function_name - The name of an aggregate function.
• Type - Types of the aggregate function arguments.

Supported functions

The following aggregate functions are supported:

• any
• any_respect_nulls
• anyLast
• anyLast_respect_nulls
• min
• max
• sum
• sumWithOverflow
• groupBitAnd
• groupBitOr
• groupBitXor
• groupArrayArray
• groupUniqArrayArray
• groupUniqArrayArrayMap
• sumMap (sumMappedArrays)
• minMap (minMappedArrays)
• maxMap (maxMappedArrays)

Values of the SimpleAggregateFunction(func, Type) have the same Type, so unlike with the AggregateFunction type there is no need to apply -Merge/-State combinators.

The SimpleAggregateFunction type has better performance than the AggregateFunction for the same aggregate functions.

Example

CREATE TABLE simple (id UInt64, val SimpleAggregateFunction(sum, Double)) ENGINE=AggregatingMergeTree ORDER BY id;

Related Content

• Blog: Using Aggregate Combinators in ClickHouse - Blog: Using Aggregate Combinators in ClickHouse
• AggregateFunction type.