Window framing

The window frame or window aggregation grouping defines the boundary within the window partition for which some computation is being done.

<window frame clause> ::= <window frame units>
<window frame extent> [ <window frame exclusion> ]
<window frame units> ::= ROWS | RANGE
<window frame extent> ::=
   <window frame start> | <window frame between>
<window frame start> ::=
   unbounded preceding | <unsigned value specification> preceding | current row
<window frame between> ::=
   between <window frame bound 1> and <window frame bound 2>
<window frame bound 1> ::= <window frame bound>
<window frame bound 2> ::= <window frame bound>
<window frame bound> ::=
<window frame start>
   | unbounded following
   | <unsigned value specification> following
<window frame exclusion> ::=
   exclude current row
   | exclude group
   | exclude ties
   | exclude no others
You can specify a frame as either row-based or range-based:
  • Use rows for physical rows

    Only if the input data is dense; that is, there are no gaps in the sequence of ordering column values, but not too dense, such as having multiple rows for a month. In that case you can have a tie, which can result in nondeterministic results. You can have more than one ordering column and you are not limited to a simple formula for computing the preceding and succeeding ordering column values.

  • Use range for logical rows

    Only when there is a simple formula that you can use to add or subtract a value from the ordering column. Your order column must be a numeric, datetime, or an interval data type, because these data types are the only types for which addition and subtraction are defined. Although, you cannot use more than one ordering column, your queries are not limited to dense data and ties do not lead to non-determinism.

You can specify bounded or unbounded frames:
Bounded frames
The frame boundary is determined by a specified number or rows, or by a range of values.
Unbounded frames
The frame extends to partition boundary in the direction, that is, it can be unbounded preceding the current row, unbounded following the current row, or both.

If you do not specify window framing and window ordering, then the effect is as though you specified between unbounded preceding and unbounded following.

If you do not specify a window framing, but you do specify a window ordering, then the implicit window framing is range unbounded preceding.