ROLLINGMODE Function

Computes the rolling mode (most common value) forward or backward of the current row within the specified column. Input values can be Integer, Decimal, or Datetime data type.

If an input value is missing or null, it is not factored in the computation. For example, for the first row in the dataset, the rolling mode of 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.
If you are working on a randomly generated sample of your dataset, 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 parameters 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 Window transform. See Window Transform.

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

Wrangle vs. SQL: This function is part of Wrangle, a proprietary data transformation language. Wrangle is not SQL. For more information, see Wrangle Language.

Basic Usage

Column example:

rollingmode(myCol)

Output: Returns the rolling mode of all values in the myCol column.

Rows before example:

rollingmode(myNumber, 3)

Output: Returns the rolling mode of the current row and the three previous row values in the myNumber column.

Rows before and after example:

rollingmode(myNumber, 3, 2)

Output: Returns the rolling mode of the three previous row values, the current row value, and the two rows after the current one in the myNumber column.

Syntax and Arguments

rollingmode(col_ref, rowsBefore_integer, rowsAfter_integer) order: order_col [group: group_col]

Argument	Required?	Data Type	Description
col_ref	Y	string	Name of column whose values are applied to the function
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 Notes.

col_ref

Name of the column whose values are used to compute the function.

Nota

If the input is in Datetime type, the output is in unixtime format. You can wrap these outputs in the DATEFORMAT function to generate the results in the appropriate Datetime format. See DATEFORMAT Function.

Multiple columns and wildcards are not supported.

Usage Notes:

Required?	Data Type	Example Value
Yes	String (column reference to Integer or Decimal values)	myColumn

rowsBefore_integer, rowsAfter_integer

Integers representing the number of rows before or after the current one from which to compute the rolling function, including the current row. For example, if the first value is 5, the current row and the five rows before it are used in the computation. Negative values for rowsAfter_integer compute the rolling function from rows preceding the current one.

rowBefore=0 generates the current row value only.
rowBefore=-1 uses all rows preceding the current 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:

Required?	Data Type	Example Value
No	Integer	4

Examples

Dica

For additional examples, see Common Tasks.

Example - Counting most common coin flips

In the following table, 20 coin flips are tabulated. You want to capture a rolling evaluation of the most common value.

Source:

Turn	Result
1	heads
2	heads
3	tails
4	heads
5	heads
6	tails
7	tails
8	heads
9	tails
10	heads
11	tails
12	heads
13	tails
14	heads
15	heads
16	tails
17	tails
18	tails
19	heads
20	heads

Transformation:

To use the ROLLINGMODE function, the results need to be converted to numeric values:

Transformation Name	`Edit column with formula`
Parameter: Columns	Result
Parameter: Formula	if(Result == 'heads', 0, 1)

Now calculate the ROLLINGMODE for the preceding five values for each row:

Transformation Name	`New formula`
Parameter: Formula type	Multiple row formula
Parameter: Formula	rollingmode(Result, 4, 0)
Parameter: Sort rows by	Turn
Parameter: New column name	'mostCommonLast5'

You can now convert the binary values back to text information:

Transformation Name	`Edit column with formula`
Parameter: Columns	mostCommonLast5
Parameter: Formula	if(mostCommonLast5 == 0, 'heads-last5', 'tails-last5')

Results:

Turn	Result	mostCommonLast5
1	heads	heads-last5
2	heads	heads-last5
3	tails	heads-last5
4	heads	heads-last5
5	heads	heads-last5
6	tails	heads-last5
7	tails	tails-last5
8	heads	heads-last5
9	tails	tails-last5
10	heads	tails-last5
11	tails	tails-last5
12	heads	heads-last5
13	tails	tails-last5
14	heads	heads-last5
15	heads	heads-last5
16	tails	heads-last5
17	tails	tails-last5
18	tails	tails-last5
19	heads	tails-last5
20	heads	tails-last5

Nesta secção: