Window functions in SQL Server

this is part one 1/6 of signals over tutorial in this video well discus vendor functions in sequel server we have different categories of window functions in sequel server we've got aggregate functions examples include some count min max etc ranking functions like rank dense rank row number etc and analytic functions like lead lag first value last value etc with all these functions we can use the over Clause / clause when used with these functions defines the partitioning and ordering of rows that is a vendor for these functions to operate on as these functions are called vendor functions the over clause has got these three arguments which define a window for these functions to operate on so this order by Clause defines the logical order of the rows partition by clause divides the query result set into partitions the window function is then applied to each partition separately rows or range clause this Clause further limits the rows within the partition by specifying start and end points within that partition so far in this video series we've looked at examples of using order by and partition by along with the over Clause but we haven't looked at any example of using rules or range clause if we don't specify an explicit value for this rows or range Clause then it's going to take up its default value which is range between unbounded preceding and current row let's understand what this value means with an example we'll use this employees table in this demo we want to retrieve name gender salary along with these three columns we also want to compute the average salary and display that against every employee row now to compute the average salary we use average function so average of salary from employees table so when we execute this we get the overall average which is 54 50 so we want this number to be displayed against every employee row so let's use this average of salary function and let's use our favorite over Clause along or buy salary okay and let's give this column an alias let's call this average let's execute this and see what we get so we expected that 5450 be to be displayed against every employee row but look at what we got we got you know a different average salary for every employee only the last employee row has got the correct average which is 54 50 now let's understand how this varies value is computed the first thing to notice here is that we are not specifying an explicit value for this rules or range Clause so since we haven't specified an explicit value it's going to take this default value range between unbounded preceding and current row let's understand what this value actually means now let's say this average function is being applied for the second row now in that context let's understand what this Clause means this value means range between unbounded preceding and current row so for this average function the range of rows are going to be between unbounded preceding and current row now we know what is current row current row is the row for which you know the beverage is being computed unbounded preceding means the vendor for this average function starts at the first row within the result set okay now this order by Clause is being imposed on the rules in this employees table so that's our result set so within that ordered result set unbounded proceeding means the window starts for this average function at the first row so the rows ranges between unbounded proceeding that is the first rule within the result set and current row so when it is on the second row the range of rules for the average function you know is the first row and this current row so it passes thousand and two thousand so the average of those two rows is 1500 and that's what we get when we are on the third row you know the range of flows is between the first row and that row so it passes all these three values average of three thousand plus two thousand plus one thousand you know totally six thousand when we divide that by 3 we get two thousand that's how it's computing average at the moment and that's because of this default value for this rows range Clause and if you look at the last row it has got the correct value and how is that possible because you know it's going to compute the average of all these rules you know when it came to this 10th row okay now we will have the same problem whenever you be used any of these window functions that we have because of this default value for that clause okay so let's look at for example count and some and let's give these columns aliases as well let's call the sum since that is a function name let's wrap it with a pair of square brackets and let's change this to count all right so let's execute this query and see what we get look at that count and some also work in the same way so when it is on the first row it says the count is one that's on the second row both the rows are passed so the count is two so on and so forth and some same idea that's on the first row it's thousand when it's on the second row Buddha rules are passed because the default value is unbounded preceding and current row okay so we get 3000 when we are on the third row you know 6000 and it goes on now how to correct this now to correct this we will have to somehow tell to these window functions that the window of rules that they have to operate on starts at the first row within this result set and ends at the last row within the result set now what is the default value range between unbounded preceding and current row now I'm going to change that to rows between unbounded preceding so what does unbounded proceeding means that means the window starts at the first row within the result set and we want the window to end at the last row in the result set and the way we tell that is by using unbounded following okay unbounded following means the window ends at the last row within the result set so let's copy this and paste it for all the three functions and see what happens all right so let's execute this and look at what we get now we get the total count total average and total sum okay now at the moment we don't have any partitions involved if partitions are involved then the meaning of this is going to slightly change now let's actually involve partitions so let part let's partition this data by gender column so let's copy and paste that for all the three functions alright let's execute this and see what we get look at that now these functions are applied within that partition and rows between unbounded preceding and unbounded following in this case means unbounded preceding means the window starts at the first row within that partition and unbounded following means the window ends at the last row within the partition not within the entire result set if you don't have partitions involved then unbounded proceeding means the window starts at the first row within that entire result set and it ends at the last row within the entire result set when you use unbounded following alright now let's look at one more example let's say you know I want to compute the average of the current row and the row preceding that only one row preceding that and one row following that basically is what we want to do so here is our first example where we are not specifying an explicit value for rows or range Clause our second example where we are specifying an explicit value for rows or range Clause so rules between unbounded proceeding and unbounded following now what we want to do is compute the average salary of the current row one row preceding the current row and one rule following the current row that means in our example when we are on the third row you know we want to compute average of you know this row plus one row before that and one row after that similarly when we are on fourth row we want to compute average of the current row plus one row before it and one row after that now the way we achieve that let's actually remove this partition by clause to keep things simple let's do the same thing for this last okay now here unbounded preceding means the window starts at the first row within the result set but that's not what we want we want in a one row from the current row right so one preceding and one following so from the current row one row preceding and one row following similarly let's do the same thing for sum and count so now this is going to do what we want let's actually look at that in action let's execute this and see what we get now look at the count now this is the first row before this row we don't have any row so count of that row we don't have anything before so zero after that we have one so we get two so count then is for every other row it's going to be three except for the last row because for the last row we have one row before it but we don't have any row after that now if you look at average it's going to be interesting so for the first row look at that and now we don't have any row before that one row after that so it's only going to take average of these two thousand plus two thousand three thousand divided by two 1500 when it comes to the second row 2 + 3 5 5 plus 1 6 6 divided by 3 - okay so it's doing what we want and the same is true for some now if you want to do it for like two rows preceding and two rows following simply change that number to two and you get what you want so we have more control and flexibility by using this you know rows or range claws thank you for listening and have a great day

Channel: kudvenkat

Views: 177,116

Keywords: window, functions, sql server, examples, rows range clause, range between unbounded preceding and current row, range between unbounded preceding and unbounded following

Id: TzsrO4zTQj8

Length: 10min 59sec (659 seconds)

Published: Wed Oct 07 2015