Рассчитать общий общий/текущий баланс

Ответ 1

Для тех, кто не использует SQL Server 2012 или выше, курсор, скорее всего, самый эффективный поддерживаемый и гарантированный метод вне CLR. Существуют и другие подходы, такие как "причудливое обновление", которое может быть немного быстрее, но не гарантировано работать в будущем, и, конечно же, основанные на наборе подходы с гиперболическими профилями производительности, поскольку таблица становится больше, и рекурсивными методами CTE, которые часто требуют прямого #tempdb ввода/вывода или привести к разливам, которые оказывают примерно одинаковое воздействие.

INNER JOIN - не делайте этого:

Медленный, основанный на наборе подход имеет вид:

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;

Привычное обновление - сделайте это на свой страх и риск:

Метод "quirky update" более эффективен, чем описанный выше, но поведение не документировано, нет никаких гарантий порядка, и поведение может работать сегодня, но может сломаться в будущем. Я включаю это, потому что это популярный метод, и он эффективен, но это не значит, что я его одобряю. Основная причина, по которой я даже ответил на этот вопрос, вместо того, чтобы закрыть его как дубликат, состоит в том, что другой вопрос имеет причудливое обновление в качестве принятого ответа.

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;

CLR

Для полноты я предлагаю ссылку на метод CLR Pavel Pawlowski, который, безусловно, является предпочтительным методом для версий до SQL Server 2012 (но не 2000).

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

Заключение

Если вы находитесь на SQL Server 2012 или выше, выбор очевиден - используйте новую конструкцию SUM() OVER() (с ROWS vs. RANGE). Для более ранних версий вам нужно сравнить эффективность альтернативных подходов в вашей схеме, данные и - принимая во внимание факторы, не связанные с производительностью - определить, какой подход подходит именно вам. Очень хорошо может быть подход CLR. Вот мои рекомендации в порядке предпочтения:

• SUM() OVER() ... ROWS, если в 2012 году или выше
• Метод CLR, если возможно
• Первый рекурсивный метод CTE, если возможно
• Курсор
• Другие рекурсивные методы CTE
• Привычное обновление
• Присоединение и/или коррелированный подзапрос

Для получения дополнительной информации при сопоставлении производительности этих методов см. этот вопрос на 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

Разделение результатов в текущем итоговом запросе

Несколько заработанных итогов с группой по

Ответ 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

Ответ 3

Мы в 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

У нас есть таблица транзакций с 2,3 миллионами строк с элементом, который имеет более 3300 транзакций, и запуск этого типа запроса против этого занимает совсем немного времени.

Ответ 4

В 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 Server 2012 +

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

Ответ 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