分区排名方案和排名值效率分析【图文+测试代码】

SQL排名函数详解
最新推荐文章于 2025-08-21 17:12:24 发布
转载 最新推荐文章于 2025-08-21 17:12:24 发布 · 122 阅读 · 0 ·
CC 4.0 BY-SA版权
原文链接：http://www.cnblogs.com/chjw8016/archive/2009/08/01/1536022.html

本文深入探讨了SQLServer中不同版本的行号计算方法，包括ROW_NUMBER函数、基于集合的解决方案、基于游标的解决方案及基于IDENTITY的解决方案。通过实际案例对比了各种方法的性能表现。

      示例中用的表和数据
      
ContractedBlock.gifExpandedBlockStart.gifCode
if OBJECT_ID ('Sales') is not null
    drop table Sales;
create table Sales
(
    empid varchar(10) not null primary key,
    mgrid varchar(10) not null,
    qty int not null
)
insert into Sales (empid ,mgrid ,qty ) values ('A','Z',300);
insert into Sales (empid ,mgrid ,qty ) values ('E','Z',250);
insert into Sales (empid ,mgrid ,qty ) values ('F','Z',200);
insert into Sales (empid ,mgrid ,qty ) values ('G','Z',100);
insert into Sales (empid ,mgrid ,qty ) values ('I','Y',300);
insert into Sales (empid ,mgrid ,qty ) values ('D','Y',200);
insert into Sales (empid ,mgrid ,qty ) values ('J','Y',150);
insert into Sales (empid ,mgrid ,qty ) values ('B','X',100);
insert into Sales (empid ,mgrid ,qty ) values ('H','X',100);
insert into Sales (empid ,mgrid ,qty ) values ('C','X',200);
insert into Sales (empid ,mgrid ,qty ) values ('K','X',250);
create index idx_qty_empid on Sales(qty,empid)
create index idx_mgrid_qty_empid on Sales(mgrid,qty,empid)
一、基于SQL Server 2005中的ROW_NUMBER函数

 

ROW_NUMBER函数MSDN的解释：返回结果集分区内行的序列号，每个分区的第一行从 1 开始。也就是说ROW_NUMBER函数按指定的顺序为查询结果集中的行分配连续的整数，并可选择在每个分区内单独的分配。

       语法：ROW_NUMBER ( )     OVER ( [ <partition_by_clause> ] <order_by_clause> )

       参数：

1、<partition_by_clause> 将 FROM 子句生成的结果集划入应用了 ROW_NUMBER 函数的分区。

2、<order_by_clause> 确定将 ROW_NUMBER 值分配给分区中的行的顺序。当在排名函数中使用 <order_by_clause> 时，不能用整数表示列。

       例如：下面的查询返回员工的销售数据，并按qty的顺序分配行号。

select empid ,qty,ROW_NUMBER () over(order by qty ) as rownum from Sales order by qty

      

查询结果：      

       
       为了理解ROW_NUMBER函数的执行效率，我们来看看它的执行计划。 

       
       为了计算排名值，优化器会先按分区列和排序列对数据进行排序。Segment主要用于确定分区边界，是一个分区内的行返回“TRUE”，否则返回“FALES”。示例没有指定Partition字段，所以整个表就是一个分区。Compute Scalar用回答当前值是否不等于前一个值。对于ROW_NUMBER，排名值必须为每一行递增，不管排序值是否变化，所示例中Compute Scalar的值永远返回“TRUE”。

       此查询计划和优化器计算排名值的方法可能还不太明显，但可以肯定的是数据保被扫描了一次。这比SQL Server 2000中计算排名值的方法要快的多。

       分区排名：      

select empid ,qty,ROW_NUMBER () over( partition by mgrid order by qty ) as rownum from Sales order by mgrid,qty,empid ;


 

二、基于集合的解决方案（SQL Server 2005之前的版本）

       所有排名值计算都可以由统计行数实现。要计算行号，你可以使用下面的基本方法，用子查询统计具有更小或相等排序值的行数，得到的行数就是期望的行号。例如下面的查询将成生基于empid排序的行号。        

select empid ,(select COUNT (*) from Sales as S2 where S2 .empid <=S1.empid ) as rownum from Sales as S1 order by empid ;

    查询结果：

   

    尽管这种方法计算行号的方法很简单，但是它执行非常慢。为什么呢？我们来查看一下它的执行计划。
   
    

排序列的索引是Sales表的聚合索引，执行计划完整的扫描整个表（Clustered运算）返回所有行，对于扫描返回的每一行，嵌套循环运算将调用通过统计行数生成行号的操作。每次行号计算都要调用一次对聚合索引查找操作，然后再执行局部扫描操作，以完成统计。

       大家可能都知道，影响查询性能的主要因素通常就是I/O操作。粗略地估计一下此计划所访问的行数就可以说明此查询为什么为如此的低效。要计算表的第一行的rownum，SQL Server要扫描索引表1行，对于第二行，则要扫描索引表2行，对于第三行，需要扫描索引表3行，等等。对于表的第N行，它需要扫描N行。对于包含N行且在排序列具有索引的表扫描总行数是：1+2+3+ …… +N。你可能没有马上意识到被扫描的行数有多庞大，对于一个包含100000行的表，总共会扫描5000050000行。当表没有索引时，情况就更加糟糕了。每计算一个号都要扫描整个表，查询扫描的总行数将会是N*N，同样的表，扫描的总行数为10000000000。

       分区排名：      

ContractedBlock.gifExpandedBlockStart.gifCode
select mgrid,empid,qty ,(select COUNT (*) from Sales  as S2 where S2.mgrid  =S1.mgrid   and (S2.qty <S1.qty or (S2.qty =S1.qty and S2.empid <=S1.empid ))) as rownum from Sales  as S1 order by mgrid,qty,empid ;


三、基于游标的解决方案


    你也可以用游标来计算行号，相对于上述方案，基于游标的解决方案就非常简单明了。创建一个快速向前的游标，它的查询按分区表，排序列和附加列排序。当你从游标提取行时递增计数器，并在检测到新分区时重置此计数器。你可以把结查列和行号存储到临时表或表变量。例如：使用游标按qyt和empid 的顺序计算行号。

ContractedBlock.gifExpandedBlockStart.gifCode
declare @salesRN table (empid varchar (5),qty int ,rn int);
declare @empid as varchar(5) ,@qty as int ,@rn as int;
begin tran
    declare rncursor Cursor FAST_FORWARD for select empid,qty from Sales order by qty,empid ;
    open rncursor;
    set @rn =0;
    fetch next from rncursor into @empid,@qty;
    while @@FETCH_STATUS =0
        begin 
        set @rn=@rn+1;
        insert into @salesRN (empid ,qty ,rn ) values (@empid ,@qty ,@rn);
        fetch next from rncursor into @empid,@qty;
        end
    close rncursor;
    deallocate rncursor;
commit tran
select * from @salesRN

执行结果：  
    
    一般来说，应该尽量避免使用游标，因为游标包含许多的开销，会影响性能的。本示例中，除非分区非常小，否则基于游标的解决方案要比基于集合的解决方案的速度更快，因为它只扫描数据表一次。这主意味着随着表越来越大，游标方案呈线性下降，而集合方案是按N*N的速度下降。

 

四、基于IDENTITY的解决方案

       在SQL Server 2005之前的版本中，你可以利用IDENTITY函数和IDENTITY列属性来计算行号。用IDENTITY来计算行号是一个非常有用的方法。IDENTITY函数和IDENTITY列属性唯一的区别是：IDENTITY函数无法保证IDENTITY值的分配顺序，但是IDENTITY列属性可以保证。

       未分区   在 SELECT INTO 语句中使用IDENTITY函数是到目前为止在SQL Server 2005之前的版本中计算行号最快的方法。原因是因为它只扫描表一次，而且没有游标操作的开销。

       例如下面代码演示了如何用IDENTITY函数创建临时表，并用没有任何特定顺序的行号来填充此表。      

select empid,qty,IDENTITY(int,1,1) as rn into #SalesRN  from Sales ;
select * from #SalesRN;
drop table #SalesRN;

    查询结果：    


    已分区     当你在乎IDENTITY值的分配顺序时---换句话说，当行号应该基于指定顺序时---先创建表，然后加载数据。这个方法没有SELECT  TNTO方法快，因为INSERT SELECT总是被完整记录日志。不过它还是比SQL Server 2005之前的版本快得多。用IDENTITY来生成分区行号要求执行额外的一步：行号=分区内最小行号+1。下面是按qty和empid计算行号的示例：

ContractedBlock.gifExpandedBlockStart.gifCode
create table #SalesRN (mgrid varchar(5),empid varchar(5),qty int,rownum  int identity);
insert into  #SalesRN (mgrid,empid ,qty ) select mgrid,empid,qty from Sales order by mgrid,qty ,empid ;
select mgrid,qty,empid,rownum-(select MIN(rownum) from #SalesRN as S2 where S2.mgrid =S2 .mgrid) +1 from #SalesRN as S1
drop table #SalesRN

查询结查：

 

五、

计算排名行号的各种方法的性能测试

    系统配置：CPU：Inter (R) Pentium(R) 4 CPU 3.40GHz         内存：1G    硬盘：WDC wd800jd-60lsa5

测试代码如下：    

ContractedBlock.gifExpandedBlockStart.gif测试代码
set nocount on;
go
if OBJECT_ID ('RNBenchmark')is not null
    drop table RNBenchmark;
if OBJECT_ID('RNTechniques')is not null
    drop table RNTechniques;
if OBJECT_ID ('SalesBM') is not null
    drop table SalesBM
if OBJECT_ID ('SalesBMIdentity') is not null
    drop table SalesBMIdentity;
if OBJECT_ID ('SalesBMCursor') is not null
    drop table SalesBMCursor;
if OBJECT_ID ('Nums') is not null
    drop table Nums;
go
create table Nums(n int not null primary key);
go
declare @max int ,@rc int 
set @max =1000000
set @rc =1;
insert into Nums values (1)
while @rc * 2<=@max 
begin
  insert into Nums select Nums.n +@rc from Nums ;
  set @rc =@rc * 2;
end
insert into Nums select Nums.n+@rc from Nums where n+@rc<=@max;
go
create table RNTechniques(tid int not null primary key,technique varchar(25) not null);
insert into RNTechniques(tid ,technique ) values (1,'Set_Based_2000');
insert into RNTechniques(tid ,technique ) values (2,'IDENTITY');
insert into RNTechniques(tid ,technique ) values (3,'CURSOR');
insert into RNTechniques(tid ,technique ) values (4,'ROW_NUMBER_2005');
go
create table RNBenchmark
(
    tid int not null references RNTechniques(tid),
    numrows int not null,
    runtimes bigint not null,
    primary key (tid,numrows)
);
go
create table SalesBM
(
    empid int not null IDENTITY primary key,
    qty int not null
);
create index idx_qty_empid on SalesBM(qty,empid);
go
create table SalesBMIdentity(empid int ,qty int,rn int identity );
go
create table SalesBMCursor(empid int ,qty int,rn int );
go
declare @maxnumrows as int,@steprows as int,@curnumrows as int,@dt as datetime;
set @maxnumrows =100000;
set @steprows =10000;
set @curnumrows =10000;
while @curnumrows <=@maxnumrows 
begin
    truncate table SalesBM;
    insert into SalesBM(qty ) select CAST (1+999.9999999999*RAND(CHECKSUM(NEWID())) as int) from Nums where n<@curnumrows ;
    ---Set-Based 2000
    dbcc  FREEPROCCACHE with NO_INFOMSGS;
    dbcc DROPCLEANBUFFERS with NO_INFOMSGS;
    set @dt =GETDATE ();
    select empid,qty ,(select COUNT (*) from SalesBM  as S2 where S2.qty <S1.qty  or  (S2.qty =S1.qty and S2.empid <=S1.empid)) as rn from SalesBM  as S1 order by qty,empid ; 
    insert into RNBenchmark (tid,numrows ,runtimes ) values (1,@curnumrows ,DATEDIFF (MS,@dt,GETDATE()));
    ---IDENTITY
    truncate table SalesBMIdentity;
    dbcc  FREEPROCCACHE with NO_INFOMSGS;
    dbcc DROPCLEANBUFFERS with NO_INFOMSGS;
    set @dt =GETDATE ();
    insert into SalesBMIdentity(empid ,qty ) select empid,qty from SalesBM order by qty ,empid ;
    select * from SalesBMIdentity;
    insert into RNBenchmark (tid,numrows ,runtimes ) values (2,@curnumrows ,DATEDIFF (MS,@dt,GETDATE()));
    ---Cursor
    truncate table SalesBMCursor;
    dbcc  FREEPROCCACHE with NO_INFOMSGS;
    dbcc DROPCLEANBUFFERS with NO_INFOMSGS;
    set @dt =GETDATE ();
    
    declare @empid as int ,@qty as int ,@rn as int;
    begin tran
    declare rncursor Cursor FAST_FORWARD for select empid,qty from SalesBM order by qty,empid ;
    open rncursor;
    set @rn =0;
    fetch next from rncursor into @empid,@qty;
    while @@FETCH_STATUS =0
        begin 
        set @rn=@rn+1;
        insert into SalesBMCursor (empid ,qty ,rn ) values (@empid ,@qty ,@rn);
        fetch next from rncursor into @empid,@qty;
        end
    close rncursor;
    deallocate rncursor;
    commit tran
    insert into RNBenchmark (tid,numrows ,runtimes ) values (3,@curnumrows ,DATEDIFF (MS,@dt,GETDATE()));
    ----ROW_NUMBER 2005
    dbcc  FREEPROCCACHE with NO_INFOMSGS;
    dbcc DROPCLEANBUFFERS with NO_INFOMSGS;
    set @dt =GETDATE ();
    select empid ,qty,ROW_NUMBER () over(order by qty,empid ) as rn from SalesBM order by qty,empid ; 
    insert into RNBenchmark (tid,numrows ,runtimes ) values (4,@curnumrows ,DATEDIFF (MS,@dt,GETDATE()));
    set @curnumrows =@curnumrows+@steprows;
end;
go
select numrows ,[Set_Based_2000],[IDENTITY],[CURSOR],[ROW_NUMBER_2005] from (select technique , numrows ,runtimes  from RNBenchmark as B join RNTechniques as T on B.tid =T .tid  ) as D
    pivot (max(runtimes) for technique in ([Set_Based_2000],[IDENTITY],[CURSOR],[ROW_NUMBER_2005])) as p order by numrows ;

测试结果如下：    


    结论如下：

从上表可以轻易的看出那种方法的比较好，在SQL Server 2005中我们应该总是使用新的排名函数。而在SQL Server 2000中，如果基于集合的方案对你来说是很重要，则只有在分区中的行数很少时（数十行）才使用此方法。否则应该使用基于IDENTITY的方案---先创建表，然后加载数据。

 

转载于:https://www.cnblogs.com/chjw8016/archive/2009/08/01/1536022.html

程序人生成长记：技术写作的学习力量
AI天才研究院
05-04 895
技术写作如何重构认知体系与思维模式如何通过写作实现知识的高效沉淀与传播写作能力对职业发展的长期价值赋能覆盖范围包括技术写作的核心概念、思维训练方法、知识管理体系、实战工具链及职业应用场景，适合希望突破成长瓶颈的各阶段开发者。核心概念：解析技术写作的本质特征及其与程序员思维的内在关联思维训练：揭示写作如何促进逻辑思维、系统思维和批判性思维的提升知识管理：构建“写作即存储”的高效知识复利模型实战体系：提供从选题到发布的全流程操作框架职业赋能：分析写作能力在技术影响力构建中的关键作用工具生态。
MATLAB算法实战应用案例精讲-【数模应用】路径分析(附R语言代码实现)
qq_36130719的博客
05-21 478
在互联网数据化运营实践中，有一类数据分析应用是互联网行业所独有的——路径分析。路径分析应用是对特定页面的上下游进行可视化展示并分析用户在使用产品时的路径分布情况。比如：当用户使用某APP时，是怎样从【首页】进入【详情页】的，用户从【首页】分别进入【详情页】、【播放页】、【下载页】的比例是怎样的，以及可以帮助我们分析用户离开的节点是什么。在场景对应到具体的技术方案设计上，我们将访问数据根据session划分，挖掘出用户频繁访问的路径；
深度学习与总结JVM专辑（三）：垃圾回收器—G1（图文+代码）
左灯右行的爱情
11-26 4069
我需要糖分
C语言流程图生成器：编程效率与代码理解力的提升工具
weixin_35578748的博客
09-04 2704
本文还有配套的精品资源，点击获取 简介：C语言流程图生成器是一款能直观展示C程序执行流程的工具，它通过图形化方式简化复杂逻辑结构的理解和沟通。开发者可以通过此工具提高代码的可读性，优化算法设计，并在团队合作中便于成员间交流。此生成器可以分析源代码，自动绘制流程图，并提供交互式编辑和导出功能。本文介绍了生成器的使用步骤、注意事项以及拓展应用，旨在帮助初学者和专业开发者提升编程...
面试必备：高频算法题汇总「图文解析 + 教学视频 + 范例代码」必问之 排序 + 二叉树 部分！
圆号本号
10-11 502
排序 冒泡排序 思路： 1、比较相邻的元素。如果第一个比第二个大（小），就交换他们两个。 2、对每一对相邻元素作同样的工作，从开始第一对到结尾的最后一对。这步做完后，最后的元素会是最大（小）的数。 3、针对所有的元素重复以上的步骤，除了最后已经选出的元素（有序）。 4、持续每次对越来越少的元素（无序元素）重复上面的步骤，直到没有任何 视频： 数据结构排序算法之冒泡排序演示 示例代码： publ...
c语言中分区,5C语言内存分区
weixin_39790760的博客
05-19 138
1、指针强化11指针是一种数据类型1指针变量也是一种变量，占有内存空间，用来保存内存地址测试指针变量占有内存空间大小。2*p操作内存intmain(){inta=10;int*p=NULL;//指针指向谁，就把谁的地址赋值给指针p=&a;//通过*可以找到指针指向的内存区域，操作的是内存*p=20;printf("p=%d,&a=%d\n",p,&a);printf("*p...
虚拟化的发展历程和实现原理——图文详解
GarfieldEr007的专栏
02-21 4550
前言 现在市场上最常见的虚拟化软件有VMWare workstation(VMWare)、VirtualBox(Oracle)、Hyper-V(Microsoft)、KVM(Redhat)、Xen等，这些软件统称之为VMM(Virtual Machine Monitor)，使用不同的虚拟化实现。而这些虚拟化实现的方式可以分为全虚拟化、半虚拟化、硬件虚拟化等，本篇主要是理解这些虚拟化实现的原理
面试必备：高频算法题汇总「图文解析 + 教学视频 + 范例代码」必知必会 排序 + 二叉树 部分！
代码小牛的博客
10-11 338
排序时光小说网 https://wap.youxs.org/ 所谓排序算法，即通过特定的算法因式将一组或多组数据按照既定模式进行重新排序。这种新序列遵循着一定的规则，体现出一定的规律，因此，经处理后的数据便于筛选和计算，大大提高了计算效率。 对于排序： 我们首先要求其具有一定的稳定性 即当两个相同的元素同时出现于某个序列之中 则经过一定的排序算法之后 两者在排序前后的相对位置不发生变化...
测试技术面试题
weixin_44684812的博客
05-19 2688
测试技术面试题测试技术面试题开发及环境搭建类面试题人力资源面试题 测试技术面试题 1、什么是兼容性测试？兼容性测试侧重哪些方面？ 参考答案： 兼容测试主要是检查软件在不同的硬件平台、软件平台上是否可以正常的运行，即是通常说的软件的可移植性。 兼容的类型，如果细分的话，有平台的兼容，网络兼容，数据库兼容，以及数据格式的兼容。 兼容测试的重点是，对兼容环境的分析。通常，是在运行软件的环境不是很确定的情况下，才需要做兼容。根据软件运行的需要，或者根据需求文档，一般都能够得出用户会在什么环境下使用该软件，把这些环境
软件测试面试题汇总
热门推荐
u014646597的博客
09-27 2万+
转载自： https://blog.youkuaiyun.com/koudaidai/article/details/7394126 软件测试面试题汇总 测试技术面试题 ...........................................................................................................
【VBA与OneNote集成】：构建企业级的数据同步解决方案，提升工作效率！
[【VBA与OneNote集成】：构建企业级的数据同步解决方案，提升工作效率！](https://www.superexcelvba.com/static/images/en/tutorial/excel-vbe.png) # 摘要 本文探讨了VBA与OneNote集成的多个层面，从基础知识的...
国产7B大模型InternLM-XComposer媲美GPT-4V | 新增“多图多轮对话、指令生成网页、图文混排”等实用功能！
强化学习曾小健
07-19 1314
上图展示了InternetLM-XComposer-2.5（IXC-2.5）在不同领域的基准测试上的性能概述，包括视频基准测试、结构高分辨率基准测试、通用视觉QA基准测试、多真实多图像基准测试和网页制作基准测试。在模型微调阶段，统一动态图像分区策略的最大数量H是24，以处理超大的图像和视频。InternetLM-XComposer-2.5的模型架构主要遵循InternetLM-XComposer2和InternetLMXComposer2-4KHD（为了简单来说，是IXC2和IXC2-4KHD）的设计，
AI产品经理面试宝典第73天：AI技术应用与商业模型深度解析相关题
打造全国最全的AI Agent开发知识领域的博客
08-21 58
本文深入解析AI规模化发展、商业模型设计、多模态技术应用等核心面试考点。涵盖CLIP模型架构、SD文生图流程、潜空间原理等前沿技术，提供可落地的决策冲突解决方案，助您掌握AI产品经理必备技术思维与商业视野。
2022年单片机-第讲.ppt
09-10
2022年单片机-第讲.ppt
protobuf-lite-3.5.0-13.el8.tar.gz
09-10
# 适用操作系统：Centos8 #Step1、解压 tar -zxvf xxx.el8.tar.gz #Step2、进入解压后的目录，执行安装 sudo rpm -ivh *.rpm
2020年大学生网络公司实习报告范文.doc
09-10
2020年大学生网络公司实习报告范文.doc
计算机工作总结范文.doc
09-10
计算机工作总结范文.doc
Excel表格模板：年终原材料领料、退料明细汇总表.xlsx
最新发布
09-10
Excel表格模板：年终原材料领料、退料明细汇总表.xlsx
2020届-二轮-基因工程-专题卷-(江苏版).doc
09-10
2020届-二轮-基因工程-专题卷-(江苏版).doc
MySQL 5.5.8分区表性能测试结果分析
这些数据通常用于后续的分析和性能评估，是性能测试不可或缺的一部分。 #### 10. MySQL 5.5.8 版本的其他特性 除了分区功能，MySQL 5.5.8版本还引入了其他一些重要的特性，例如： - **复制增强：**支持基于行的日志...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值