This is the multi-page printable view of this section. Click here to print.

Return to the regular view of this page.

Window clauses

When used with an analytic function, window clauses specify how to partition and sort function input, as well as how to frame input with respect to the current row.

When used with an analytic function, window clauses specify how to partition and sort function input, as well as how to frame input with respect to the current row. When used with a single-phase transform function, the PARTITION ROW window clause supports single-row partitions for single-phase transform functions, rather than analytic functions.

1 - Window partition clause

When specified, a window partition clause divides the rows of the function input based on user-provided expressions.

When specified, a window partition clause divides the rows of the function input based on user-provided expressions. If no expression is provided, the partition clause can improve query performance by using parallelism. If you omit specifying a window partition clause, all input rows are treated as a single partition.

Window partitioning is similar to the GROUP BY clause. However, PARTITION BEST and PARTITION NODES may only be used with analytic functions and return only one result per input row, while PARTITION ROW and PARTITION LEFT JOIN can be used for single-phase transform functions and return multiple values per input row.

When used with analytic functions, results are computed per partition and start over again (reset) at the beginning of each subsequent partition.

Syntax

{ PARTITION BY expression[,...] | PARTITION BEST | PARTITION NODES | PARTITION ROW }

Arguments

PARTITION BY expression Expression on which to sort the partition, where expression can be a column, constant, or an arbitrary expression formed on columns. Use PARTITION BY for functions with specific partitioning requirements.
PARTITION BEST

Use parallelism to improve performance for multi-threaded queries across multiple nodes.

OVER(PARTITION BEST) provides the best performance on multi-threaded queries across multiple nodes.

The following considerations apply to using PARTITION BEST:

  • Use PARTITION BEST for analytic functions that have no partitioning requirements and are thread safe—for example, a one-to-many transform.

  • Do not use PARTITION BEST on user-defined transform functions (UDTFs) that are not thread-safe. Doing so can produce an error or incorrect results. If a UDTF is not thread safe, use PARTITION NODES .

PARTITION NODES

Use parallelism to improve performance for single-threaded queries across multiple nodes.

OVER(PARTITION NODES) provides the best performance on single-threaded queries across multiple nodes.

PARTITION ROW

Use to feed input partitions of exactly one row. If used, any arbitrary expression may be used in the query target list alongside the UDTF.

May not be used for analytic functions or multi-phase transform functions. Note that only one PARTITION ROW transform function is allowed in the target list for each level of the query.

Examples

See Window partitioning.

2 - Window order clause

Specifies how to sort rows that are supplied to an analytic function.

Specifies how to sort rows that are supplied to an analytic function. If the OVER clause also includes a window partition clause, rows are sorted within each partition.

The window order clause only specifies order within a window result set. The query can have its own ORDER BY clause outside the OVER clause. This has precedence over the window order clause and orders the final result set.

A window order clause also creates a default window frame if none is explicitly specified.

Syntax

ORDER BY { expression [ ASC | DESC [ NULLS { FIRST | LAST | AUTO } ] ]
  }[,...]

Arguments

expression A column, constant, or arbitrary expression formed on columns on which to sort input rows.
ASC | DESC Specifies the ordering sequence as ascending (default) or descending.
NULLS {FIRST | LAST | AUTO}

Specifies whether to position null values first or last. Default positioning depends on whether the sort order is ascending or descending:

  • Ascending default: NULLS LAST

  • Descending default: NULLS FIRST

If you specify NULLS AUTO, Vertica chooses the positioning that is most efficient for this query, either NULLS FIRST or NULLS LAST.

If you omit all sort qualifiers, Vertica uses ASC NULLS LAST.

For more information, see:

Examples

See Window ordering.

3 - Window frame clause

Specifies a window frame, which comprises a set of rows relative to the row that is currently being evaluated by an analytic function.

Specifies a window frame, which comprises a set of rows relative to the row that is currently being evaluated by an analytic function. After the function processes that row and its window, Vertica advances the current row and adjusts the window boundaries accordingly. If the OVER clause also specifies a partition, Vertica also checks that window boundaries do not cross partition boundaries. This process repeats until the function evaluates the last row of the last partition.

Syntax

{ ROWS | RANGE } { BETWEEN start‑point AND end‑point } | start‑point

start-point / end‑point:

{ UNBOUNDED {PRECEDING | FOLLOWING}
  | CURRENT ROW
  | constant-value  {PRECEDING | FOLLOWING}}

Arguments

ROWS | RANGE Specifies whether Vertica determines window frame dimensions as physical or logical offsets from the current row. See ROWS versus RANGE below for details.
BETWEEN start-pointANDend-point

Specifies the window's first and last rows, where start-point and end-point can be one of the following (discussed in detail below):

  • UNBOUNDED {PRECEDING | FOLLOWING}

  • CURRENT ROW

  • constant-value {PRECEDING | FOLLOWING}

start-point must resolve to a row or value that is less than or equal to end-point.

UNBOUNDED PRECEDING Specifies that the window frame extends to the current partition's first row.
start-point If ROWS or RANGE specifies only a start point, Vertica uses the current row as the end point and creates the window frame accordingly. In this case, start-point must resolve to a row that is less than or equal to the current row.
UNBOUNDED FOLLOWING Specifies that the window frame extends to the current partition's last row.
CURRENT ROW Specifies the current row or value as the window's start or end point.
constant-value {PRECEDING | FOLLOWING}

Specifies a constant value or expression that evaluates to a constant value. The value specifies a physical or logical offset from the current row, depending on whether you specify ROWS or RANGE.

Other dependencies also pertain, depending whether you specify ROWS and RANGE. See ROWS versus RANGE below for details.

Requirements

In order to specify a window frame, the OVER must also specify a window order (ORDER BY) clause. If the OVER clause omits specifying a window frame, the function creates a default window that extends from the current row to the first row in the current partition. This is equivalent to the following clause:

RANGE UNBOUNDED PRECEDING AND CURRENT ROW

ROWS versus RANGE

The window frame's offset from the current row can be physical or logical:

  • ROWS specifies the window's start-point and end-point as a number of rows relative to the current row. If start-point and end-point are expressed as constant values, the value must evaluate to a positive integer.

  • RANGE specifies the window as a logical offset such as time. The range value must match the window order (ORDER BY) clause data type: NUMERIC, DATE/TIME, FLOAT or INTEGER.

Use of ROWS or RANGE imposes specific requirements on setting the window's start and end points as constant values. When setting constant values for ROWS, the constant must evaluate to a positive INTEGER.

When setting constant values for RANGE, the following requirements apply:

  • The constant must evaluate to a positive numeric value or INTERVAL literal.

  • If the constant evaluates to a NUMERIC value, the ORDER BY column type must be a NUMERIC data type.

  • If the constant evaluates to an INTERVAL DAY TO SECOND subtype, the ORDER BY column type must be one of the following: TIMESTAMP, TIME, DATE, or INTERVAL DAY TO SECOND.

  • If the constant evaluates to an INTERVAL YEAR TO MONTH, the ORDER BY column type must be one of the following: TIMESTAMP, DATE, or INTERVAL YEAR TO MONTH.

  • The window order clause can specify only one expression.

Examples

See Window framing.

4 - Window name clause

Defines a named window that specifies window partition and order clauses for an analytic function.

Defines a named window that specifies window partition and order clauses for an analytic function. This window is specified in the function's OVER clause. Named windows can be useful when you write queries that invoke multiple analytic functions with similar OVER clauses—for example, they use the same partition (PARTITION BY) clauses.

Syntax

WINDOW window-name AS ( window-partition-clause [window-order-clause] )

Arguments

WINDOW window-name Specifies the window name. All window names must be unique within the same query.
window-partition-clause [window-order-clause]

Clauses to invoke when an OVER clause references this window.

If the window definition omits a window order clause, the OVER clause can specify its own order clause.

Requirements

  • A WINDOW clause cannot include a window frame clause.

  • Each WINDOW clause within the same query must have a unique name.

  • A WINDOW clause can reference another window that is already named. For example, the following query names window w1 before w2. Thus, the WINDOW clause that defines w2 can reference w1:

    => SELECT RANK() OVER(w1 ORDER BY sal DESC), RANK() OVER w2
   FROM EMP WINDOW w1 AS (PARTITION BY deptno), w2 AS (w1 ORDER BY sal);

Examples

See Named windows.

See also

Analytic functions