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 BEST
|
Use parallelism to improve performance for multi-threaded queries across multiple nodes.
The following considerations apply to using
|
PARTITION NODES
|
Use parallelism to improve performance for single-threaded queries across multiple nodes.
|
Examples
See Window Partitioning in Analyzing Data.