SQL语句中的分析函数

SQL语句中的分析函数

1.row_number
row_number的用途的非常广泛,排序最好用他,一般可以用来实现web程序的分页,他会为查询出来的每一行记录生成一个序号,依次排序且不会重复,注意使用row_number函数时必须要用over子句选择对某一列进行排序才能生成序号。

select ROW_NUMBER() OVER(order by [SubTime] desc) as row_num,* from [Order] order by [TotalPrice] desc
复制代码

这里写图片描述

使用row_number经行分页查询时:
为了确保row_num是连续变化的 要求 over子句中的order by 要与Sql排序记录中的order by 保持一致。

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
复制代码

2.Rank
rank函数用于返回结果集的分区内每行的排名, 行的排名是相关行之前的排名数加一。
与row_number函数不同的是,rank函数考虑到了over子句中排序字段值相同的情况。
举个例子:
并列第一名 770 2位 A、B
并列第二名 769 2位 C、D
第三名 768 1位 E
那么E的名次为 2+2+1=5 第五名
C、D 第三名
A、B 第一名

select RANK() OVER(order by [UserId]) as rank,* from [Order]
复制代码

3.DENSE_RANK
dense_rank函数的功能与rank函数类似,dense_rank函数在生成序号时是连续的,而rank函数生成的序号有可能不连续。
奥运金牌:
A、B 共获金牌 第一名
C 获银牌 第二名
D 获铜牌 第三名

select DENSE_RANK() OVER(order by [UserId]) as den_rank,* from [Order]
复制代码

4.NTILE
ntile函数可以对序号进行分组处理,将有序分区中的行分发到指定数目的组中。 ntile函数为每条记录生成的序号就是这条记录所有的数组元素的索引(从1开始)。

select NTILE(4) OVER(order by [SubTime] desc) as ntile,* from [Order]
复制代码

ntile函数的分组依据(约定):
1、每组的记录数不能大于它上一组的记录数,即编号小的桶放的记录数不能小于编号大的桶。也就是说,第1组中的记录数只能大于等于第2组及以后各组中的记录数。
2、所有组中的记录数要么都相同,要么从某一个记录较少的组(命名为X)开始后面所有组的记录数都与该组(X组)的记录数相同。也就是说,如果有个组,前三组的记录数都是9,而第四组的记录数是8,那么第五组和第六组的记录数也必须是8。
即记录数只会有2种。