How to Implement NTILE functions in SQL Server 2000

Each group is numbered from the beginning. For each row, the NTILE function returns the ID of the group to which the row belongs. If the number of rows in a partition cannot be divisible by integers, a Member may have two groups of different sizes. In the order specified by the OVER clause, a large group is placed before a small group. Using the NTILE function to calculate the ranking value is the same as other methods. Simple partition ranking scheme and ranking value efficiency analysis graphic + test code]), the only difference is that, the NTILE function accepts a parameter that represents the number of groups, while other methods do not.

The SQL code and effects are as follows:

 
 

  
  
Code  
  
  
ifOBJECT_ID('Sales')isnotnull  
  
  
droptableSales;  
  
  
createtableSales  
  
  
(  
  
  
empidvarchar(10)notnullprimarykey,  
  
  
qtyintnotnull  
  
  
)  
  
  
insertintoSales(empid,qty)values('A',300);  
  
  
insertintoSales(empid,qty)values('B',100);  
  
  
insertintoSales(empid,qty)values('C',200);  
  
  
insertintoSales(empid,qty)values('D',200);  
  
  
insertintoSales(empid,qty)values('E',250);  
  
  
insertintoSales(empid,qty)values('F',300);  
  
  
insertintoSales(empid,qty)values('H',250);  
  
  
insertintoSales(empid,qty)values('I',250);  
  
  
insertintoSales(empid,qty)values('J',100);  
  
  
insertintoSales(empid,qty)values('K',200);  
  
  
insertintoSales(empid,qty)values('G',100);  
  
  
--------------------------------------------------  
  
  
selectempid,qty,NTILE(9)over(orderbyqty)astilefromSales 
 
 

Execution result:

So how did we achieve this effect before SQLServer2005 appeared? Below I will introduce two methods to meet this requirement:

Method 1: Calculate the row number ranking value of the table first), and obtain the number of records in each group based on the number of specified groups. The group number calculation formula is as follows: row number-1)/group size + 1. The group number of each record is returned.

The SQL code is as follows:

 
 

  
  
Code
  
  
Eclare @ numtilesint;
  
  
Set@ Numtiles = 9;-- Number of groups 
  
  
 
  
  
Selectempid, qty,CAST(Rn-1)/tilesize + 1 asint) astile
  
  
From(Selectempid, qty, rn, 1.0 * numrows/@ numtilesastilesizefrom (selectempid, qty, (selectCOUNT (*) fromSalesasS2whereS2. qty 
 
 

Method 2: Calculate the row number ranking value of the table first), and obtain the number of records in each group based on the number of specified groups. Then, use the following group number calculation formula to return the group number of each record.

If (row number <= group size + 1) * Number of remaining rows) then

Group number = row number-1)/group size + 1) + 1

Else

Group number = row number-remaining lines-1)/group size + 1

The SQL code is as follows:

 
 

  
  
Code
  
  
Declare@ Numtileint;
  
  
Set@ Numtile = 9;-- Number of groups 
  
  
 
  
  
Selectempid, qty, rn,
  
  
Casewhenrn <= (tilesize + 1) * remainder
  
  
ThenRn-1/(tilesize + 1) + 1
  
  
ElseRn-remainder-1/(tilesize) + 1
  
  
Endastiles
  
  
From 
  
  
(
  
  
Selectempid, qty, rn, numrows/@ numtileastilesize, numrows % @ numtileasremainder
  
  
From 
  
  
(
  
  
Selectempid, qty, (selectCOUNT (*) fromSalesasS2whereS2. qty 
  
  
) AsD1
  
  
) AsD2orderbyqty, empid
 
 

1. 10 key features of SQL Server 2005 Business Intelligence
2. Notes for using temporary tables in a T-SQL
3. SQL Server database management common SQL and T-SQL statements (1)
4. Interview SQL Server developers with T-SQL operations (1)
5. T-SQL in SQL Server 2005
6. T-SQL