And the final two functions will look at the final two window functions.
We'll look at our lead and lag.
And these ones can seem intimidating up front, but they're quite useful.
They are most often used to make some comparison to find a difference between one row and the preceding
row or the next row.
So the way that it works syntax wise is we have some expression that we provide between the parentheses,
and then if we're doing lag, that will return the value of that expression from the previous row.
If we're doing lead, that will return the value of the expression from the next row.
And I think it's easiest to show this as an example, because it is a little confusing when you just
read about it on the docks.
So if we do a salary for every employee and then I do lag of salary.
That will evaluate to if I let me do an over I have to have that let's do order by salary to sending
something like that.
This is going to evaluate to the previous salary for each row in a given window.
And if we only have one window, then it will just be the previous salary and you'll see what we get.
So for this first row, 159,000, there is no row before it.
There is no previous salary.
But then we get to this row 103,000.
What's the previous value?
159,000 right from right there.
So maybe useful, maybe not useful on its own.
But what we could do is calculate the difference.
And what is the salary jump between these rows.
So salary minus the previous row salary.
And then we'll call this as I don't know, salary dif.
Okay, let's try running that.
Here we go.
And here's our difference.
So the first salary up here, 159,000.
There is no difference with the previous value because there is no previous value.
That's the highest.
But then we have 103,000, which is a 56,000 difference.
And then this row, 91,000 versus 103,000.
The difference is 12,000.
89,000 versus 91,000.
The difference is 2000.
So hopefully you can see how that works.
We also have lead.
So if I did minus lead.
That's going to compute to the value of the the next row's salary.
So right here we have 159,000 and 103.
This was calculated based off of 159,000 minus the next row's salary, which is this right here.
So we can look forward or backwards.
And again, that's within each partition or within each window.
And right now we have one window.
But let's try another example where we partition by department.
So I'll do salary minus the previous rose salary over and then I'll say partition by department.
So now we end up with our different windows.
Each one is ordered by salary.
So then we're going to calculate the difference between each row and its previous row in terms of department
salary.
So we'll call this department salary diff.
And I'll run this.
And here we are.
We see within customer service the highest paid person of 61,000.
There is no lag, so the value here is null.
But then the next person is 56,000.
That's a difference of 5000.
We took this row and we subtracted the previous value for salary.
Same thing within engineering 103,000 sets the top salary.
And then the next one is 90.
1000 is a $12,000 difference.
Nowhere near as big as this one within sales.
I guess our sales team works on commission and this person is a real producer, a real sales guy or
gal, 159,000.
And then the drop of 87,000 in salary to 72,000 is the second highest paid employee within sales.
So that is lag and lead.
It's often used to calculate differences between rows.
It doesn't have to be a difference.
You could do whatever you want.
I mean, as you saw, we could just put the lag.
That's only the previous salary.
Not that useful, but that's what we see, right?
Previous salary was 61,000.
Previous salary for this row, 56,000.
More often than not, you'll do some calculation.
You can actually even get fancier and you can specify how many rows back.
Let me what happened here.
You can specify how many rows back by providing a number afterwards.
Like if I do salary comma to this will get me the salary from two rows previous.
So we have this row right here.
55,002 rows ago was 61,000.
Not useful probably in this situation, but you can do that.
All right.
So that wraps up our discussion of window functions.
There's a lot of tricky stuff here, but I hope you can walk away understanding by now the concept of
partitioning your data, your rows into some set of windows.
Each one of those windows can be ordered and then you can run functions on them.
Get the first value, get the nth value, get the average, get the sum, get the rank.
And then instead of being reduced down to one value, we end up with rows where we have the results
of those window functions alongside the individual rows.
So we take some big chunk of data, hundreds of rows, maybe thousands of rows, partitioned it into
windows, smaller pieces, and then do some analysis and then recombine it all back into one big table.
So that is window functions.