10.4Window Frames

A window frame specifies which rows to consider for the current row when evaluating the window function.

The frame comprises three pieces: unit, start bound, and end bound. The unit can be RANGE or ROWS, which defines how the bounds will work.

The bounds are:

Both UNBOUNDED PRECEDING and UNBOUNDED FOLLOWING work identical with RANGE and ROWS. UNBOUNDED PRECEDING start at the first row of the current partition, and UNBOUNDED FOLLOWING ends at the last row of the current partition.

The frame syntax with <window-frame-start> specifies the start-frame, with the end-frame being CURRENT ROW.

Some window functions discard frames:

Example Using Frame

When the ORDER BY clause is used, but a frame clause is omitted, the default considers the partition up to the current row. When combined with SUM, this results in a running total:

  |select
  |  id,
  |  salary,
  |  sum(salary) over (order by salary) sum_salary
  |from employee
  |order by salary;

Result:

  || id | salary | sum_salary |
  ||---:|-------:|-----------:|
  ||  3 |   8.00 |       8.00 |
  ||  4 |   9.00 |      17.00 |
  ||  1 |  10.00 |      37.00 |
  ||  5 |  10.00 |      37.00 |
  ||  2 |  12.00 |      49.00 |

On the other hand, if we apply a frame for the entire partition, we get the total for the entire partition.

  |select
  |  id,
  |  salary,
  |  sum(salary) over (
  |    order by salary
  |    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  |  ) sum_salary
  |from employee
  |order by salary;

Result:

  || id | salary | sum_salary |
  ||---:|-------:|-----------:|
  ||  3 |   8.00 |      49.00 |
  ||  4 |   9.00 |      49.00 |
  ||  1 |  10.00 |      49.00 |
  ||  5 |  10.00 |      49.00 |
  ||  2 |  12.00 |      49.00 |

This example is to demonstrate how this works; the result of this example would be simpler to produce with sum(salary) over().

We can use a range frame to compute the count of employees with salaries between (an employee’s salary - 1) and (their salary + 1) with this query:

  |select
  |  id,
  |  salary,
  |  count(*) over (
  |    order by salary
  |    RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING
  |  ) range_count
  |from employee
  |order by salary;

Result:

  || id | salary | range_count |
  ||---:|-------:|------------:|
  ||  3 |   8.00 |           2 |
  ||  4 |   9.00 |           4 |
  ||  1 |  10.00 |           3 |
  ||  5 |  10.00 |           3 |
  ||  2 |  12.00 |           1 |