Analysis functions in SQL statements

Row_number row_number is a very wide range of uses, the best use of him, generally can be used to achieve the web program paging, he will query out of each row of records to generate a sequence number, sorted and will not repeat, Note that when using the row_number function, you must select a column to be sorted using the over clause to generate an ordinal.

select ROW_NUMBER(a) OVER(order by [SubTime] desc) as row_num,* from [Order] order by [TotalPrice] desc
Copy the code

The order by in the over clause must be consistent with the order by in the Sql sorting record in order to ensure that row_num is continuously changing.

with orderSection as
(
select ROW_NUMBER() OVER(order by [TotalPrice] desc) rownum,* from [Order]
)
select * from [orderSection] where rownum between 3 and 5 order by [TotalPrice] desc
Copy the code

The Rank function returns the Rank of each row in the partition of the result set. The Rank of each row is the number of rows that precede the row in question plus one. Unlike the row_number function, the rank function takes into account the case that the sorted fields in the over clause have the same value. For example: the rank of E is 2+2+1=5. The rank of E is 2+2+1=5. The rank of C, D is 3

select RANK(a) OVER(order by [UserId]) as rank,* from [Order]
Copy the code

The DENSE_RANK function is similar to that of the rank function. The DENSE_RANK function generates sequential numbers, which may not be consecutive. Olympic Gold MEDALS: A and B won the first gold medal, C the second silver medal and D the third bronze medal

select DENSE_RANK(a) OVER(order by [UserId]) as den_rank,* from [Order]
Copy the code

The NTILE function groups ordinals by distributing rows from an ordered partition to a specified number of groups. The sequence number generated by the ntile function for each record is the index of all the array elements (starting at 1) for that record.

select NTILE(4) OVER(order by [SubTime] desc) as ntile,* from [Order]
Copy the code

The ntile function is grouped according to the following conventions: 1. The number of records in each group cannot be larger than the number of records in the previous group. That is, the number of records in the bucket with a small number cannot be smaller than the number of records in the bucket with a large number. That is, 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 from 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 (X). That is, if you have a group where the number of records in the first three groups is nine and the number of records in the fourth group is eight, then the number of records in the fifth and sixth groups must also be eight. That is, there will only be 2 records.