计算运行总计/运行余额

Question

我有一个表格：

create table Transactions(Tid int,amt int)

有 5 行：

insert into Transactions values(1, 100)
insert into Transactions values(2, -50)
insert into Transactions values(3, 100)
insert into Transactions values(4, -100)
insert into Transactions values(5, 200)

期望输出：

TID  amt  balance
--- ----- -------
1    100   100
2    -50    50
3    100   150
4   -100    50
5    200   250

基本上对于第一个记录余额将与amt相同，第二个以后的余额将是加上以前的余额+当前amt。我正在寻找一种最佳方法。我可以考虑使用函数或相关的子查询，但不确定如何做到这一点。

Answer 1

对于不使用 SQL Server 2012 或更高版本的用户，游标可能是 CLR 之外最有效的受支持和保证方法。还有其他方法，例如"古怪的更新"，它可以稍微快一点，但不能保证将来会起作用，当然还有基于集合的方法，随着表格变大，具有双曲线性能配置文件，以及递归CTE方法，通常需要直接 #tempdb I/O或导致产生大致相同影响的溢出。

<小时 />

内部联接 - 不要这样做：

基于集合的慢速方法的形式如下：

SELECT t1.TID, t1.amt, RunningTotal = SUM(t2.amt)
FROM dbo.Transactions AS t1
INNER JOIN dbo.Transactions AS t2
  ON t1.TID >= t2.TID
GROUP BY t1.TID, t1.amt
ORDER BY t1.TID;

这很慢的原因是什么？随着表变大，每个增量行都需要读取表中的 n-1 行。这是指数级的，必然会导致失败、超时或只是愤怒的用户。

<小时 />

相关子查询 - 也不要这样做：

由于同样痛苦的原因，子查询表单同样痛苦。

SELECT TID, amt, RunningTotal = amt + COALESCE(
(
  SELECT SUM(amt)
    FROM dbo.Transactions AS i
    WHERE i.TID < o.TID), 0
)
FROM dbo.Transactions AS o
ORDER BY TID;

<小时 />

古怪的更新 - 这样做需要您自担风险：

"古怪更新"方法比上述方法更有效，但行为没有记录，没有关于顺序的保证，并且该行为今天可能有效，但将来可能会中断。我包括这个是因为它是一种流行的方法并且有效，但这并不意味着我认可它。我什至回答这个问题而不是将其作为副本关闭的主要原因是因为另一个问题有一个古怪的更新作为可接受的答案。

DECLARE @t TABLE
(
  TID INT PRIMARY KEY,
  amt INT,
  RunningTotal INT
);
 
DECLARE @RunningTotal INT = 0;
 
INSERT @t(TID, amt, RunningTotal)
  SELECT TID, amt, RunningTotal = 0
  FROM dbo.Transactions
  ORDER BY TID;
 
UPDATE @t
  SET @RunningTotal = RunningTotal = @RunningTotal + amt
  FROM @t;
 
SELECT TID, amt, RunningTotal
  FROM @t
  ORDER BY TID;

<小时 />

递归 CTE

第一个依赖于TID是连续的，没有间隙：

;WITH x AS
(
  SELECT TID, amt, RunningTotal = amt
    FROM dbo.Transactions
    WHERE TID = 1
  UNION ALL
  SELECT y.TID, y.amt, x.RunningTotal + y.amt
   FROM x 
   INNER JOIN dbo.Transactions AS y
   ON y.TID = x.TID + 1
)
SELECT TID, amt, RunningTotal
  FROM x
  ORDER BY TID
  OPTION (MAXRECURSION 10000);

如果你不能依赖这个，那么你可以使用这个变体，它只是使用 ROW_NUMBER() 构建一个连续的序列：

;WITH y AS 
(
  SELECT TID, amt, rn = ROW_NUMBER() OVER (ORDER BY TID)
    FROM dbo.Transactions
), x AS
(
    SELECT TID, rn, amt, rt = amt
      FROM y
      WHERE rn = 1
    UNION ALL
    SELECT y.TID, y.rn, y.amt, x.rt + y.amt
      FROM x INNER JOIN y
      ON y.rn = x.rn + 1
)
SELECT TID, amt, RunningTotal = rt
  FROM x
  ORDER BY x.rn
  OPTION (MAXRECURSION 10000);

根据数据的大小（例如，我们不知道的列），通过先将相关列填充到 #temp 表中，然后针对该表而不是基表进行处理，您可能会发现更好的整体性能：

CREATE TABLE #x
(
  rn  INT PRIMARY KEY,
  TID INT,
  amt INT
);
INSERT INTO #x (rn, TID, amt)
SELECT ROW_NUMBER() OVER (ORDER BY TID),
  TID, amt
FROM dbo.Transactions;
;WITH x AS
(
  SELECT TID, rn, amt, rt = amt
    FROM #x
    WHERE rn = 1
  UNION ALL
  SELECT y.TID, y.rn, y.amt, x.rt + y.amt
    FROM x INNER JOIN #x AS y
    ON y.rn = x.rn + 1
)
SELECT TID, amt, RunningTotal = rt
  FROM x
  ORDER BY TID
  OPTION (MAXRECURSION 10000);
DROP TABLE #x;

只有第一种CTE方法才能提供与古怪更新相媲美的性能，但它对数据的性质做出了很大的假设（没有差距）。其他两种方法将回退，在这些情况下，您也可以使用游标（如果您不能使用 CLR 并且您尚未使用 SQL Server 2012 或更高版本）。

<小时 />

光标

每个人都被告知游标是邪恶的，应该不惜一切代价避免它们，但这实际上击败了大多数其他支持方法的性能，并且比古怪的更新更安全。相对于游标解决方案，我唯一更喜欢的是 2012 和 CLR 方法（如下）：

CREATE TABLE #x
(
  TID INT PRIMARY KEY, 
  amt INT, 
  rt INT
);
INSERT #x(TID, amt) 
  SELECT TID, amt
  FROM dbo.Transactions
  ORDER BY TID;
DECLARE @rt INT, @tid INT, @amt INT;
SET @rt = 0;
DECLARE c CURSOR LOCAL STATIC READ_ONLY FORWARD_ONLY
  FOR SELECT TID, amt FROM #x ORDER BY TID;
OPEN c;
FETCH c INTO @tid, @amt;
WHILE @@FETCH_STATUS = 0
BEGIN
  SET @rt = @rt + @amt;
  UPDATE #x SET rt = @rt WHERE TID = @tid;
  FETCH c INTO @tid, @amt;
END
CLOSE c; DEALLOCATE c;
SELECT TID, amt, RunningTotal = rt 
  FROM #x 
  ORDER BY TID;
DROP TABLE #x;

<小时 />

SQL Server 2012 或更高版本

SQL Server 2012 中引入的新窗口函数使此任务变得更加容易（并且它的性能也比上述所有方法都好）：

SELECT TID, amt, 
  RunningTotal = SUM(amt) OVER (ORDER BY TID ROWS UNBOUNDED PRECEDING)
FROM dbo.Transactions
ORDER BY TID;

请注意，在较大的数据集上，您会发现上述选项的性能比以下两个选项中的任何一个都要好得多，因为 RANGE 使用磁盘假脱机（默认使用 RANGE）。但是，同样重要的是要注意，行为和结果可能不同，因此在根据此差异在它们之间做出决定之前，请确保它们都返回正确的结果。

SELECT TID, amt, 
  RunningTotal = SUM(amt) OVER (ORDER BY TID)
FROM dbo.Transactions
ORDER BY TID;
SELECT TID, amt, 
  RunningTotal = SUM(amt) OVER (ORDER BY TID RANGE UNBOUNDED PRECEDING)
FROM dbo.Transactions
ORDER BY TID;

<小时 />

中罗

为了完整起见，我提供了一个指向Pavel Pawlowski的CLR方法的链接，这是迄今为止SQL Server 2012之前版本（但显然不是2000）上的首选方法。

http://www.pawlowski.cz/2010/09/sql-server-and-fastest-running-totals-using-clr/

<小时 />

结论

如果您使用的是 SQL Server 2012 或更高版本，则选择是显而易见的 - 使用新的SUM() OVER()构造（使用 ROWS 与 RANGE ）。对于早期版本，您需要比较架构、数据上替代方法的性能，并考虑与性能无关的因素，确定哪种方法适合您。这很可能是 CLR 方法。以下是我的建议，按优先顺序排列：

1. SUM() OVER() ... ROWS ， 如果是 2012 年或以上
2. CLR 方法（如果可能）
3. 如果可能，第一个递归 CTE 方法
4. 光标
5. 其他递归 CTE 方法
6. 古怪的更新
7. 联接和/或相关子查询

<小时 />

有关这些方法的性能比较的详细信息，请参阅 http://dba.stackexchange.com 上的此问题：

https://dba.stackexchange.com/questions/19507/running-total-with-count

<小时 />

我还在博客上写了有关这些比较的更多详细信息：

http://www.sqlperformance.com/2012/07/t-sql-queries/running-totals

<小时 />

此外，对于分组/分区的运行总计，请参阅以下帖子：

http://sqlperformance.com/2014/01/t-sql-queries/grouped-running-totals

分区结果为正在运行的总计查询

分组依据的多个运行总计

Answer 2

> 如果您使用版本 2012，这里有一个解决方案

select *, sum(amt) over (order by Tid) as running_total from Transactions 

对于早期版本

select *,(select sum(amt) from Transactions where Tid<=t.Tid) as running_total from Transactions as t

Answer 3

在 SQL Server 2008+ 中

SELECT  T1.* ,
        T2.RunningSum
FROM    dbo.Transactions As T1
        CROSS APPLY ( SELECT    SUM(amt) AS RunningSum
                      FROM      dbo.Transactions AS CAT1
                      WHERE     ( CAT1.TId <= T1.TId )
                    ) AS T2

在 SQL 服务器 2012+ 中

SELECT  * ,
        SUM(T1.amt) OVER ( ORDER BY T1.TId 
                        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS RunningTotal
FROM    dbo.Transactions AS t1

Answer 4

我们在 2008R2 上，我使用变量和临时表。这也允许您在使用 case 语句计算每一行时执行自定义操作（即某些事务可能有不同的行为，或者您可能只需要特定事务类型的总计）

DECLARE @RunningBalance int = 0
SELECT Tid, Amt, 0 AS RunningBalance
INTO #TxnTable
FROM Transactions
ORDER BY Tid
UPDATE #TxnTable
SET @RunningBalance = RunningBalance = @RunningBalance + Amt
SELECT * FROM #TxnTable
DROP TABLE #TxnTable

我们有一个包含 230 万行的事务表，其中包含一个包含 3,300 多个事务的项目，并且针对该表运行这种类型的查询根本不需要时间。

Answer 5

select v.ID
,CONVERT(VARCHAR(10), v.EntryDate, 103) + ' '  + convert(VARCHAR(8), v.EntryDate, 14) 
as EntryDate
,case
when v.CreditAmount<0
then
    ISNULL(v.CreditAmount,0) 
    else 
    0 
End  as credit
,case
when v.CreditAmount>0
then
    v.CreditAmount
    else
    0
End  as debit
,Balance = SUM(v.CreditAmount) OVER (ORDER BY v.ID ROWS UNBOUNDED PRECEDING)
      from VendorCredit v
    order by v.EntryDate desc

Answer 6

使用 2012 SUM 和 OVER 函数，您现在可以嵌套sum和counts。

SELECT date, sum(count(DISTINCT unique_id)) OVER (ORDER BY date) AS total_per_date
FROM dbo.table
GROUP BY date