Skip to content

Aggregate Functions

Aggregate functions are functions that define an operation which consumes values from multiple records to a produce a single output. Aggregate functions in SQL are typically used in GROUP BY functions. Aggregate functions are similar to scalar functions and function signatures with a small set of different properties.

Aggregate function signatures contain all the properties defined for scalar functions. Additionally, they contain the properties below:

Property Description Required
Inherits All properties defined for scalar function. N/A
Ordered Whether the result of this function is sensitive to sort order. Optional, defaults to false
Maximum set size Maximum allowed set size as an unsigned integer. Optional, defaults to unlimited
Decomposable Whether the function can be executed in one or more intermediate steps. Valid options are: NONE, ONE, MANY, describing how intermediate steps can be taken. Optional, defaults to NONE
Intermediate Output Type If the function is decomposable, represents the intermediate output type that is used, if the function is defined as either ONE or MANY decomposable. Will be a struct in many cases. Required for ONE and MANY.
Invocation Whether the function uses all or only distinct values in the aggregation calculation. Valid options are: ALL, DISTINCT. Optional, defaults to ALL

Aggregate Binding

When binding an aggregate function, the binding must include the following additional properties beyond the standard scalar binding properties:

Property Description
Phase Describes the input type of the data: [INITIAL_TO_INTERMEDIATE, INTERMEDIATE_TO_INTERMEDIATE, INITIAL_TO_RESULT, INTERMEDIATE_TO_RESULT] describing what portion of the operation is required. For functions that are NOT decomposable, the only valid option will be INITIAL_TO_RESULT.
Ordering Zero or more ordering keys along with key order (ASC|DESC|NULL FIRST, etc.), declared similar to the sort keys in an ORDER BY relational operation. If no sorts are specified, the records are not sorted prior to being passed to the aggregate function.