SQL Server ranking functions and ranking Windows functions

What is a ranking function? To tell the truth, I'm not quite sure, I know that order by is sort, so what is the ranking function?

Let's take a look at a few examples.

Start by creating a table and inserting some data.

row_number functions: Direct Ordering, The Row_number function is ordered directly in ascending order, and a unique ordinal number is given to each row of data in a sequential sequence. (i.e. ranked consecutively)

 1  --  The following is a column ranking according to U_PWD (ascending)  2  select  Span style= "COLOR: #808080" >*   3  Span style= "COLOR: #ff0000" > "   "  Span style= "COLOR: #808080" >+  convert  ( varchar , row_number () over  (order  Span style= "COLOR: #0000ff" >by  u_pwd)) +     name   "  RowNum  4  from  UserInfo 

rank function: Sort The column specified in the ORDER BY clause, and if a row of data is returned with the same value as another row, the Rank function assigns the rows the same rank value.

In the process of ranking, keep an internal count value, when the values change, the rank number will have a jump. (That is, the ranking is not continuous)

 1  --  The following is a column ranking according to U_PWD (ascending)  2  select  Span style= "COLOR: #808080" >*   3  Span style= "COLOR: #ff0000" > "   "  Span style= "COLOR: #808080" >+  convert  ( varchar , rank () over  (order  by  u_pwd)) +     name   "  RowNum  4  from  UserInfo 

You can clearly see that there are 4 rows of data in 2nd place, and then directly 6th, because the column u_pwd specified in the ORDER BY clause is the same value.

Dense_rank functions: Parallel sorting, which is similar to the rank () function, where the ORDER BY clause specifies the same value for the column, the rank value is the same, but the subsequent is contiguous. (i.e. ranked consecutively)

 1  --  The following is a column ranking according to U_PWD (ascending)  2  select  Span style= "COLOR: #808080" >*   3  Span style= "COLOR: #ff0000" > "   "  Span style= "COLOR: #808080" >+  convert  ( varchar , Dense_rank () over  (order  Span style= "COLOR: #0000ff" >by  u_pwd)) +     name   "  RowNum  4  from  UserInfo 

You can see that even though there are 4 rows of data in 2nd place, the next is still 3rd.

NTILE Function: distributes the results of a query to a specified number of groups. Each group has a number, numbering starting from 1. For each row, NTILE returns the number of the group to which this row belongs.

The number of rows in a group is calculated as Total_num_rows (total number of rows in the result set)/num_groups (number of groups specified).

If there is a number n, the first n groups get an additional row. Therefore, it may not be possible for all groups to have an equal number of rows, but the maximum group size can only be one row.

For example, if the total number of rows is 53, the number of groups is 5,53/5 equals 10 is 3, according to the above rule, each group is assigned 10 rows, and the remainder is 3, so the preceding 3 groups have a row attached to each group.

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.

1 --The following is a grouping based on the u_pwd column2 Select *,3 'Section'+Convert(varchar, NTILE (3) Over(Order  byU_PWD))+'Group'RowNum4  fromUserInfo

There are 10 data in this table, which are divided into 3 groups and 10/3 equals 3 + 1.

PS: There must be a over () clause behind the rank function.

Ranking window function:

Row_number, Dense_rank, rank, ntile belong to the rank function, and over () is the window function.

The window function over () specifies a set of rows and the window function calculates the values of the rows in the result set from the window function output.

The window function does not need to use Group by to group the data, but it can also return the columns and aggregate columns of the underlying row.

The Rank window function can be used by either the order BY statement alone or with partition by.

ODER by specifies the order of the ranking window functions. The order BY statement must be used in the ranking window function.

PARTITION by is used to group the result set, and the window function is applied to each group.

1 --The following is a first grouping based on the U_pwd column, then each group is then sorted by U_pwd2 Select *,3 'Section'+Convert(varchar, Row_number () Over(Partition byU_pwdOrder  byU_PWD))+'name'RowNum4  fromUserInfo

Because the U_PWD column has 4 different values, it is divided into 4 groups, and then row_number in each group for sequential sorting.

1 --The following is a first grouping based on the U_pwd column, then each group is then sorted by U_pwd2 Select *,3 'Section'+Convert(varchar, Rank () Over(Partition byU_pwdOrder  byU_PWD))+'name'RowNum4  fromUserInfo

Because U_PWD has 4 different values for this column, it is also divided into 4 groups, and rank is then sorted in each group, because rank is a side-by-side sort, so it's all in the first place. Let's try a different sort of field below.

1 --The following is a first grouping based on the U_pwd column, then each group is then sorted by U_name2 Select *,3 'Section'+Convert(varchar, Rank () Over(Partition byU_pwdOrder  byU_name))+'name'RowNum4  fromUserInfo

1 --The following is a first grouping based on the U_pwd column, then each group is then sorted by U_pwd2 Select *,3 'Section'+Convert(varchar, Dense_rank () Over(Partition byU_pwdOrder  byU_PWD))+'name'RowNum4  fromUserInfo

Because U_PWD has 4 different values for this column, it is also divided into 4 groups, and then Dense_rank in each group, because Dense_rank is also a side-by-side sort, so it's all in the first place. Let's try a different sort of field below.

1 --The following is a first grouping based on the U_pwd column, then each group is then sorted by U_name2 Select *,3 'Section'+Convert(varchar, Dense_rank () Over(Partition byU_pwdOrder  byU_name))+'name'RowNum4  fromUserInfo

1 --The following is a first grouping based on the U_pwd column, then each group is then grouped according to the number of groups specified by NTILE (3), and finally sorted by the field u_pwd specified by the ORDER BY clause2 Select *,3 'Section'+Convert(varchar, NTILE (3) Over(Partition byU_pwdOrder  byU_PWD))+'name'RowNum4  fromUserInfo

Because the U_PWD column has 4 different values, it is also divided into 4 groups. The 1th group has 1 data, so there are 1 districts. The 2nd group has 4 data, 4/3 equals 1 1, so the 2nd group is divided into 3 districts, and the remainder is 1, so the 1th zone is 1 rows. There are 3 data in group 3rd, 3/3 equals 1 to 0, so the 3rd group has 3 districts. The 4th group has 2 data, so it is divided into 2 districts.

PS: Using the PARTITION by clause in the Sort window function needs to be placed before the ORDER BY clause.

Reference:

Http://www.cnblogs.com/jhxk/articles/2531595.html

SQL Server ranking functions and ranking Windows functions