善用SQL排名函数,让您的查询飞的更精彩

经常写SQL脚本的朋友,对查询的多样化要求可能会经常头疼。数据库SQL的语法是固定的、但应用要求却是千差万别的。依靠我们所掌握的知识,大部分的查询需求我们还是有办法的解决的,但总有那么一些要求,把我们搞的非常被动。

今天我们就谈一个会让我们头疼的问题:如何对查询的结果进行排名。没错,是排名,不是排序,跟Order By有点关系,但可以认为是另一个问题。排名函数不但可以实现排序,还能够生成排序的排名序列

演示数据准备

今天我们就以SQL Server为例,结合四大排名函数,详细讲解一下如何使用四大排名函数实现查询结果的排名。MySQL也有类似的排名函数,使用方法与SQL Server大同小异

为了演示需要,我们需要先创建一个表变量作为销售表,记录不同区域、城市、年月的销售金额,然后再对表中的数据进行排名处理。演示数据创建脚本如下:

declare @sale table( FName nvarchar(50), FDistrict nvarchar(50), FYear smallint, FMonth smallint, FAmount decimal(28,10));insert into @salevalues("张三","北京",2019,4,20000),("张三","深圳",2019,4,40000),("李四","北京",2019,4,30000),("李四","深圳",2019,4,40000),("王二","北京",2019,4,70000),("王二","深圳",2019,4,60000),("马六","北京",2019,4,80000),("马六","深圳",2019,4,70000);

运行结果请参考下图:

有了演示数据,我们就把四个排名函数的应用和区别挨个理一理。

ROW_NUMBER,简单方便又强大

row_number是最常用、最简单的排名函数,其语法格式如下:

row_number() over(order by field列表 asc|desc)

语法格式看上去有点怪怪的,前半截row_number()是排名函数,紧接其后的over子句则是指定排序的规则。其它三个排名函数也有over子句,用途和语法也是一样的。整个函数作为一个整体,其返回值就是排名序列号,序列号从1开始依次累加。

比如我们要按照销售金额进行排名,语法格式如下:

select ROW_NUMBER() over(order by FAmount desc) as FRank,* from @sale;

运行效果参考下图:

通过上图的查询结果可以看出,FRank字段就是返回的排名字段。根据over子句,可以定义任何自己需要的排名规则。

如果您认为row_number函数只是能实现如此排名,那您就想的太简单了。row_number函数还有一个很重要的扩展用途,可以实现查询分页,我们举一个例子来说明如何使用row_number实现查询分页。

为了使演示效果更明显,我们使用公用表表达式返回排名序列值和数据,根据要求的每页行数和第几页,通过where子句限定排名序列的起点和终点。

declare @pagesize int =4;--每页记录数declare @pagenum int =1;--第几页with cte as( select row_number() over(order by FAmount desc) as FRank,* from @sale)select * from ctewhere FRank between  @pagesize*(@pagenum-1) 1  and  @pagenum*@pagesize;

运行效果如下图所示:

这种写法基本是通用的,您可以比葫芦画瓢稍加改造,就可以用在您的分页脚本中。

如果在查询中使用order by子句,要注意order by子句最好与排名中over子句的order by一致,如果不一致,可能导致返回排名序列是不连续的,但这并不影响数据页的正确性。

还有一种可以实现分页写法,使用offset进行分页,这里我就不再赘述了。如果您想要更进一步了解分页语法,可以参考我之前写过的文章《如何在SQLServer查询中实现高效分页》。

RANK,相同值共用排名值,跳着排

RANK函数与ROW_NUMBER函数的语法和效果类似,最大的区别在于,如果碰到相同的字段值,会使用相同的排名序列值,后续的序列值则会跳过共用序列值。

其语法格式如下:

rank() over(order by field列表 asc|desc)

下面我们就实战一下:

select RANK() over(order by FAmount desc) as FRank,* from @sale;

运行效果如下图所示:

我们从上图可以很明显看出来,第2、3条记录因值相同,使用了相同的序列值“2”,到了第四条,排名序列值直接使用了“4”而跳过了“3”。

DENSE_RANK,相同值共用排名值,接着排

DENSE_RANK函数与RANK函数的语法和效果类似,区别就像函数名称中的DENSE(紧密的)含义一样,如果碰到相同的字段值,虽然都会使用相同的排名序列值,但序列值是连续的。

其语法格式如下:

dense_rank() over(order by field列表 asc|desc)

下面我们就实战一下:

select DENSE_RANK() over(order by FAmount desc) as FRank,* from @sale;

运行效果如下图所示:

可以看出RANK和DENSE_RANK确实有共同点,但也有区别。RANK排名值如果碰到相同字段值,则会使用相同的排名值,后续会跳过断开使用新的排名值;DENSE_RANK碰到相同字段值同样使用相同的排名值,但是接着排下来,不会断开

NTILE,根据约定分组

NTILE函数的语法和用途上面三个明显不同,其功能主要用来实现对记录进行分组。根据NTILE约定分组的组数,将查询结果按照over子句的排序标准进行分组,分组组号按照序列排列,同一个组使用同一个组号。

其语法格式如下:

ntile(组数) over(order by field列表 asc|desc)

如果指定的组数刚好实现均分,则每一组的记录数是相同的,比如我们将8条记录分成四组,则会有四个组,组号分别是1、2、3、4,每一组都会有两个记录,举例如下:

select NTILE(4) over(order by FAmount desc) as FRank,* from @sale;

运行效果如下:

如果分成五组,明显无法均分,那如何分呢?

当然是有一种逻辑存在的。这种逻辑可以这样理解,从第一组开始,使用总记录数除以组数,获取等于或最大于相除结果的最小整数,作为第一组的记录条数;剩余的记录条数按照相同算法依次类推。通俗来讲,NTITLE在优先配足靠前的分组的记录数的前提下,尽量进行均分。

下面我们我实战将以上记录分成五组的执行情况:

select NTILE(5) over(order by FAmount desc) as FRank,* from @sale;

运行效果如下:

从上图可以看出,记录条数是8条,分成5组,我们用8除以5,最接近且大于等于的整数是2,第一组占用了两条记录,这时候还剩余6条记录分4组;依次类推,6除以4,最接近且大于等于的整数是2,第二组还是2条记录;此时剩余4条记录要分3组,4除以3,最接近且大于等于的整数还是2;再往下,剩余两条分两组,这时候就刚好均分了。

需要明确的是,NTILE也可用来分页,但因分组逻辑的原因,效果就不如ROW_NUMBER理想了。


通过上述分析,我们对排名函数就有了充分的认识,您大可根据需要使用。希望对您有所帮助!

免责声明:本文仅代表文章作者的个人观点,与本站无关。其原创性、真实性以及文中陈述文字和内容未经本站证实,请读者仅作参考,并自行核实相关内容。如发现有害或侵权内容,请联系邮箱:jubao@pinlue.com,我们将在第一时间进行核实处理。

http://image95.pinlue.com/image/24.jpg
分享
评论
首页