SQL SERVER ranking function RANK, DENSE_RANK, NTILE, ROW_NUMBER

SQL SERVER ranking function RANK, DENSE_RANK, NTILE, ROW_NUMBER
Preface
This document describes how to use instance data to help you understand SQL SERVER ranking functions RANK, DENSE_RANK, NTILE, and ROW_NUMBER.

Preparations
Create a test table:

create table test(id int identity(1,1) primary key,testid int,name varchar(100))

Insert test data:

Insert into test (testid, name) select 1, 'leewhoee ee 'insert into test (testid, name) select 1, 'leewhoee University' insert into test (testid, name) select 1, 'leewhoee ee 'insert into test (testid, name) select 2, 'leewhoee ee' insert into test (testid, name) select 3, 'rank function 'insert into test (testid, name) select 4, 'rank function 'insert into test (testid, name) select 4, 'rank function'

Use an SQL statement to view the usage of each function:

select id,testid,ROW_NUMBER() over( order by testid) as rownum,RANK() over(order by testid) as ranknum,DENSE_RANK() over(order by testid) as denseranknum,Ntile(4) over ( order by testid) as ntilenumfrom testorder by testid

The running result is as follows:

Id testid rownum ranknum denseranknum ntilenum
1 1 1 1 1
2 1 2 1 1
3 1 3 1 2
4 2 4 4 2 2
5 3 5 5 3 3
6 4 6 6 4 3
7 4 7 6 4 4

ROW_NUMBER () over (order by testid) 

Generate a sequence number corresponding to each testid in ascending order of testid. These numbers are uninterrupted numbers starting from 1. Each serial number is unique.

RANK () over (order by testid)

Generate a ranking number corresponding to each testid in ascending order of testid. These numbers are sorted from small to large starting from 1 (may be interrupted ). The ranking numbers generated by the same testid are also the same, but the next ranking number is not calculated by the previous ranking number plus 1, but by the total number, that is, the number of rows.

DENSE_RANK () over (order by testid) 

Generate a ranking number corresponding to each testid in ascending order of testid, which is an uninterrupted number (which may be repeated) from 1 ). The ranking numbers generated by the same testid are the same, but the next ranking number is calculated by adding 1 to the previous ranking number, rather than the total number or number of rows.

Ntile (4) over (order by testid)

Sort by testid in ascending order and divide all testids4Group (the total number of testids In the last group may be less than that in other groups), and a corresponding group ID is generated for each testid. The group number is an uninterrupted number starting from 1.

Partition

The following shows an SQL statement with partition by to view the usage of each function:

select id,testid,name,ROW_NUMBER() over(partition by name order by testid) as rownum,RANK() over(partition by name order by testid) as ranknum,DENSE_RANK() over(partition by name order by testid) as denseranknum,Ntile(2) over (partition by name order by testid) as ntilenumfrom testorder by name

Running result:

Id testid name rownum ranknum denseranknum ntilenum
1 1 LeeWhoee University 1 1 1 1
2 1 LeeWhoee University 2 1 1
3 1 LeeWhoee University 3 1 2
4 2 LeeWhoee University 4 4 2 2
5 3 ranking function 1 1 1 1
6 4 ranking function 2 2 2 1
7 4 ranking function 3 2 2 2

ROW_NUMBER () over (partition by name order by testid)

Partition data by name, and perform normal ROW_NUMBER () Calculation for each partition.

RANK () over (partition by name order by testid)

Partition data by name, and perform normal RANK () Calculation for each partition.

DENSE_RANK () over (partition by name order by testid)

Partition data by name and perform normal DENSE_RANK () Calculation for each partition.

Ntile (2) over (partition by name order by testid)

Partition data by name and perform normal Ntile () Calculation for each partition.

To facilitate data differentiation, NTILE (2) instead of NTILE (4) is performed here ).