Comparison of SQL Server ranking functions (row_number, rank, dense_rank, and ntile)

Address: http://www.cnblogs.com/nokiaguy/archive/2009/02/05/1384860.html

 

The ranking function isSQL server2005Added features. InSQL server2005There are four ranking functions:

1. row_number

2. Rank

3. dense_rank

4. ntile
The following describes the functions and usage of these four ranking functions. Before the introduction, assume that there is a t_table table. The table structure is shown in Table 1:

Figure 1

WhereField1The field type isInt,Field2The field type isVarchar

I,Row_number

Row_numberThe function is widely used to generate a sequence number for each row of records queried.Row_numberFunction usage:SQLStatement:

 

Select Row_number () Over ( Order By Field1) As Row_number, * From T_table

 

The aboveSQLStatement Query Result2.

Figure 2

WhereRow_numberThe column is composedRow_numberThe Sequence Number Column generated by the function. In useRow_numberFunction is to useOverClause is used to sort a column before generating the sequence number.

Actually,Row_numberThe basic principle of function generation sequence number is to first useOverThe sorting statement in the clause sorts records and then generates sequence numbers in this order.OverClauseOrderClause andSQLStatementOrderClause does not have any relationship.OrderIt can be completely different, as shown in the followingSQLStatement:

 

Select Row_number () Over ( Order By Field2 Desc ) As Row_number, * From T_table Order By Field1 Desc

 

The aboveSQLStatement Query Result3.

Figure 3

We can use the row_number function to query records within the specified range in a table. Generally, it is applied to Web applications.Program. The following SQL statement can query 2nd and 3rd records in the t_table table:

 

With T_rowtable
As
(
Select Row_number () Over ( Order By Field1) As Row_number, * From T_table
)
Select * From T_rowtable Where Row_number > 1 And Row_number < 4 Order By Field1

 

The preceding SQL statement Query Result 4 is shown.

Figure 4

The above SQL Statement used CTE , About CTE For more information, see SQL server2005 Miscellaneous (1): using a common table expression (CTE) to simplify nested SQL .
Note that Row_numberFunctions are used for paging, Over Clause Order And sorting records Order It should be the same; otherwise, the generated sequence number may not be continuous.
Of course, do not use Row_number The function can also query records within a specified range, which is troublesome. The general method is to use inverted Top For example, query T_table The 2 And 3 Records. You can check the records before 3 Records, and then sort the three queried records in reverse order. 2 Records, and 2 Records are sorted in reverse order, which is the final result. SQL The statement is as follows:

 

Select * From ( Select Top 2 * From ( Select Top 3 * From T_table Order By Field1) Order By Field1 Desc ) B Order By Field1

 

The aboveSQLStatement Query Result5.

Figure 5

 

This query result contains no serial number columnRow_number, Other and Graphs4The query results are identical.

II,Rank

RankThe function has taken into accountOverTo make it easier to describe the problemT_tableAdd a record to the table,6.

Figure 6

The field1 Field Values of the last three records in the record shown in Figure 6 are the same. If the rank function is used to generate sequence numbers, the sequence numbers of the three records are the same, and the 4th records generate the sequence numbers based on the number of current records. The subsequent records are pushed accordingly. That is to say, in this example, the sequence number of the first 4th records is 4, not 2. The rank function is used in the same way as the row_number function. The SQL statement is as follows:

 

Select Rank () Over ( Order By Field1 ), * From T_table Order By Field1

 

 

The aboveSQLStatement Query Result7.

Figure 7

 

III,Dense_rank

Dense_rankFunctions andRankThe function is similar, but it is continuous when the serial number is generated.RankThe sequence number generated by the function may be discontinuous. In the preceding exampleDense_rankFunction, number4The number of records should be2Instead4. See the followingSQLStatement:

Select Dense_rank () Over ( Order By Field1 ), * From T_table Order By Field1

 

 

 

The aboveSQLStatement Query Result8.

Figure 8

Readers can compare Images7And Graph8What are the differences between the query results?

Iv. ntile
The ntile function can group sequence numbers. This is equivalent to placing the queried record set in an array of the specified length. Each array element stores a certain number of records. The sequence number generated by the ntile function for each record is the index of all the array elements of this record (starting from 1 ). You can also call the array element of each allocated record as a "Bucket ". The ntile function has a parameter used to specify the number of buckets. The following SQL statement uses the ntile function to perform bucket loading on the t_table table:

Select Ntile ( 4 ) Over ( Order By Field1) As Bucket, * From T_table

 

 

The aboveSQLStatement Query Result9.

Figure 9

 

BecauseT_tableThe total number of records in the table is6And the aboveSQLStatementNtileThe function specifies the number of buckets.4.

Some readers may ask this question,SQL server2005How can we determine how many records a bucket should be stored? PossibleT_tableThe number of records in the table is small, so we assume thatT_tableTable has59Number of records, while the number of buckets is5, How many records should each bucket be stored?

In fact, two conventions can generate oneAlgorithmTo determine which bucket should be stored. The two Conventions are as follows:

1.A bucket with a small number cannot be smaller than a bucket with a large number. That is to say1The number of records must be greater than or equal to2Bucket and subsequent records in each bucket.

2.The records in all buckets are either the same, or the number of all the records after a bucket with fewer records is the same as the number of records in the bucket. That is to say, if there is a bucket, the first three bucket records are all10And4The number of records is6, Then5Bucket and6The number of Bucket records must also be6.

According to the above two conventions, the following algorithms can be obtained:

// MoD indicates the remainder, and Div indicates the integer.
If (Total number of records mod buckets = 0 )
{
Recordcount = Total number of Div buckets;
Set the number of records per barrel to recordcount
}
Else
{
Recordcount1 = Total number of records Div buckets + 1 ;
Int N = 1 ; // N indicates the maximum number of record records in the bucket of recordcount1
M = Recordcount1 * N;
While (Total number of records - M) mod (number of buckets - N )) ! = 0 )
{
N ++ ;
M = Recordcount1 * N;
}
Recordcount2 = (Total number of records - M) Div (number of buckets - N );
Set the number of records in the first n buckets to recordcount1.
Set n + The number of records from one bucket to the next is set to recordcount2.
}

 

 

Based on the above algorithm, if the total number of records is59, Number of buckets5Before4The number of records in each bucket is12The number of records in the last bucket is11.

If the total number of records is53, Number of buckets5Before3Number of records per bucket11, After2Number of records per bucket10.

In this example, the total number of records is6, Number of buckets4Will calculateRecordcount1Is2At the endWhileAfter the loopRecordcount2The value is1, Therefore, before2Bucket records are2, After2Bucket records are1.