Skip to main content

Aggregation Functions

Functions that operate on groups of rows to produce summary values when used with AGG or WINDOW commands.

count([expression])​

Returns the number of rows in each group during aggregation.

Parameters​

  • expression (optional) - When provided, only counts rows where this expression evaluates to a non-null value

Description​

The count() function returns the number of rows in each group when you use it with AGG or WINDOW commands. Unlike SQL, Hamelin uses count() rather than count(*). When no grouping is specified, it counts all rows in the result.

When you provide an expression parameter, count() only counts rows where that expression evaluates to a non-null value. This lets you count specific conditions without using separate filtering.

When you omit an explicit field name, Hamelin automatically generates the field name count() or count(expression) that you can reference using backticks in subsequent commands.

count_distinct(x)​

Counts the number of distinct values in each group.

Parameters​

  • x - Expression of any type to count distinct values for

Description​

The count_distinct() function counts only unique values within each group, ignoring duplicates. Null values are not counted. This provides the same functionality as SQL's COUNT(DISTINCT x) but with cleaner syntax. For example, counting distinct user IDs shows how many unique users performed actions, regardless of how many actions each user performed.

approx_distinct(x)​

Returns an approximate count of distinct values in each group.

Parameters​

  • x - Expression of any type to count distinct values for

Description​

The approx_distinct() function provides an approximate count of distinct values using probabilistic algorithms that are much faster and use less memory than exact counting, especially for high-cardinality data. The result is typically accurate within a few percent. This is particularly useful when you need fast estimates for large datasets where exact precision isn't critical.

approx_top_k(value [, k [, max_items_tracked]])​

Returns the most frequent values and their estimated counts in each group.

Parameters​

  • value - A scalar expression. Hamelin supports binary, boolean, interval, calendar_interval, int, double, decimal, string, and timestamp. The function does not support complex types, such as arrays, maps, structs, tuples, ranges, or variants.
  • k (optional) - An integer literal that sets the maximum number of items in the result. It must be greater than 0. The default is 5. The maximum is 100000.
  • max_items_tracked (optional) - An integer literal that sets the maximum number of candidate items in the aggregate state. The default is 10000. This value must be greater than or equal to k. The maximum is 100000.

Return value​

The return type is array(struct(item <value type>, count int)). The item field has the same type as value. On DataFusion, the function includes SQL null as an item. On Trino, null inputs are dropped by the underlying aggregate. It returns an empty array for an empty input group.

Hamelin sorts results by count in descending order and then by item in ascending order. This item order gives stable results when counts are equal. Hamelin does not truncate item values.

Accuracy and memory​

On DataFusion, the function uses a bounded, mergeable Misra-Gries summary. This summary supports partial and final distributed aggregation. It tracks no more than max_items_tracked candidate items. If the number of distinct values is not greater than max_items_tracked, the returned items and counts are exact.

If the number of distinct values is greater than max_items_tracked, each returned count is an upper estimate. For a group with rows input rows, the estimate error is not greater than floor(rows / (max_items_tracked + 1)).

When the result contains k items, no omitted value has a true count that is greater than the estimated count of the last returned item. A value that is not in the candidate summary has a true count that is not greater than the same error bound. Candidate identities can change when frequency differences are within this bound.

One retained item representation can use no more than 8 MiB. The aggregate state can use no more than 64 MiB. The query returns a resource-limit error if an item or the state is larger than its limit.

On Trino, Hamelin uses approx_most_frequent with the same k and max_items_tracked arguments. Accuracy follows Trino's stream-summary implementation rather than the DataFusion Misra-Gries bounds above.

Backend and window support​

approx_top_k() is available on the DataFusion and Trino backends. On Trino, Hamelin lowers the call to approx_most_frequent, then reshapes the map result into the Hamelin array(struct(item, count)) return type and sorts it. Trino accepts only bigint and varchar values in that aggregate, so other Hamelin scalar types are cast to a supported form for aggregation and cast back for the result item. Trino aggregate semantics drop SQL null inputs, so null is not returned as an item on Trino. DataFusion does not support bounded sliding WINDOW frames. Trino uses the window frames that its backend supports.

On DataFusion, a sorted growing WINDOW evaluates and sorts up to max_items_tracked candidates for each output row. Use a smaller max_items_tracked value when this per-row cost matters.

count_if(condition)​

Counts the number of rows where a condition is true in each group.

Parameters​

  • condition - Boolean expression that must evaluate to true for the row to be counted

Description​

The count_if() function counts only rows where the specified condition evaluates to true. This provides a more concise alternative to using WHERE clauses or case() expressions for conditional counting.

When you omit an explicit field name, Hamelin automatically generates the field name count_if(condition) that you can reference using backticks in subsequent commands.

sum(x)​

Returns the sum of all values in each group.

Parameters​

  • x - Numeric expression to sum, or a day-time interval (interval type)

Description​

The sum() function adds up all non-null values of the specified expression within each group. If all values are null, it returns null. The result type matches the input type for exact numeric types and for day-time intervals.

avg(x)​

Returns the average (arithmetic mean) of all values in each group.

Parameters​

  • x - Numeric expression to average, or a day-time interval (interval type)

Description​

The avg() function calculates the arithmetic mean of all non-null values within each group. If all values are null, it returns null. For integer inputs the result is double; for other numeric types the result matches the input type. For day-time intervals the result is an interval (the mean duration).

stddev(x)​

Returns the standard deviation of all values in each group.

Parameters​

  • x - Numeric expression to calculate standard deviation for

Description​

The stddev() function calculates the sample standard deviation of all non-null values within each group. If there are fewer than two non-null values, it returns null. The result is always a floating-point type.

approx_percentile(x, percentile)​

Returns an approximate percentile value for the specified expression.

Parameters​

  • x - Numeric expression to calculate percentile for
  • percentile - Numeric value between 0.0 and 1.0 representing the desired percentile

Description​

The approx_percentile() function calculates an approximate percentile using efficient algorithms suitable for large datasets. The percentile parameter should be between 0.0 (minimum) and 1.0 (maximum). For example, 0.5 returns the median, 0.95 returns the 95th percentile.

min(x)​

Returns the minimum value in each group.

Parameters​

  • x - Expression of numeric, string, timestamp, or day-time interval type

Description​

The min() function finds the smallest value within each group. It works with numeric types (returning the numerically smallest), strings (lexicographic ordering), timestamps (chronologically earliest), and day-time intervals (shortest duration). If all values are null, it returns null.

max(x)​

Returns the maximum value in each group.

Parameters​

  • x - Expression of numeric, string, timestamp, or day-time interval type

Description​

The max() function finds the largest value within each group. It works with numeric types (returning the numerically largest), strings (lexicographic ordering), timestamps (chronologically latest), and day-time intervals (longest duration). If all values are null, it returns null.

any_value(x)​

Returns an arbitrary value from each group.

Parameters​

  • x - Expression of any type to select a value from

Description​

The any_value() function returns an arbitrary non-null value from each group. This is useful when you're grouping data and need to include a field that's not part of the grouping criteria, where you know all values in the group are the same or where you don't care which specific value is selected. Unlike min() or max(), this function doesn't impose any ordering overhead, making it more efficient when you just need any representative value from the group.

array_agg(x)​

Collects all values in each group into an array.

Parameters​

  • x - Expression of any type to collect into an array

Description​

The array_agg() function creates an array containing all non-null values from the specified expression within each group. The order of elements in the resulting array follows the order specified by any SORT clause in the aggregation command. If there are no non-null values, it returns an empty array.

schema_of_variant_agg(value)​

DataFusion only. Infers and merges a Hamelin type from all variant values in a group.

Signature: schema_of_variant_agg(value: variant) -> string

Parameters​

  • value - Variant expression to inspect across the group

Description​

Returns one deterministic Hamelin type string for the group. Merge rules, limits, examples, and backend notes live in Variant.

set_agg(x)​

Collects distinct values in each group into an array.

Parameters​

  • x - Expression of any type to collect distinct values for

Description​

The set_agg() function creates an array containing the unique values from the specified expression within each group. Null values are kept as a single null entry when present.

Use set_agg(x) instead of array_distinct(array_agg(x)) when you only need unique values. The aggregate deduplicates values while aggregating, so it avoids materializing every input value before removing duplicates.

map_agg(key, value)​

Collects key-value pairs in each group into a map.

Parameters​

  • key - Expression of any type to use as map keys
  • value - Expression of any type to use as map values

Description​

The map_agg() function creates a map from key-value pairs within each group. If the same key appears multiple times, only the last value is retained. The order of processing follows any SORT clause in the aggregation command.

multimap_agg(key, value)​

Collects key-value pairs in each group into a map where each key maps to an array of values.

Parameters​

  • key - Expression of any type to use as map keys
  • value - Expression of any type to collect into arrays

Description​

The multimap_agg() function creates a map where each unique key maps to an array of all values associated with that key within each group. This preserves all values for duplicate keys, unlike map_agg() which keeps only the last value.

any(x)​

Returns true if any value in the group is true (logical OR aggregation).

Parameters​

  • x - Boolean expression to test

Description​

The any() function performs logical OR aggregation on boolean values within each group. It returns true if at least one value is true, false if all values are false, and null if all values are null.

all(x)​

Returns true if all values in the group are true (logical AND aggregation).

Parameters​

  • x - Boolean expression to test

Description​

The all() function performs logical AND aggregation on boolean values within each group. It returns true if all values are true, false if at least one value is false, and null if all values are null.