SQL Server Ranking functions

Content Summary: Transact-SQL provides a 4 a ranking function : RANK (), Dense_rank (), Row_number (), NTILE () , the following is the 4 interpretation of a function.

The following is an explanation of these 4 functions:

　　RANK ()

Returns the rank of each row within the partition of the result set. The rank of the row is the number of positions before the related row plus one.

If two or more lines are associated with a rank, each association row gets the same rank.

For example, if the two-bit salesperson has the same SALESYTD value, they will tie. Since there are already two lines ranked in front, the salesperson with the next largest SALESYTD will be ranked third.

Therefore, the RANK function does not always return consecutive integers.

　　Dense_rank ()

Returns the rank of the row in the result set partition without any interruption in the ranking. The row is ranked equal to the number of all positions before the row discussed plus one.

If two or more rows are constrained by the rank in the same partition, each constraint row receives the same rank.

For example, if a two-bit top salesperson has the same SalesYTD value, they will tie. Next SalesYTD the highest sales staff in the second. The rank equals the number of rows before the row plus one.

Therefore, the number returned by the Dense_rank function is uninterrupted and always has a continuous rank.

　　Row_number ()

Back to the result set partition the serial number of the expert, the first row of each partition starts from 1.

An ORDER BY clause determines the order in which the rows are assigned unique row_number in a particular partition.

　　NTILE ()

Distributes the rows in an ordered partition to a specified number of groups. Each group has a number, numbering from the beginning. For each row, NTILE returns the number of the group to which this row belongs.

If the number of rows in a partition cannot be divisible by integer_expression, then one member will have two groups of different sizes. Larger groups precede smaller groups in the order specified by the OVER clause.

For example, if the total number of rows is 53 and the number of groups is 5, the first three groups contain 11 rows per group, and the remaining two groups contain 10 rows per group.

On the other hand, if the total number of rows is divisible by the number of groups, the number of lines is evenly distributed among the groups.

For example, if the total number of rows is 50 and there are five groups, each group will contain 10 rows.

--demo example, build a table

CreateTable rankorder (ordered Int,qty int)
Go

--Inserting data

Insert Rankorder VALUES (30,10)
Insert Rankordervalues (10,10)
Insert Rankordervalues (80,10)
Insert Rankordervalues (40,10)
Insert Rankordervalues (30,15)
Insert Rankordervalues (30,20)
Insert Rankordervalues (22,20)
Insert Rankordervalues (21,20)
Insert Rankordervalues (10,30)
Insert Rankordervalues (30,30)
Insert Rankordervalues (40,40)
Go

--Check out various rankings

Selectorderid,qty,
Row_number () over (ORDER Byqty) as RowNumber,
Rank () Over (qty) as [rank],
Dense_rank () over (Orderbyqty) as Denserank,
NTILE (3) Over (qty) as [NTILE]
From Rankorder
Qty

-Results

--row_number () is ranked by qty from small to large, not tied, ranked consecutively

--rank () is ranked by qty from small to large, tied, ranked discontinuous

--dense_rank () is ranked by qty from small to large, tied, ranked consecutively

--ntile () is ranked by qty from small to large into 3 groups, tied, ranked consecutively

Orderidqty Rownumberrankdenserankntile
30101111
10102111
80103111
40104111
30155522
30206632
22207632
21208632
10309943
303010943
4040111153

The SQL 2005 implementation ranking is very convenient, but the SQL 2000 implementation ranking is more troublesome, the following is the implementation code of SQL 2000:

Implementation of--rank in SQL 2000

Selectorderid,qty,
(SelectCount (1) +1fromrankorderwhereqty
Fromrankorderr
Orderbyqty
Go

Implementation of--row_number in SQL 2000

--using temporal tables and identity (functions)

selectidentity (int,1,1) As[row_number],orderid,qty
Into#tem
Fromrankorder
Selectorderid,qty,[row_number]
From#tem
Droptable#tem
Go
--dense_rankin thesql2000in the implementation
Select Identity (int,1,1) asids,qty
into#t
Fromrankorder
Groupbyqty
Orderbyqty
selectr Orderid,r.qty,t.idsas[dense_rank]
From Rankorderrjoin#tt
Onr.qty=t.qty
droptable#t

The ranking function is associated with the window function Over () used in conjunction with the.

　If you use Over argument of the clause PARTITION by, You can divide the result set into multiple partitions. The ranking function will be ranked within each partition .

--Examples

Selectorderid,qty,
Dense_rank () over (Orderbyqty) as a,
Dense_rank () Over (Partitionby OrderID ORDER by qty) as B
From Rankorder
ORDER by Qty

--Description:

The--a column is the ranking on all records

The--b column divides the records in the OrderID into the 6 districts of 10,21,22,30,40,80, and then ranks them in each district.

Orderidqtyab
101011
301011
401011
801011
301522
302033
212031
222031
103042
303044
404052

Oh, that's great.

SELECTClass name,Name,Candidate Scores,Row_number() Over(PARTITION byClass nameORDER byCandidate Scoresdesc) asSerial number of the results of the natural link,RANK() Over (PARTITION byClass nameORDER byCandidate Scoresdesc) asGrade number not connected number, Dense_rank() Over(PARTITION byClass nameORDER byCandidate Scores) asRepeat serial number of grade row number fromStudentORDER byClass name,Candidate Scoresdesc;

We see the ranking function as a simple way to get the rank of various types.

Here is my analogy table for 4 ranking functions:

Rank continuity ranking Parallel sex

RANK () does not necessarily have a continuous juxtaposition

Dense_rank () Continuous and parallel

Row_number () Continuous no parallel

NTILE () Continuous and parallel