Original subtitles

In this section, we will learn about the Windows function.

Now before restarting, let's see how Windows function are different from the functions that we have

already discussed.

Now let's take an example to highlight such differences.

Suppose we have a data of a retail chain.

We have three columns.

We have quarter store and the sales value of that particular store in that particular quarter.

So, for example, in this first row in quarter one, the sales of store A is around 40 units.

No.

If we applied the rule functions that we have discussed, rule functions such as lower upper case or

any mathematical functions such as multiplication, divide, etc. those function will only use the value

of that particular row only.

For example, if we want to multiply the sales value by 0.2 or you can say we are calculating 20% of

the sale.

Then we get this new column where each of this cell is only using the value that is available in that

particular row.

For example, here the sales is 40 units, so point two of that, 40 is eight.

Similarly, for 60, we are getting 12.

And similarly, 0.2 of 100 is 20.

So you can see that we are only using the values that are available in any particular row.

We can't access the values of any other role, for example, in this particular cell.

I wanted the value of some of all the sales values of a story.

I won't be able to do that.

Now, another type of functions that we have discussed are aggregate functions in aggregate function.

We were able to utilize the information available in other roles.

For example, if I group my data by a store and apply the sum aggregate function on sales column, then

I would have got this table.

A store and a total so aggregate functions were able to use the information available in multiple rows.

And role functions.

We were only able to use the information available in that particular row, but in aggregate function

we were able to access the information or data available in multiple rows.

But the drawback of aggregate function is that it will roll up your data.

For example, here I have five rows, but if I use aggregate function, I will get only two rows.

There is no way with these two particular functions to get the aggregated values without rolling up

your data.

The solution to this problem is window function.

In window function.

You can preserve the modularity of your data and you will be able to get the aggregated values or any

other value using the values of other rows of your data.

So for example, if I wanted another column of total in front of each row, I can easily use Windows

function to calculate this column.

So let me repeat once again in the row functions that we have discussed, we can only use the data that

is available in that particular row.

In aggregate function, I can use the data available in multiple rows, but the final result in table

will be a rolled up table.

It will not preserve the number of rows that you have in your original dataset, but with Windows function,

you can utilize the information available in other rows.

And you can also reserve the number of rows or data that you have in your original table.

Now let's see the definition.

So when those function are the functions that provide ability to perform calculation across multiple

rows that are related to your current query row.

And the result of Windows function are calculated using a sliding window frame.

So here our sliding window is this If we are calculating for storage, since we are calculating the

sum of store sales, so the sum is 180.

So for all the store A, we are getting 181 8180.

So this is our sliding window for rows with store B, this will be our sliding window.

And the sum will be 160.

Now, again, I have explained to you how Windows functions are different, so let me repeat once more.

So unlike aggregate function, Windows function does not cause these rules to become grouped into a

single output unit.

So as I have mentioned, you, if you are using aggregate function, all your rules will be grouped

into a single row.

But if you are using windows function, the rows will not be grouped and you will get the aggregated

value depending on the rows values of multiple rows.

And as I have mentioned earlier, Windows functions have access not just to the current row but to the

multiple rows in the sliding window.

Now let's look at the syntax of Windows function.

So the syntax is window function.

You have to mention the window function name you want to apply.

Then you have to write over.

Over is a keyword over here.

And in bracket you have to write partition by and order by.

In front of partition by you have to write the column on which you want grouping and in the order by

you have to write the column name on which you want the ordering.

Now let's take an example.

Suppose in the first column we have customers.

In the second column we have estate.

And in the third column we have the number of orders each one of this customer have placed.

Now suppose we want to rank our customers in each state depending on the number of orders they have

placed.

The syntax will be this only here the window function is row number.

Don't worry, we will discuss all the window functions that are available in more detail in our later

lectures.

But here I just wanted to show you how the syntax looked like in a real life situation.

So the window function is row number.

Then there is this keyword over, and in the bracket we want to write the partition by and order by.

So since we want the top customer from each estate, we will be grouping our data on a state column.

So we have to write partition by state and then we want to give rank as one to the customer with the

most number of orders.

So in order buy, we will write the orders or the number of orders if the column name is number orders.

So in order why we have to write orders and then just like other order statements, you have to write

ascending or descending with the column.

So here I want to order my data in descending order.

So partition by state and order by number of orders.

The resultant column, which I will get is this.

You can see that my data is grouped by stores and the ranking is done on the basis of number of orders

in the descending order.

So Customer C five.

And store a with maximum number of orders will get a value of one in this new column.

C seven customer will get a value of two.

See one customer will get a value of three.

And similarly, the ranking is start.

If we change the estate or store.

So you will find partition by and order by used in each window.

Function will discuss more about this in our later videos.