-: Hi guys, and welcome back.
In this video we're going to look at how to use PARTITION
with window function so that we can further subdivide
the data that the window function is working with.
So let's get to it.
Here, we've got the data for this lesson.
Again, we're working with polls, options and votes,
and we've got two polls in this case,
five options, and a few votes down here.
What we want to achieve is to get,
for each option, how many votes they have
and rank the options in order of their votes,
but we want to make sure that they are subdivided by poll.
So the top option in a poll will have rank one
and the second option in a poll
will have rank two, and so on.
So here, for example, we've got Flask
that has the most options in this poll,
that would have rank one.
And Python that has the most options
in this poll would also have rank one
because they are the top option in their poll.
So that's what we want to achieve.
What we've got down here is some code
that is gonna give us the poll title,
the option text for each poll,
as well as the count of votes for each option.
And so what I'm gonna do though,
is make sure that this is correct.
Like that.
And press run.
And now what we get here is Python vs. Java,
option is Python and the votes are two for that option.
And in Flask vs. Django we've got
these different vote counts there.
And finally we get back to Python vs. Java.
You can see these votes are,
or these options are not ordered by any way
and you can see the vote counts in each.
So what we want to do is use the rank function.
But how are we going to order
because if we order by vote count
then you can see that we're gonna get
some strange results, right?
And so, let's try it and see what happens.
And the reason why we're gonna get strange results is
because these options are not subdivided
by poll so when we rank, we're gonna rank
all of the options together.
So let me do rank, over and then we're gonna order
by the count of votes in descending order,
see what happens, so I'll run this.
Just gonna come down here
and so you can see a bit better
and you can see that we get rank one for Flask
because this is the top voted option
across all of the votes.
Rank two for Python and then Django
and Java have both rank three
because they have the same number of votes.
Finally, we've got It Depends on rank five.
The reason why it's got rank five and not rank four
is because the rank function skips a rank
if it has a duplicate rank.
You can use RANK_DENSE, by the way,
in order to, or sorry, it's DENSE_RANK, not RANK_DENSE.
DENSE_RANK there in order to avoid that skip,
so now you've got rank three,
rank three, and then rank four.
Just up to you which one you wanna do.
I'm gonna stick with rank for now.
So we've just ordered and we've told rank
what column to use for ranking,
but we haven't told it to treat each poll
as a separate group of ranks, but we can do that,
simply by using PARTITION BY and then polls.title.
If we do this, then rank is going to take the first title
and it's gonna rank the rows for that title,
then it's gonna take the next title,
and it's gonna rank the rows
for that title separately and so on.
So if we run this, then you'll see that we get
what we expected, one two and three, and one and two.
Notice that this also ends up as ordered in a way,
because of how the rank function operates.
I'm gonna leave a link in the resources section
of this video for you to read a bit more
about some of these functions that you can use,
some of these window functions.
There's a great post over at PostgreSQL Tutorial
that covers some of these most used window functions
and it's really good.
So I recommend that you check it out
if you want to learn more about window functions,
partitioning, and ordering.
Other than that, thank you guys
for joining me in this video.
I hope you've enjoyed it.
Thanks for watching.
And I'll see you in the next one.