Rolling, Cumulative & Moving Averages

Cloud9QL provides a set of operations for rolling and cumulative calculations: accumulate, growth, delta, simple moving average, cumulative moving average, time moving average, time moving sum, rank, and fill null.

These operations compute a value across a sorted dataset, optionally grouped by one or more fields:

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

For example, to compute the DELTA of Sent across Week, grouped by Customer:

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

select Customer, delta(Sent, Customer) as SentDelta

Important: these functions operate on rows in their existing order, so the input data must be sorted by the <grouping field(s)> first and the <dimension field(s)> second. The dimension field is not passed to the function itself; it determines the row order the calculation walks through.

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 difference 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)

RANK

Rank of records, given a sorted dataset.

rank([<grouping field(s)>]);

select rank(), date

The above example returns the rank (increment by 1) of each row for a pre-sorted date order.

FILL_NULL

Forward-fills null values with the last non-null value seen, based on the current row order. Leading nulls (before any non-null value) remain null. Sort your data first so values are carried in the intended order.

FILL_NULL(<field>[, <grouping field(s)>])

select date, fill_null(status) as status

Provide grouping field(s) to fill each group independently, resetting at the start of each group:

select date, fill_null(status, customer) as status