Window analytic functions
The IBM® Netezza® SQL analytic functions include window aggregation, reporting
aggregation, lag and lead, first and last, ranking, and row count
families. The following table describes the analytic function keywords.
| Syntax | Description |
|---|---|
| ALL | Applies the analytic function to all values. This value is the default and you do not need to specify it. |
| ASC | DESC | Specifies the ordering sequence, either ascending or descending. |
| BETWEEN … AND | Specifies starting and ending points for the window. The first expression (before and) specifies the start; the second expression (after and) specifies the end. |
| CURRENT ROW | As a starting point, specifies that the window begins at the current row or value. As an ending point, specifies that the window ends at the current row or value. |
| DISTINCT | Specifies that the function aggregates results for each unique value. DISTINCT is not supported for the FIRST_VALUE, LAST_VALUE, LEAD, or LAG functions. It is also not supported for STDDEV, STDDEV_POP, STDDEV_SAMP, VARIANCE, VAR_POP, OR VAR_SAMP functions in either a grouped or windowed aggregate. |
| EXCLUDE CURRENT ROW | Specifies excluding the current row. |
| EXCLUDE GROUP | Specifies excluding the current row and all rows that are tied with it. Ties occur when there is a match on the order column or columns. |
| EXCLUDE NO OTHERS | Specifies not excluding any rows. This value is the default if you specify no exclusion. |
| EXCLUDE TIES | Specifies excluding all rows that are tied with the current row (peer rows), but retaining the current row. |
| NULLS {FIRST | LAST} | Specifies whether nulls display before or after non-null values in the sort ordering. By default, null values sort as if they are lower than any non-null value; that is, NULLS FIRST is the default for DESC order, and NULLS LAST otherwise. |
| ORDER BY | Specifies how the data is ordered within the partition. You can order the values on multiple keys, each defined by an ordering sequence. |
| OVER | Indicates that the function operates on a query result set that is computed after the FROM, WHERE, and HAVING clauses. Use over to define the window of rows to include in the function. |
| PARTITION BY | Partitions the query result into groups that are based on one or more columns. If you omit this clause, the query handles the query result as a single partition. |
| ROWS | RANGE | Defines the window as physical rows or as a logical range. To use range between, you must specify the order by clause. |
| UNBOUNDED FOLLOWING | Specifies that the window ends at the last row of the partition. If there is no partition by clause, the end is the last row of the data set. |
| UNBOUNDED PRECEDING | Specifies that the window starts at the first row of the partition. If there is no partition by clause, the start is the first row of the data set. |