Window frame clause

  • Article

Applies to: check marked yes Databricks SQL check marked yes Databricks Runtime

Specifies a sliding subset of rows within the partition on which the aggregate or analytic window function operates.

Syntax

{ frame_mode frame_start |
  frame_mode BETWEEN frame_start AND frame_end } }

frame_mode
{ RANGE | ROWS }

frame_start
{ UNBOUNDED PRECEDING |
  offset_start PRECEDING |
  CURRENT ROW |
  offset_start FOLLOWING }

frame_end
{ offset_stop PRECEDING |
  CURRENT ROW |
  offset_stop FOLLOWING |
  UNBOUNDED FOLLOWING }

Parameters

  • frame_mode

    • ROWS

      If specified, the sliding window frame is expressed in terms of rows preceding or following the current row.

    • RANGE

      If specified, the window function must specify an ORDER BY clause with a single expression obExpr.

      The boundaries of the sliding window are then expressed as an offset from the obExpr for the current row.

  • frame_start

    The starting position of the sliding window frame relative to the current row.

    • UNBOUNDED PRECEDING

      Specifies that the window frame starts at the beginning of partition.

    • offset_start PRECEDING

      If the mode is ROWS, offset_start is the positive integral literal number defining how many rows prior to the current row the frame starts.

      If the mode is RANGE, offset_start is a positive literal value of a type which can be subtracted from obExpr. The frame starts at the first row of the partition for which obExpr is greater or equal to obExpr - offset_start at the current row.

    • CURRENT ROW

      Specifies that the frame starts at the current row.

    • offset_start FOLLOWING

      If the mode is ROWS, offset_start is the positive integral literal number defining how many rows past to the current row the frame starts. If the mode is RANGE, offset_start is a positive literal value of a type which can be added to obExpr. The frame starts at the first row of the partition for which obExpr is greater or equal to obExpr + offset_start at the current row.

  • frame_stop

    The end of the sliding window frame relative to the current row.

    If not specified, the frame stops at the CURRENT ROW. The end of the sliding window must be greater than the start of the window frame.

    • offset_start PRECEDING

      If frame_mode is ROWS, offset_stop is the positive integral literal number defining how many rows prior to the current row the frame stops. If frame_mode is RANGE, offset_stop is a positive literal value of the same type as offset_start. The frame ends at the last row off the partition for which obExpr is less than or equal to obExpr - offset_stop at the current row.

    • CURRENT ROW

      Specifies that the frame stops at the current row.

    • offsetStop FOLLOWING

      If frame_mode is ROWS, offset_stop is the positive integral literal number defining how many rows past to the current row the frame ends. If frame_mode is RANGE, offset_stop is a positive literal value of the same type as offset_start. The frame ends at the last row of the partition for which obExpr is less than or equal to obExpr + offset_stop at the current row.

    • UNBOUNDED FOLLOWING

      Specifies that the window frame stops at the end of the partition.