Next up, we've got a brand new topic, window functions.
Now, I'm going to warn you up front, this isn't terribly hard.
However, it can be a little confusing when you're first starting out.
It has a lot of moving pieces that have to be explained.
So bear with me.
We're going to conquer window functions in this section, and the first thing we have to do is try and
understand what they are.
So if you go to the docs and you look at the window function section, we get a little tiny bit of information
on this first page.
It says, My SQL supports window functions that for each row from a query, perform a calculation using
rows related to that row.
So it's a little confusing if you go to what I what I recommend is go into the concepts and syntax page
to get a better explanation of how it works.
Because if you just go to this page that has the descriptions, you'll see not all that much in the
way of explanation and examples.
It's more of just documentation.
Here's how this one function works.
Here's how this other function works.
But it doesn't really explain what this partition by or over is some of the new syntax that you'll see.
But if you go to this other page that I mentioned, the concepts and syntax, it's a decent reference,
although it is a little brief.
So with that out of the way, let's actually talk about what window functions are and how they work.
So I'm going to be using a very simple data set in these slides just for rows where we have employees.
This would be the employees table that each have an employee number.
So this is from a real data set that I have that we'll be working with shortly that has, I don't know,
fif 20, 30 different employees.
But for the slides I only put four of them.
So they have an employee number, their ID, what department they're in and what their salary is.
Okay, so we have two departments here, sales and customer service, and this is going to be a lot
to look at.
But if I used group B, this is nothing new.
If I did a group by department when I tried to calculate the average salary, well, this is kind of
a visualization of what happens, right?
We would get two groups, one based off of the department sales, another based off of the department
customer service.
We grouped by department, and each of those groups is then reduced down to a single row where we're
selecting the department name.
And the average salary again should be recap even if we don't know this data set.
Exactly.
It's a very simple group by group by department.
Calculate the average salary so we get one row per group and that's how group BI works.
We get one row that is collapsed down from a group of rows.
Here.
We had two sales rose to customer service rows.
They get collapsed down into a single row each.
So that's grouped by, we can call all sorts of aggregate functions and reduce down these groups into
a single row.
Now window functions are very similar to group by in some ways we can perform these aggregate operations
on groups of rows.
But the big difference is that window functions will produce a result for every row in a group or what
we call in the window or windows.
So I'm going to show you an example of one, and then we're going to dive into the syntax in future
videos in this section.
So it's a little overwhelming up front.
Like I said, we're not going to worry about the syntax, but here's my data set.
Imagine this is the whole table.
Maybe there's thousands of rows, but we're just looking at for.
And I run this code here that has a window function in it.
Average salary over partitioned by department.
So this is all new here.
We've seen average but this over partition by.
We're going to get to that.
But think of it as basically saying calculate the average salary by department.
But instead of doing a group by there is no group by happening here, it's very different.
We still get these windows, these basically these groups we partition by department.
So sales is grouped together, customer service is grouped together and the average is calculated for
each of those.
But here's the really big difference.
Look at our output.
Our output has every single row present.
It's not been reduced or collapsed down like a group by.
So we still have the same four rows.
But what we have is the department average that we calculated next to all the other information from
each row.
So if we go back to this definition of window functions, we performed an aggregate operation.
We calculated the average by department, but we produced a result for each row.
Now, a lot of these rows have the same value.
I mean, that's that's how these window functions work.
Often if I'm calculating the average department salary, well, any row that's in customer service will
have that same department average.
Any row from sales has the same sales department average one more time.
Look at the end result here where every row has the department average calculated next to it for that
corresponding department.
If we had a group by like on this slide here we have the same numbers.
Customer service average was five or $55,500.
Sales department average was 59,500.
Same thing here as you can see, 59 555 five.
The difference, of course, is that we took these groups or SQL took those groups from group by and
collapses them into a single row.
So there could have been 1000 sales salaries, sales employees, their average is calculated and collapsed
into a single value one row.
And sometimes that's what we want.
But what window functions allow us to do is to look at aggregate information alongside individual rows
that that information was calculated based upon.
So if you're still confused at is very normal, we're going to take time to go through how this works
and look at the syntax we're going to talk about over in partition by and order by.
And we'll look at some special window functions in the coming videos.
But for now, the main takeaway is when we use a window function, whatever the operation is that we're
performing, it will be performed on some windows of data.
Think of it as just groups, groups of data that is somehow grouped together in some way, in our case
by department name, and then some value is produced that is then re integrated alongside the initial
rows as our output.
So don't feel bad if you're confused.
Hang in there.
We'll learn more in the next video.