Aggregate Function Combinators

Aggregate function can have a suffix appended to it, this changes the way the aggregate function works.

<aggregate>If

If can be appended to the name of any aggregate function. The aggregate function accepts an extra argument – a condition (Uint8 type).

With conditional aggregate functions, you can calculate aggregates for several conditions at once, without using subqueries and JOINs.

Parameters:

  • condition -

Usage:

  • sumIf(column, condition)
  • countIf(column, condition)
  • avgIf(column, condition)
  • quantilesTimingIf(level1, level2)(column, condition)
  • argMinIf(arg_column, value_column, condition)
  • .. and many more.

Returns:

  • Aggregated rows that triggered the condition. If the condition was not triggered it returns a default value (zero or empty string).

<aggregate>Array

Array can be appended to any aggregate function allowing it to accept an array of elements of the same type.

If and Array can be combined, with Array specified before If.

Usage:

  • sumArray(array)
  • uniqueArray(array)
  • avgArray(array)
  • or combined ..
  • uniqArrayIf(array, condition)
  • quantilesTimingArrayIf(level1, level2)(arr, condition)
  • ..and many more

Returns:

  • The aggregate function works like the original aggregate function across all array elements.

<aggregate>ForEach

ForEach can be appended to any aggregate function and will convert that aggregate function to a function, accepting arrays.

Applied aggregation for each corresponding elements of arrays independently, returning arrays of aggregated values on corresponding positions.

Usage:

  • sumForEach(array)
  • countForEach(array)
  • avgForEach(array)
  • .. and many more

Returns:

  • Array of results

<aggregate>OrDefault

orDefault can be appended to any aggregate function, providing a response fallback if there is nothing to aggregate.

Usage:

  • avgOrDefault(agg)
  • sumOrDefault(agg)
  • ..and many more

Returns:

  • Aggregated value or the default value based on the aggregate function return type.

<aggregate>OrNull

orNull can be appended to any aggregate function, providing a response fallback if there is nothing to aggregate.

Usage:

  • avgOrNull(agg)
  • sumOrNull(agg)
  • ..and many more

Returns:

  • Aggregated value or null

<aggregate>Resample

Resample can be appended to any aggregate function, lets you divide data into groups, and then separately aggregates the data in those groups. Groups are created by splitting the values from one column into intervals.

Parameters:

  • start — starting value of the interval.
  • stop (Optional) — ending value of interval. If not specified, full interval will be used.
  • step — number of sub-intervals. The aggregation is executed over each of those sub-intervals independently.
  • params — aggregarion function parameters.
  • column — whose values are used for separating data into intervals.

Usage:

  • countResample(start, end, step)(params, column)
  • sumResample(start, end, step)(params, column)
  • avgResample(start, end, step)(params, column)
  • .. and many more

Returns:

  • Array of aggregation results for each sub-interval.

Did this page help you?