SQL Academy
Premium
Advanced SQL

Window Frames, RANGE and ROWS

In the context of SQL window functions, a "window frame" defines a subset of rows that are considered by the SQL function when performing calculations.

In other words, a window frame is a dynamic set of rows that "slides" through your query result, forming different data sets for each row, depending on your defined frame.

Window frame vs Partition

Although the terms "window frame" and "partition" may seem similar, they represent different concepts:

  • Partition (PARTITION BY). This is the division of the entire result set into non-overlapping subsets, where each subset contains rows with the same values in one or more columns. Window functions are applied separately to each partition, as if each were a separate data set.

    Partition divition schema

  • Window frame. Defines which specific rows in each partition will be used for calculating the window function for each row. The frame can change from row to row.

    For example, if the rule ROWS BETWEEN 1 PRECEDING AND CURRENT ROW is used, for each row the frame will consist of the row itself and one preceding row. This is like a "subpartition" within an existing partition.

    Window frame formation schema

    That is:

    • The first frame consists only of the 1st record, because there is no previous record. The single record is passed to the aggregate function AVG(price) and the result is added to the avg_price field.
    • The second frame contains records 1 and 2, which are sent to AVG(price) and return (170 + 220) / 2 = 195.
    • The third frame contains records 2 and 3, resulting in (220 + 150) / 2 = 185.
    • and so on.

Note on window frame without ROWS/RANGE

If ROWS/RANGE is missing in the definition of a window function, then the default frame depends on whether ORDER BY is present.

If ORDER BY is absent, the frame coincides with the partition. In this case, the window function will process all rows within the partition, not limited to a subset. This means that the function result will be the same for all rows within the same partition.

If ORDER BY is present, the default rule is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. In this case, the window frame starts with the first row and ends with the current row, including all rows with the same sorting field values as the current row.

Defining window frames

Using the ROWS or RANGE syntax, we can define exactly which window frame will be passed to the window function for calculating the value for the current row.

The syntax for defining window frames looks like specifying a range relative to the current row.

MySQL 8.1
SELECT  <window_function>(<table_field>)
OVER (
      ...
      ROWS|RANGE BETWEEN <frame start> AND <frame end>
)

For example, if we want only the two preceding records and the current row to be included in the window function calculation, then the syntax would look like this:

MySQL 8.1
... ROWS|RANGE BETWEEN 2 PRECEDING AND CURRENT ROW

If we want the window function to include the current row and all subsequent ones, then the syntax would look like this:

MySQL 8.1
... ROWS|RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING

Possible window frame definitions

  • UNBOUNDED PRECEDING, all rows preceding the current one
  • N PRECEDING, N rows before the current row
  • CURRENT ROW, the current row
  • N FOLLOWING, N rows after the current row
  • UNBOUNDED FOLLOWING, all subsequent rows

Window frame definition schema

Window frame definition schema

The difference between ROWS and RANGE

For defining window frames, there are the keywords ROWS and RANGE. They work differently:

ROWS

  • Based on physical rows:

    When using ROWS, the frame definition is based on the physical position of rows relative to the current row. For example, 1 PRECEDING means one row before the current one.

  • Precise frame:

    Defining a frame with ROWS clearly limits the number of rows included in the frame, making it predictable and specific.

Window frame definition schema with rows

RANGE

  • Based on values:

    RANGE, unlike ROWS, defines window frames based on column values, ordered according to ORDER BY in the window function.

  • Dynamic frames:

    Frames defined with RANGE can vary depending on the data, making the frame flexible but potentially less predictable.

Window frame definition schema with range

Let's check if you understood the difference between ROWS and RANGE correctly:

Which of the following statements best describes the difference between using ROWS and RANGE in the context of SQL window functions?

Edit on GitHub