Introduction
ROWS
5. General syntax
RANGE
Default window frame
Summary

Instruction

Alright, let's get down to work! In this part, we're finally going to learn about window frames.

Window frames define precisely which rows should be taken into account when computing the results and are always relative to the current row.

For example, you can create a window frame that includes the current row, the three rows before it, and the three rows after it. This opens up the possibility of new kinds of queries.

In a moment, you'll discover how such queries can come in handy. For now, take a look at the example window frame, where two rows before and two rows after the current row are selected:

Id Name IntroducedDate
1 Frozen Yoghurt 2016-01-26
2 Ice cubes 2016-04-10
3 Ice cream 2016-01-05
4 Skis 2016-04-09
5 Snowboard 2016-02-01
6 Sledge 2016-02-20
7 Freezer 2016-01-16

There are two kinds of window frames: those with the keyword ROWS and those with the keyword RANGE. The general syntax is the same:

<WindowFunction>
OVER (...
  ORDER BY <OrderColumn>
  [ROWS|RANGE] <WindowFrameExtent>)

Of course, other elements might be added (e.g., a PARTITION BY clause), which is why we put dots (...) in the brackets. For now, we'll focus on the meaning of ROWS and RANGE, and leave PARTITION BY for later in the course.

Let's take a look at the example:

SELECT
  Id,
  TotalPrice,
  SUM(TotalPrice) OVER(
    ORDER BY PlacedDate ASC
    ROWS UNBOUNDED PRECEDING
  ) AS SumTotalPrice
FROM SingleOrder

In the above query, we create a running sum for the column TotalPrice. For each row, we add the current row AND all the previous rows (UNBOUNDED PRECEDING) to the sum. As a result, the sum will increase with each new order.

Exercise

Click Run and check code to run the template.