For example, you can compute the rolling average for a specified column for the current row value and the three preceding rows. This transform is particularly useful for processing time or otherwise sequential data.
window transform enables you to perform summations and calculations based on a rolling window of data relative to the current row.
You can apply one or more functions to your
window transform step.
NOTE: Be careful applying this transform across a large number of rows. In some cases, the application can run out of memory generating the results, and your results can fail.
|window value: ROLLINGAVERAGE(myValues,3) order: MyDate group: customerId|
Output: Generates a new column called,
window, which contains the result of the
ROLLINGAVERAGE function applied from the current row in the
myValues column across the
3 rows forward, ordered by
MyDate and grouped by
window value: WINDOW_FUNCTION(arg1,arg2) order: order_col [group: group_col]
|Parameter||Required?||Transform Builder||Data Type||Description|
|value||Y||Formula||string||Expression that evaluates to the window function call and its parameters|
|order||Y||Order by||string||Column or column names by which to sort the dataset before the |
value expression is applied
|group||N||Group by||string||Column name or names containing the values by which to group for calculation|
window transform, the
value parameter contains the function call or calls, which define the set of rows to which the function is applied.
You can specify multiple window functions for the value. Each function reference must be separated by a comma. The transform generates a new column for each window function.
This transform uses a special set of functions. For more information on the available functions, see Window Functions.
window transform, this parameter specifies the column on which to sort the dataset before applying the specified function. For combination sort keys, you can add multiple comma-separated columns.
order parameter only applies to transform steps containing a window function or to the
sort transform. For steps that contain a transform supporting an
order parameter with a non-window function, the
order parameter does nothing.
NOTE: If you are applying a window function, it requires a primary key to identify rows in the output. Otherwise, results can be ambiguous. For more information on defining a primary key, see Window Functions.
NOTE: If it is present, the dataset is first grouped by the
group value before it is ordered by the values in the
order column does not need to be sorted before the
window transform is executed on it.
Tip: To sort in reverse order, prepend the column name with a dash (
|Yes||String (column name)|
window transform, this parameter specifies the column whose values are used to group the dataset prior to applying the specified function. For combination grouping, you can specify multiple comma-separated column names.
NOTE: Be careful applying this transform across groups containing a large number of rows. In some cases, the application can run out of memory generating the results, and your results can fail.
|No||String (column name)|
See the individual functions for examples. See Window Functions.
|D s also|