EXAMPLE - Rolling Functions 2

This example describes how to use the rolling computational functions:

• ROLLINGAVERAGE - computes a rolling average from a window of rows before and after the current row. See ROLLINGAVERAGE Function.
• ROLLINGMIN - computes a rolling minimum from a window of rows. See ROLLINGMIN Function.
• ROLLINGMAX - computes a rolling maximum from a window of rows.  See ROLLINGMAX Function.
• ROLLINGSTDEV - computes a rolling standard deviation from a window of rows. See ROLLINGSTDEV Function.
• ROLLINGVAR - computes a rolling variance from a window of rows. See ROLLINGVAR Function.
• ROLLINGSTDEVSAMP - computes a rolling standard deviation from a window of rows using the sample method of statistical calculation. See ROLLINGSTDEVSAMP Function.
• ROLLINGVARSAMP - computes a rolling variance from a window of rows using the sample method of statistical calculation. See ROLLINGVARSAMP Function.

Source:

In this example, the following data comes from times recorded at regular intervals during a three-lap race around a track. The source data is in cumulative time in seconds (time_sc). You can use ROLLING and other windowing functions to break down the data into more meaningful metrics.

Transformation:

Primary key: Since the quarter information repeats every lap, there is no unique identifier for each row. The following steps create this identifier:

Get split times: Use the following transform to break down the splits for each quarter of the race:

Compute rolling computations: You can use the following types of computations to provide rolling metrics on the current and three previous splits:

Compute rolling computations using sample method: These metrics compute the rolling STDEV and VAR on the current and three previous splits using the sample method:

Results:

When the above transforms have been completed, the results look like the following:

You can reduce the number of steps by applying a window transform such as the following:

However, you must rename all of the generated windowX columns.