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 could 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.

WINDOW function changes the sequence of the rows using 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 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 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 FRAMEROWS or FRAMERANGE.

FRAMEROWS

The FRAMEROWS allows you to limit the rowset 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 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

• The FRAMERANGE requires the 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 the ORDERBY is removed, the default frame expression is FRAMERANGE(-1, -1) even if the FRAMERANGE is specified.

• If the ORDERBY contains more than one column, only -1 and [@] can be used in the 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.