Date: Sat, 1 Oct 2022 02:29:57 +0000 (UTC) Message-ID: <474083089.5421.1664591397963@93e1396c9615> Subject: Exported From Confluence MIME-Version: 1.0 Content-Type: multipart/related; boundary="----=_Part_5420_693599777.1664591397963" ------=_Part_5420_693599777.1664591397963 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable Content-Location: file:///C:/exported.html ROLLINGVAR Function

# ROLLINGVAR Function

=20

Contents:

=20

=20
=20
=20

=20
=20

Computes the rolling variance of values forward or backward of the c= urrent row within the specified column.
• If an input value is missing or null, it is not factored in the computa= tion. For example, for the first row in the dataset, the rolling variance o= f previous values is undefined.
• The row from which to extract a value is determined by the order in = which the rows are organized based on the `order` parameter.&nbs= p;

If you are working on a randomly generated sample of your dat= aset, the values that you see for this function might not correspond to the= values that are generated on the full dataset during job execution.

• The function takes a column name and two optional integer pa= rameters that determine the window backward and forward of the current row.=
• The default integer parameter values are `-1` and=  `0`, which computes the rolling function from the current = row back to the first row of the dataset.
• This function works with the following transforms:

For more information on a non-rolling version of this function, see = ;VAR Function.

## Basic Usa= ge

=20

Column example:

=20

derive type:single value:ROLLINGVAR(myCol)

Output: Generates a new column containing the roll= ing variance of all values in the `myCol` column from = the first row of the dataset to the current one.

Rows before example:

=20

window value:ROLLINGVAR(myNumber, 100)

Output: Generates the new column, which contains the ro= lling variance of the current row and the 100 previous row values in the myNumber column.

Rows before and after example:

=20

window value:ROLLINGVAR(myNumber, 3, 2) <= /p>

Output: Generates the new column, which contains the rolling variance of the three previous row values, t= he current row value, and the two rows after the current one in the myNumber column.

## = Syntax and Arguments

=20

=20

window value:ROLLINGVAR(col_ref, ro= wsBefore_integer, rowsAfter_integer) order: order_col [group: group_c= ol]

=20 =20 =20 =20 =20
Argument Required? Data Type Description
col_ref Y string Name of column whose values are applied to the f= unction
rowsBefore_integer N integer Number of rows before the current one to include= in the computation
rowsAfter_integer N integer Number of rows after the current one to include = in the computation

For more information on the `order` and ```= group``` parameters, see Window Transform.

For more information on syntax standards, see Language Documentation Syntax Note= s.

### col_ref

Name of the column whose values are used to compute the function. <= /p>

• Multiple columns and wildcards are not supported.

Usage Notes:

=20

=20 =20 =20 =20
Required? Data Type Example Value
Yes String (column reference to Integer or Decimal v= alues) `myColumn`

### ro= wsBefore_integer, rowsAfter_integer

Integers representing the number of rows before or after the curre= nt one from which to compute the rolling function, including the current ro= w. For example, if the first value is `5`, the current row = and the five rows before it are used in the computation. Negati= ve values for `k` compute the rolling average from row= s preceding the current one.

• `rowBefore=3D0` generates the current row value only.
• `rowBefore=3D-1`  uses all rows preceding the cur= rent one.
• If `rowsAfter` is not specified, then the value 0 is applied.
• If a `group` parameter is applied, then these parameter= values should be no more than the maximum number of rows in the groups.

Usage Notes:

=20

=20 =20 =20 =20
Required? Data Type Example Value
No Integer `4`

## Examples=

=20
=20
=20

Tip: For additional examples, see Common Tasks.

=20

### Ex= ample - Rolling computations for racing splits

This example describes how to use the rolling computational funct= ions:

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 cu= mulative time in seconds (`time_sc`). You can use ROLLING and ot= her 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

Transform:

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

=20

settype col: lap,quarter type: 'String' <= /p>=20

derive type:single value: MERGE(['l= ',lap,'q',quarter]) as: 'splitId'

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

=20

derive type:single value: ROUND(tim= e_sc - PREV(time_sc, 1), 3) order: splitId as: 'split_time_sc'

Compute rolling computations: You can use the foll= owing types of computations to provide rolling metrics on the current and t= hree previous splits:

=20

derive type:single value: ROLLINGAV= ERAGE(split_time_sc, 3) order: splitId as: 'ravg'

=20

derive type:single value: ROLLINGMA= X(split_time_sc, 3) order: splitId as: 'rmax'

=20

derive type:single value: ROLLINGMI= N(split_time_sc, 3) order: splitId as: 'rmin'

=20

derive type:single value: ROUND(ROL= LINGSTDEV(split_time_sc, 3), 3) order: splitId as: 'rstdev'

=20

derive type:single value: ROUND(ROL= LINGVAR(split_time_sc, 3), 3) order: splitId as: 'rvar'

Results:

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

lap quarter splitId time_sc split_time_sc 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.029 0.169 20.096 20.434 20.265
1 3 l1q3 61.031 20.501 0.031 0.177 20.096 20.501 20.344
2 0 l2q0 81.087 20.056 0.039 0.198 20.056 20.501 20.272
2 1 l2q1 101.383 20.296 0.029 0.17 20.056 20.501 20.322
2 2 l2q2 122.092 20.709 0.059 0.242 20.056 20.709 20.39
2 3 l2q3 141.886 19.794 0.113 0.337 19.794 20.709 20.214
3 0 l3q0 162.581 20.695 0.139 0.373 19.794 20.709 20.373
3 1 l3q1 183.018 20.437 0.138 0.371 19.794 20.709 20.409
3 2 l3q2 203.493 20.475 0.113 0.336 19.794 20.695 20.35
3 3 l3q3 222.893 19.4 0.252 0.502 19.4 20.695 20.252

You can reduce the number of steps by applying a ```wind= ow``` transform such as the following:

=20

window value: window1 =3D lap,rollingaverage(spl= it_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

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

=20
=20

=20
=20

=20

=20
• =20
Page:<= /span>=20
=20
• =20
• =20
Page:<= /span>=20
=20
• =20
• =20
Page:<= /span>=20
=20
• =20
• =20
Page:<= /span>=20
=20
• =20
• =20
Page:<= /span>=20
=20
• =20
• =20
Page:<= /span>=20
=20
• =20
• =20
Page:<= /span>=20
=20
• =20
• =20
Page:<= /span>=20
=20
• =20
• =20
Page:<= /span>=20
=20
• =20
• =20
Page:<= /span>=20
=20
• =20
=20

=20
=20
=20

------=_Part_5420_693599777.1664591397963--