Aggregate Functions
Aggregate functions perform operations on multiple values to create summary results.
Functions
AGGREGATE AT
This function aggregates columns based on the level or levels you specify. Using
AGGREGATE AT guarantees that the aggregate for the measure always occurs at
the levels specified after the keyword AT, regardless of the WHERE clause.
Syntax
AGGREGATE(expr AT level [, level1, levelN])
Where
expr is any expression that references at least one measure column.
level is the level at which you want to aggregate. You can optionally specify
multiple levels.
You cannot specify a level from a dimension that contains levels that are being
used as the measure level for the measure you specified in the first argument. For
example, you cannot write the function as AGGREGATE(yearly_sales AT month)
because “month” is from the same time dimension that is being used as the measure
level for “yearly_sales.”
Because the AGGREGATE AT operator is always executed before the predicates,
it always returns the correct total for the time level specified after the keyword AT.
Example
AGGREGATE(sales AT Year)
AVG
This function calculates the average (mean) value of an expression in a result set.
The function requires a numeric expression as its argument.
note: The denominator of AVG is the number of rows aggregated. For this reason,
it is usually a mistake to use AVG(x) in a calculation in Xactly Analytics. Instead,
write the expression manually so that you can control both the numerator and
denominator (x/y).
Syntax
AVG(numExpr)
Where
numExpr is any expression that evaluates to a numerical value.
AVGDISTINCT
This function calculates the average (mean) of all distinct values of an expression.
The function requires a numeric expression as its argument.
Syntax
AVG(DISTINCT numExpr)
Where
numExpr is any expression that evaluates to a numerical value.
BOTTOMN
This function ranks the lowest n values of the expression argument from 1 to n, 1
corresponding to the lowest numerical value. The BOTTOMN function operates on
the values returned in the result set. A request can contain only one BOTTOMN
expression.
Syntax
BOTTOMN(numExpr, integer)
Where
numExpr is any expression that evaluates to a numerical value.
integer is any positive integer. Represents the bottom number of rankings displayed
in the result set, 1 being the lowest rank.
COUNT
This function calculates the number of rows having a non-null value for the expression.
The expression is typically a column name, in which case the number of rows
with non-null values for that column is returned.
Syntax
COUNT(numExpr)
Where
numExpr is Any expression that evaluates to a numerical value.
COUNTDISTINCT
This function adds distinct processing to the COUNT function.
Syntax
COUNT(DISTINCT numExpr)
Where
numExpr is any expression that evaluates to a numerical value.
COUNT(*)
This function counts the number of rows.
Syntax
COUNT(*)
MAX
This function calculates the maximum value (highest numeric value) of the rows satisfying the numeric expression argument.
Syntax
MAX(numExpr)
Where
numExpr is any expression that evaluates to a numerical value.
MEDIAN
This function calculates the median (middle) value of the rows satisfying the numeric expression argument. When there is an even number of rows, the median is the mean of the two middle rows. This function always returns a double.
Syntax
MEDIAN(numExpr)
Where
numExpr is any expression that evaluates to a numerical value.
MIN
This function calculates the minimum value (lowest numeric value) of the rows satisfying
the numeric expression argument.
Syntax
MIN(numExpr)
Where
numExpr is any expression that evaluates to a numerical value.
NTILE
This function determines the rank of a value in terms of a user-specified range. It
returns integers to represent any range of ranks. In other words, the resulting sorted
data set is broken into several tiles where there are roughly an equal number of
values in each tile.
NTILE with numTiles=100 returns what is commonly called the “percentile” (with
numbers ranging from 1 to 100, with 100 representing the high end of the sort). This
value is different from the results of the Percentile function, which conforms to
what is called “percent rank” in SQL 92 and returns values from 0 to 1.
Syntax
NTILE(numExpr, numTiles)
Where
numExpr is any expression that evaluates to a numerical value.
numTiles is any positive, non-null integer.
PERCENTILE
This function calculates a percentile rank for each value satisfying the numeric
expression argument. The percentile rank ranges are between 0 (0th percentile) to
1 (100th percentile), inclusive. The percentile is calculated based on the values in
the result set.
Syntax
PERCENTILE(numExpr)
Where
numExpr is any expression that evaluates to a numerical value.
RANK
This function calculates the rank for each value satisfying the numeric expression
argument. The highest number is assigned a rank of 1, and each successive rank is
assigned the next consecutive integer (2, 3, 4,…).
If certain values are equal, they are assigned the same rank (for example, 1, 1, 1,
4, 5, 5, 7…). The rank is calculated based on the values in the result set.
Syntax
RANK(numExpr)
Where
numExpr is any expression that evaluates to a numerical value.
STDDEV
This function returns the standard deviation for a set of values. STDDEV_SAMP is a
synonym for STDDEV.
Syntax
STDDEV([ALL | DISTINCT] numExpr)
Where
numExpr is any expression that evaluates to a numerical value.
ALL is used when you want the standard deviation to be calculated for all
data in the set.
DISTINCT is used when you want all duplicates ignored in the calculation.
SUM
This function calculates the sum obtained by adding all values satisfying the
numeric expression argument.
Syntax
SUM(numExpr)
Where
numExpr is any expression that evaluates to a numerical value.
SUMDISTINCT
This function calculates the sum obtained by adding all of the distinct values satisfying
the numeric expression argument.
Syntax
SUM(DISTINCT numExpr)
Where
numExpr is any expression that evaluates to a numerical value
TOPN
This function ranks the highest n values of the expression argument from 1 to n, 1
corresponding to the highest numerical value. The TOPN function operates on the
values returned in the result set. A request can contain only one TOPN expression.
Syntax
TOPN(numExpr, integer)
Where
numExpr is any expression that evaluates to a numerical value.
integer is any positive integer. This represents the bottom number of rankings
displayed in the result set, 1 being the highest rank.