/*
需求：累計求和六種算法效率比較
作者：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