SQL技巧-如何⼤⼤提升row_number函数的效率
在sql server中 , 有⼀个很常⽤的窗⼝函数row_number
⼀般写法是这样的
1select*from
2 (
3select*,rn=ROW_NUMBER()over(partition by column_a order by column_b desc) from table_a
4 ) a =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)=50then round(avg(volume),0) end from (
select secid,volume,rn=ROW_NUMBER()over(partition by secid order by date desc)
sql中round函数怎么使用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)=50then 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 top50 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函数
虽然他的写法简单思路清晰也是⼀个好⽅法但是如果想要提⾼效率可以考虑选择我提供的这种⽅法
谢谢

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。