Shulou(Shulou.com)05/31 Report--

This article mainly introduces what the ranking function in Sql has, which has a certain reference value, interested friends can refer to, I hope you can learn a lot after reading this article, let the editor take you to understand it.

The ranking function is a new feature of Sql Server2005. Here's a brief introduction to their respective usage and differences. Let's create a new Order table and add some initial data to make it easier for us to see the effect.

Table structure and initial data Sql

Attach the table structure and the initial data map:

1. ROW_NUMBER

The use of row_number is very wide, sorting had better use him, generally can be used to achieve web program paging, he will query out of each row of records to generate a sequence number, sorted in turn and will not repeat, note that when using the row_number function must use the over clause to select a column to sort in order to generate the sequence number. Examples of row_number usage:

Select ROW_NUMBER () OVER (order by [SubTime] desc) as row_num,* from [Order]

The query results are shown in the following figure:

The row_num column in the figure is the sequence number column generated by the row_number function. The basic principle is to sort the records using the sort statement in the over clause, and then generate the sequence numbers in this order. The order by clause in the over clause has nothing to do with the order by clause in the SQL statement, and the order by can be completely different, for example, the following sql,over clause is sorted according to SubTime descending order, and the Sql statement is sorted in TotalPrice descending order.

Select ROW_NUMBER () OVER (order by [SubTime] desc) as row_num,* from [Order] order by [TotalPrice] desc

The query results are shown in the following figure:

We can use row_number to realize the paging of web program, and we can query the table data of the specified range. Ex.: get the third to fifth pieces of data in reverse order according to the time of order submission.

With orderSection as (select ROW_NUMBER () OVER (order by [SubTime] desc) rownum,* from [Order]) select * from [orderSection] where rownum between 3 and 5 order by [SubTime] desc

The query results are shown in the following figure:

Note: when using row_number for paging, you should pay special attention to the fact that the order by in the over clause should be consistent with the order by in the Sql sorted record, otherwise the resulting sequence number may not be contiguous. Let's write an example to prove this by changing the sort field in the above Sql statement from SubTime to TotalPrice. Also mention that for queries with subqueries and CTE, the ordering of subqueries and CTE queries does not mean that the entire query is ordered unless order by is specified.

With orderSection as (select ROW_NUMBER () OVER (order by [SubTime] desc) rownum,* from [Order]) select * from [orderSection] where rownum between 3 and 5 order by [TotalPrice] desc

The query results are shown in the following figure:

II. RANK

The rank function returns the ranking of each row in the partition of the result set, which is the number of rankings before the relevant rows plus one. To put it simply, the rank function is to rank the queried records. Unlike the row_number function, the rank function takes into account the situation of the same sort field value in the over clause. If you use the rank function to generate the sequence number, the order number with the same sort field value in the over clause is the same, and the sequence number with the same field value will skip the next ranking number, that is, the number of rankings before the relevant lines. It can be understood to generate sequence numbers based on the current number of records, subsequent records, and so on. Maybe what I describe is pale and difficult to understand. Let's go straight to the code. The rank function is used in exactly the same way as the row_number function.

Select RANK () OVER (order by [UserId]) as rank,* from [Order]

The query results are shown in the following figure:

As can be seen from the above figure, when the rank function is ranked, the sequence number of the same group is the same, while the following is based on the current number of records, and so on, the first and second records in the chart have the same user Id, so their sequence number is the same, and the sequence number of the third record is 3.

III. DENSE_RANK

The function of the dense_rank function is similar to that of the rank function. The dense_rank function is continuous when generating the sequence number, while the sequence number generated by the rank function may not be contiguous. When the same ranking occurs in the dense_rank function, the same ranking number will not be skipped, and the rank value immediately follows the previous rank value. Within each group, rank () is the jump sort, followed by the fourth place when there are two first places, and dense_rank () is the sequential sort, and the second place is still followed when there are two first places. Change the above Sql statement to the dense_rank function.

Select DENSE_RANK () OVER (order by [UserId]) as den_rank,* from [Order]

The query results are shown in the following figure:

The first and second records in the figure have the same user Id, so their sequence numbers are the same. The sequence number of the third record is immediately followed by the sequence number of the previous one, so it is 2 instead of 3, and so on.

IV. NTILE

The ntile function can group the sequence numbers and distribute the rows in the ordered partition to a specified number of groups. Each group has a number, numbered from the beginning. For each row, ntile returns the number of the group to which the row belongs. This is equivalent to putting the queried recordset into an array of specified length, with each array element storing 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 that record (starting at 1). You can also refer to each array element that allocates records as "buckets". The ntile function takes an argument that specifies the number of barrels. The following SQL statement buckles the Order table using the ntile function:

Select NTILE (4) OVER (order by [SubTime] desc) as ntile,* from [Order]

The query results are shown in the following figure:

The total number of records in the Order table is 6, and the number of groups specified in the above Sql statement ntile function is 4, so how does Sql Server2005 determine how many records each group should be divided into? Here we need to understand the grouping basis (convention) of the ntile function.

Grouping of ntile functions by (convention):

1. The number of records of each group cannot be greater than the number of records of the previous group, that is, the number of records placed in a barrel with a small number cannot be less than that of a bucket with a large number. In other words, the number of records in group 1 can only be greater than or equal to the number of records in group 2 and subsequent groups.

2. The number of records in all groups is either the same, or starting with a group with fewer records (named X), the number of records in all subsequent groups is the same as the number of records in that group (group X). That is, if there is a group, the number of records in the first three groups is 9, and the number of records in the fourth group is 8, then the number of records in the fifth and sixth groups must also be 8.

Convention 2 is explained in detail here for a better understanding.

First of all, the system will check whether all the records that meet the conditions can be grouped equally, and if so, the grouping will be completed; if not, it will be divided into a group first. how many records does this component have? That is, (total number of records / total number of groups) + 1, the reason for the allocation (total number of records / total number of groups) + 1 is that when the average grouping cannot be carried out, the total number of records% of the total number of groups must be more than 1, and because of the grouping agreement 1, so the group that is divided out first needs + 1.

After the division, the system will continue to compare whether the remaining records and unallocated groups can be evenly distributed, if so, the remaining records can be equally distributed; if not, another group will be divided, and the number of records in this group is also (total number of records / total number of groups) + 1.

Then the system continues to compare whether the number of remaining records and the number of unallocated groups can be evenly distributed, and if so, the remaining records are distributed equally; if not, another group is allocated to continue to compare the rest. This goes on until the grouping is completed.

For example, if 51 records are divided into 5 groups and 51% 5 records cannot be evenly distributed, then a group of 51-11 records is divided into 5 groups + 11 records, and then compare whether the remaining 51-11 records can be evenly distributed to the unassigned 4 groups, and if they can be evenly distributed, the remaining 4 groups, each group of 40 purses, is completed. The distribution result is as follows: 11 minutes, 10 minutes. At the beginning, I mistakenly thought that Xiao rookie would be assigned as 11pr 11pl 11pl 11pr 7.

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

/ / mod indicates surplus, p indicates rounding. If (the total number of records mod = = 0) {the total number of recordCount= records p barrels; / / set the number of records per barrel to recordCount.} else {the total number of recordCount1= records p barrels + 1; the number of records in the bucket is the maximum number of records of recordCount1. M=recordCount1*n While (total records-m) mod (barrels-n))! = 0) {nrecords; mrecords recordCount1records;} recordCount2= (total records-m) p (barrels-n); / / set the number of records for the first n barrels to recordCount1. / / set the number of records from one barrel to the next to recordCount2.} NTILE () function algorithm implementation code

According to the above algorithm, if the total number of records is 59 and the total number of groups is 5, then the grouping results are as follows: 12, recordCount1=12, 12, recordCount2=11, 12, 12, 12, 12, 11.

If the total number of records is 53 and the total number of groups is 5, then nasty 3, recordCount1=11, recordCount2=10, the grouping results are: 11, 11, 11, 11, 10, 10.

Take the above example, the total number of records is 6, the total number of groups is 4, through the algorithm to get nasty 2, recordCount1=2, recordCount2=1, the grouping result is: 2, 2, 2, 1, 1.

Select ntile,COUNT ([ID]) recordCount from (select NTILE (4) OVER (order by [SubTime] desc) as ntile,* from [Order]) as tgroup by t.ntile

Run Sql, and the grouping result is shown in the figure:

The comparison algorithm is consistent with the grouping result of Sql Server, which shows that the algorithm is correct. :)

Thank you for reading this article carefully. I hope the article "what are the ranking functions in Sql" shared by the editor will be helpful to everyone? at the same time, I also hope you can support us and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.