溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
前言：
今天在優化工作中遇到的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
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、使用開窗函數來實現一些功能，還是很方便能實現效果，并且它的速度也是很快，值得推薦。
到此這篇關于SQL Server 開窗函數 Over()代替游標的使用的文章就介紹到這了,更多相關SQL Server 開窗函數 Over()內容請搜索億速云以前的文章或繼續瀏覽下面的相關文章希望大家以后多多支持億速云！
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
