Shulou(Shulou.com)06/01 Report--

Editor to share with you how to use the NTILE function in SQL, I believe most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to know it!

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.

The above is all the contents of the article "how to use the NTILE function in SQL". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

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.