Window Partition Clause

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.

Window partitioning is similar to the GROUP BY clause except that it returns only one result row per input row. If you omit specifying a window partition clause, all input rows are treated as a single partition.

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 }

Parameters

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 analytic 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.

Examples

See Window Partitioning in Analyzing Data.