List of aggregators

The aggregators are similar to functions, but they benefit from a richer syntax with options. Below, we list the aggregators supported by Envision.

Aggregator usage

There are two main usages for aggregators, whether a join between tables is performed or not.

Simple aggregation

When there is no join, the general usage for an aggregator agg is:

u = agg(v) by [w1, w2, w3] sort [w4, w5] if c or x over [a .. b]


When by is not specified, the grouping happens according to the context, typically using the left-side of the statement in the case of an assignment. It is possible to list multiple variables after by; if only one variable is used, then the parentheses are not required.

The sort option has typically no impact on most aggregators, e.g. max, because those operators do not depend on any sort order in the first place. However, we suggest to leverage this option with first and last aggregators.

When if is not specified, all lines are included that are equivalent to if true.

When or is not specified, default value of each aggregator/type is returned when the grouping is empty. The default values for each aggregator are defined below.

The over option is available when aggregating data into a calendar table (see also Calendar tables). The boundaries of the time interval are expressed in days, weeks or months and they are inclusive. For example, considering a table Day, the option over [-5 .. 0] will perform an aggregation over 6 days, namely the day of the target line and the 5 previous days.

Aggregation with table joins

The general syntax for this second case is similar but different to the one described above:

u = agg(v) by [w1, w2, w3] sort [w4, w5] if c at [z1, z2, z3] or x

The at option indicates the groups used when joining the tables. Note that there must be a one-to-one correspondence between these groups and the groups used in the by option. As a consequence, the by option is mandatory and the number of entries specified in the by and in the at options must be identical.

Basic aggregators

The most commonly used aggregators are:

The first, last and same aggregators of an empty group return the default value accordingly to the input data type.

Logical aggregators

The aggregators below returns a Boolean value:

Any logical aggregator of an empty group returns false.

Temporal aggregators

These aggregators apply only to tables that benefit from a Date column:

Statistical aggregators

The aggregators below provide statistical calculations:

Random variables

Ranvars can be generated through aggregation (see also algebra of ranvars):