Window functions in SQL Server Video Lecture | SQL Server Administration: Basic Tutorials - Database Management

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