sql server 累计求和方法

原文：https://zhuanlan.zhihu.com/p/150291837

看了一眼自关联，没搞懂，试了一下也没成功。

over方式一下结果就出来了，好用。

 

/*
需求：累计求和六种算法效率比较
作者：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;
--GO
--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;
GO
;
--第二种方法，递归，sql server 2008以上版本测试通过，157255661.40
DECLARE @bt DATETIME2 = GETDATE();
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);
DECLARE @et DATETIME2 = GETDATE();
INSERT INTO dbo.record_time
(
    算法,
    bt,
    et
)
VALUES
('递归', @bt, @et);
GO
--第三种方法，over 子句，sql server 2012测试通过，sql server 2008不支持，157255661.40
DECLARE @bt DATETIME2 = GETDATE();

SELECT i,
       d,
       SUM(d) OVER (ORDER BY i) AS total_sum
FROM dbo.t;
DECLARE @et DATETIME2 = GETDATE();

INSERT INTO dbo.record_time
(
    算法,
    bt,
    et
)
VALUES
('over子句', @bt, @et);
GO
--第四种，相关子查询，sql server 2008以上版本测试通过，156625045.22
DECLARE @bt DATETIME2 = GETDATE();
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;
DECLARE @et DATETIME2 = GETDATE();
INSERT INTO dbo.record_time
(
    算法,
    bt,
    et
)
VALUES
('相关子查询', @bt, @et);
--ORDER BY outquery.i; --外部查询
GO
--游标方法，有两种方法可以实现，一种是临时表更新，一种是变量叠加更新，157255661.40
--先增加一个存储累计和的列
--第5种，游标_临时表更新
--ALTER TABLE dbo.t ADD total_d MONEY DEFAULT (0);--只运行一次
GO
DECLARE @bt DATETIME2 = GETDATE();
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
FROM dbo.t AS a
    INNER JOIN @t AS b
        ON a.i = b.i;
SELECT *
FROM dbo.t;
DECLARE @et DATETIME2 = GETDATE();
INSERT INTO dbo.record_time
(
    算法,
    bt,
    et
)
VALUES
('游标_临时表更新', @bt, @et);
GO
--第6种，游标_变量叠加更新
DECLARE @bt DATETIME2 = GETDATE();
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;
    UPDATE dbo.t
    SET total_d = @total_d
    WHERE i = @i;
    FETCH c1
    INTO @i,
         @d;
END;
CLOSE c1;
DEALLOCATE c1;
SELECT *
FROM dbo.t;
DECLARE @et DATETIME2 = GETDATE();
INSERT INTO dbo.record_time
(
    算法,
    bt,
    et
)
VALUES
('游标_变量叠加更新', @bt, @et);

--执行时间 over子句<游标临时表更新<游标变量叠加更新<自连接<相关子查询<递归查询