Narrator: Hi guys, and Welcome back.
In this video we're going to talk
about PostgreSQL window functions, window functions
are a bit more advanced than anything we've seen so far.
So please do bear with me while I explain
this using some diagrams.
First of all, let me show you what we're working with,
we've got four different segments in this screen,
the data that we're gonna be working
with is on the top left, the query that we're gonna run
is on the bottom left, the output that we get running,
that query will be on the bottom right, and the explanation,
or some bullet points about what's going on
will be at the top right.
So here's the data that we're gonna be working
with just now for this example.
We've got employee data, and these employees have a name,
a salary and a department.
And now let's say that we want to run a query like this one.
Select salary and the average of salary
from the employees table.
Now, we know that we can't do this and because we are trying
to select the average of something that hasn't been grouped.
So this is ideally what we would want,
but it's not really what we're gonna get.
So we know that this doesn't work, it looks like the average
for this query should be calculated in the whole column.
But it doesn't work because we don't have a group by.
So what can we do in order to make this work?
Well, we have to use a window function.
So here's the same data set,
but now we're going to group by id.
Now we know that when we calculate
the average of a column that we have a group for,
that's gonna work, but what it's gonna do
is going to give us the average of each group as the result,
which seems we only have one id per person with that salary
is just giving us the same value that we started with.
So this is almost completely useless.
But it does work because we have a group by.
Same thing again, but now we're going
to use the window function,
so what we've got here is the average of the salary
and then we've got the keyword over,
which is itself a function as well.
Now, this is actually gonna give us what we want.
We have the salary for each person,
and then the average of all salaries on the right.
Why might we want this, for example, if we want to compare
whether each person salary is greater than
or lower than the average for example.
So average of salary over
is what we call a window function.
And it operates on the related rows
of the row that we're calculating,
rather than on the same road that it would without the over.
So I'm gonna explain a bit more
what that means in just a moment.
The related rows in this case though,
the thing that the average of salary is operating on,
are all the rows in the table.
So what we're doing here is we're selecting the salary
as well as the average salary of the entire table,
from the employees table,
I'll show you a quick diagram on what I mean you
here we've got that same query.
And what the query does when you use a window function
is essentially get the non window functions
and get the window functions separately,
because it's a window function, it runs in its own area
if you like to think about it that way.
So we've got the Select salary in one side,
and select the average of salary
from the employees table on the other side.
Something to note is that the window function
has access to everything that we have in our FROM clause.
So here we're simply using that to select
the average of the column.
Then what happens at the end is these two results
get merged together, and they give us the salary
and the average for everything.
So this is how the window function works
when you have the over clause with nothing in the brackets,
so, you can use the brackets in the over function to define
the window more specifically.
Inside the brackets we can add code that tells PostgreSQL
what the related rows are.
And if you don't put anything in the brackets,
then PostgreSQL assumes the related rows
are the entire thing that you've tried to get.
PostgreSQL comes with a bunch of functions
that must use over and they are called window functions.
And other functions like average count,
or sum can also be used as window functions.
Something important to note, and we're gonna elaborate
on this in the next few videos,
is when our window functions evaluated?
So here's a quote from the official documentation
that says that if the query that you're running
contains window functions, then PostgreSQL
is gonna evaluate those after grouping aggregation
and having filtering is performed.
So the query users and the aggregates group by
or having, then the rows that the window function
sees are the group rows instead of the original table rows.
So in our example just now where we use the average over,
we didn't have any of these and grouping aggregation
or having filtering, so the window function saw everything.
But that changes when you have aggregates
group by or having.
Window functions also run after normal
where filtering is applied.
So the window function will only run on data
that has been filtered out by where,
and window functions have access to all the columns
that you have in your FROM clause
including joins and all that stuff,
as we're gonna see with another example in the next video,
not just the selected columns.
Alright, thank you guys for joining me for this video,
I hope this starts to make a little bit of sense,
but over the next few videos, we're gonna look
into more detail as to what window functions
are and how we can use them, as well as a bunch
of different options that we can give them.
I'll see you in the next video.