Wyn Enterprise User Guide

Window Functions

The window functions perform calculations across a set of table rows that are somehow related to the current row.

Following is the list of Window functions supported in Wyn Enterprise:

AVGW
COUNTW
DENSERANKW
FIRSTVALUEW
LAGW
LEADW
MAXW
MINW
RANKW
ROWNUMBERW
SUMW

AVGW

Description

The AVGW function calculates the AVERAGE value for each column across a set of table rows.

Syntax

COUNTW(<table>,<expression> [,PARTITIONBY(<expression>[,[expression] [,...]])] [,ORDERBY(<expression> [(ASC|DESC)] [,<expression> [(ASC|DESC)] [,...] ] )] [,(ROWS|RANGE)( ((UNBOUNDED|<Number>) (PRECEDING|FOLLOWING) | CURRENT ROW) [,((UNBOUNDED|<Number>) (PRECEDING|FOLLOWING) | CURRENT ROW)])])

Parameters

Parameter	Description
table	The table name that contains the rows for which the expression will be evaluated.
expression	The expression is to be evaluated for each row of the table.
PARTITION BY clause	Divides the query result set into partitions. The window function is applied to each partition separately and computation restarts for each partition.
ORDER BY clause	Defines the logical order of the rows within each partition of the result set. That is, it specifies the logical order in which the window function calculation is performed.
ROWS \| RANGE clause	Further limits the rows within the partition by specifying start and endpoints within the partition. This is done by specifying a range of rows to the current row either by logical association or physical association. Physical association is achieved by using the ROWS clause. The ROWS clause limits the rows within a partition by specifying a fixed number of rows preceding or following the current row. Alternatively, the RANGE clause logically limits the rows within a partition by specifying a range of values to the value in the current row. Preceding and following rows are defined based on the ordering in the ORDER BY clause. The window frame "RANGE ... CURRENT ROW ..." includes all rows that have the same values in the ORDER BY expression as the current row. For example, ROWS BETWEEN 2 PRECEDING AND CURRENT ROW means that the window of rows that the function operates on is three rows in size, starting with 2 rows preceding until and including the current row.
UNBOUNDED PRECEDING	Specifies that the window starts at the first row of the partition. UNBOUNDED PRECEDING can only be specified as a window starting point.
CURRENT ROW	Specifies that the window starts or ends at the current row when used with ROWS or the current value when used with RANGE. CURRENT ROW can be specified as both a starting and ending point.
UNBOUNDED FOLLOWING	Specifies that the window ends at the last row of the partition. UNBOUNDED FOLLOWING can only be specified as a window endpoint.
PRECEDING \| FOLLOWING	Specifies that the window starts or ends at the next or previous rows when used with ROWS or RANGE.

Return Value

A decimal number.

Example

AVGW
(
    'DimProduct',
    'DimProduct'[ListPrice],
    PARTITIONBY('DimProductCategory'[EnglishProductCategoryName]),
    ORDERBY('DimProduct'[ProductName]),
    ROWS(1 PRECEDING , 2 FOLLOWING)
)

COUNTW

Description

The COUNTW function calculates the COUNT value for each column across a set of table rows

Syntax

COUNTW(<table>,<expression> [,PARTITIONBY(<expression>[,[expression] [,...]])] [,ORDERBY(<expression> [(ASC|DESC)] [,<expression> [(ASC|DESC)] [,...] ] )] [,(ROWS|RANGE)( ((UNBOUNDED|<Number>) (PRECEDING|FOLLOWING) | CURRENT ROW) [,((UNBOUNDED|<Number>) (PRECEDING|FOLLOWING) | CURRENT ROW)])])

Parameters

Term	Description
table	The table name that contains the rows for which the expression will be evaluated.
expression	The expression is to be evaluated for each row of the table.
PARTITION BY clause	Divides the query result set into partitions. The window function is applied to each partition separately and computation restarts for each partition.
ORDER BY clause	Defines the logical order of the rows within each partition of the result set. That is, it specifies the logical order in which the window function calculation is performed.
ROWS \| RANGE clause	Further limits the rows within the partition by specifying start and endpoints within the partition. This is done by specifying a range of rows to the current row either by logical association or physical association. Physical association is achieved by using the ROWS clause. The ROWS clause limits the rows within a partition by specifying a fixed number of rows preceding or following the current row. Alternatively, the RANGE clause logically limits the rows within a partition by specifying a range of values to the value in the current row. Preceding and following rows are defined based on the ordering in the ORDER BY clause. The window frame "RANGE ... CURRENT ROW ..." includes all rows that have the same values in the ORDER BY expression as the current row. For example, ROWS BETWEEN 2 PRECEDING AND CURRENT ROW means that the window of rows that the function operates on is three rows in size, starting with 2 rows preceding until and including the current row.
UNBOUNDED PRECEDING	Specifies that the window starts at the first row of the partition. UNBOUNDED PRECEDING can only be specified as a window starting point.
CURRENT ROW	Specifies that the window starts or ends at the current row when used with ROWS or the current value when used with RANGE. CURRENT ROW can be specified as both a starting and ending point.
UNBOUNDED FOLLOWING	Specifies that the window ends at the last row of the partition. UNBOUNDED FOLLOWING can only be specified as a window endpoint.
PRECEDING \| FOLLOWING	Specifies that the window starts or ends at the next or previous rows when used with ROWS or RANGE.

Return Value

A decimal number.

Example

COUNTW
(
    'DimProduct',
    'DimProduct'[ListPrice],
    PARTITIONBY('DimProductCategory'[EnglishProductCategoryName]),
    ORDERBY('DimProduct'[ProductName]),
    ROWS(1 PRECEDING , 2 FOLLOWING)
)

DENSERANKW

Description

The DENSERANKW function calculates the DENSE RANK value for each column across a set of table rows.

Syntax

DENSERANKW(<table>[,PARTITIONBY(<expression>[,[expression] [,...]])] ,ORDERBY(<expression> [(ASC|DESC)] [,<expression> [(ASC|DESC)] [,...] ] ))

Parameters

Term	Description
table	The table contains the rows for which the expression will be evaluated.
PARTITION BY clause	Divides the query result set into partitions. The window function is applied to each partition separately and computation restarts for each partition.
ORDER BY clause	Defines the logical order of the rows within each partition of the result set. That is, it specifies the logical order in which the window function calculation is performed.

Return Value

A whole number.

Example

DENSERANKW
(
    'DimProduct',
    PARTITIONBY('DimProductCategory'[EnglishProductCategoryName]),
    ORDERBY('DimProduct'[ProductName])
)

FIRSTVALUEW

Description

The FIRSTVALUEW function calculates the FIRST value for each column across a set of table rows.

Syntax

FIRSTVALUEW(<table>,<expression> [,PARTITIONBY(<expression>[,[expression] [,...]])] ,ORDERBY(<expression> [(ASC|DESC)] [,<expression> [(ASC|DESC)] [,...] ] ) [,(ROWS|RANGE)( ((UNBOUNDED|<Number>) (PRECEDING|FOLLOWING) | CURRENT ROW) [,((UNBOUNDED|<Number>) (PRECEDING|FOLLOWING) | CURRENT ROW)])])

Parameters

Term	Description
table	The table name that contains the rows for which the expression will be evaluated.
expression	The expression is to be evaluated for each row of the table.
PARTITION BY clause	Divides the query result set into partitions. The window function is applied to each partition separately and computation restarts for each partition.
ORDER BY clause	Defines the logical order of the rows within each partition of the result set. That is, it specifies the logical order in which the window function calculation is performed.
ROWS \| RANGE clause	Further limits the rows within the partition by specifying start and endpoints within the partition. This is done by specifying a range of rows to the current row either by logical association or physical association. Physical association is achieved by using the ROWS clause. The ROWS clause limits the rows within a partition by specifying a fixed number of rows preceding or following the current row. Alternatively, the RANGE clause logically limits the rows within a partition by specifying a range of values to the value in the current row. Preceding and following rows are defined based on the ordering in the ORDER BY clause. The window frame "RANGE ... CURRENT ROW ..." includes all rows that have the same values in the ORDER BY expression as the current row. For example, ROWS BETWEEN 2 PRECEDING AND CURRENT ROW means that the window of rows that the function operates on is three rows in size, starting with 2 rows preceding until and including the current row.
UNBOUNDED PRECEDING	Specifies that the window starts at the first row of the partition. UNBOUNDED PRECEDING can only be specified as a window starting point.
CURRENT ROW	Specifies that the window starts or ends at the current row when used with ROWS or the current value when used with RANGE. CURRENT ROW can be specified as both a starting and ending point.
UNBOUNDED FOLLOWING	Specifies that the window ends at the last row of the partition. UNBOUNDED FOLLOWING can only be specified as a window endpoint.
PRECEDING \| FOLLOWING	Specifies that the window starts or ends at the next or previous rows when used with ROWS or RANGE.

Return Value

The first value.

Example

FIRSTVALUEW
(
    'DimProduct',
    'DimProduct'[ListPrice],
    PARTITIONBY('DimProductCategory'[EnglishProductCategoryName]),
    ORDERBY('DimProduct'[ProductName]),
    ROWS(1 PRECEDING , 2 FOLLOWING)
)

LAGW

Description

The LAGW function calculates the next value for each column across a set of table rows.

Syntax

LAGW(<table>,<expression> [,PARTITIONBY(<expression>[,[expression] [,...]])] ,ORDERBY(<expression> [(ASC|DESC)] [,<expression> [(ASC|DESC)] [,...] ] ))

Parameters

Term	Description
table	The table contains the rows for which the expression will be evaluated.
expression	The expression is to be evaluated for each row of the table.
PARTITION BY clause	Divides the query result set into partitions. The window function is applied to each partition separately and computation restarts for each partition.
ORDER BY clause	Defines the logical order of the rows within each partition of the result set. That is, it specifies the logical order in which the window function calculation is performed.

Return Value

The next value.

Example

LAGW (
    'DimProduct',
    'DimProduct'[ListPrice],
    PARTITIONBY('DimProductCategory'[EnglishProductCategoryName]),
    ORDERBY('DimProduct'[ProductName])
)

LEADW

Description

The LEADW function calculates the previous value for each column across a set of table rows.

Syntax

LEADW(<table>,<expression> [,PARTITIONBY(<expression>[,[expression] [,...]])] ,ORDERBY(<expression> [(ASC|DESC)] [,<expression> [(ASC|DESC)] [,...] ] ))

Parameters

Term	Description
table	The table contains the rows for which the expression will be evaluated.
expression	The expression is to be evaluated for each row of the table.
PARTITION BY clause	Divides the query result set into partitions. The window function is applied to each partition separately and computation restarts for each partition.
ORDER BY clause	Defines the logical order of the rows within each partition of the result set. That is, it specifies the logical order in which the window function calculation is performed.

Return Value

The previous value.

Example

LEADW (
    'DimProduct',
    'DimProduct'[ListPrice],
   PARTITIONBY('DimProductCategory'[EnglishProductCategoryName]),
    ORDERBY('DimProduct'[ProductName])
)

MAXW

Description

The MAXW function calculates the MAX value for each column across a set of table rows.

Syntax

MAXW(<table>,<expression> [,PARTITIONBY(<expression>[,[expression] [,...]])] [,ORDERBY(<expression> [(ASC|DESC)] [,<expression> [(ASC|DESC)] [,...] ] )] [,(ROWS|RANGE)( ((UNBOUNDED|<Number>) (PRECEDING|FOLLOWING) | CURRENT ROW) [,((UNBOUNDED|<Number>) (PRECEDING|FOLLOWING) | CURRENT ROW)])])

Parameters

Term	Description
table	The table name that contains the rows for which the expression will be evaluated.
expression	The expression is to be evaluated for each row of the table.
PARTITION BY clause	Divides the query result set into partitions. The window function is applied to each partition separately and computation restarts for each partition.
ORDER BY clause	Defines the logical order of the rows within each partition of the result set. That is, it specifies the logical order in which the window function calculation is performed.
ROWS \| RANGE clause	Further limits the rows within the partition by specifying start and endpoints within the partition. This is done by specifying a range of rows to the current row either by logical association or physical association. Physical association is achieved by using the ROWS clause. The ROWS clause limits the rows within a partition by specifying a fixed number of rows preceding or following the current row. Alternatively, the RANGE clause logically limits the rows within a partition by specifying a range of values to the value in the current row. Preceding and following rows are defined based on the ordering in the ORDER BY clause. The window frame "RANGE ... CURRENT ROW ..." includes all rows that have the same values in the ORDER BY expression as the current row. For example, ROWS BETWEEN 2 PRECEDING AND CURRENT ROW means that the window of rows that the function operates on is three rows in size, starting with 2 rows preceding until and including the current row.
UNBOUNDED PRECEDING	Specifies that the window starts at the first row of the partition. UNBOUNDED PRECEDING can only be specified as a window starting point.
CURRENT ROW	Specifies that the window starts or ends at the current row when used with ROWS or the current value when used with RANGE. CURRENT ROW can be specified as both a starting and ending point.
UNBOUNDED FOLLOWING	Specifies that the window ends at the last row of the partition. UNBOUNDED FOLLOWING can only be specified as a window endpoint.
PRECEDING \| FOLLOWING	Specifies that the window starts or ends at the next or previous rows when used with ROWS or RANGE.

Return Value

The largest value.

Example

MAXW
(
    'DimProduct',
    'DimProduct'[ListPrice],
    PARTITIONBY('DimProductCategory'[EnglishProductCategoryName]),
    ORDERBY('DimProduct'[ProductName]),
    ROWS(1 PRECEDING , 2 FOLLOWING)
)

MINW

Description

The MINW function calculates the MIN value for each column across a set of table rows.

Syntax

MINW(<table>,<expression> [,PARTITIONBY(<expression>[,[expression] [,...]])] [,ORDERBY(<expression> [(ASC|DESC)] [,<expression> [(ASC|DESC)] [,...] ] )] [,(ROWS|RANGE)( ((UNBOUNDED|<Number>) (PRECEDING|FOLLOWING) | CURRENT ROW) [,((UNBOUNDED|<Number>) (PRECEDING|FOLLOWING) | CURRENT ROW)])])

Parameters

Term	Description
table	The table name that contains the rows for which the expression will be evaluated.
expression	The expression is to be evaluated for each row of the table.
PARTITION BY clause	Divides the query result set into partitions. The window function is applied to each partition separately and computation restarts for each partition.
ORDER BY clause	Defines the logical order of the rows within each partition of the result set. That is, it specifies the logical order in which the window function calculation is performed.
ROWS \| RANGE clause	Further limits the rows within the partition by specifying start and endpoints within the partition. This is done by specifying a range of rows to the current row either by logical association or physical association. Physical association is achieved by using the ROWS clause. The ROWS clause limits the rows within a partition by specifying a fixed number of rows preceding or following the current row. Alternatively, the RANGE clause logically limits the rows within a partition by specifying a range of values to the value in the current row. Preceding and following rows are defined based on the ordering in the ORDER BY clause. The window frame "RANGE ... CURRENT ROW ..." includes all rows that have the same values in the ORDER BY expression as the current row. For example, ROWS BETWEEN 2 PRECEDING AND CURRENT ROW means that the window of rows that the function operates on is three rows in size, starting with 2 rows preceding until and including the current row.
UNBOUNDED PRECEDING	Specifies that the window starts at the first row of the partition. UNBOUNDED PRECEDING can only be specified as a window starting point.
CURRENT ROW	Specifies that the window starts or ends at the current row when used with ROWS or the current value when used with RANGE. CURRENT ROW can be specified as both a starting and ending point.
UNBOUNDED FOLLOWING	Specifies that the window ends at the last row of the partition. UNBOUNDED FOLLOWING can only be specified as a window endpoint.
PRECEDING \| FOLLOWING	Specifies that the window starts or ends at the next or previous rows when used with ROWS or RANGE.

Return Value

The smallest value.

Example

MINW
(
    'DimProduct',
    'DimProduct'[ListPrice],
    PARTITIONBY('DimProductCategory'[EnglishProductCategoryName]),
    ORDERBY('DimProduct'[ProductName]),
    ROWS(1 PRECEDING , 2 FOLLOWING)
)

RANKW

Description

The RANKW function calculates the RANK value for each column across a set of table rows.

Syntax

RANKW(<table>[,PARTITIONBY(<expression>[,[expression] [,...]])] ,ORDERBY(<expression> [(ASC|DESC)] [,<expression> [(ASC|DESC)] [,...] ] ))

Parameters

Term	Description
table	The table contains the rows for which the expression will be evaluated.
PARTITION BY clause	Divides the query result set into partitions. The window function is applied to each partition separately and computation restarts for each partition.
ORDER BY clause	Defines the logical order of the rows within each partition of the result set. That is, it specifies the logical order in which the window function calculation is performed.

Return Value

A whole number.

Example

RANKW
(
    'DimProduct',
    PARTITIONBY('DimProductCategory'[EnglishProductCategoryName]),
    ORDERBY('DimProduct'[ProductName])
)

ROWNUMBERW

Description

The ROWNUMBERW function calculates the ROW NUMBER for each column across a set of table rows.

Syntax

ROWNUMBERW(<table>[,PARTITIONBY(<expression>[,[expression] [,...]])] ,ORDERBY(<expression> [(ASC|DESC)] [,<expression> [(ASC|DESC)] [,...] ] ))

Parameters

Term	Description
table	The table contains the rows for which the expression will be evaluated.
PARTITION BY clause	Divides the query result set into partitions. The window function is applied to each partition separately and computation restarts for each partition.
ORDER BY clause	Defines the logical order of the rows within each partition of the result set. That is, it specifies the logical order in which the window function calculation is performed.

Return Value

A whole number.

Example

ROWNUMBERW
(
    'DimProduct',
    PARTITIONBY('DimProductCategory'[EnglishProductCategoryName]),
    ORDERBY('DimProduct'[ProductName])
)

SUMW

Description

The SUMW function calculates the SUM value for each column across a set of table rows.

Syntax

SUMW(<table>,<expression> [,PARTITIONBY(<expression>[,[expression] [,...]])] [,ORDERBY(<expression> [(ASC|DESC)] [,<expression> [(ASC|DESC)] [,...] ] )] [,(ROWS|RANGE)( ((UNBOUNDED|<Number>) (PRECEDING|FOLLOWING) | CURRENT ROW) [,((UNBOUNDED|<Number>) (PRECEDING|FOLLOWING) | CURRENT ROW)])])

Parameters

Term	Description
table	The table name that contains the rows for which the expression will be evaluated.
expression	The expression is to be evaluated for each row of the table.
PARTITION BY clause	Divides the query result set into partitions. The window function is applied to each partition separately and computation restarts for each partition.
ORDER BY clause	Defines the logical order of the rows within each partition of the result set. That is, it specifies the logical order in which the window function calculation is performed.
ROWS \| RANGE clause	Further limits the rows within the partition by specifying start and endpoints within the partition. This is done by specifying a range of rows to the current row either by logical association or physical association. Physical association is achieved by using the ROWS clause. The ROWS clause limits the rows within a partition by specifying a fixed number of rows preceding or following the current row. Alternatively, the RANGE clause logically limits the rows within a partition by specifying a range of values to the value in the current row. Preceding and following rows are defined based on the ordering in the ORDER BY clause. The window frame "RANGE ... CURRENT ROW ..." includes all rows that have the same values in the ORDER BY expression as the current row. For example, ROWS BETWEEN 2 PRECEDING AND CURRENT ROW means that the window of rows that the function operates on is three rows in size, starting with 2 rows preceding until and including the current row.
UNBOUNDED PRECEDING	Specifies that the window starts at the first row of the partition. UNBOUNDED PRECEDING can only be specified as a window starting point.
CURRENT ROW	Specifies that the window starts or ends at the current row when used with ROWS or the current value when used with RANGE. CURRENT ROW can be specified as both a starting and ending point.
UNBOUNDED FOLLOWING	Specifies that the window ends at the last row of the partition. UNBOUNDED FOLLOWING can only be specified as a window endpoint.
PRECEDING \| FOLLOWING	Specifies that the window starts or ends at the next or previous rows when used with ROWS or RANGE.

Return Value

A decimal number.

Example

SUMW
(
    'DimProduct',
    'DimProduct'[ListPrice],
    PARTITIONBY('DimProductCategory'[EnglishProductCategoryName]),
    ORDERBY('DimProduct'[ProductName]),
    ROWS(1 PRECEDING , 2 FOLLOWING)
)