-: Hi guys and welcome back.
In this video, we're going to continue talking
about window functions with the example
from our polling app.
So here is the query that we had earlier on.
You can see that everything is stuff
that we're familiar with,
except for the thing in the middle
which is the count divided by the sum of the count over.
So we're going to be focusing
on that specific line as we go through,
but first of all, let me just bring this up here
and we're going to evaluate what data we're working with
and whenever you're analysing one of these functions,
that should be the first port of call.
What is the data that you have?
And really the data that we've got here
is everything in the from clause downwards.
And so we've got all of this stuff here.
Now, from options gives us the options table.
When we left join it on vote, we're going to get
duplicate rows in the option table, one for each vote.
Run this separately in a fiddle or on your database
if you want and you're not exactly sure what I'm saying,
but essentially we'd end up with a bunch of duplicate rows
for options and each row would have a different vote data.
But then, because we're grouping,
we reduce the duplication in the options rows
and we end up with some hidden groups for the votes.
Those groups, as we know, can only be accessed
with an aggregate function
like sum, count, average, et cetera.
Note that I'm ignoring the where clause here
because it's not really important at this point in time.
So, the question now is, is this data here,
the rows and the hidden groups,
what the window function has access to?
And the answer to that is no
because the aggregate functions,
in this case it's count, run first.
So we're going to get this data,
but with the count already calculated.
And it's going to look something like this.
We're going to have all of the option data
and then, instead of the hidden groups,
we have the already calculated count.
So that gives us three, two, and one for each of these rows.
Note that the column name here is count of votes,
the option ID, and I don't mean to imply
that has to yet be calculated, that's already calculated,
that's just the name of the column.
So, now that we've got this, the count runs first
and that is the data that the window function,
the sum function in this case, has to work with.
So let me just bring this up here to the middle
and now you can see that we can very easily run sum
on this stuff here.
Why?
Because we've just got four columns
and one of them has a bunch of numbers
so clearly we can add those.
So what we're going to do is we're going to focus
on this particular part of the select statement
and we're going to focus
on the first row of data specifically.
By the time that this output is evaluated,
that means that the count has already been calculated.
That's why we've got the number three in there.
So we're going to start
with the calculated counts down there.
Those rectangles there mean the value
that these expressions represent.
So you can see that on the left,
the count is on its own, it's not in a window function,
and what it evaluates to is the value that the current row
is working with, so that is the count
of the associated votes, that's three.
But on the right, we don't quite get the three
because we're going to run this in a window function
and what that is going to represent
is the counts throughout the entire window.
That's what the over means.
So we end up with three, two, and one.
When we add them together, using the window function,
that has access to this entire result set
because the window function runs
after the count has evaluated.
So it has access to the calculated counts.
Then we can divide and we can multiply by a hundred,
giving us the fifty percent.
So that's really the important thing to take into account.
By the time the window function runs,
the count is no longer a function.
It is just the value and sum is operating
over the entire results,
whereas without the window function,
you're just thinking about individual rows.
That's what makes the window functions a little bit tricky
because they are operating essentially
at two different stages
and have access to two different groups of data.
So hopefully these diagrams have
somewhat helped you understand what's going on.
But in the next two videos we've got a few more examples,
including a couple of things that you can do
with window functions to, for example,
sort columns and partition them before operating on them.
So let's go over to the next video
where we're going to look at that.
Thanks for watching and I'll see you there.