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

来源：cnblogs　　作者：至情相遇　　时间：2020/11/9 15:56:03　　对本文有异议

前言：今天在优化工作中遇到的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、构建需要用到的表和数据（简略版）

--客户表
CREATE TABLE Organization(
	FItemID		INT NOT NULL PRIMARY KEY IDENTITY(1,1),
	FNumber		NVARCHAR(255),
	FName		NVARCHAR(255)
)
 
--期初数据表
CREATE TABLE InitialData(
	FID			INT NOT NULL PRIMARY KEY IDENTITY(1,1),
	FCustId			INT NOT NULL,
	FPreAmount		DECIMAL(28,10) NOT NULL DEFAULT(0),		--预收金额
	FReceivableAmount	DECIMAL(28,10) NOT NULL DEFAULT(0),		--应收金额
	FReceiveAmount		DECIMAL(28,10) NOT NULL DEFAULT(0)		--实收金额
)
 
--单据明细表
CREATE TABLE DetailData(
	FID			INT NOT NULL PRIMARY KEY IDENTITY(1,1),
	FCustId			INT NOT NULL,
	FDate			DATETIME NOT NULL,
	FBillType		NVARCHAR(64) NOT NULL,
	FBillNo			NVARCHAR(64) NOT NULL,
	FPreAmount		DECIMAL(28,10) NOT NULL DEFAULT(0),		--预收金额
	FReceivableAmount	DECIMAL(28,10) NOT NULL DEFAULT(0),		--应收金额
	FReceiveAmount		DECIMAL(28,10) NOT NULL DEFAULT(0)		--实收金额
)
 
INSERT INTO Organization(FNumber,FName) VALUES('001','北京客户')
INSERT INTO Organization(FNumber,FName) VALUES('002','上海客户')
INSERT INTO Organization(FNumber,FName) VALUES('003','广州客户')
 
INSERT INTO InitialData(FCustId,FPreAmount,FReceivableAmount,FReceiveAmount)
VALUES(1,0,0,0)
INSERT INTO InitialData(FCustId,FPreAmount,FReceivableAmount,FReceiveAmount)
VALUES(2,8000,7245,0)
INSERT INTO InitialData(FCustId,FPreAmount,FReceivableAmount,FReceiveAmount)
VALUES(3,0,1068.21,1068.00)
 
INSERT INTO DetailData(FCustId,FDate,FBillType,FBillNo,FPreAmount,FReceivableAmount,FReceiveAmount)
VALUES(1,'2020-06-30','委托结算','XSD20200700008',0,1221.56,0)
INSERT INTO DetailData(FCustId,FDate,FBillType,FBillNo,FPreAmount,FReceivableAmount,FReceiveAmount)
VALUES(1,'2020-06-30','委托结算','XSD20200700009',0,373.46,0)
INSERT INTO DetailData(FCustId,FDate,FBillType,FBillNo,FPreAmount,FReceivableAmount,FReceiveAmount)
VALUES(1,'2020-06-30','委托结算退货','XSD20200700010',0,-427.05,0)
INSERT INTO DetailData(FCustId,FDate,FBillType,FBillNo,FPreAmount,FReceivableAmount,FReceiveAmount)
VALUES(1,'2020-07-30','销售商品返利','XSFL20200700005',0,-17.9,0)
 
INSERT INTO DetailData(FCustId,FDate,FBillType,FBillNo,FPreAmount,FReceivableAmount,FReceiveAmount)
VALUES(2,'2020-06-25','预收退款','SKD20200700002',-755,0,0)
INSERT INTO DetailData(FCustId,FDate,FBillType,FBillNo,FPreAmount,FReceivableAmount,FReceiveAmount)
VALUES(2,'2020-06-20','销售发货','XSD20200700006',0,6169.50,6169.50)
INSERT INTO DetailData(FCustId,FDate,FBillType,FBillNo,FPreAmount,FReceivableAmount,FReceiveAmount)
VALUES(2,'2020-07-30','销售总额返利','XSFL20200700002',0,-493.56,-421.85)
INSERT INTO DetailData(FCustId,FDate,FBillType,FBillNo,FPreAmount,FReceivableAmount,FReceiveAmount)
VALUES(2,'2020-07-31','其他应收','QTYS20200900001',0,6000.00,0)
INSERT INTO DetailData(FCustId,FDate,FBillType,FBillNo,FPreAmount,FReceivableAmount,FReceiveAmount)
VALUES(2,'2020-06-20','预收冲应收','HXD20200700006',-7245.00,0,7245.00)
 
INSERT INTO DetailData(FCustId,FDate,FBillType,FBillNo,FPreAmount,FReceivableAmount,FReceiveAmount)
VALUES(3,'2020-06-30','销售收款','SKD20200700003',0,0,2386.96)
INSERT INTO DetailData(FCustId,FDate,FBillType,FBillNo,FPreAmount,FReceivableAmount,FReceiveAmount)
VALUES(3,'2020-06-30','应收转应收','HXD20200700007',0,2386.75,0)
INSERT INTO DetailData(FCustId,FDate,FBillType,FBillNo,FPreAmount,FReceivableAmount,FReceiveAmount)
VALUES(3,'2020-07-08','销售退货','XSD20200700014',0,-46.80,0)
GO

2、以往的游标写法

SET NOCOUNT ON
--建立临时表处理获取数据
CREATE TABLE #DATA(
	FID			INT NOT NULL PRIMARY KEY IDENTITY(1,1),
	FClassTypeId		INT NOT NULL,
	FCustId			INT NOT NULL,
	FNumber			NVARCHAR(255),
	FName			NVARCHAR(255),
	FDate			DATETIME  NULL,
	FBillType		NVARCHAR(64)  NULL,
	FBillNo			NVARCHAR(64)  NULL,
	FPreAmount		DECIMAL(28,10) NOT NULL DEFAULT(0),		--预收金额
	FReceivableAmount	DECIMAL(28,10) NOT NULL DEFAULT(0),		--应收金额
	FReceiveAmount		DECIMAL(28,10) NOT NULL DEFAULT(0),		--实收金额
	FBalanceAmount		DECIMAL(28,10) NOT NULL DEFAULT(0)              --期末余额
)
 
Declare @Id					INT
Declare @CustId				INT
Declare @PreAmount			decimal(28,10)
Declare @ReceivableAmount	decimal(28,10)
Declare @ReceiveAmount		decimal(28,10)
Declare @OldCustId			int
Declare @Count				int
Declare @LastAmount			decimal(28,10)
Declare @SumPreAmount		decimal(28,10)
Declare @SumReceivableAmount decimal(28,10)
Declare @SumReceiveAmount decimal(28,10)
Declare @SumBalanceAmount decimal(28,10)
 
--使用游标
Declare Data_cursor Cursor
For Select FID,FCustId,FPreAmount,FReceivableAmount,FReceiveAmount
    From DetailData
    Order By FCustId,FDate,FID
OPEN Data_cursor
FETCH NEXT FROM Data_Cursor INTO @Id,@CustId,@PreAmount,@ReceivableAmount,@ReceiveAmount
SET @OldCustId = @CustId
SET @Count = 0
SET @LastAmount = 0 
SET @SumPreAmount = 0 
SET @SumReceivableAmount = 0 
SET @SumReceiveAmount = 0 
SET @SumBalanceAmount = 0
WHILE @@FETCH_STATUS = 0 
BEGIN	
	IF @Count > 0 
	BEGIN
		IF @OldCustId <> @CustId  
		BEGIN
			--表示客户已经变了，要插入小计
			SET @Count = 0
			INSERT INTO #DATA(FClassTypeId,FBillType,FCustId,FNumber,FName,FPreAmount,FReceivableAmount,FReceiveAmount,FBalanceAmount) 
			SELECT -9999,FName + '小计',FItemID,FNumber,FName,@SumPreAmount,@SumReceivableAmount,@SumReceiveAmount,@LastAmount
			FROM Organization 
			WHERE FItemID = @OldCustId
			Select @SumPreAmount=0,@SumReceivableAmount=0,@SumReceiveAmount=0,@SumBalanceAmount=0,@LastAmount=0
		END      
    END  
	IF @Count = 0 
	BEGIN
		Set @OldCustId=@CustId
		--插入一行空行
		INSERT INTO #DATA(FClassTypeId,FBillType,FCustId,FNumber,FName) 
		SELECT -1000,FName,FItemID,FNumber,FName
		FROM Organization 
		WHERE FItemID = @CustId
 
		--获取期初的期末余额
		SELECT @LastAmount=isnull(FReceivableAmount,0) - isnull(FPreAmount,0) - isnull(FReceiveAmount,0),@PreAmount=isnull(FPreAmount,0),@ReceivableAmount=isnull(FReceivableAmount,0),@ReceiveAmount=isnull(FReceiveAmount,0) 
		FROM InitialData
		WHERE FCustId = @CustId
 
		INSERT INTO #DATA(FClassTypeId,FBillType,FCustId,FNumber,FName,FBalanceAmount) 
		VALUES(-1000,'期初余额',@CustId,'','',@LastAmount)
 
		SELECT @Count = 1
		SELECT @SumBalanceAmount = @LastAmount
    END  
 
	--插入单据明细
	INSERT INTO #DATA(FClassTypeId,FCustId,FNumber,FName,FDate,FBillType,FBillNo,FPreAmount,FReceivableAmount,FReceiveAmount,FBalanceAmount) 
	SELECT 0,d.FCustId,o.FNumber,o.FName,FDate,FBillType,FBillNo,FPreAmount,FReceivableAmount,FReceiveAmount,@LastAmount + FReceivableAmount - FPreAmount - FReceiveAmount
	FROM DetailData d
	INNER JOIN Organization o ON d.FCustId = o.FItemID
	WHERE d.FCustId = @CustId AND FID = @Id
 
	SELECT 
	@LastAmount = @LastAmount + FReceivableAmount - FPreAmount - FReceiveAmount,
	@SumPreAmount=@SumPreAmount + FPreAmount,@SumReceivableAmount=@SumReceivableAmount + FReceivableAmount,
	@SumReceiveAmount=@SumReceiveAmount + FReceiveAmount
	FROM DetailData 
	WHERE FCustId = @CustId AND FID = @Id
 
	FETCH NEXT FROM Data_cursor INTO @Id,@CustId,@PreAmount,@ReceivableAmount,@ReceiveAmount
END 
IF @Count > 0 
BEGIN
	INSERT INTO #DATA(FClassTypeId,FBillType,FCustId,FNumber,FName,FPreAmount,FReceivableAmount,FReceiveAmount,FBalanceAmount) 
	SELECT -9999,FName + '小计',FItemID,FNumber,FName,@SumPreAmount,@SumReceivableAmount,@SumReceiveAmount,@LastAmount
	FROM Organization 
	WHERE FItemID = @OldCustId
	Select @SumPreAmount=0,@SumReceivableAmount=0,@SumReceiveAmount=0,@SumBalanceAmount=0,@LastAmount=0
END
CLOSE Data_cursor
DEALLOCATE Data_cursor
 
SELECT * FROM #DATA
ORDER BY FCustId,FID
 
DROP TABLE #DATA

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

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

SET NOCOUNT ON
--建立临时表处理获取数据
CREATE TABLE #DATA(
	FID					INT NOT NULL PRIMARY KEY IDENTITY(1,1),
	FClassTypeId		INT NOT NULL,
	FCustId				INT NOT NULL,
	FNumber				NVARCHAR(255),
	FName				NVARCHAR(255),
	FDate				DATETIME  NULL,
	FBillType			NVARCHAR(64)  NULL,
	FBillNo				NVARCHAR(64)  NULL,
	FPreAmount			DECIMAL(28,10) NOT NULL DEFAULT(0),		--预收金额
	FReceivableAmount	DECIMAL(28,10) NOT NULL DEFAULT(0),		--应收金额
	FReceiveAmount		DECIMAL(28,10) NOT NULL DEFAULT(0),		--实收金额
	FBalanceAmount		DECIMAL(28,10) NOT NULL DEFAULT(0)      --期末余额
)
 
--插入空行
INSERT INTO #DATA(FClassTypeId,FBillType,FCustId,FNumber,FName) 
SELECT -1000,FName,FItemID,FNumber,FName
FROM Organization o
INNER JOIN (SELECT FCustId FROM DetailData GROUP BY FCustId) d ON d.FCustId = o.FItemID
 
--插入期初余额
INSERT INTO #DATA(FClassTypeId,FBillType,FCustId,FNumber,FName,FBalanceAmount) 
SELECT -1000,'期初余额',FItemID,'','',i.FReceivableAmount - i.FPreAmount -i.FReceiveAmount
FROM Organization o
INNER JOIN InitialData i ON o.FItemID = i.FCustId
INNER JOIN (SELECT FCustId FROM DetailData GROUP BY FCustId) d ON d.FCustId = o.FItemID
 
--插入单据明细(关键代码SUM() Over() )
INSERT INTO #DATA(FClassTypeId,FCustId,FNumber,FName,FDate,FBillType,FBillNo,FPreAmount,FReceivableAmount,FReceiveAmount,FBalanceAmount) 
SELECT 0,d.FCustId,o.FNumber,o.FName,d.FDate,d.FBillType,d.FBillNo,d.FPreAmount,d.FReceivableAmount,d.FReceiveAmount,
SUM(d.FReceivableAmount - d.FPreAmount - d.FReceiveAmount) OVER(PARTITION BY d.FCustId ORDER BY d.FCustId,d.FDate,d.FID)
+ i.FReceivableAmount - i.FPreAmount - i.FReceiveAmount
FROM DetailData d WITH(NOLOCK)
INNER JOIN Organization o WITH(NOLOCK) ON o.FItemID = d.FCustId
INNER JOIN InitialData i WITH(NOLOCK) ON o.FItemID = i.FCustId
ORDER BY d.FCustId,d.FDate,d.FID
 
--插入小计
INSERT INTO #DATA(FClassTypeId,FBillType,FCustId,FNumber,FName,FPreAmount,FReceivableAmount,FReceiveAmount,FBalanceAmount) 
SELECT -9999,FName + '小计',d.FCustId,FNumber,FName,SUM(FPreAmount),SUM(FReceivableAmount),SUM(FReceiveAmount),0
FROM dbo.DetailData d
INNER JOIN dbo.Organization o ON d.FCustId = o.FItemID
GROUP BY d.FCustId,o.FName,o.FNumber
 
--更新小计的期末余额
UPDATE d SET d.FBalanceAmount = d.FReceivableAmount - d.FPreAmount - d.FReceiveAmount + i.FReceivableAmount - i.FPreAmount - i.FReceiveAmount
FROM #DATA d
INNER JOIN InitialData i ON d.FCustId = i.FCustId
WHERE d.FClassTypeId = -9999
 
SELECT * FROM #DATA 
ORDER BY FCustId,FID
 
DROP TABLE #DATA

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

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、使用开窗函数来实现一些功能，还是很方便能实现效果，并且它的速度也是很快，值得推荐。

原文链接：http://www.cnblogs.com/cxt618/p/13826000.html

友情链接：直通硅谷　点职佳　北美留学生论坛

MS SQL Server热门文章

[sql Server]除非另外还指定了TOP 或 FOR XML，否则，ORDER BY 子句在视图、内联函数、派生表、子查询和公用表表达式中无效

SQL 拼接字符串使用IN查询方法

Microsoft SQL Server 双机热备份，实时同步

SQL Server 2016 + AlwaysOn 无域集群

Microsoft SQL Server 2012安装说明

mssql sqlserver 不固定行转列数据(动态列)

解釋 SQL Server 的狀態值 Runnable、Running、Suspended、Sleeping

sql server中format函数的yyyyMMddHHmmssffff时间格式兼容旧版sql写法

The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

SQL去除数据库表中tab、空格、回车符等特殊字符的解决方法

MS SQL Server推荐文章

拯救SQL Server数据库事务日志文件损坏的终极大招

SQL Server实战七：自定义数据类型、标量值、内嵌表值、多语句表值函数的操作

SQL Server实战六：T-SQL、游标、存储过程的操作

SQL Server实战四：查询数据库的数据

SQL Server 2022 AlwaysOn新特性之包含可用性组介绍

当 SQL Server（mssql-jdbc）遇上 BigDecimal → 精度丢失，真坑！