Video summary
Window functions in PostgreSQL represent a powerful extension of standard SQL that allows for calculations across a set of table rows related to the current row without collapsing them into a single aggregate result. Unlike traditional GROUP BY clauses which reduce multiple rows into one, window functions operate row-by-row while maintaining the context of surrounding data. The tutorial introduces this concept using the ROW_NUMBER() function, which assigns a unique sequential integer to each row within a partition of a result set. A key mechanism discussed is the OVER clause, specifically the PARTITION BY keyword, which acts similarly to a GROUP BY but preserves all individual rows. This allows users to reset numbering or calculations for specific groups, such as assigning row numbers separately for different species in a dataset, rather than creating a single continuous sequence across the entire table.
Beyond simple numbering, the video explores how to order partitions and compare ranking functions like RANK() and DENSE_RANK(). These functions are particularly useful when dealing with ties in data values, such as estimated net worth. The distinction between them lies in how they handle gaps in rankings: RANK() skips numbers for tied entries (e.g., if two people tie for first place, the next rank is third), whereas DENSE_RANK() does not skip numbers, ensuring a continuous sequence of ranks regardless of ties. The lesson also demonstrates cumulative calculations using SUM() and AVG() over ordered rows to create rolling sums and rolling averages. By adding an ORDER BY clause within the OVER specification, these functions can calculate a running total or average up to the current row, which is essential for financial analysis where one might want to see the sum of all transactions leading up to a specific date.
To make these calculations more precise and relevant to real-world scenarios, the tutorial explains how to limit the scope of window functions using the ROWS BETWEEN clause. This syntax allows users to define exactly how many preceding rows should be included in the calculation alongside the current row. For instance, instead of averaging every single previous value ever recorded, a user can configure the function to look back only at the two preceding months and the current month. This approach often yields more accurate rolling averages for time-series data, as it reflects recent trends rather than being skewed by historical outliers from years ago. The video concludes by mentioning that while these are the most commonly used window functions, others like LAG, LEAD, and NTILE exist for specific needs, and future lessons will cover advanced topics like Common Table Expressions (CTEs) to further enhance data manipulation capabilities.
Read the full video transcript
What's going on everybody? Welcome back
to another video. Today we're continuing
our Postgrace SQL series. In this
lesson, we're taking a look at window
functions. Now, window functions have
been notoriously confusing for a lot of
people, but I'm going to try to break it
down really simply so you understand the
main types of window functions and how
to use them. Before we dive in, let's
actually come right down here and I'm
going to pull up this image because I
think this describes window functions
pretty well. When I think of a window
function, I actually compare it a lot to
something like a group by where you're
taking multiple rows of data and you're
aggregating them down to one row. This
is fantastic. We love group by and
aggregate functions within SQL. But
window functions right over here, you'll
notice we take it row by row, but then
we apply it to each row. We don't
aggregate it all into one row. So this
is window functions in its simplest
terms, but there are window functions
that do different things and we'll get
into all of those in this lesson. Now
let's get rid of this because we're
going to start writing out some window
functions. Now I'm just going to give us
some room really quick and we're going
to keep that everything just in case we
want to see it. Let's take everything
and let's take our character_ame.
Then let's come down here. Let's add
some tabs and we're going to start with
row number. I think row number is kind
of the simplest window function and it's
the easiest to explain and then we'll
kind of go on from there. So we're going
to do row
number and then we're going to say over
and this is everything we're going to
write. Now this row number function is
going to apply a single number to each
row. That's all it's going to do. Now
this overwrite here is a specific
keyword. It's a function that tells us
how to look across the rows without
grouping them. So it won't be like an
aggregate function. It'll handle it row
by row. Now let's run this because this
is just the simplest one that we're
going to see. Let's go ahead and run
this.
And there we go. So we have Luke
Skywalker, which is one layer 2 3 4 all
the way down to 12. We really didn't
give it any information here. We just
said apply a row number. That's all we
did. Now let's come back up here. What
we can do is we can make this a little
bit more advanced. Maybe we want to give
it a row number, but we want to break it
out by the species. So, we want to say
within the species, give it a number.
So, we're going to use something called
partition by. Now, partition by is kind
of like a grouping. We're grouping it by
the species, and then we're applying
this row number, but we're not actually
grouping it, right? We're keeping it
rowby row, but we're going to apply that
row number for each of the species. So,
let's break it out by species. And let's
bring the species up here as well.
And let's go ahead and run this. And I
think I forgot a comma here. Let's add
our comma. And we actually may need to
add an alias to this. I know um
sometimes it requires an alias. Let's go
ahead and try to run this now. And there
we go. So now we're breaking it out by
species. It already kind of has it
ordered for us, right? We have droid
one, two, and then it resets at the next
species. So, Gungan 1, human 1 2 3 4 5 6
Unknown 1, Wookie 1, Zack 1. And so,
what it's doing is we're partitioning it
by each species and then we're applying
the row number to that partition. Now,
right now, we are just giving it a
random row number based on the species.
That's it. We're just numbering within
each species, but that isn't super
helpful. Let's go back to our data
really quick and let's say we want to do
it based off of the estimated net worth.
So, let's add this in here. We'll say
estimated
net worth, and then we're going to come
right down here. Now, since we're adding
one more thing in here, I'm going to
kind of make it a little easier to read
and put it like this. So, I'm going to
say partition by and then I'm going to
say order by. Now, we're going to order
by this estimated net worth. So, let's
take this right here, and I'm going to
say descending. So, I want the people
with the highest estimated net worth to
get the first row number. Before we run
this, here's what we're doing. We're
assigning a row number over, and the
over is taking it rowby row, but we're
giving it some extra context. We're
partitioning it by the species. So,
we're applying row number within the
species, and then we're ordering it by
the estimated net worth. So, let's go
ahead and run this entire thing. So, now
we have a droid and we have another
droid. Right here, we see their
estimated net worth. R2-D2 has a higher
estimated net worth than C3PO. So, R2-D2
is number one and C3PO is number two.
Now, let's go down to the humans. We
have all of our humans right here. We
have our estimated net worth going
highest to lowest. And so, we have our
numbers going one all the way down to
six. And so within the human species, we
are giving them a rank based off of
their estimated net worth. Now, in this
exact example, we're kind of ranking
them. And if we scroll back up, we
actually have two functions called rank
and dense rank. And both of these
functions are used for these exact
circumstances. And let's take a look at
the difference between these window
functions. So let's copy this entire
thing just so we preserve it. And let's
come down here and we're going to change
this one to rank. Now, rank and dense
rank are super super similar except for
one tiny difference. And we're going to
see that difference between Han Solo and
Obi-Wan Kenobi. So, take a look at these
people right here and how they actually
rank them. So, let's just keep it as
rank. Everything is the same. Let's go
ahead and run this right down here for
Han Solo and Obi-Wan. They have the
exact same estimated net worth. They're
tied in net worth. And so what it does
is it's going to say 1 2 3 4 4, but then
it doesn't have a fifth place after it.
It goes right down to sixth place. So if
there were 10 other people who had
30,000, it would go 44 44 for 10 people
and then it would start at 15 or 16. But
let's look at dense rank. And this is
the only difference between rank and
dense rank right here. So let's run
this.
So now you'll see we have Han Solo goes
30,000 1 2 3 44 but now Luke Skywalker
it picks up at the next numerical number
and so that is the only difference
between rank and dense rank and this is
a question you might get in like an
interview or something and so knowing
the difference between these can be very
useful not just in real world but also
if you get asked this in an interview so
we've already covered a ton of things
and we can you know change these up by
partitioning by different things by
ordering them on different things. But I
think what we're going to do is we're
going to start over and we're going to
look at things like moving averages,
moving sums, and things like that
because these are especially helpful as
well. I use these quite a bit,
especially when I'm working with money
or finances or things like that. So,
what we're going to do is we're going to
come right here. We're going to say
character_ame
and then we're going to bring in our
estimated net worth.
Let's add our comma. Let's come down
here. And now we're going to use just a
typical aggregation. We're just going to
say sum. And let's just keep it like
this really quickly. We're going to say
sum over. Now let's just run this. Now
what we're going to be doing is this
over right here takes us rowby row. And
in fact, let me just get our default
data in here. And so let's go down here.
And so we have this sum over. Now all
this over is going to do is take it row
by row by row. We aren't partitioning on
anything just yet, right? So, this is
the simplest form. But what we're going
to do is we're going to take our
estimated net worth and we're going to
sum every single one of these estimated
net worths, but then apply it to each
row. So, let's go ahead and run this.
And so, our sum right here is 24,14,000.
Yeah, 24 million. So 24 million is being
applied to this because we're summing
every single row, but we're applying it
to each row at the end in its own
column. And we can name this, we can say
as uh total net worth. And we'll keep it
really simple. So all we're doing is
we're summing across everything and then
we're applying it. That's all we're
doing. But what if we add one simple
thing? And that's going to be an order
by. So, I'm going to come right over
here and I'm going to say order by and
then we'll do estimated net worth and
let's start from the smallest. We'll
make it ascending and we'll make that to
the largest. So, I'm just formatting it
so you can kind of easily visually see
what we're doing here. But we're summing
the estimated net worth over, but now
we're ordering it by the estimated net
worth, lowest to highest. Let's go ahead
and run this. So now we're not simply
summing everything at one time and
applying it. Now we're doing it rowby
row ordered by the estimated net worth.
So here we have 500, we start with 500.
Then we add the 1500, we get 2,000. Then
we add the 2,000, we get 4,000. The
50,000, we get 54,000. The 120,000, the
174,000. So you can see we're just going
zigzag pattern back and forth. And so
this is really helpful. This is what's
called a rolling sum. As we go down,
we're just adding the next row into this
total net worth until we get to the
bottom. And that's where we add up every
single one. And that's our final number.
Now, just like we did before, we could
also add a partition in here. So, let's
just add in our species. And I need to
spell species, right? And we'll add in a
partition just like we did before. And
I'm just going to bring it over here.
We're going to say partition
by then we put our species. So now we're
going to take it species by species and
then we're going to do our rolling sum.
So let's go ahead and run this. So now
we're doing droid. So we have 1500. Then
we're adding the 2,000 to get 3500.
Let's go down to human. We start with
our 150,000. We add in our 300,000. Then
we add in and because these are actually
tied, these get combined into one
aggregation. So we actually add 15,000
to 60,000 which equals 75,000. And
that's just one weird quirk. Now, there
are ways to actually get around this,
but it gets a little complex. You have
to have like a CTE and then you assign a
row number and then you can add it rowby
row. But that gets a little bit advanced
and we haven't even covered CTS yet in
this series. And then we add our 5
million and then our 8 million and then
our 10 million. And so this is our
rolling sum partitioned by the species.
Now, let's change this really quick.
Just changing the aggregation. We're
going to say this is our average
estimated net worth. So instead of a
total net worth, we're going to say our
average
underscore net worth. Now let's run this
and just see how it acts. So now what
we're doing is we're taking 1500 and
then we take the 2,00 and we're
averaging between them. So now our
average between these two numbers is
1750. Now, we're going to do the same
thing, but in this one, we're averaging
all of the previous ones that we do. So,
when we get to the bottom, we're just
taking an average of all of these. So,
for 150,000, it's only 150,000. But when
we add these two together, it's 250,000
because we're taking into account all of
these. Then, when we add in the 5
million, it's 1.4. Then, we add in the 8
million, it's 2.75. Then, we add in the
10 million, it's 3.958.
So, this is what's called a rolling
average. Now, this in its simplest form
is totally fine. I'm going to give you a
real use case. A real rolling average
won't look back 6 months, a year, 10
years, whenever. You're going to do it
chunk by chunk. Maybe you look back one
month and you look forward one month and
that's your average for your current
month. Or maybe you just look back two
months. And you can actually specify
that in something like this. So, let's
look at how this syntax would look.
because what we want to do, especially
for this human because there's lots of
humans, we only want to look back a
certain amount. So, let's run this.
We're going to say rows between,
and this should look very familiar. If
you watch the string and date functions
lesson, this should look very similar
for date functions, but we're going to
say rows between two preceding
and current rows. Now, let's go ahead
and run this and then we'll take a look
at it. If we go down to human, so this
is going to be the exact same. All we're
doing is taking the current row and
we're getting the average. But what
we're going to be doing in the next ones
is we're only taking the two previous
ones and the current row. So let's take
a look at this one. We're taking this
one plus these ones. But then when we
get to the 5 million, we're only taking
these. We are not taking this one into
account because this is three
proceeding. Then when we get to the 8
million, we're now only taking into
account these ones because this is the
current row plus the two preceding. Then
we get to the 10 million, it's only
taking into account these ones because
the current and the two preceding. This
is probably a more accurate rolling
average than taking it all the way back.
So we can come up here and this one
would now probably more accurately be
called a rolling average. Now, even
before we added in the two preceding and
current row, I guess you could call it a
rolling average, although you're taking
every single previous value. But if you
just look at maybe one or two months
behind and you average those out, those
are probably a more accurate average to
what you're currently working with
depending on your data and the scenario
in which you're using. And so those are
some of the major window functions.
We're going to take a look at three more
window functions in the next lesson that
are kind of different, right? It's
called lag, lead, and end tile. They're
a little bit different than the window
functions that you're probably going to
use for the most part, which are the
ones that we covered in this lesson. And
so, I'm going to have one more lesson
before we then look at CTE, which are
common table expressions, which are so
useful. I really hope you learned
something in this lesson. If you did, be
sure to like and subscribe, and I will
see you in the next lesson.
[music]