Want to learn more on NTILE()

问题:

I was reading on RANKING function for ms sql. I understand the others function except NTILE(). Lets say if i have this data:

   StudentID     MARKS  
      S1           75  
      S2           83
      S3           91
      S4           83
      S5           93  

So if i do a NTILE(2) OVER(ORDER BY MARKS desc) what will be the result and why?
And what if it is a NTILE(3)? Simple explaination anyone?

回答1:

Think of it as buckets, NTILE(2) will make 2 buckets, half the rows will have the value 1 and the other half the value 2

example

create table  #temp(StudentID char(2),    Marks  int) 
insert #temp  values('S1',75 ) 
insert #temp  values('S2',83)
insert #temp  values('S3',91)
insert #temp  values('S4',83)
insert #temp  values('S5',93 ) 


select NTILE(2) over(order by Marks),*
from #temp
order by Marks

Here is the output, since you have an uneven number of rows, bucket 1 will have 1 row more

1   S1  75
1   S2  83
1   S4  83
2   S3  91
2   S5  93

If you add one more row

insert #temp  values('S6',92 ) 

Now both buckets have 3 rows

1   S1  75
1   S2  83
1   S4  83
2   S3  91
2   S6  92
2   S5  93

In reality I have never used NTILE in production code but I can see the use where you need to split the results into n number of buckets

回答2:

It will arrange the data in descending order of marks and then split it into 2 groups.

If the data cannot be split into equal groups, then the first few groups will have more rows than the latter groups.

So NTILE(2) will give you

StudentID       MARKS       NTILE  
      S5           93           1 
      S3           91           1 
      S2           83           1
      S4           83           2
      S1           75           2 

Similarly NTILE(3) will give you

StudentID       MARKS       NTILE  
      S5           93           1 
      S3           91           1 
      S2           83           2
      S4           83           2
      S1           75           3  

回答3:

I use NTILE quite frequently to split email lists into buckets for 10/10/80 testing. For example, we are testing a subject line of an email, and want to send one of two options to 10% each of the list, with the one that performs better being sent to the remaining 80%.

SELECT [field list],(NTILE(10) OVER(order by newid()))-1 AS Segment FROM [data]

The "order by newid()" ensures a random order. The "[NTILE ...]-1" syntax is a direct result of some of the other tools we use doing text parsing instead of integer math, so it was easier to have the results run from 0-9 rather than 1-10. The segment field will be populated with a value from 0-9 which I can use to separate out 10% of the records quite easily, and more than once for campaigns with multiple efforts to them.

If you need a query with replicable results, you would need to use something deterministic in the "order by" clause, or add a column with a GUID to use for the order by clause.

The PARTITION BY clause would be used to create groups of buckets based on state, or profession, or some other predetermined grouping, i.e. NTILE(10) OVER (PARTITION BY State ORDER BY newid()) or some such. I believe the ORDER BY clause is required - the PARTITION BY is optional.

回答4:

In Ntile function first it count the number of rows and divide it by the paramenter passed in ntile and then make a equal group of rows according to the quotient and rank them and then remaining rows will distributed by each group from the top in a shifting manner and will not take it from the least rows eg if group1 has 4 rows then it will take 5th row in its group not the last row.

Thanks

回答5:

Ntile without using partition clause, just divide the dataset based on the number in the ntile(number) such that : if no of rows are 7, example: 1,1,1,2,3,4,5 ntile(3) will give 3,2,2. How did i get 3,2,2?. Firstly assume 7 as 6 (one less to make it even), 6/3 gives 2,2,2 , then add +! to first partition. If the no.of rows are even then no problem. just divide the dataset

Ntile using partition clause, just divide the dataset based on the values in dataset such that : if no of rows are 7,Example row values are: 1,1,1,2,3,4,5 then: ntile(3) partitioned by value will give: 1,2,3,1,1,1,1. How did i get this?. Firstly break the dataset based on values: here, 1,1,1 is one partiton, next all values form a different partition. Then start assigning ntile rank to each partition. Here, 1,1,1 will become 1,2,3 then continue with the next partition, you can pull the rank only till the number specified in ntile() function