SQL Server 开窗函数 Over()代替游标的使用

当前位置:

首页 > 数据库 > T-SQL >

SQL Server 开窗函数 Over()代替游标的使用

前言：今天在优化工作中遇到的sql慢的问题，发现以前用了挺多游标来处理数据，这样就导致在数据量多的情况下，需要一行一行去遍历从而计算需要的数据，这样处理的结果就是数据慢，容易卡死。

语法介绍：

1、与Row_Number() 函数结合使用，对结果进行排序，这个是我们使用的非常多的

			
									SELECT ROW_NUMBER() OVER(ORDER BY FItemID DESC) FSort,* FROM Organization

2、与聚合函数结合使用，利用over子句的分组和排序，对需要的数据进行操作

例如：SUM() Over() 累加值、AVG() Over() 平均数
MAX() Over() 最大值、MIN() Over() 最小值

具体介绍：

下面模拟工作中通过开窗函数代替游标的例子，通过期初余额与单据的预收金额、应收金额、实收金额来计算截止本单的期末余额，在以往就是通过游标一行一行去遍历，计算需要的期末余额，现在使用SUM() Over()来代替，最终要实现的效果图如下：

第一行表示标题；第二行表示客户，是一行空行；第三行是期初余额，只显示期末余额的数据，第四至第六行表示的是每种单据的余额情况，并逐步汇总当前行的期末余额数据；最后一行表示的是对客户的合计。

1、构建需要用到的表和数据（简略版）

2、以往的游标写法

代码说明：创建了一个临时表，使用游标遍历我们的DetailData数据表，为了呈现我们最终需要的数据样式，插入客户空行、期初余额、单据信息、客户小计等，逐行计算期末余额值的情况，最终效果如下：

3、使用SUM() Over()的写法

代码说明：相比第二种，去除了游标的写法，通过了

			
									SUM(d.FReceivableAmount - d.FPreAmount - d.FReceiveAmount) OVER(PARTITION BY d.FCustId ORDER BY d.FCustId,d.FDate,d.FID)

来计算我们需要的值，这个语法说明一下，sum是累加计算，计算应收金额 - 预收金额 - 实收金额（第二行计算出来的结果要加上第一行计算出来的结果，第三行计算出来的结果要加上第二行计算出来的结果，依次类推，所以，其他聚合函数也是这种用法哦），PARTITION BY分组统计客户，并通过ORDER BY指定排序
这个PARTITION BY和Order By结果的用法就很关键了，不然计算就不是预期想要的
再举个例子：比如使用Count() Over() 计算客户的订单号

			
									SELECT DISTINCT FCustId,COUNT(FBillNo) OVER(PARTITION BY FCustId) FBillNum FROM DetailData

总结：

1、游标的使用场景可以很广，但是在数据量大的时候，就会显得很慢，一行一行遍历的速度还是挺久的

2、使用开窗函数来实现一些功能，还是很方便能实现效果，并且它的速度也是很快，值得推荐。

出处：https://www.cnblogs.com/cxt618/p/13826000.html

栏目列表