Aggregation functions

EQL supports the following aggregation functions.

Function Description
ARB Selects an arbitrary but consistent value from the set of values in a field. Works on both multi-assign attributes (sets) and single-assign attributes.
AVG Computes the arithmetic mean value for a field.
CORRELATION Computes the correlation coefficient between two numeric fields.
COUNT Counts the number of records with valid non-NULL values in a field for each GROUP BY result. Works on both multi-assign attributes (sets) and single-assign attributes.
COUNT_APPROX Counts the most frequent refinements. Works on both multi-assign attributes (sets) and single-assign attributes.
COUNTDISTINCT Counts the number of unique, valid non-NULL values in a field for each GROUP BY result. Works on both multi-assign attributes (sets) and single-assign attributes.
APPROXCOUNTDISTINCT Counts the number of unique, valid non-NULL values in a field for each GROUP BY result. Works on both multi-assign attributes (sets) and single-assign attributes.
HAS_REFINEMENTS Determines whether a specific attribute has non-implicit refinements.
MAX Finds the maximum value for a field.
MIN Finds the minimum value for a field.
MEDIAN Finds the median value for a field. (Note that PAGE PERCENT provides overlapping functionality). If the argument is an integer, a double is always returned.
Note that the EQL definition of MEDIAN is the same as the normal statistical definition when EQL is computing the median of an even number of numbers. That is, given an input relation containing {1,2,3,4}, the following query: 
RETURN results AS SELECT
  MEDIAN(a) AS med
FROM SaleState
GROUP
produces the mean of the two elements in the middle of the sorted set, or 2.5.
PERCENTILE Computes the percentile for a field.
RECORD_IN_FAST_SAMPLE Returns a sample of the records in the named state.
STDDEV Computes the standard deviation for a field.
STRING_JOIN Creates a single string containing all the values of a string attribute.
SUM Computes the sum of field values.
VARIANCE Computes the variance (that is, the square of the standard deviation) for a field.

MIN and MAX results ordering

The MIN and MAX functions work with int, double, dateTime, duration, Boolean, and string fields, as follows:
  • For int and double values, MIN finds the numerically smallest integer or double, while MAX finds the largest integer or double.
  • For dateTime values, MIN finds the earliest date while MAX finds the latest date.
  • For duration values, MIN finds the shortest time duration date while MAX finds the longest time duration. Note that a negative duration is considered to be less than a positive duration.
  • For Boolean values, both MIN and MAX consider FALSE to be less than TRUE (if the data set has both values assigned). If the data set has only Boolean type assigned, then that value is returned by both functions.
  • For string values, both functions use the lexicographical ordering (for example, "89" < "9" < "90" < "ab" < "xy"). In this example, MIN would return "89" while MAX would return "xy".

STRING_JOIN function

The STRING_JOIN function takes a string property and a delimiter and creates a single string containing all of the property's values, separated by the delimiter. Its syntax is: 
STRING_JOIN('delimiter', string_attribute)

The delimiter is a string literal enclosed in single quotation marks.

The resulting strings are sorted in a lexicographical order within each group. NULL values are ignored in the output, but values having the empty string are not.

For this sample query, assume that the R_NAME attribute is of type string and contains names of regions, while the N_NAME attribute is also of type string and contains the names of nations: 
RETURN results AS SELECT
  STRING_JOIN(', ',R_NAME) AS Regions,
  STRING_JOIN(',',N_NAME) AS Nations
FROM ProductState
GROUP
The query returns the region and country names delimited by commas: 
Nations
ALGERIA, ARGENTINA, BRAZIL, CANADA, CHINA, EGYPT, ETHIOPIA, FRANCE, GERMANY, INDIA, INDONESIA, IRAN,
IRAQ, JAPAN, JORDAN, KENYA, MOROCCO, MOZAMBIQUE, PERU, ROMANIA, RUSSIA, SAUDI ARABIA, UNITED KINGDOM,
UNITED STATES, VIETNAM
Regions
AFRICA,AMERICA,ASIA,EUROPE,MIDDLE EAST
Note: The Regions delimiter includes a space while the Nations delimiter does not. That is, if you want a space between the output terms, you must specify it in the delimiter.