Rolling, Cumulative & Moving Averages

Rolling & Cumulative

Cloud9QL provides a set of operations which can be utilized to calculate rolling and cumulative operations such as accumulate, growth, delta, simple moving average, cumulative moving average, and time moving average.

The standard use case for these operations is to allow computation <operation> of a <value field> across a set of <dimension field(s)> and optionally grouping by a set of <grouping field(s)>.

<operation>(<value field>[, <grouping field(s)>]);

For example, compute the DELTA of Sent across Week grouping by Customer. In this example:

<operation>: DELTA
<value field>: Sent
<dimension field(s)>: Week
<grouping field(s)>: Customer

Example:

select Customer, delta(Sent, Customer) as SentDelta

There is one important restriction when using these Cloud9 QL functions: the input data need to be ordered by the <grouping field(s)> and <dimension field(s)> in that order.

ACCUMULATE

Creates cumulative totals for a field between records, given a sorted dataset.

accumulate(<value field>[, <grouping field(s)>]);

select accumulate(sent), date

The above example returns a cumulative sum of sent count for a pre-sorted date order.

GROWTH

Calculates a growth percentage for a field between records, for a sorted dataset.

growth(<value field>[, <grouping field(s)>]);

 select growth(sent), date

DELTA

Calculates a different for a field between records, for a sorted dataset.

delta(<value field>[, <grouping field(s)>]);

 select delta(sent), date

SMA

Simple moving average based on a field and a window size for it. Assumes a sorted dataset.

SMA(<value field>, <window size>[, <grouping field(s)>]);

select sma(sent, 10)

CMA

Cumulative moving average returns the moving average of all data up to the current data point.

CMA(<value field>[, <grouping field(s)>]);

select cma(sent)

TMA

Time moving average based on a field, date field, and a window time unit size for it. See Time Units for all available time units. Assumes a sorted dataset

TMA(<value field>, <date field>, <time unit window>[, <grouping field(s)>]);

select tma(sent, date, 1w)

For more details on moving average definitions, see http://en.wikipedia.org/wiki/Moving_average

TMS

Time moving sum based on a field, date field, and window time unit size. 

TMS(<value field>, <date field>, <time unit window>[, <grouping field(s)>]);

select tms(sent, date, 1w)