Window functions to add formula columns

Window functions to add formula columns



A window function is used to perform calculations across a group of rows on a table. This set of rows is termed a window. You can use window functions to perform summations and calculations based on a rolling window of data, relative to the current row.

In contrast to a normal aggregate function which is applied on a row or multiple rows to return a single output, the window function even when applied over a set of rows (a window), does not literally group all the rows in its output. It retains the rows in the result as shown in the image below.




Learn more about window functions with an example from this video.



List of window functions in DataPrep

Rolling Sum

rolling_sum ( col1 , rowsbefore , rowsafter )

Returns the rolling sum value from a window of rows, consisting of a number of rows before and after the current row.

Parameters
Name
Description

col1

Decimal

Specifies the source column. This parameter is mandatory and must be a numeric value from a column or an expression that returns a number.

rowsbefore

Number

[Optional] Specifies the number of rows before the current row. The default value is -1 which includes all the rows before the current row.

rowsafter

Number

[Optional] Specifies the number of rows after the current row. The default value is 0 which includes the current row.
Example
Function
Sort rows by
Group rows by
rolling_sum(`Price`, 1, 1)
Date Product
Input
Date Product Price
10/04/2012 Stationery 100
22/01/2012 Stationery 200
21/02/2012 Groceries 120
12/05/2012 Groceries 100
15/06/2012 Groceries 110
Output
Date Product
Price
Rolling_sum
21/02/2012 Groceries 120 220
12/05/2012 Groceries 100 330
15/06/2012 Groceries 110 210
22/01/2012
Stationery
200
300
10/04/2012
Stationery
100
300
Note : Optional parameters can be skipped and the default values will be auto-applied. For example: rolling_sum(`Price ` ) is the same as rolling_sum(`Price ` , -1, 0).


Rolling Average


rolling_avg ( col1 , rowsbefore , rowsafter )

Returns the rolling average value from a window of rows consisting of a number of rows before and after the current row.

Parameters
ro
Name
Description

col1

Decimal

Specifies the source column. This parameter is mandatory and must be a numeric value from a column or an expression that returns a number.

rowsbefore

Number

[Optional] Specifies the number of rows before the current row. The default value is -1 which includes all the rows before the current row.

rowsafter

Number

[Optional] Specifies the number of rows after the current row. The default value is 0 which includes the current row.
Example
Function
Sort rows by
Group rows by
rolling_avg(`Price` , 1 , 1)
Date Product
Input
Date Product Price
10/04/2012 Stationery 100
22/01/2012 Stationery 200
21/02/2012 Groceries 120
   12/05/2012
   Groceries
  100
   15/06/2012
   Groceries
  110
Output
Date Product Price Rolling_avg
21/02/2012 Groceries 120 110
12/05/2012 Groceries 100
110
15/06/2012 Groceries 110 105
   22/01/2012
   Stationery
   200
   150
   10/04/2012
   Stationery
   100
   150
Note : Optional parameters can be skipped and the default values will be auto-applied. For example: rolling_avg(`Price`) is the same as rolling_avg(`Price`, -1, 0).


Rolling Minimum

rolling_min ( col1 , rowsbefore , rowsafter )

Returns the rolling minimum value from a window of rows consisting of a number of rows before and after the current row

Parameters
Name
Description

col1

Decimal

Specifies the source column. This parameter is mandatory and must be a numeric value from a column or an expression that returns a number.

rowsbefore

Number

[Optional] Specifies the number of rows before the current row. The default value is -1 which includes all the rows before the current row.

rowsafter

Number

[Optional] Specifies the number of rows after the current row. The default value is 0 which includes the current row.
Example
Function
Sort rows by
Group rows by
rolling_min(`Price` , 1 , 1)
Date Product
Input
Date Product Price
10/04/2012 Stationery 100
22/01/2012 Stationery 200
21/02/2012 Groceries 120
   12/05/2012
   Groceries
   100
   15/06/2012
   Groceries
   110
Output
Date Product Price Rolling_min
21/02/2012 Groceries 120
100
12/05/2012 Groceries 100
100
15/06/2012 Groceries 110 100
   22/01/2012
   Stationery
   200
  100
   10/04/2012
   Stationery
   100
  100
Note : Optional parameters can be skipped and the default values will be auto-applied. For example: rolling_min(`Price `) is the same as rolling_min(`Price`, -1, 0).

Rolling Maximum

rolling_max ( col1 , rowsbefore , rowsafter )

Returns the rolling maximum value from a window of rows consisting of a number of rows before and after the current row.

Parameters
Name
Description

col1

Decimal

Specifies the source column. This parameter is mandatory and must be a numeric value from a column or an expression that returns a number.

rowsbefore

Number

[Optional] Specifies the number of rows before the current row. The default value is -1 which includes all the rows before the current row.

rowsafter

Number

[Optional] Specifies the number of rows after the current row. The default value is 0 which includes the current row.
Example
Function
Sort rows by
Group rows by
rolling_max(`Price` , 1 , 1)
Date Product
Input
Date Product Price
10/04/2012 Stationery 100
22/01/2012 Stationery 200
21/02/2012 Groceries 120
   12/05/2012
   Groceries
   100
   15/06/2012
   Groceries
   110
Output
Date Product Price
Rolling_max
21/02/2012 Groceries 120
120
12/05/2012 Groceries 100 120
15/06/2012 Groceries 110 110
   22/01/2012
   Stationery
   200
   200
   10/04/2012
   Stationery
   100
   200
Note : Optional parameters can be skipped and the default values will be auto-applied. For example: rolling_max(`Price `) is the same as rolling_max(`Price`, -1, 0).

Rolling Standard Deviation

rolling_stddev ( col1 , rowsbefore , rowsafter )

Returns the rolling standard deviation value from a window of rows consisting of a number of rows before and after the current row.

Parameters
Name
Description

col1

Decimal

Specifies the source column. This parameter is mandatory and must be a numeric value from a column or an expression that returns a number.

rowsbefore

Number

[Optional] Specifies the number of rows before the current row. The default value is -1 which includes all the rows before the current row.

rowsafter

Number

[Optional] Specifies the number of rows after the current row. The default value is 0 which includes the current row.
Example
Function
Sort rows by
Group rows by
rolling_stddev(`Price` , 1 , 1)
Date Product
Input
Date Product Price
10/04/2012 Stationery 100
22/01/2012 Stationery 200
21/02/2012 Groceries 120
  12/05/2012
  Groceries
100
  15/06/2012
  Groceries
110
Output
Date Product Price Rolling_stddev
21/02/2012 Groceries 120
10
12/05/2012 Groceries 100 8.16496580927726
15/06/2012 Groceries 110
5
22/01/2012
Stationery
200
50
10/04/2012
Stationery
100
50
Note : Optional parameters can be skipped and the default values will be auto-applied. For example: rolling_stddev(` Price`) is the same as rolling_stddev(`Price`, -1, 0).

Rolling Variance

rolling_variance ( col1 , rowsbefore , rowsafter )

Returns the rolling variance value from a window of rows consisting of a number of rows before and after the current row.

Parameters
Name
Description

col1

Decimal

Specifies the source column. This parameter is mandatory and must be a numeric value from a column or an expression that returns a number.

rowsbefore

Number

[Optional] Specifies the number of rows before the current row. The default value is -1 which includes all the rows before the current row.

rowsafter

Number

[Optional] Specifies the number of rows after the current row. The default value is 0 which includes the current row.
Example
Function
Sort rows by
Group rows by
rolling_variance(`Price` , 1 , 1)
Date Product
Input
Date Product Price
10/04/2012 Stationery 100
22/01/2012 Stationery 200
21/02/2012 Groceries 120
  12/05/2012
  Groceries
   100
  15/06/2012
  Groceries
   110
Output
Date Product Price Rolling_variance
21/02/2012 Groceries 120
100
12/05/2012 Groceries 100
66.66666666666667
15/06/2012 Groceries 110 25
22/01/2012
Stationery
200
2500
10/04/2012
Stationery
100
2500
Note : Optional parameters can be skipped and the default values will be auto-applied. For example: rolling_variance(` Price`) is the same as rolling_variance(`Price`, -1, 0).


Rolling Count

rolling_count ( col1 , rowsbefore , rowsafter )

Returns the rolling count values (that are not null) from a window of rows consisting of a number of rows before and after the current row.

Parameters
Name
Description

col1

Decimal

Specifies the source column. This parameter is mandatory and must be a numeric value from a column or an expression that returns a number.

rowsbefore

Number

[Optional] Specifies the number of rows before the current row. The default value is -1 which includes all the rows before the current row.

rowsafter

Number

[Optional] Specifies the number of rows after the current row. The default value is 0 which includes the current row.
Example
Function
Sort rows by
Group rows by
rolling_count(`Price` , 1 , 1)
Date Product
Input
Date Product Price
10/04/2012 Stationery 100
22/01/2012 Stationery 200
21/02/2012 Groceries 120
  12/05/2012
   Groceries
  100
  15/06/2012
   Groceries
  110
Output
Date Product Price Rolling_count
21/02/2012 Groceries 120
2
12/05/2012 Groceries 100 3
15/06/2012 Groceries 110 2
   22/01/2012
   Stationery
   200
   2
   10/04/2012
   Stationery
   100
   2
Note : Optional parameters can be skipped and the default values will be auto-applied. For example: rolling_count(`Price `) is the same as rolling_count(`Price`, -1, 0).

Row Number

row_number ()

Returns the row number from a window of rows based on the sort by and group by conditions.

Example
Function
Sort rows by
Group rows by
row_number()
Date Product
Input
Date Product Price
10/04/2012 Stationery 100
22/01/2012 Stationery 200
21/02/2012 Groceries 120
   12/05/2012
  Groceries
  100
   15/06/2012
  Groceries
  110
Output
Date Product Price Row_number
21/02/2012 Groceries 120
1
12/05/2012 Groceries 100 2
15/06/2012 Groceries 110
3
  22 /01/2012
  Stationery
   200
   1
  10/04/2012
  Stationery
   100
   2

Fill

fill ( col1 , fillEmpty , rowsbefore , rowsafter )

Returns the values from the column with empty cells filled by the closest non-empty value from the preceding rows within the window of rows consisting of a number of rows before and after the current row.

Parameters
Name
Description

col1

Decimal

Specifies the source column. This parameter is mandatory and must be a numeric value from a column or an expression that returns a number.

fillEmpty

Boolean

Specify true if the empty rows should be filled, false otherwise.

rowsbefore

Number

[Optional] Specifies the number of rows before the current row. The default value is -1 which includes all the rows before the current row.

rowsafter

Number

[Optional] Specifies the number of rows after the current row. The default value is 0 which includes the current row.
Example
Function
Sort rows by
Group rows by
fill(`Price`, false, 1 , 1)
Date Product
Input
Date Product Price
10/04/2012 Stationery 100
22/01/2012 Groceries
21/02/2012 Groceries
   12/05/2012
  Groceries
  120
   15/06/2012
  Groceries
  120
Output
Date Product Price Fill
22/01/2012 Groceries

21/02/2012 Groceries
120
12/05/2012 Groceries 120 120
   15/06/2012
   Groceries
  120
   120
   10/04/2012
   Stationery
  100
   100
Note : Optional parameters can be skipped and the default values will be auto-applied. For example: fill(`Price`, false) is the same as fill(`Price`, false, -1, 0).


Lag

lag ( col1 , rowsbefore )

Returns the value at a specified number of rows preceding the current row from a window of rows in the column.

Parameters
Name
Description

col1

Text

Specifies the source column. It is mandatory and can be of a string type column.

rowsbefore

Number

Specifies the number of rows before the current row.
Example
Function
Group rows by
lag(`Price` , 1)
Product
Input
Date Product Price
10/04/2012 Stationery 100
22/01/2012 Stationery 200
21/02/2012 Groceries 120
  12/05/2012
   Groceries
  100
  15/06/2012
   Groceries
  110
Output
Date Product Price Lag
21/02/2012 Groceries 120

12/05/2012 Groceries 100 120
15/06/2012 Groceries 110 100
   22/01/2012
   Stationery
   200

   10/04/2012
   Stationery
   100
   200


Lead

lead ( col1 , rowsafter )

Returns the value at a specified number of rows after the current row from a window of rows in the column.

Parameters
Name
Description

col1

Text

Specifies the source column. It is mandatory and can be of a string type column.

rowsafter

Number

Specifies the number of rows after the current row.
Example
Function
Sort rows by
Group rows by
lead(`Price` , 1)
Date Product
Input
Date Product Price
10/04/2012 Stationery 100
22/01/2012 Stationery 200
21/02/2012 Groceries 120
   12/05/2012
  Groceries
  100
   15/06/2012
  Groceries
  110
Output
Date Product Price Lead
21/02/2012 Groceries 120
100
12/05/2012 Groceries 100 110
15/06/2012 Groceries 110

  22/01/2012
   Stationery
  200
  100
  10/04/2012
   Stationery
  100

Rank

rank ()

Returns the ranking from a window of rows based on the sort by and group by conditions. If there is a tie, the subsequent number of ranks are skipped in the following rows.

Example
Function
Sort rows by
Group rows by
rank()
Date Product
Input
Date Product Price
10/04/2012 Stationery 100
10/05/2012 Stationery 200
21/02/2012 Groceries 120
   21/02/2012
  Groceries
  100
  15/06/2012
  Groceries
  110
Output
Date Product Price Rank
21/02/2012 Groceries 120
1
21/02/2012 Groceries 100
1
15/06/2012 Groceries 110 3
   10/04/2012
   Stationery
   100
   1
   10/05/2012
   Stationery
   200
   2

Dense Rank

dense_rank ()

Returns the dense ranking from a window of rows based on the sort by and group by conditions. If there is a tie, there is no rank that is skipped.

Example
Function
Sort rows by
Group rows by
dense_rank()
Date Product
Input
Date Product Price
10/04/2012 Stationery 100
10/05/2012 Stationery 200
21/02/2012 Groceries 120
  21/02/2012
  Groceries
  100
  15/06/2012
  Groceries
  110
Output
Date Product Price Dense Rank
21/02/2012 Groceries 120
1
21/02/2012 Groceries 100
1
15/06/2012 Groceries 110 2
   10/04/2012
  Stationery
   100
   1
   10/05/2012
  Stationery
   200
   2

Cumulative Distribution

cumulative_distribution ()

Returns the cumulative distribution values from a window of rows.

Example
Function
Sort rows by
Group rows by
cumulative_distribution()
Date Product
Input
Date Product Price
10/04/2012 Stationery 100
22/01/2012 Stationery 200
21/02/2012 Groceries 120
12/05/2012
Groceries
100
15/06/2012
Groceries
110
Output
Date Product Price Cumulative Distribution
21/02/2012 Groceries 120
0.333333333333333
12/05/2012 Groceries 100
0.666666666666667
15/06/2012 Groceries 110 1
22/01/2012
Stationery
200
0.5
10/04/2012
Stationery
100
1
SEE ALSO