Window partitioning is the division of user-specified result sets (input rows) using a PARTITION BY clause. A partition is defined by one or more value expressions separated by commas. Partitioned data is also implicitly sorted and the default sort order is ascending (ASC).
<WINDOW PARTITION CLAUSE> ::= PARTITION BY <WINDOW PARTITION EXPRESSION LIST>
If a window partition clause is not specified, then the input is treated as single partition.
The term partition as used with
analytic functions, refers only to dividing the set of result rows
using a PARTITION BY clause.
A window partition can be defined based on an arbitrary expression. Also, because window partitioning occurs after GROUPING (if a GROUP BY clause is specified), the result of any aggregate function, such as SUM, AVG, and VARIANCE, can be used in a partitioning expression. Therefore, partitions provide another opportunity to perform grouping and ordering operations in addition to the GROUP BY and ORDER BY clauses; for example, you can construct queries that compute aggregate functions over aggregate functions, such as the maximum SUM of a particular quantity.
You can specify a PARTITION BY clause, even it there is no GROUP BY clause.
