Running Aggregate Functions

Running aggregate functions perform operations on multiple values to create summary results.

Function Example Description Syntax

MAVG

  

Calculates a moving average (mean) for the last n rows of data in the result set, inclusive of the current row.

expr is any expression that evaluates to a numerical value. integer is any positive integer. Represents the average of the last n rows of data.

MAVG(expr, integer)

MSUM

select month, revenue, MSUM(revenue, 3) as 3_MO_SUM from sales_subject_area

Calculates a moving sum for the last n rows of data, inclusive of the current row.

expr is any expression that evaluates to a numerical value. integer is any positive integer. Represents the sum of the last n rows of data.

MSUM(expr, integer)

RSUM

SELECT month, revenue, RSUM(revenue) as RUNNING_SUM from sales_subject_area

Calculates a running sum based on records encountered so far.

expr is any expression that evaluates to a numerical value.

RSUM(expr)

RCOUNT

select month, profit, RCOUNT(profit) from sales_subject_area where profit > 200

Takes a set of records as input and counts the number of records encountered so far.

expr is an expression of any datatype.

RCOUNT(expr)

RMAX

SELECT month, profit,RMAX(profit) from sales_subject_area

Takes a set of records as input and shows the maximum value based on records encountered so far.

expr is an expression of any datatype.

RMAX(expr)

RMIN

select month, profit,RMIN(profit) from sales_subject_area

Takes a set of records as input and shows the minimum value based on records encountered so far.

expr is an expression of any datatype.

RMIN(expr)