OPAL Aggregate Functions

Aggregate functions are used with aggregating verbs like timechart, aggregate, and statsby.

Aggregate functions accept multiple input values from a set of input rows and emit one output value as a single output row. For example, sum calculates the sum of all input numbers, and max returns the largest value.

Function

Description

any

Return any value of one column across a group

any_not_null

Return any non-null value of one column across a group.

array_agg

Returns an array of concatenated input values.

array_agg_distinct

Returns an array of distinct input values.

avg

Calculate the arithmetic average of the input expression across the group.

count

Count the number of non-null items in the group.

count_distinct

Estimate the approximate number of distinct values in the input using hyper-log-log.

count_distinct_exact

Count the exact number of distinct values in the input using complete enumeration (slower than count_distinct).

deriv

Calculate the average per-second derivative of the argument across the group.

first

Return the first value of one column across an ordered group.

first_not_null

Return the first non-null value of one column across an ordered group.

last

Return the last value of one column across an ordered group.

last_not_null

Return the last non-null value of one column across an ordered group.

max

Compute the maximum of one column across a group (with one argument) or the scalar greatest value of its arguments (with more than

median

Return the fast approximate median value of one column.

median_exact

Return the exact median value of one column.

min

Compute the minimum of one column across a group (with one argument) or the scalar least value of its arguments (with more than

object_agg

Returns one OBJECT per group.

percentile

Returns an approximated value for the specified percentile of the input expression across the group.

percentile_cont

Assuming a continuous distribution, it returns the value for the specified percentile of the input expression across the group.

percentile_disc

Assuming a discrete distribution, it returns the value for the specified percentile of the input expression across the group.

prom_quantile

prom_quantile calculates an approximate percentile value based on the

rate

Calculate the average per-second rate of the argument across the group.

stddev

Calculate the standard deviation across the group.

string_agg

Returns concatenated input values, separated by the delimiter.

string_agg_distinct

Returns concatenated input values, separated by the delimiter.

sum

Calculate the sum of the argument across the group, or of the scalar arguments if more than one.