The use of SQL server window function over() instead of cursor

Time:2021-4-15

preface: in today’s optimization work, we encountered the problem of slow SQL. We found that many cursors were used to process data before, which led to the need to traverse line by line to calculate the required data in the case of a large amount of data. The result of such processing is that the data is slow and easy to get stuck.

Introduction to Grammar:

1. And row_ The number () function is used in combination to sort the results, which we use a lot

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

  

2. In combination with aggregate function, the over clause is used to group and sort the needed data

For example: sum () over () cumulative value, AVG () over () average
Max() over() max, min() over() min

Specific introduction:

In the following simulation work, we use the window function instead of the cursor to calculate the ending balance of the current document through the opening balance and the amount received in advance, the amount receivable and the amount actually received. In the past, we used to traverse the cursor line by line to calculate the ending balance. Now we use sum() over() instead. The final effect is as follows:

The first line represents the title; the second line represents the customer, which is an empty line; the third line is the opening balance, which only displays the data of the ending balance; the fourth to sixth lines represent the balance of each document, and gradually summarize the ending balance data of the current line; the last line represents the total of customers.

1. Tables and data needed to build (abbreviated version)

--Customer list
CREATE TABLE Organization(
	FItemID		INT NOT NULL PRIMARY KEY IDENTITY(1,1),
	FNumber		NVARCHAR(255),
	FName		NVARCHAR(255)
)

--Opening data sheet
CREATE TABLE InitialData(
	FID			INT NOT NULL PRIMARY KEY IDENTITY(1,1),
	FCustId			INT NOT NULL,
	Fpreamount decimal (28,10) not null default (0), -- amount received in advance
	Freceivableamount decimal (28,10) not null default (0), -- amount receivable
	Freeiveamount digital (28,10) not null default (0) -- paid in amount
)

--List of documents
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), -- amount received in advance
	Freceivableamount decimal (28,10) not null default (0), -- amount receivable
	Freeiveamount digital (28,10) not null default (0) -- paid in amount
)

Insert into organization (fnumber, fname) values ('001 ','Beijing customer')
Insert into organization (fnumber, fname) values ('002 ','shanghai customer')
Insert into organization (fnumber, fname) values ('003 ','Guangzhou customer')

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','entrusted settlement ','xsd20200700008', 01221.56,0)
INSERT INTO DetailData(FCustId,FDate,FBillType,FBillNo,FPreAmount,FReceivableAmount,FReceiveAmount)
Values (1, '2020-06-30','entrusted settlement ','xsd20200700009', 0373.46,0)
INSERT INTO DetailData(FCustId,FDate,FBillType,FBillNo,FPreAmount,FReceivableAmount,FReceiveAmount)
Values (1, '2020-06-30','entrusted settlement return ','xsd20200700010', 0, - 427.05,0)
INSERT INTO DetailData(FCustId,FDate,FBillType,FBillNo,FPreAmount,FReceivableAmount,FReceiveAmount)
Values (1, '2020-07-30','sales rebate ','xsfl20200700005', 0, - 17.9,0)

INSERT INTO DetailData(FCustId,FDate,FBillType,FBillNo,FPreAmount,FReceivableAmount,FReceiveAmount)
Values (2, '2020-06-25','advance refund ','skd20200700002', - 755,0,0)
INSERT INTO DetailData(FCustId,FDate,FBillType,FBillNo,FPreAmount,FReceivableAmount,FReceiveAmount)
Values (2, '2020-06-20','sales delivery ','xsd20200700006', 06169.506169.50)
INSERT INTO DetailData(FCustId,FDate,FBillType,FBillNo,FPreAmount,FReceivableAmount,FReceiveAmount)
Values (2, '2020-07-30', 'gross sales rebate', 'xsfl2020070002', 0, - 493.56, - 421.85)
INSERT INTO DetailData(FCustId,FDate,FBillType,FBillNo,FPreAmount,FReceivableAmount,FReceiveAmount)
Values (2, '2020-07-31','Other receivables', 'qtys2020090001', 06000.00,0)
INSERT INTO DetailData(FCustId,FDate,FBillType,FBillNo,FPreAmount,FReceivableAmount,FReceiveAmount)
Values (2, '2020-06-20','advance receipt offset a / R ','hxd2020070006', - 7245.00, 07245.00)

INSERT INTO DetailData(FCustId,FDate,FBillType,FBillNo,FPreAmount,FReceivableAmount,FReceiveAmount)
Values (3, '2020-06-30','sales collection ','skd20200700003', 0,02386.96)
INSERT INTO DetailData(FCustId,FDate,FBillType,FBillNo,FPreAmount,FReceivableAmount,FReceiveAmount)
Values (3, '2020-06-30','a / R to a / R ','hxd2020070007', 02386.75,0)
INSERT INTO DetailData(FCustId,FDate,FBillType,FBillNo,FPreAmount,FReceivableAmount,FReceiveAmount)
Values (3, '2020-07-08', 'sales return', 'xsd20200700014', 0, - 46.80,0)
GO

2. Cursor writing in the past

SET NOCOUNT ON
--Establishing temporary table to process and obtain data
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), -- amount received in advance
	Freceivableamount decimal (28,10) not null default (0), -- amount receivable
	Freeiveamount decimal (28,10) not null default (0), -- paid in amount
	Fbalanceamount digital (28,10) not null default (0) -- closing balance
)

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)

--Using cursors
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
			--It means that the customer has changed, so the subtotal should be inserted
			SET @Count = 0
			INSERT INTO #DATA(FClassTypeId,FBillType,FCustId,FNumber,FName,FPreAmount,FReceivableAmount,FReceiveAmount,FBalanceAmount) 
			Select - 9999, fname + 'subtotal' ,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 @[email protected]
		--Insert a blank line
		INSERT INTO #DATA(FClassTypeId,FBillType,FCustId,FNumber,FName) 
		SELECT -1000,FName,FItemID,FNumber,FName
		FROM Organization 
		WHERE FItemID = @CustId

		--Get the closing balance at the beginning of the period
		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, 'opening balance' ,@CustId ,'','' ,@LastAmount )

		SELECT @Count = 1
		SELECT @SumBalanceAmount = @LastAmount
    END  

	--Insert document details
	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,
	@[email protected] + FPreAmount,@[email protected] + FReceivableAmount,
	@[email protected] + 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 + 'subtotal' ,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

Code Description: create a temporary table, use the cursor to traverse our detaildata data table. In order to present the final data style we need, insert customer blank line, opening balance, document information, customer subtotal, etc., and calculate the closing balance line by line. The final effect is as follows:

3. Using sum() over() to write

SET NOCOUNT ON
--Establishing temporary table to process and obtain data
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), -- amount received in advance
	Freceivableamount decimal (28,10) not null default (0), -- amount receivable
	Freeiveamount decimal (28,10) not null default (0), -- paid in amount
	Fbalanceamount digital (28,10) not null default (0) -- closing balance
)

--Insert blank line
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 opening balance
INSERT INTO #DATA(FClassTypeId,FBillType,FCustId,FNumber,FName,FBalanceAmount) 
Select - 1000, 'opening balance', fitemid, '' ',', i.freeiveamount - i.fpreamount - i.freeiveamount
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

--Insert document details (key code 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 subtotal
INSERT INTO #DATA(FClassTypeId,FBillType,FCustId,FNumber,FName,FPreAmount,FReceivableAmount,FReceiveAmount,FBalanceAmount) 
Select - 9999, fname + 'subtotal', d.fcustid, fnumber, fname, sum (fpreamount), sum (freeseivableamount), sum (freeseiveamount), 0
FROM dbo.DetailData d
INNER JOIN dbo.Organization o ON d.FCustId = o.FItemID
GROUP BY d.FCustId,o.FName,o.FNumber

--Update ending balance of subtotal
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

Code Description: compared with the second method, it removes the writing method of cursor and passes the

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

To calculate the value we need, this syntax explains that sum is cumulative calculation, which calculates the amount receivable – amount received in advance – amount actually received (the result calculated in the second line should be added with the result calculated in the first line, the result calculated in the third line should be added with the result calculated in the second line, and so on, so other aggregation functions are also used in this way),Partition by group counts customers and specifies the sort by order by
The usage of the partition by and order by results is crucial, otherwise the calculation is not expected
Another example: for example, use count () over () to calculate the customer’s order number

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

Conclusion:

1. Cursor can be used in a wide range of scenarios, but when the amount of data is large, it will appear very slow, and the traversal speed of line by line is very long

2. Using window function to achieve some functions, it is very convenient to achieve the effect, and its speed is also very fast, it is worth recommending.