- Getting Started
- Administration Guide
-
User Guide
- An Introduction to Wyn Enterprise
- Document Portal for End Users
- Data Governance and Modeling
- Working-with-Resources
- Working with Reports
- Working with Dashboards
- View and Manage Documents
- Understanding Wyn Analytical Expressions
- Section 508 Compliance
- Developer 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
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. |
| 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. |
| 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. |
| 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. |
| 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. |
| 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. |
| 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)
)