All language subtitles for 5. NTILE function

Now the next window function we are going to discuss as entail.

And title divides the rows within the partition and two and groups.

It is called end title because and it stands for number and tile is number of tiles.

So suppose if I want to divide the data within the stores into two titles or two groups, I can use

Intel.

So, for example.

There are three customers in a story and four customers in store.

B if I want to divide these three customers into two groups.

The first two customers will get group value of one and the next customer will get group value of two.

If we have four customers as a store b.

The first two customers will get group value of one, and the next two customers will get a group value

of two.

The syntax is also very similar.

Instead of rank or row number, you have to write entail and within the bracket you have to write the

number of tiles you want in your partitions.

So if I wanted three tiles.

My result will be one, two, three.

So three customers will get equally divided into three titles or three groups.

In case of store be the first two customer will get a value of one and third, and for customer will

be getting a value of two and three.

So this function will try to divide the number of rows in each partition into the number of tiles you

have mentioned in the formula.

So what is the use case of this?

So suppose in our rank and roll number video, you have seen that the number of customers from California

are much larger than the number of customers from Alabama.

So suppose if I wanted to take top 20% of customers from each state, how can I do that?

To select 20%.

You can divide the number of customers in each estate into five parts.

So if you divide 100 by five, so each part will contain a 20% of population.

So to select 20%, I can use until five and then select only the tile value one customers.

And similar to rule number and rank functions here also you can use order by.

So let's select top 20% of customers from each state.

So let's go back to our PG admin.

So you can see that.

There are around nine customers in Alabama.

And there are much more customers in California.

So we want 20% of customer from each estate.

So we should get these two values from Alabama and a lot more people from California because there are

much more people in California.

So I will use the.

Same table.

Let's provide intel.

Since we want 20% of customer, we will be dividing our data into five groups.

If you want 10%, then you have to divide your data into ten groups.

If you want just 5%, you have to divide your data into 20 groups so 100 divided by the percentage you

want.

So I want 20%.

So 100 divided by 20, which is five.

That's why I have to write and tell.

Five.

Divide my data into five groups.

Again, partitioned by state and order by order.

And I will write it.

Biden number.

If I run this, you can see that the first two customers are getting tail number as one.

The next two getting title number is two, then next to three, then next to four, and then a single

customer with group number five.

If you can go to California here, you will find many more customers within each group.

So you can see there are a lot more people in group one, Group three, group four and grow five for

a small estate.

For example, in Kentucky, we are getting just four customer, so roughly around 20% of customer from

each state we are getting and how to filter out.

Since our task is to get only 20% of customers from each state on the basis of their number of orders,

we can just select very close.

Select a start from this.

Where.

Dale.

And.

Is equal to one.

Hmm.

There is something wrong with our Twitter.

Okay.

I see.

And we'll write a lot.

You can see that now we are getting top 20% of customer from each set.

Now for bottom 20%.

You can just select the last title for data.

So these are the people with least number of orders in each state.

So you can easily use an intel function when you want to segregate your data on the basis of percentages

for ranks, you can use any of the three rank function For percentages, you have to use Intel function.