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. In this way, we can create new kinds of queries.
For instance, you may say that for each row, 3 rows before and 3 rows after it are taken into account; or rows from the beginning of the partition until the current row. In a moment, you'll discover how such queries can come in handy. Take a look at the example window frame, where two rows before and two rows after the current row are selected:
The are two kinds of window frames: those with the keyword
ROWS and those with
RANGE instead. The general syntax is as follows:
<window function> OVER (...
ORDER BY <order_column>
[ROWS|RANGE] <window frame extent>
Of course, other elements might be added above (for instance, a
PARTITION BY clause), which is why we put dots
(...) in the brackets. For now, we'll focus on the meaning of
RANGE. We'll talk about
PARTITION BY later in the course.
Let's take a look at the example:
ORDER BY placed
ROWS UNBOUNDED PRECEDING)
In the above query, we sum the column
total_price. For each row, we add the current row AND all the previously introduced rows (
UNBOUNDED PRECEDING) to the sum. As a result, the sum will increase with each new order.