Bookshelf Home | Contents | Index | Search | PDF

Siebel Analytics Server Administration Guide > SQL Reference > SQL Reference >

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.

This section describes the running aggregate functions supported by the Siebel Analytics Server.

Mavg

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

Syntax:

MAVG (n_expression, n)

where:

n_expression	Any expression that evaluates to a numerical value.
n	Any positive integer. Represents the average of the last n rows of data.

The MAVG function resets its values for each group in the query, according to the rules outlined in Display Function Reset Behavior.

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.

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.

This function resets its values for each group in the query according to the rules described in Display Function Reset Behavior.

Syntax:

MSUM (n_expression, n)

Where:

n_expression	Any expression that evaluates to a numerical value.
n	Any positive integer. Represents the sum of the last n rows of data.

Example:

The following example shows a query that uses the MSUM function and the query results.

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

MONTH	REVENUE	3_MO_SUM
JAN	100.00	100.00
FEB	200.00	300.00
MAR	100.00	400.00
APRIL	100.00	400.00
MAY	300.00	500.00
JUNE	400.00	800.00
JULY	500.00	1200.00
AUG	500.00	1400.00
SEPT	500.00	1500.00
OCT	300.00	1300.00
NOV	200.00	1000.00
DEC	100.00	600.00

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.

This function resets its values for each group in the query according to the rules described in Display Function Reset Behavior.

Syntax:

RSUM (n_expression)

Where:

n_expression

Any expression that evaluates to a numerical value.

Example:

The following example shows a query that uses the RSUM function and the query results.

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

MONTH	REVENUE	3_MO_SUM
JAN	100.00	100.00
FEB	200.00	300.00
MAR	100.00	400.00
APRIL	100.00	500.00
MAY	300.00	800.00
JUNE	400.00	1200.00
JULY	500.00	1700.00
AUG	500.00	2200.00
SEPT	500.00	2700.00
OCT	300.00	3000.00
NOV	200.00	3200.00
DEC	100.00	3300.00

RCOUNT

This function takes a set of records as input and counts the number of records encountered so far.

This function resets its values for each group in the query according to the rules described in Display Function Reset Behavior.

Syntax:

RCOUNT (Expr)

Where:

Expr	An expression of any datatype.

Example:

The following example shows a query that uses the RCOUNT function and the query results.

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

MONTH	PROFIT	RCOUNT (profit
MAY	300.00	2
JUNE	400.00	3
JULY	500.00	4
AUG	500.00	5
SEPT	500.00	6
OCT	300.00	7

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.

This function resets its values for each group in the query according to the rules described in Display Function Reset Behavior.

Syntax:

RMAX (expression)

Where:

expression

An expression of any datatype. The datatype must be one that has an associated sort order.

Example:

The following example shows a query that uses the RMAX function and the query results.

select month, profit, RMAX(profit) from sales_subject_area

MONTH	PROFIT	RMAX (profit)
JAN	100.00	100.00
FEB	200.00	200.00
MAR	100.00	200.00
APRIL	100.00	200.00
MAY	300.00	300.00
JUNE	400.00	400.00
JULY	500.00	500.00
AUG	500.00	500.00
SEPT	500.00	500.00
OCT	300.00	500.00
NOV	200.00	500.00
DEC	100.00	500.00

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.

This function resets its values for each group in the query according to the rules described in Display Function Reset Behavior.

Syntax:

RMIN (expression)

Where:

expression

An expression of any datatype. The datatype must be one that has an associated sort order.

Example:

The following example shows a query that uses the RMIN function and the query results.

select month, profit, RMIN(profit) from sales_subject_area

MONTH	PROFIT	RMIN (profit)
JAN	400.00	400.00
FEB	200.00	200.00
MAR	100.00	100.00
APRIL	100.00	100.00
MAY	300.00	100.00
JUNE	400.00	100.00
JULY	500.00	100.00
AUG	500.00	100.00
SEPT	500.00	100.00
OCT	300.00	100.00
NOV	200.00	100.00
DEC	100.00	100.00

Bookshelf Home | Contents | Index | Search | PDF