SQL函数3种排名方法.docx
- 文档编号:27247003
- 上传时间:2023-06-28
- 格式:DOCX
- 页数:15
- 大小:101.44KB
SQL函数3种排名方法.docx
《SQL函数3种排名方法.docx》由会员分享,可在线阅读,更多相关《SQL函数3种排名方法.docx(15页珍藏版)》请在冰豆网上搜索。
SQL函数3种排名方法
排名函数是SQLServer2005新加的功能。
在SQLServer2005中有如下四个排名函数:
1.row_number
2.rank
3.dense_rank
4.ntile
下面分别介绍一下这四个排名函数的功能及用法。
在介绍之前假设有一个t_table表,表结构与表中的数据如图1所示:
图1
其中field1字段的类型是int,field2字段的类型是varchar
一、row_number
row_number函数的用途是非常广泛,这个函数的功能是为查询出来的每一行记录生成一个序号。
row_number函数的用法如下面的SQL语句所示:
selectrow_number()over(orderbyfield1)asrow_number,*fromt_table
上面的SQL语句的查询结果如图2所示。
图2
其中row_number列是由row_number函数生成的序号列。
在使用row_number函数是要使用over子句选择对某一列进行排序,然后才能生成序号。
实际上,row_number函数生成序号的基本原理是先使用over子句中的排序语句对记录进行排序,然后按着这个顺序生成序号。
over子句中的orderby子句与SQL语句中的orderby子句没有任何关系,这两处的orderby可以完全不同,如下面的SQL语句所示
selectrow_number()over(orderbyfield2desc)asrow_number,*fromt_tableorderbyfield1desc
上面的SQL语句的查询结果如图3所示。
图3
我们可以使用row_number函数来实现查询表中指定范围的记录,一般将其应用到Web应用程序的分页功能上。
下面的SQL语句可以查询t_table表中第2条和第3条记录:
witht_rowtable
as
(
selectrow_number()over(orderbyfield1)asrow_number,*fromt_table
)
select*fromt_rowtablewhererow_number>1androw_number<4orderbyfield1
上面的SQL语句的查询结果如图4所示。
图4
上面的SQL语句使用了CTE,关于CTE的介绍将读者参阅《SQLServer2005杂谈
(1):
使用公用表表达式(CTE)简化嵌套SQL》。
另外要注意的是,如果将row_number函数用于分页处理,over子句中的orderby与排序记录的orderby应相同,否则生成的序号可能不是有续的。
当然,不使用row_number函数也可以实现查询指定范围的记录,就是比较麻烦。
一般的方法是使用颠倒Top来实现,例如,查询t_table表中第2条和第3条记录,可以先查出前3条记录,然后将查询出来的这三条记录按倒序排序,再取前2条记录,最后再将查出来的这2条记录再按倒序排序,就是最终结果。
SQL语句如下:
select*from(selecttop2*from(selecttop3*fromt_tableorderbyfield1)a
orderbyfield1desc)borderbyfield1
上面的SQL语句查询出来的结果如图5所示。
图5
这个查询结果除了没有序号列row_number,其他的与图4所示的查询结果完全一样。
二、rank
rank函数考虑到了over子句中排序字段值相同的情况,为了更容易说明问题,在t_table表中再加一条记录,如图6所示。
图6
在图6所示的记录中后三条记录的field1字段值是相同的。
如果使用rank函数来生成序号,这3条记录的序号是相同的,而第4条记录会根据当前的记录数生成序号,后面的记录依此类推,也就是说,在这个例子中,第4条记录的序号是4,而不是2。
rank函数的使用方法与row_number函数完全相同,SQL语句如下:
selectrank()over(orderbyfield1),*fromt_tableorderbyfield1
上面的SQL语句的查询结果如图7所示。
图7
三、dense_rank
dense_rank函数的功能与rank函数类似,只是在生成序号时是连续的,而rank函数生成的序号有可能不连续。
如上面的例子中如果使用dense_rank函数,第4条记录的序号应该是2,而不是4。
如下面的SQL语句所示:
selectdense_rank()over(orderbyfield1),*fromt_tableorderbyfield1
上面的SQL语句的查询结果如图8所示。
图8
读者可以比较图7和图8所示的查询结果有什么不同
四、ntile
ntile函数可以对序号进行分组处理。
这就相当于将查询出来的记录集放到指定长度的数组中,每一个数组元素存放一定数量的记录。
ntile函数为每条记录生成的序号就是这条记录所有的数组元素的索引(从1开始)。
也可以将每一个分配记录的数组元素称为“桶”。
ntile函数有一个参数,用来指定桶数。
下面的SQL语句使用ntile函数对t_table表进行了装桶处理:
selectntile(4)over(orderbyfield1)asbucket,*fromt_table
上面的SQL语句的查询结果如图9所示。
图9
由于t_table表的记录总数是6,而上面的SQL语句中的ntile函数指定了桶数为4。
也许有的读者会问这么一个问题,SQLServer2005怎么来决定某一桶应该放多少记录呢?
可能t_table表中的记录数有些少,那么我们假设t_table表中有59条记录,而桶数是5,那么每一桶应放多少记录呢?
实际上通过两个约定就可以产生一个算法来决定哪一个桶应放多少记录,这两个约定如下:
1.编号小的桶放的记录不能小于编号大的桶。
也就是说,第1捅中的记录数只能大于等于第2桶及以后的各桶中的记录。
2.所有桶中的记录要么都相同,要么从某一个记录较少的桶开始后面所有捅的记录数都与该桶的记录数相同。
也就是说,如果有个桶,前三桶的记录数都是10,而第4捅的记录数是6,那么第5桶和第6桶的记录数也必须是6。
根据上面的两个约定,可以得出如下的算法:
//mod表示取余,div表示取整
if(记录总数mod桶数==0)
{
recordCount=记录总数div桶数;
将每桶的记录数都设为recordCount
}
else
{
recordCount1=记录总数div桶数+1;
intn=1; // n表示桶中记录数为recordCount1的最大桶数
m=recordCount1*n;
while(((记录总数-m) mod (桶数- n)) !
=0)
{
n++;
m=recordCount1*n;
}
recordCount2=(记录总数-m)div (桶数-n);
将前n个桶的记录数设为recordCount1
将n+1个至后面所有桶的记录数设为recordCount2
}
根据上面的算法,如果记录总数为59,桶数为5,则前4个桶的记录数都是12,最后一个桶的记录数是11。
如果记录总数为53,桶数为5,则前3个桶的记录数为11,后2个桶的记录数为10。
就拿本例来说,记录总数为6,桶数为4,则会算出recordCount1的值为2,在结束while循环后,会算出recordCount2的值是1,因此,前2个桶的记录是2,后2个桶的记录是1。
ROW_NUMBER、RANK、DENSE_RANK和NTILE,这些新函数使您可以有效地分析数据以及向查询的结果行提供排序值。
您可能发现这些新函数有用的典型方案包括:
将连续整数分配给结果行,以便进行表示、分页、计分和绘制直方图。
SpeakerStatistics方案
下面的SpeakerStatistics方案将用来讨论和演示不同的函数和它们的子句。
大型计算会议包括三个议题:
数据库、开发和系统管理。
十一位演讲者在会议中发表演讲,并且为他们的讲话获得范围为1到9的分数。
结果被总结并存储在下面的SpeakerStats表中:
CREATETABLESpeakerStats(
speaker VARCHAR(10)NOTNULLPRIMARYKEY
,track VARCHAR(10)NOTNULL
,score INT NOTNULL
,pctfilledevalsINT NOTNULL
,numsessions INT NOTNULL)
SETNOCOUNTON
INSERTINTOSpeakerStatsVALUES('Dan', 'Sys',3,22,4)
INSERTINTOSpeakerStatsVALUES('Ron', 'Dev',9,30,3)
INSERTINTOSpeakerStatsVALUES('Kathy', 'Sys',8,27,2)
INSERTINTOSpeakerStatsVALUES('Suzanne','DB',9,30,3)
INSERTINTOSpeakerStatsVALUES('Joe', 'Dev',6,20,2)
INSERTINTOSpeakerStatsVALUES('Robert','Dev',6,28,2)
INSERTINTOSpeakerStatsVALUES('Mike', 'DB',8,20,3)
INSERTINTOSpeakerStatsVALUES('Michele','Sys',8,31,4)
INSERTINTOSpeakerStatsVALUES('Jessica','Dev',9,19,1)
INSERTINTOSpeakerStatsVALUES('Brian', 'Sys',7,22,3)
INSERTINTOSpeakerStatsVALUES('Kevin', 'DB',7,25,4)
每个演讲者都在该表中具有一个行,其中含有该演讲者的名字、议题、平均得分、填写评价的与会者相对于参加会议的与会者数量的百分比以及该演讲者发表演讲的次数。
本节演示如何使用新的排序函数分析演讲者统计数据以生成有用的信息。
ROW_NUMBER
ROW_NUMBER函数使您可以向查询的结果行提供连续的整数值。
例如,假设您要返回所有演讲者的speaker、track和score,同时按照score降序向结果行分配从1开始的连续值。
以下查询通过使用ROW_NUMBER函数并指定OVER(ORDERBYscoreDESC)生成所需的结果:
SELECTROW_NUMBER()OVER(ORDERBYscoreDESC)ASrownum, speaker,track,scoreFROMSpeakerStatsORDERBYscoreDESC以下为结果集:
rownumspeaker track score
-------------------------------------
1 Jessica Dev 9
2 Ron Dev 9
3 Suzanne DB 9
4 Kathy Sys 8
5 Michele Sys 8
6 Mike DB 8
7 Kevin DB 7
8 Brian Sys 7
9 Joe Dev 6
10 Robert Dev 6
11 Dan Sys 3
得分最高的演讲者获得行号1,得分最低的演讲者获得行号11。
ROW_NUMBER总是按照请求的排序为不同的行生成不同的行号。
请注意,如果在OVER()选项中指定的ORDERBY列表不唯一,则结果是不确定的。
这意味着该查询具有一个以上正确的结果;在该查询的不同调用中,可能获得不同的结果。
例如,在我们的示例中,有三个不同的演讲者获得相同的最高得分(9):
Jessica、Ron和Suzanne。
由于SQLServer必须为不同的演讲者分配不同的行号,因此您应当假设分别分配给Jessica、Ron和Suzanne的值1、2和3是按任意顺序分配给这些演讲者的。
如果值1、2和3被分别分配给Ron、Suzanne和Jessica,则结果应该同样正确。
如果您指定一个唯一的ORDERBY列表,则结果总是确定的。
例如,假设在演讲者之间出现得分相同的情况时,您希望使用最高的pctfilledevals值来分出先后。
如果值仍然相同,则使用最高的numsessions值来分出先后。
最后,如果值仍然相同,则使用最低词典顺序speaker名字来分出先后。
由于ORDERBY列表—score、pctfilledevals、numsessions和speaker—是唯一的,因此结果是确定的:
SELECTROW_NUMBER()OVER(ORDERBYscoreDESC,pctfilledevalsDESC, numsessionsDESC,speaker)ASrownum, speaker,track,score,pctfilledevals,numsessionsFROMSpeakerStatsORDERBYscoreDESC,pctfilledevalsDESC,numsessionsDESC,speaker以下为结果集:
rownumspeaker track score pctfilledevalsnumsessions
--------------------------------------------------------------
1 Ron Dev 9 30 3
2 Suzanne DB 9 30 3
3 Jessica Dev 9 19 1
4 Michele Sys 8 31 4
5 Kathy Sys 8 27 2
6 Mike DB 8 20 3
7 Kevin DB 7 25 4
8 Brian Sys 7 22 3
9 Robert Dev 6 28 2
10 Joe Dev 6 20 2
11 Dan Sys 3 22 4
新的排序函数的重要好处之一是它们的效率。
SQLServer的优化程序只需要扫描数据一次,以便计算值。
它完成该工作的方法是:
使用在排序列上放置的索引的有序扫描,或者,如果未创建适当的索引,则扫描数据一次并对其进行排序。
另一个好处是语法的简单性。
为了让您感受一下通过使用在SQLServer的较低版本中采用的基于集的方法来计算排序值是多么困难和低效,请考虑下面的SQLServer2000查询,它返回与上一个查询相同的结果:
SELECT(SELECTCOUNT(*) FROMSpeakerStatsASS2
WHERES2.score>S1.score
OR(S2.score=S1.score ANDS2.pctfilledevals>S1.pctfilledevals)
OR(S2.score=S1.score ANDS2.pctfilledevals=S1.pctfilledevals ANDS2.numsessions>S1.numsessions)
OR(S2.score=S1.score ANDS2.pctfilledevals=S1.pctfilledevals ANDS2.numsessions=S1.numsessions ANDS2.speaker )+1ASrownum ,speaker,track,score,pctfilledevals,numsessions FROMSpeakerStatsASS1 ORDERBYscoreDESC,pctfilledevalsDESC,numsessionsDESC,speaker 该查询显然比SQLServer2005查询复杂得多。 此外,对于SpeakerStats表中的每个基础行,SQLServer都必须扫描该表的另一个实例中的所有匹配行。 对于基础表中的每个行,平均大约需要扫描该表的一半(最少)行。 SQLServer2005查询的性能恶化是线性的,而SQLServer2000查询的性能恶化是指数性的。 即使是在相当小的表中,性能差异也是显著的。 行号的一个典型应用是通过查询结果分页。 给定页大小(以行数为单位)和页号,需要返回属于给定页的行。 例如,假设您希望按照“scoreDESC,speaker”顺序从SpeakerStats表中返回第二页的行,并且假定页大小为三行。 下面的查询首先按照指定的排序计算派生表D中的行数,然后只筛选行号为4到6的行(它们属于第二页): SELECT* FROM(SELECTROW_NUMBER()OVER(ORDERBYscoreDESC,speaker)ASrownum, speaker,track,score FROMSpeakerStats)ASD WHERErownumBETWEEN4AND6 ORDERBYscoreDESC,speaker 以下为结果集: rownumspeaker track score ------------------------------------- 4 Kathy Sys 8 5 Michele Sys 8 6 Mike DB 8 用更一般的术语表达就是,给定@pagenum变量中的页号和@pagesize变量中的页大小,以下查询返回属于预期页的行: DECLARE@pagenumASINT,@pagesizeASINT SET@pagenum=2 SET@pagesize=3 SELECT*FROM(SELECTROW_NUMBER()OVER(ORDERBYscoreDESC,speaker)ASrownum ,speaker ,track ,score FROMSpeakerStats) ASDWHERErownumBETWEEN(@pagenum-1)*@pagesize+1AND@pagenum*@pagesize ORDERBYscoreDESC,speaker 上述方法对于您只对行的一个特定页感兴趣的特定请求而言已经足够了。 但是,当用户发出多个请求时,该方法就不能满足需要了,因为该查询的每个调用都需要您对表进行完整扫描,以便计算行号。 当用户可能反复请求不同的页时,为了更有效地进行分页,请首先用所有基础表行(包括计算得到的行号)填充一个临时表,并且对包含这些行号的列进行索引: SELECTROW_NUMBER()OVER(ORDERBYscoreDESC,speaker)ASrownum,* INTO#SpeakerStatsRN FROMSpeakerStats CREATEUNIQUECLUSTEREDINDEXidx_uc_rownumON#SpeakerStatsRN(rownum) 然后,对于所请求的每个页,发出以下查询: SELECTrownum,speaker,track,score FROM#SpeakerStatsRN WHERErownumBETWEEN(@pagenum-1)*@pagesize+1AND@pagenum*@pagesize ORDERBYscoreDESC,speaker 只有属于预期页的行才会被扫描。 分段 可以在行组内部独立地计算排序值,而不是为作为一个组的所有表行计算排序值。 为此,请使用PARTITIONBY子句,并且指定一个
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- SQL 函数 排名 方法