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.