Window Functions

WINDOW functions provide a set of functions for data analysis in a TableSheet. The functions perform calculations across the window (a set of table rows) that are somehow related to the current row and generate a column to display the results. The row for which function evaluation occurs is called the current row.

A window function performs aggregate, ranking, and analytic functions over a particular window and produces a result for each row.

Notes:

• The results of window functions may have an impact on the row order of the TableSheet.

  If the TableSheet has been sorted or grouped the result will be constant.

• When there is a tree structure in the hierarchical data of TableSheet, the window function is ineffective.

• The TableSheet filter executes after the window function is evaluated and filters only the specific rows.

WINDOW can only be used with window functions. By default, it considers the entire row as a window.

Syntax

WINDOW(window_function, [partitionby_function], [orderby_function], [frame_function])

Arguments

This function has the following arguments:

WINDOW

The WINDOW function determines the partitioning, ordering, and limiting window before the associated window function is applied.

The WINDOW function changes the sequence of the rows using the PARTITIONBY and ORDERBY parameters.

Note that if multiple WINDOW functions are applied in a view, then the PARTITIONBY and ORDERBY of them must be the same because the order of the entire row will be reordered by the PARTITIONBY and ORDERBY parameters. Otherwise, entire rows will show the sequence from the last applied WINDOW.

Example

WINDOW(ROWNUMBER(), PARTITIONBY([country]))

To know more about all available window functions, please refer to the Window Functions List section.

PARTITIONBY

The PARTITIONBY function divides the row into partitions in ascending order and window functions are applied to each partition separately. If PARTITIONBY is not specified, the entire row will be treated as a window. PARTITIONBY must have one or more column expressions that can be field names or formulas.

Syntax

PARTITIONBY(field_function [, field_function [, ... ] ])

Arguments

This function has the following argument:

Example

WINDOW(SUM([Sales]), PARTITIONBY([Product], YEAR([@OrderDate])))

ORDERBY

The ORDERBY defines the logical order of the rows within each partition. It will affect the window specified and the calculation of the window functions. ORDERBY must have one or more column expressions that can be field names or formulas.

Syntax

ORDERBY(field_function [, field_function [, ... ] ])

Arguments

This function has the following argument:

You can also use ORDERASC and ORDERDESC to sort data in ascending or descending order. By default, the sort order is ORDERASC, and NULL values are treated as the lowest value.

Note:

• If ORDERBY is not specified, the window functions will use all rows in the partition that are equal to FRAMERANGE(-1, -1).

• If ORDERBY is specified and no FRAMEROWS/FRAMERANGE is specified, the FRAMERANGE(-1, [@]) will be the default range expression to limit the window for the calculation of the window functions.

Syntax

ORDERASC(field function)

ORDERDESC(field function)

Example

WINDOW(SUM([Sales]), ORDERBY([Product], ORDERDESC(QUARTER([@OrderDate]))))

FRAME

The FRAME function specifies start and end points to combine the rows to a window within the partition against the current row. The window functions will use the row set that is specified by the window. A window is closed with start, end, and current rows. A FRAME function can be defined as FRAMFRAMEROWS, FRAMERANGE, or FRAMEGROUPS.

FRAMEROWS

The FRAMEROWS function allows you to limit the rows of the window by specifying a nonnegative integer row count before and after the current row. The start or end row of the partition is used if one of the preceding or following row counts is outside the current partition bounds. The same result is obtained if the preceding or following row is specified as -1. However, if both are outside, no rows are returned.

Note: The default value is [@] if the second parameter is missing in the FRAMEROWS function.

Syntax

FRAMEROWS(preceding_function [, following_function ])

Arguments

This function has the following arguments:

Example

WINDOW(SUM([Sales]), PARTITIONBY([Product], YEAR([@OrderDate])), ORDERBY(QUARTER([@OrderDate])), FRAMEROWS([@-1], [@]))

FRAMERANGE

The FRAMERANGE function limits the range of the window by specifying a nonnegative number as the distance around the same values from peer rows which have the same value in the current row composed of the ORDERBY columns.

The FRAMERANGE function has two main parameters, each indicating the distance before and after the peer rows of the current row.

The frame range is a fully closed interval. It requires the ORDERBY to provide the first column with the numeric data type. However, if there is more than 1 ordered column, only -1 and [@] are accepted.

FRAMERANGE with ORDERBY

• FRAMERANGE requires ORDERBY to provide only one column with a numeric data type to retrieve the value in the current row to find the tied rows for [@].

• If ORDERBY is removed, the default frame expression is FRAMERANGE(-1, -1) even if the FRAMERANGE is specified.

• If ORDERBY contains more than one column, only -1 and [@] can be used in FRAMERANGE, and [@-/+n] will default to -1. The current tied rows may be defined by the combined column values.

FRAMERANGE with NULL

• If some of the values in the ordered column are NULL, the rows with NULL values will be arranged side by side at the top/bottom within the partition.

• If the values of current tied rows are NULL, [@+/-n] will be similar to [@+/-0] which is equal to [@].

• If the values of current tied rows are not NULL, the retrieving of [@+/-n] will stop to the rows with NULL.

Syntax

FRAMERANGE(preceding_function [, following_function ])

Arguments

This function has the following arguments:

Example

WINDOW(SUM([Sales]), PARTITIONBY([Product], YEAR([@OrderDate])), ORDERBY(QUARTER([@OrderDate])), FRAMERANGE([@-1], [@]))

The following image shows how to get the moving average revenue of each product and revenue trends using aggregate window functions.

FRAMEGROUPS

The FRAMEGROUPS function specifies the starting and ending boundaries based on the number of "groups" relative to the current group where a “group” refers to the row sets with equivalent values derived from the window ORDERBY.

A group count that is outside the bounds of a partition falls back to the start/end group and is specified if both preceding/following are –1 but if both are outside, none is returned.

Note: The default value is [@] if the second parameter is missing in the FRAMEGROUPS function.

Syntax

FRAMEGROUPS(BeginningExpression, [EndingExpression], [ExcludeMode])

Arguments

This function has the following arguments:

Example

WINDOW(SUM([Sales]), PARTITIONBY([Product], YEAR([@OrderDate])), ORDERBY(DATEPART([@OrderDate], \"Q\")), FRAMEGROUPS([@-1], [@]))

FRAMEGROUPS with ORDERBY

• The FRAMEGROUPS require the ORDERBY to provide the column to retrieve the value in the current row to find the tied rows for [@].

• If the ORDERBY is removed, the default frame expression is FRAMEGROUPS(-1, -1) even if the FRAME GROUPS is specified.

• If ORDERBY has more than one column, the current tied rows are defined according to the combined column values. For example, if you order by year, quarter, and month, the current tied rows are identified by those same columns.

FRAME with EXCLUDE Mode

The frame with exclude mode is the last parameter in the FRAME expression. It is an optional parameter and has four types.

EXCLUDE NO OTHERS

Indicates that “No rows are Excluded” and is represented by 0.

Example

WINDOW(SUM([Sales]), PARTITIONBY([Product], YEAR([@OrderDate])), ORDERBY(DATEPART([@OrderDate], \"Q\")), FRAMEGROUPS([@-1], [@], 1))

The following image shows how to get the monthly average revenue, nearly average revenue of each product and revenue trends using aggregate window functions with EXCLUDE mode.

EXCLUDE CURRENT ROW

Indicates that the current row is excluded, however, the other peers of the current row remain for FRAMEGROUPS and FRAMERANGE and are represented by 1.

Example

WINDOW(SUM([Sales]), PARTITIONBY([Product], YEAR([@OrderDate])), ORDERBY(DATEPART([@OrderDate], \"Q\")), FRAMEGROUPS([@-1], [@], 1))

The following image shows how to get the monthly average revenue, nearly average revenue of each product, and revenue trends using aggregate window functions with EXCLUDE mode.

EXCLUDE GROUP

Indicates that the current row and the peers are all excluded, even if it’s FRAMEROWS and is represented by 2.

Example

WINDOW(SUM([Sales]), PARTITIONBY([Product], YEAR([@OrderDate])), ORDERBY(DATEPART([@OrderDate], \"Q\")), FRAMEGROUPS([@-1], [@], 2))

The following image shows how to get the monthly average revenue, nearly average revenue of each product, and revenue trends using aggregate window functions with EXCLUDE mode.

EXCLUDE TIES

Indicates that the current row remains, however, the other peers are excluded and are represented by 3.

Example

WINDOW(SUM([Sales]), PARTITIONBY([Product], YEAR([@OrderDate])), ORDERBY(DATEPART([@OrderDate], \"Q\")), FRAMEGROUPS([@-1], [@], 3))

The following image shows how to get the monthly average revenue, nearly average revenue of each product, and revenue trends using aggregate window functions with EXCLUDE mode.

Window Chaining

Window Chaining allows you to define one window before and reuse the new window which implicitly specifies the PARTITYIONBY, ORDERBY, or Window Frame.

In a pre-defined window, PARTITIONBY, ORDERBY, and Window Frame are overridden by the expression from the new window.

Example

WINDOWDEF(PARTITIONBY([Product], YEAR([@OrderDate])), ORDERBY(DATEPART([@OrderDate], \"Q\")), FRAMEROWS([@-2], [@], 3))

The following image shows how to get the average/quality of revenue of each product and revenue trends using window functions.