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:
There are two kinds of window frames: those with the keyword
ROWS and those with the keyword
RANGE. The general syntax is the same:
ORDER BY <OrderColumn>
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
RANGE, and leave
PARTITION BY for later in the course.
Let's take a look at the example:
ORDER BY PlacedDate ASC
ROWS UNBOUNDED PRECEDING
) AS SumTotalPrice
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.