Page tree

Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.
Comment: Published by Scroll Versions from space DEV and version r081


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.

lapquartertime_sc
100.000
1119.554
1239.785
1360.021
2080.950
21101.785
22121.005
23141.185
30162.008
31181.887
32200.945
33220.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
RawWrangletrue
Typestep
WrangleTextsettype col: lap,quarter type: String
p01NameColumns
p01Valuelap,quarter
p02NameNew type
p02ValueString
SearchTermChange column data type

D trans
RawWrangletrue
p03Value'splitId'
Typestep
WrangleTextderive type:single value: MERGE(['l',lap,'q',quarter]) as: 'splitId'
p01NameFormula type
p01ValueSingle row formula
p02NameFormula
p02ValueMERGE(['l',lap,'q',quarter])
p03NameNew column name
SearchTermNew formula

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

D trans
RawWrangletrue
p03ValuesplitId
Typestep
WrangleTextderive type:single value: ROUND(time_sc - PREV(time_sc, 1), 3) order: splitId as: 'split_time_sc'
p01NameFormula type
p01ValueMultiple row formula
p02NameFormula
p02ValueROUND(time_sc - PREV(time_sc, 1), 3)
p03NameOrder rows by
p04Value'split_time_sc'
p04NameNew column name
SearchTermNew 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
RawWrangletrue
p03ValuesplitId
Typestep
WrangleTextderive type:single value: ROLLINGAVERAGE(split_time_sc, 3) order: splitId as: 'ravg'
p01NameFormula type
p01ValueMultiple row formula
p02NameFormula
p02ValueROLLINGAVERAGE(split_time_sc, 3)
p03NameOrder rows by
p04Value'ravg'
p04NameNew column name
SearchTermNew formula

D trans
RawWrangletrue
p03ValuesplitId
Typestep
WrangleTextderive type:single value: ROLLINGMAX(split_time_sc, 3) order: splitId as: 'rmax'
p01NameFormula type
p01ValueMultiple row formula
p02NameFormula
p02ValueROLLINGMAX(split_time_sc, 3)
p03NameOrder rows by
p04Value'rmax'
p04NameNew column name
SearchTermNew formula

D trans
RawWrangletrue
p03ValuesplitId
Typestep
WrangleTextderive type:single value: ROLLINGMIN(split_time_sc, 3) order: splitId as: 'rmin'
p01NameFormula type
p01ValueMultiple row formula
p02NameFormula
p02ValueROLLINGMIN(split_time_sc, 3)
p03NameOrder rows by
p04Value'rmin'
p04NameNew column name
SearchTermNew formula

D trans
RawWrangletrue
p03ValuesplitId
Typestep
WrangleTextderive type:single value: ROUND(ROLLINGSTDEV(split_time_sc, 3), 3) order: splitId as: 'rstdev'
p01NameFormula type
p01ValueMultiple row formula
p02NameFormula
p02ValueROUND(ROLLINGSTDEV(split_time_sc, 3), 3)
p03NameOrder rows by
p04Value'rstdev'
p04NameNew column name
SearchTermNew formula

D trans
RawWrangletrue
p03ValuesplitId
Typestep
WrangleTextderive type:single value: ROUND(ROLLINGVAR(split_time_sc, 3), 3) order: splitId as: 'rvar'
p01NameFormula type
p01ValueMultiple row formula
p02NameFormula
p02ValueROUND(ROLLINGVAR(split_time_sc, 3), 3)
p03NameOrder rows by
p04Value'rvar'
p04NameNew column name
SearchTermNew 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
RawWrangletrue
p03ValuesplitId
Typestep
WrangleTextderive type:single value: ROUND(ROLLINGSTDEVSAMP(split_time_sc, 3), 3) order: splitId as: 'rstdev_samp'
p01NameFormula type
p01ValueMultiple row formula
p02NameFormula
p02ValueROUND(ROLLINGSTDEVSAMP(split_time_sc, 3), 3)
p03NameOrder rows by
p04Value'rstdev_samp'
p04NameNew column name
SearchTermNew formula

D trans
RawWrangletrue
p03ValuesplitId
Typestep
WrangleTextderive type:single value: ROUND(ROLLINGVARSAMP(split_time_sc, 3), 3) order: splitId as: 'rvar_samp'
p01NameFormula type
p01ValueMultiple row formula
p02NameFormula
p02ValueROUND(ROLLINGVARSAMP(split_time_sc, 3), 3)
p03NameOrder rows by
p04Value'rvar_samp'
p04NameNew column name
SearchTermNew formula

Results:

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

lapquartersplitIdtime_scsplit_time_scrvar_samprstdev_samprvarrstdevrminrmaxravg
10l1q00 

     
11l1q120.09620.096

0020.09620.09620.096
12l1q240.5320.4340.2290.4790.0290.16920.09620.43420.265
13l1q361.03120.5010.1540.3920.0310.17720.09620.50120.344
20l2q081.08720.0560.3150.5610.0390.19820.05620.50120.272
21l2q1101.38320.2960.1420.3760.0290.1720.05620.50120.322
22l2q2122.09220.7090.6170.7860.0590.24220.05620.70920.39
23l2q3141.88619.7940.6210.7880.1130.33719.79420.70920.214
30l3q0162.58120.6950.5790.7610.1390.37319.79420.70920.373
31l3q1183.01820.4370.4430.6660.1380.37119.79420.70920.409
32l3q2203.49320.4750.5370.7330.1130.33619.79420.69520.35
33l3q3222.89319.40.5200.7210.2520.50219.420.69520.252

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

D trans
RawWrangletrue
p03Valuerollingmax(split_time_sc, 0, 3)
p06NameFormula6
p09Valuelap
WrangleTextwindow 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
p01NameFormula1
p06Valueround(rollingvar(split_time_sc, 0, 3), 3)
p10Valuelap
p03NameFormula3
p07Valueround(rollingstdevsamp(split_time_sc, 0, 3), 3)
p04Valuerollingmin(split_time_sc, 0, 3)
SearchTermWindow
p07NameFormula7
p09NameGroup by
Typestep
p05NameFormula5
p01Valuelap
p02NameFormula2
p02Valuerollingaverage(split_time_sc, 0, 3)
p05Valueround(rollingstdev(split_time_sc, 0, 3), 3)
p10NameOrder by
p04NameFormula4
p08Valueround(rollingvarsamp(split_time_sc, 0, 3), 3)
p08NameFormula8

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