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.

array_union_agg

Returns an array containing the multiset union of input arrays.

avg

Calculate the arithmetic average of the input expression across the group, or of the scalar arguments if more than one.

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.

delta

Calculates the value difference of the argument in each time bin for each group.

delta_monotonic

Calculates the amount of difference in a column in each time bin for each group.

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

Returns the maximum value of a group of inputs.

median

Return the fast approximate median value of one column.

median_exact

Return the exact median value of one column.

min

Returns the minimum value of a group of inputs.

object_agg

Extract aggregated fields and values from a group of rows into a new JSON object.

percentile

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

percentile_cont

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

percentile_disc

Assuming a discrete distribution, return 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 distribution in a histogram metric that was generated by a

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.

topk_agg

Returns an approximation of the top K most frequent values in the input, along with their approximate frequencies.