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.
| lap | quarter | time_sc |
|---|
| 1 | 0 | 0.000 |
| 1 | 1 | 19.554 |
| 1 | 2 | 39.785 |
| 1 | 3 | 60.021 |
| 2 | 0 | 80.950 |
| 2 | 1 | 101.785 |
| 2 | 2 | 121.005 |
| 2 | 3 | 141.185 |
| 3 | 0 | 162.008 |
| 3 | 1 | 181.887 |
| 3 | 2 | 200.945 |
| 3 | 3 | 220.856 |
Transformation:
Primary key: Since the quarter information repeats every lap, there is no unique identifier for each row. The following steps create this identifier:
| D trans |
|---|
| RawWrangle | true |
|---|
| Type | step |
|---|
| WrangleText | settype col: lap,quarter type: String |
|---|
| p01Name | Columns |
|---|
| p01Value | lap,quarter |
|---|
| p02Name | New type |
|---|
| p02Value | String |
|---|
| SearchTerm | Change column data type |
|---|
|
| D trans |
|---|
| RawWrangle | true |
|---|
| p03Value | 'splitId' |
|---|
| Type | step |
|---|
| WrangleText | derive type:single value: MERGE(['l',lap,'q',quarter]) as: 'splitId' |
|---|
| p01Name | Formula type |
|---|
| p01Value | Single row formula |
|---|
| p02Name | Formula |
|---|
| p02Value | MERGE(['l',lap,'q',quarter]) |
|---|
| p03Name | New column name |
|---|
| SearchTerm | New formula |
|---|
|
Get split times: Use the following transform to break down the splits for each quarter of the race:
| D trans |
|---|
| RawWrangle | true |
|---|
| p03Value | splitId |
|---|
| Type | step |
|---|
| WrangleText | derive type:single value: ROUND(time_sc - PREV(time_sc, 1), 3) order: splitId as: 'split_time_sc' |
|---|
| p01Name | Formula type |
|---|
| p01Value | Multiple row formula |
|---|
| p02Name | Formula |
|---|
| p02Value | ROUND(time_sc - PREV(time_sc, 1), 3) |
|---|
| p03Name | Order rows by |
|---|
| p04Value | 'split_time_sc' |
|---|
| p04Name | New column name |
|---|
| SearchTerm | New formula |
|---|
|
Compute rolling computations: You can use the following types of computations to provide rolling metrics on the current and three previous splits:
| D trans |
|---|
| RawWrangle | true |
|---|
| p03Value | splitId |
|---|
| Type | step |
|---|
| WrangleText | derive type:single value: ROLLINGAVERAGE(split_time_sc, 3) order: splitId as: 'ravg' |
|---|
| p01Name | Formula type |
|---|
| p01Value | Multiple row formula |
|---|
| p02Name | Formula |
|---|
| p02Value | ROLLINGAVERAGE(split_time_sc, 3) |
|---|
| p03Name | Order rows by |
|---|
| p04Value | 'ravg' |
|---|
| p04Name | New column name |
|---|
| SearchTerm | New formula |
|---|
|
| D trans |
|---|
| RawWrangle | true |
|---|
| p03Value | splitId |
|---|
| Type | step |
|---|
| WrangleText | derive type:single value: ROLLINGMAX(split_time_sc, 3) order: splitId as: 'rmax' |
|---|
| p01Name | Formula type |
|---|
| p01Value | Multiple row formula |
|---|
| p02Name | Formula |
|---|
| p02Value | ROLLINGMAX(split_time_sc, 3) |
|---|
| p03Name | Order rows by |
|---|
| p04Value | 'rmax' |
|---|
| p04Name | New column name |
|---|
| SearchTerm | New formula |
|---|
|
| D trans |
|---|
| RawWrangle | true |
|---|
| p03Value | splitId |
|---|
| Type | step |
|---|
| WrangleText | derive type:single value: ROLLINGMIN(split_time_sc, 3) order: splitId as: 'rmin' |
|---|
| p01Name | Formula type |
|---|
| p01Value | Multiple row formula |
|---|
| p02Name | Formula |
|---|
| p02Value | ROLLINGMIN(split_time_sc, 3) |
|---|
| p03Name | Order rows by |
|---|
| p04Value | 'rmin' |
|---|
| p04Name | New column name |
|---|
| SearchTerm | New formula |
|---|
|
| D trans |
|---|
| RawWrangle | true |
|---|
| p03Value | splitId |
|---|
| Type | step |
|---|
| WrangleText | derive type:single value: ROUND(ROLLINGSTDEV(split_time_sc, 3), 3) order: splitId as: 'rstdev' |
|---|
| p01Name | Formula type |
|---|
| p01Value | Multiple row formula |
|---|
| p02Name | Formula |
|---|
| p02Value | ROUND(ROLLINGSTDEV(split_time_sc, 3), 3) |
|---|
| p03Name | Order rows by |
|---|
| p04Value | 'rstdev' |
|---|
| p04Name | New column name |
|---|
| SearchTerm | New formula |
|---|
|
| D trans |
|---|
| RawWrangle | true |
|---|
| p03Value | splitId |
|---|
| Type | step |
|---|
| WrangleText | derive type:single value: ROUND(ROLLINGVAR(split_time_sc, 3), 3) order: splitId as: 'rvar' |
|---|
| p01Name | Formula type |
|---|
| p01Value | Multiple row formula |
|---|
| p02Name | Formula |
|---|
| p02Value | ROUND(ROLLINGVAR(split_time_sc, 3), 3) |
|---|
| p03Name | Order rows by |
|---|
| p04Value | 'rvar' |
|---|
| p04Name | New column name |
|---|
| SearchTerm | New formula |
|---|
|
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:
| D trans |
|---|
| RawWrangle | true |
|---|
| p03Value | splitId |
|---|
| Type | step |
|---|
| WrangleText | derive type:single value: ROUND(ROLLINGSTDEVSAMP(split_time_sc, 3), 3) order: splitId as: 'rstdev_samp' |
|---|
| p01Name | Formula type |
|---|
| p01Value | Multiple row formula |
|---|
| p02Name | Formula |
|---|
| p02Value | ROUND(ROLLINGSTDEVSAMP(split_time_sc, 3), 3) |
|---|
| p03Name | Order rows by |
|---|
| p04Value | 'rstdev_samp' |
|---|
| p04Name | New column name |
|---|
| SearchTerm | New formula |
|---|
|
| D trans |
|---|
| RawWrangle | true |
|---|
| p03Value | splitId |
|---|
| Type | step |
|---|
| WrangleText | derive type:single value: ROUND(ROLLINGVARSAMP(split_time_sc, 3), 3) order: splitId as: 'rvar_samp' |
|---|
| p01Name | Formula type |
|---|
| p01Value | Multiple row formula |
|---|
| p02Name | Formula |
|---|
| p02Value | ROUND(ROLLINGVARSAMP(split_time_sc, 3), 3) |
|---|
| p03Name | Order rows by |
|---|
| p04Value | 'rvar_samp' |
|---|
| p04Name | New column name |
|---|
| SearchTerm | New formula |
|---|
|
Results:
When the above transforms have been completed, the results look like the following:
| lap | quarter | splitId | time_sc | split_time_sc | rvar_samp | rstdev_samp | rvar | rstdev | rmin | rmax | ravg |
|---|
| 1 | 0 | l1q0 | 0 | |
|
| | | | | |
| 1 | 1 | l1q1 | 20.096 | 20.096 |
|
| 0 | 0 | 20.096 | 20.096 | 20.096 |
| 1 | 2 | l1q2 | 40.53 | 20.434 | 0.229 | 0.479 | 0.029 | 0.169 | 20.096 | 20.434 | 20.265 |
| 1 | 3 | l1q3 | 61.031 | 20.501 | 0.154 | 0.392 | 0.031 | 0.177 | 20.096 | 20.501 | 20.344 |
| 2 | 0 | l2q0 | 81.087 | 20.056 | 0.315 | 0.561 | 0.039 | 0.198 | 20.056 | 20.501 | 20.272 |
| 2 | 1 | l2q1 | 101.383 | 20.296 | 0.142 | 0.376 | 0.029 | 0.17 | 20.056 | 20.501 | 20.322 |
| 2 | 2 | l2q2 | 122.092 | 20.709 | 0.617 | 0.786 | 0.059 | 0.242 | 20.056 | 20.709 | 20.39 |
| 2 | 3 | l2q3 | 141.886 | 19.794 | 0.621 | 0.788 | 0.113 | 0.337 | 19.794 | 20.709 | 20.214 |
| 3 | 0 | l3q0 | 162.581 | 20.695 | 0.579 | 0.761 | 0.139 | 0.373 | 19.794 | 20.709 | 20.373 |
| 3 | 1 | l3q1 | 183.018 | 20.437 | 0.443 | 0.666 | 0.138 | 0.371 | 19.794 | 20.709 | 20.409 |
| 3 | 2 | l3q2 | 203.493 | 20.475 | 0.537 | 0.733 | 0.113 | 0.336 | 19.794 | 20.695 | 20.35 |
| 3 | 3 | l3q3 | 222.893 | 19.4 | 0.520 | 0.721 | 0.252 | 0.502 | 19.4 | 20.695 | 20.252 |
You can reduce the number of steps by applying a window transform such as the following:
| D trans |
|---|
| RawWrangle | true |
|---|
| p03Value | rollingmax(split_time_sc, 0, 3) |
|---|
| p06Name | Formula6 |
|---|
| p09Value | lap |
|---|
| WrangleText | window value: window1 = lap,rollingaverage(split_time_sc, 0, 3), rollingmax(split_time_sc, 0, 3),rollingmin(split_time_sc, 0, 3),round(rollingstdev(split_time_sc, 0, 3), 3),round(rollingvar(split_time_sc, 0, 3), 3) group: lap order: lap |
|---|
| p01Name | Formula1 |
|---|
| p06Value | round(rollingvar(split_time_sc, 0, 3), 3) |
|---|
| p10Value | lap |
|---|
| p03Name | Formula3 |
|---|
| p07Value | round(rollingstdevsamp(split_time_sc, 0, 3), 3) |
|---|
| p04Value | rollingmin(split_time_sc, 0, 3) |
|---|
| SearchTerm | Window |
|---|
| p07Name | Formula7 |
|---|
| p09Name | Group by |
|---|
| Type | step |
|---|
| p05Name | Formula5 |
|---|
| p01Value | lap |
|---|
| p02Name | Formula2 |
|---|
| p02Value | rollingaverage(split_time_sc, 0, 3) |
|---|
| p05Value | round(rollingstdev(split_time_sc, 0, 3), 3) |
|---|
| p10Name | Order by |
|---|
| p04Name | Formula4 |
|---|
| p08Value | round(rollingvarsamp(split_time_sc, 0, 3), 3) |
|---|
| p08Name | Formula8 |
|---|
|
However, you must rename all of the generated windowX columns.
