SQL技巧 -> 如何大大提升row_number函数的效率

在sql server中 , 有一个很常用的窗口函数row_number

一般写法是这样的

select * from 
(
select *,rn=ROW_NUMBER()over(partition by column_a order by column_b desc) from table_a
) a where a.rn=1

row_number函数常用于分组取最值的情况下

partition by 相当于group by 指定按照哪个字段进行分组

但是由于sql的执行顺序, 当用row_number函数的时候 不得不对于那些排序结果不等于1的  即rn<>1的行记录在内层查询中也进行排序和返回

而且是所有的表记录都会参与分组排序 然后才能在外层查询中再筛选出rn=1的行记录来

这导致row_number函数的效率会很低 尤其当表很大的时候

 

 

下面考虑这样一个实际问题

在一张股票的每日日行情表中, 如何计算出每天的50日均成交量呢

行情表名为hk_StockHistoricalDaily 

表主要字段介绍: 股票内码secid , 成交量volume , 交易日期date ,

需要计算的字段50日均成交量avgvolume50

select secid,avgvolume50=case when count(volume)=50 then round(avg(volume),0) end from (
select secid,volume,rn=ROW_NUMBER()over(partition by secid order by date desc)
from hk_StockHistoricalDaily where volume is not null
)a where rn<=50
group by secid

备注: 基于实际业务考虑, 50日均不考虑停牌(volume为null直接忽略), 交易日不满50个的avgvolume50为null

当前hk_StockHistoricalDaily 表中总记录数是700w+

索引合适 上述代码running time需要15-20 seconds 

 

这是使用row_number的常规写法 下面换一种写法

 

主要是运用一个sql server的outer apply语法 相当于left join

但是却可以在一个子表查询中使用top 和 order by语句 

正是由于这个特性 可以大大减少子表查询中需要参与计算和返回的记录数 避免对所有的表记录分组排序

select a.secid,avgvolume50=case when count(b.volume)=50 then round(avg(b.volume),0) end from
(select [date],secid,volume from hk_StockHistoricalDaily x where date=(select max(date) from  hk_StockHistoricalDaily where x.secid=secid and volume is not null)) a
outer apply (select top 50 date,secid,volume from hk_StockHistoricalDaily where a.secid=secid and volume is not null order by date desc) b
group by a.secid

上述代码running time需要3-4 seconds

经过验证 两段代码的结果集完全一样 即下面的代码可以完全替代row_number函数

执行效率大概提高了 5 倍左右

在实际工作中 如果表的记录数很大(百万级) 应该尽量避免使用row_number函数

虽然他的写法简单 思路清晰 也是一个好方法 但是如果想要提高效率 可以考虑选择我提供的这种方法

谢谢