Running Aggregate Functions
Running aggregate functions are similar to functional aggregates in that they take a
set of records as input, but instead of outputting the single aggregate for the entire set
of records, they output the aggregate based on records encountered so far.
Functions
MAVG
This function calculates a moving average (mean) for the last n rows of data in the
result set, inclusive of the current row.
The average for the first row is equal to the numeric expression for the first row. The
average for the second row is calculated by taking the average of the first two
rows of data. The average for the third row is calculated by taking the average of
the first three rows of data, and so on until you reach the nth row, where the average
is calculated based on the last n rows of data.
Syntax
MAVG(numExpr, integer)
Where
numExpr is any expression that evaluates to a numerical value.
integer is any positive integer. This represents the average of the last n rows of
data.
MSUM
This function calculates a moving sum for the last n rows of data, inclusive of the
current row.
The sum for the first row is equal to the numeric expression for the first row. The sum
for the second row is calculated by taking the sum of the first two rows of data.
The sum for the third row is calculated by taking the sum of the first three rows of
data, and so on.
When the nth row is reached, the sum is calculated based on the last n rows of
data.
Syntax
MSUM(numExpr, integer)
Where
numExpr is any expression that evaluates to a numerical value.
integer is any positive integer. Represents the average of the last n rows of
data.
Example
MSUM (revenue, 3)
RSUM
This function calculates a running sum based on records encountered so far. The
sum for the first row is equal to the numeric expression for the first row. The sum for
the second row is calculated by taking the sum of the first two rows of data.
The sum for the third row is calculated by taking the sum of the first three rows of
data, and so on.
Syntax
RSUM(numExpr)
Where
numExpr is any expression that evaluates to a numerical value.
Example
RSUM(revenue)
RCOUNT
This function takes a set of records as input and counts the number of records
encountered so far.
Syntax
RCOUNT(expr)
Where
expr is an expression of any datatype.
Example
RCOUNT(profit)
RMAX
This function takes a set of records as input and shows the maximum value based
on records encountered so far. The specified datatype must be one that can be
ordered.
Syntax
RMAX(expr)
Where
expr is an expression of any datatype. The datatype must be one that has an
associated sort order.
Example
RMAX(profit)
RMIN
This function takes a set of records as input and shows the minimum value based
on records encountered so far. The specified datatype must be one that can be
ordered.
Syntax
RMIN(expr)
Where
expr is an expression of any datatype. The datatype must be one that has an
associated sort order.
Example
RMIN(profit)