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

前言：

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

语法介绍：

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

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

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     INTDeclare @CustId    INTDeclare @PreAmount   decimal(28,10)Declare @ReceivableAmount decimal(28,10)Declare @ReceiveAmount  decimal(28,10)Declare @OldCustId   intDeclare @Count    intDeclare @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 CursorFor Select FID,FCustId,FPreAmount,FReceivableAmount,FReceiveAmount From DetailData Order By FCustId,FDate,FIDOPEN Data_cursorFETCH NEXT FROM Data_Cursor INTO @Id,@CustId,@PreAmount,@ReceivableAmount,@ReceiveAmountSET @OldCustId = @CustIdSET @Count = 0SET @LastAmount = 0SET @SumPreAmount = 0SET @SumReceivableAmount = 0SET @SumReceiveAmount = 0SET @SumBalanceAmount = 0WHILE @@FETCH_STATUS = 0BEGIN  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,@ReceiveAmountENDIF @Count > 0BEGIN 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=0ENDCLOSE Data_cursorDEALLOCATE Data_cursor SELECT * FROM #DATAORDER BY FCustId,FID DROP TABLE #DATA

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,FNameFROM Organization oINNER 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.FReceiveAmountFROM Organization oINNER JOIN InitialData i ON o.FItemID = i.FCustIdINNER 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.FReceiveAmountFROM DetailData d WITH(NOLOCK)INNER JOIN Organization o WITH(NOLOCK) ON o.FItemID = d.FCustIdINNER JOIN InitialData i WITH(NOLOCK) ON o.FItemID = i.FCustIdORDER 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),0FROM dbo.DetailData dINNER JOIN dbo.Organization o ON d.FCustId = o.FItemIDGROUP BY d.FCustId,o.FName,o.FNumber --更新小计的期末余额UPDATE d SET d.FBalanceAmount = d.FReceivableAmount - d.FPreAmount - d.FReceiveAmount + i.FReceivableAmount - i.FPreAmount - i.FReceiveAmountFROM #DATA dINNER JOIN InitialData i ON d.FCustId = i.FCustIdWHERE d.FClassTypeId = -9999 SELECT * FROM #DATAORDER 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)

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

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

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

更多相关文章

随机推荐