This is by far the section that will need 120% of your attention, so please bear with me. And if you didn't understand the concept, it's fine. Take some to time to read it over again!
In the last section, we learned to SUM values across the whole table and even partition those values by specific job title. But window function can be even more powerful. You can specify a frame to tell the window function which ROWS you wanted them to go over.
Here is how the general syntax looks like (this is inside the window function btw)
ROWS BETWEEN <starting_row> AND <ending_row>
In the <starting_row> and <ending row>, you can specify whatever you want, actually not whatever, here are the options you have:
- UNBOUNDED PRECEDING — all rows before the current row in the partition, i.e. everything before that row basically.
- You can even specify how many rows before the current row you wanna look at with [some #] PRECEDING. It can be 5 PRECEDING for example.
- CURRENT ROW: It will look at the current row, nothing special here
- You can also specify rows forward using [some #] FOLLOWING. Let's say you wanna SUM the total values of the next 5 rows you can use 5 FOLLOWING.
- UNBOUNDED FOLLOWING: Unbounded means no limit, so the window function will just look at everything after that row
Alright, are you still with me?
Here’s some examples of how it could be written inside a window function:
- ROWS BETWEEN 5 PRECEDING AND CURRENT ROW — this means look back the previous 5 rows up to the current row.
- ROWS BETWEEN UNBOUNDED PRECEDING AND 1 FOLLOWING — this means look from the first row of the partition (meaning everything before that current row) to 1 row after the current row
- ROWS BETWEEN 5 PRECEDING AND 1 PRECEDING — this means look back the previous 5 rows up to 1 row before the current row
- ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING — this means look from the first row of the partition to the last row of the partition (it's UNBOUNDED, no limits!)
Now we would need to actually see how it looks like in SQL code!
