sql server 累計求和實現程式碼

/*
需求：累計求和六種演演算法效率比較
作者：felix
日期：2020-06-23

*/
--第一步，準備測試資料
--IF OBJECT_ID(N'dbo.t') IS NOT NULL
--	DROP TABLE dbo.t;
--GO
--CREATE TABLE dbo.t
--(
--	i BIGINT IDENTITY(1, 1) PRIMARY KEY,
--	d MONEY
--);
--INSERT t
--	d
--)
--SELECT TOP 31465
--	   ROUND(10000 * RAND(CHECKSUM(NEWID())), 2)
--FROM sys.all_objects AS a
--	CROSS JOIN sys.all_objects;
----第二步，建立記錄時間的表格
--IF OBJECT_ID(N'dbo.record_time') IS NOT NULL
--	DROP TABLE dbo.record_time;
--CREATE TABLE dbo.record_time
--	i INT IDENTITY PRIMARY KEY,
--	演演算法 NVARCHAR(10),
--	bt DATETIME2,--開始時間
--	et DATETIME2,--結束時間
--	idiff AS DATEDIFF(ms, bt, et)--所用的毫秒數
--第一種方法，自連線法，sql server 2008以上版本測試通過，157255661.40
SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;
GO
DECLARE @bt DATETIME2 = GETDATE();
SELECT a.i,
       a.d,
       SUM(b.d) AS total_sum
FROM dbo.t AS a
    INNER JOIN dbo.t AS b
        ON b.i <= a.i
GROUP BY a.i,
         a.d;
DECLARE @et DATETIME2 = GETDATE();
INSERT INTO dbo.record_time
(
    演演算法,
    bt,
    et
)
VALUES
('自連線', @bt, @et);
--ORDER BY a.i;
;
--第二種方法，遞迴，sql server 2008以上版本測試通過，157255661.40
WITH cte_total_sum
AS (SELECT i,
           d,
           d AS total_sum
    FROM dbo.t
    WHERE i = 1
    UNION ALL
    SELECT s.i,
           s.d,
           p.total_sum + s.d AS total_sum
    FROM dbo.t AS s
        INNER JOIN cte_total_sum AS p
            ON s.i - 1 = p.i)
SELECT *
FROM cte_total_sum
OPTION (MAXRECURSION 0);
('遞迴', @bt, @et);
--第三種方法，over 子句，sql server 2012測試通過，sql server 2008不支援，157255661.40
SELECT i,
       d,
       SUM(d) OVER (ORDER BY i) AS total_sum
FROM dbo.t;
('over子句', @bt, @et);
--第四種，相關子查詢，sql server 2008以上版本測試通過，156625045.22
SELECT outquery.i,
       outquery.d,
       (
           SELECT SUM(innerq.d) FROM dbo.t AS innerq WHERE innerq.i <= outquery.i
       ) AS ct --內部查詢
FROM dbo.t AS outquery;
('相關子查詢', @bt, @et);
--ORDER BY outquery.i; --外部查詢
--遊標方法，有兩種方法可以實現，一種是臨時表更新，一種是變數疊加更新，157255661.40
--先增加一個儲存累計和的列
--第5種，遊標_臨時表更新
--ALTER TABLE dbo.t ADD total_d MONEY DEFAULT (0);--只執行一次
DECLARE @t TABLE --定義表變數，儲存累計求和臨時結果
    i INT PRIMARY KEY IDENTITY,
    d MONEY,
    total_d MONEY
);
DECLARE @i INT = 0,
        @d MONEY = 0,
        @total_d MONEY = 0;
DECLARE c1 CURSOR FOR SELECT i, d FROM dbo.t ORDER BY i;
OPEN c1;
FETCH c1
INTO @i,
     @d;
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @total_d += @d;
    INSERT INTO @t
    (
        d,
        total_d
    )
    VALUES
    (@d, @total_d);
    FETCH c1
    INTO @i,
         @d;
END;
CLOSE c1;
DEALLOCATE c1;
UPDATE dbo.t
SET total_d = b.total_d
    INNER JOIN @t AS b
        ON a.i = b.i;
('遊標_臨時表更新', @bt, @et);
--第6種，遊標_變數疊加更新
DECLARE c1 CURSOR FOR SELECT i, d FROM dbo.t; --ORDER BY i;
    UPDATE dbo.t
    SET total_d = @total_d
    WHERE i = @i;
('遊標_變數疊加更新', @bt, @et);
--執行時間 over子句<遊標臨時表更新<遊標變數疊加更新<自連線<相關子查詢<遞迴查詢

SELECT SalesOrderID, ProductID, OrderQty
    ,SUM(OrderQty) OVER(PARTITION BY SalesOrderID) AS Total
    ,AVG(OrderQty) OVER(PARTITION BY SalesOrderID) AS "Avg"
    ,COUNT(OrderQty) OVER(PARTITION BY SalesOrderID) AS "Count"
    ,MIN(OrderQty) OVER(PARTITION BY SalesOrderID) AS "Min"
    ,MAX(OrderQty) OVER(PARTITION BY SalesOrderID) AS "Max"
FROM Sales.SalesOrderDetail 
WHERE SalesOrderID IN(43659,43664);

select SchSno,convert(varchar(10),a.Dates,120) Dates,
sum(Amt_avail) over(partition by SchSno order by convert(varchar(10),a.Dates,120)) as PeriodPreAmt
from jr_creditUserAcct a