Window functions are a really powerful way to operate within a result set on just a chunk of the rows. You get to define how that chunk is determined. This allows you to do stuff like maybe within that chunk you want to take an average of all the rows, but you don't actually want to collapse the rows, right? When we do the grouping, when we did group by in previous lessons, all those rows, all of that rich information in those rows, boom, collapsed, totally gone. You have to aggregate the columns in some way or just leave them out.

With a window function, you get the entire row back, and you can append averages, sums, moving averages, all kinds of stuff without losing the identity of that row. Let me show you how. We're gonna start again with select * from sales. If we run that, we are looking for, let's look for the average of the amount per region without losing all of the discreet sales. The way that we do this is we tell Postgres we want to start a window function. Using this keyword over is what begins the process of creating a window.

In this window, in this window particularly, we want to partition this result. This 15 rows, we want to partition this result set by region. Using the earlier terminology, we want to create chunks out of the region. Every region ends up in its own chunk and we can get an average of the amount per region. If we just say partition by region, we get the average per region. You can see here for all of the east sales, the average is the same. For all of the north sails, south and west, the averages are the same within a single region.

Within a window definition, you get to define several things. You don't have to define several things, but you get to define a few things if you want. We defined a partition, and we said, let's partition this result set up by region and then calculate the average over that partition. You do not have to declare a partition, in which case the partition becomes the entire result set. I'll show you that in a second. You can also declare or define ordering within a window definition. If you want to have a partition that's ordered ascending and a partition that's ordered descending for different columns, you can. You can absolutely do that. You can also declare a frame which tells you within a partition how far ahead and how far behind can this particular row see? We'll cover that in a second.

Let me show you a window that does not have a partition defined. We're gonna leave this partition here, and we're just gonna do another one. We'll call this as average per region, and we'll call this as average total. Average total makes no sense. We'll just call this average. Instead of saying partition by region, we're just gonna say over the entire thing. Now we're doing an aggregation while retaining the row identities, but we're doing the aggregation across the entire result set. If we run that, okay, whew, good, thank goodness. If we run that, there, you see we have the average per region. These are all still the same, but we also have the average over the entire result set, which is pretty cool. You can, of course you can switch these around. You don't have to do, you don't have to do averages. You can do any of the aggregated functions there. If you wanted, it doesn't make a lot of sense here. In fact, it doesn't make any sense here. We're gonna, we're gonna not order this. We're gonna move on.

If we did select * from bookmarks limit, let's just do limit 30. We get a few in there. We've got ID, user ID and ID. What if, what if we wanted to do a little user journey through their own bookmarks and start to put in what their first bookmark was, what their last one was, what number bookmark number 10 was, that sort of thing.

Let's do that now. This is a great use case for truly mapping user journeys if you're keeping some sort of analytics data around. Let's do that now. Let's say we want, what do we want? Let's do first value. That is, that is a, that is a window function function that you can use here. We're gonna do first value of ID, and then we're gonna say over which opens up our window definition.

We're gonna say partition by user ID. Every user gets their own chunk, they get their own partition. Order by ID ascending. We do not have a created at timestamp on here. We're gonna use ID as kind of a surrogate sort there. Totally fine. May not be true in all cases, but for this use case it's totally fine. If we run that, you'll see, look, all of the user number ones are here, and then they get the first value as 593071, which if we come up here, that is in fact this user's first ID. When we get to user number two, their first bookmark ever was 488528. We get that on here as well. You can see the partitions in action. Every time the first value changes, it does line up with the user ID changing because that is the partition.

What if, what if we did this? What if we just did last value? This is not going to work. I mean, it's gonna work, but it's not going to work the way that you want it to work. Well, it's not gonna work the way that I want it to work. This doesn't, this doesn't do what I think it should do because look, first value and last value are the same, but you move on first, value and last value are different. How can we tell what this 738 is? Well, it's this row right here. We're getting a little bit of, a little bit of happenstance. We're getting a little bit of insight accidentally into how this is being processed.

When Postgres is looking at this first row in this partition, it says, yeah, first value is that, and this is the only row I've seen, in fact. The last value is that. Then it moves on to the next one, and it says, I remember what the first value was because I saw that, but now I have a new last value. Then it goes on and on and on until you get to the very bottom when it finally becomes correct. I don't love it. This is how it's supposed to work. This is how it's supposed to work. This is legit. It is a little bit confusing if you're not prepared for it. One thing you can do, one thing you can do is you can change this to descending. If you change this to order by ID descending. I wondered why this didn't work. If you change it to descending, you also have to change the function name over here to first value. Whew, there we go.

All right, so we changed, we changed the way that the partition is ordered, and in that case, the first value is actually the last value 'cause we ordered it by descending. Clearly this is not, this doesn't make a ton of sense to me still. Clearly this is still confusing. This is just to show you that you can change the order around. I don't like this as a method. I don't like this as a method for this particular use case. It may make perfect sense to have the same partition in different orders. That's totally fine. For this use case, there's a better way to do that. The better way to do that brings us back to that third thing that you can define, which is a frame. Backing up a little bit, we defined a partition as a user ID. We said chunk the rows by this. Ten we declared a sort order of that partition.

Now within that partition, we can declare what's called a frame. A frame declares how far ahead you should look. Rather how far ahead Postgres should look within a single partition. Not beyond the bounds of a partition, but within a single partition, how far ahead and how far behind should it look as it's calculating this last value thing?

Let me see if we can prove that out. All right, this is the one that's giving us trouble. If we look, you see it just kind of increments up as it processes each new row. What we're gonna do is make some space. We can refactor this definition out to a named window, which we'll do in a second. The first thing we're gonna do is we're gonna declare a frame. You have a few options here. You have rows, you have range, you have groups. We're gonna say rows. There are different ways to declare a frame. It makes sense for us to do rows. We're gonna say between unbounded preceding, that is the start of the frame. Then we declare the end of the frame. You guessed it, it's unbounded following.

We're saying there are no bounds. Go all the way to the end of the partition. If we run that, boom, it just works. 593071 is indeed the first. If we scroll down to where it switches, 4500927, 4500927. We have successfully declared a frame within a partition. It doesn't have to be unbounded. We could say three. If we look here, while we are on row number one, or rather the first row in this partition. While we are here, it's looking forward one, two, three rows, and it's grabbing 918986, 918986. This is declaring how far ahead it's allowed to look. We can make that a little bit clearer if we did row number, which is another function that you can use here.

You've got like percentile rank, distribution, you've got all kinds of functions that work within these windows that are very useful for statistical analysis, rolling averages, that sort of thing. But let's just, let's do it like this. Always forgetting commas. If we do it like this, while we are on row number one, it looks forward to row number four down here, which is one plus three, and it says 918986. You'll see the row number indeed does reset at the new partition. You can also do between three proceeding, but you'll notice not a thing changes, not a single thing changes because the last value is always computing.

Let's change it to unbounded so it stops moving. The last value is always computing the furthest thing within the frame. And it doesn't really matter what row, how far back I'm looking 'cause in fact I'm not looking back. You can say between current row and unbounded. And that just totally works the exact same way. We could come up here. We could come up here because this one is in fact looking back, and by default the frame is the beginning of the partition through the current row.

By default, first value just works totally fine. But we could say, all right, let's do three ahead and three behind. If we do three ahead and three behind, and now we look at that, we see three, oh, I left rows in there between three preceding and three following. We can just change this one. It doesn't matter, like I said earlier. We can do three preceding and three following, and you'll see there's nowhere to look back until we get to this one. It's gonna look back three, and it's gonna come back here, and it's gonna find 738396. Similarly, the last value is looking ahead three.

To be even more correct, we would say between three and current row and between current row and three because it simply does not matter. This one is only looking backwards. We'll just stick on the current row. Who cares? This one is only looking forward, so we will just start with the current row. Who cares? I wan to look at one last thing on this example, and then we're gonna move on to our very last thing. Hopefully you love window functions because this is kind of a meaty topic.

Let's do one last thing here. Let's reset these frames to be the same. We'll say unbounded preceding and unbounded following. We're just gonna take that entire partition, and we're gonna bring that up here. So those are the same. In fact, let's just share this as well. Now we are guaranteed that Postgres is going to process this one time because it's the same partition, same order, same frame, everything. That's good news. It is a little, it is a little unwieldy, right?

What if we, what if we just put that in the clipboard and hope for the best and then say over, we'll say user bookmarks. We can come down here and say window user bookmarks as and put our window definition in here. If we come up here over user bookmarks, that worked just fine.

Let's take this and replace all of these. We have a nice little, it's almost like a variable extraction here. Spacing's a little off, but you'll see we are still getting the results that we want. The row number is counting up. First and last value work. We have factored out this user bookmarks window definition. We've factored it out to a lower part of the query. The good news is, now we are safe.

We know that if we change it in one place, it's gonna change in all places, which is good for data correctness, but it's also good for performance. That Postgres can look at it and say, oh that's the, I know that that is the exact same. I'm just gonna do that one time, and I'll calculate all three of these things at one time.

Let's look at, we're almost there. Let's look at, instead of using rows as the frame specification, let's look at using a range as the frame specification. We're just gonna delete all of that, and we're gonna go back to sales. Select * from sales. What if we want to say, I want to select all of the sales. I need to see all of it, but I also want to see the biggest sale on five days either side of the sale that I'm looking at, right? I want to know within five days preceding and five days following, was my sale the best? Beause I want to be the best. I want to be the, I want to be the best boy.

If we do max amount, then we can open up our window definition here. Normally, well, maybe not normally, but historically we've done partition by. You can skip it. You do not have to do partition by. You can skip straight to ordering. We're gonna do order by sale date ascending, and then we're going to declare our frame as range between minus five days and, nope, we're gonna do five days preceding. That was clever. Minus five days is maybe a good addition and five days following. If we run that, did we get it right? Who knows? Let's find out.

If we're sitting on January 15th, and we look five days ahead, we are allowed to see, we are allowed to see, 01-17, but five days ahead is only 01-17. The biggest sale in that 10 day span is $1200. This one worked just fine 'cause 800 is less than 1200. Then if we get to, now we're on 01-17, and we look back five days and forward five days, the biggest sale there is still $1200. This is controlling the frame specification, even though we don't really have anything to partition on. You could of course partition and do a frame or you can use the entire result set as the partition and do the frame only.

Let's find another couple of sales that are close together. These two are close enough together that they would end up in the same frame. Again, the max, the biggest one did win. We can see that our frame is working as expected. Okay, so I've probably worn you out on window functions. I'll leave a few links below, so you can see the rest of the functions.

We've done the traditional aggregate functions, average, max, sum. There are also lots of great window functions, which is like you can do lead and lag. Let's say you're within a frame that's 100, it's a 100-row frame. You can say, let's peek ahead two rows and grab a value out of there. Let's peek behind two rows and grab a value out of there. That's the lead and the lag function. You also have percentile rank and cumulative distribution and just straight rank and row number, all kinds of stuff. I'm gonna stop there.

As a reminder, window functions allow you to work on a subset of rows and do calculations over those rows within a partition, within a chunk. Unlike aggregations or groupings, it still preserves all of the values in the row. You do not lose all of that row information. You just gain additional information.