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

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

ВНУТРЕННЕЕ СОЕДИНЕНИЕ - не делайте этого:

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

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;

CLR

Для полноты я предлагаю ссылку на метод 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

Секционирование приводит к запросу промежуточных сумм

Несколько текущих итогов с группировкой

Если вы используете версию 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

В 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

У нас 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 транзакций, и выполнение этого типа запроса не требует времени.

С помощью функций SUM и OVER версии 2012 теперь можно вкладывать друг в друга sum и counts.

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