Как вычислить текущую сумму в SQL без использования курсора?

Ответ 1

Возможно, вы захотите взглянуть на обновление локального решения: http://geekswithblogs.net/Rhames/archive/2008/10/28/calculating-running-totals-in-sql-server-2005---the-optimal.aspx

DECLARE @SalesTbl TABLE (DayCount smallint, Sales money, RunningTotal money)

DECLARE @RunningTotal money

SET @RunningTotal = 0

INSERT INTO @SalesTbl 
SELECT DayCount, Sales, null
FROM Sales
ORDER BY DayCount

UPDATE @SalesTbl
SET @RunningTotal = RunningTotal = @RunningTotal + Sales
FROM @SalesTbl

SELECT * FROM @SalesTbl

Превышает все другие методы, но имеет некоторые сомнения относительно гарантированного порядка строк. Кажется, что он работает нормально, когда индексная таблица индексируется, хотя..

• Вложенный подзапрос 9300 мс
• Self join 6100 мс
• Курсор 400 мс
• Обновить локальную переменную 140 мс

Ответ 2

SQL может создавать текущие итоги без использования курсоров, но это один из немногих случаев, когда курсор на самом деле более эффективен, чем наборное решение (учитывая операторы, доступные в настоящее время на SQL Server). Альтернативно, функция CLR может иногда хорошо светиться. Itzik Ben-Gan отлично зарекомендовал себя в SQL Server Magazine по запуску агрегатов. Серия заключена в прошлом месяце, но вы можете получить доступ ко всем статьям, если у вас есть онлайн-подписка.

Изменить: здесь его последняя статья в серии (SQL CLR). Учитывая, что вы можете получить доступ к целой серии, покупая ежемесячный ежемесячный абонемент на один месяц - менее 6 долларов - это стоит того, если вы заинтересованы в поиске проблемы со всех сторон. Itzik - это Microsoft MVP и очень яркий TSQL-кодер.

Ответ 3

В Oracle и PostgreSQL 8.4 вы можете использовать функции окна:

SELECT  SUM(value) OVER (ORDER BY id)
FROM    mytable

В MySQL вы можете использовать переменную сеанса с той же целью:

SELECT  @sum := @sum + value
FROM    (
        SELECT  @sum := 0
        ) vars, mytable
ORDER BY
        id

В SQL Server это редкий пример задачи, для которой курсор является предпочтительным решением.

Ответ 4

Пример расчета общей суммы для каждой записи, но только если OrderDate для записей находится в одну дату. После того, как OrderDate отправится на другой день, будет запущен и накоплен новый текущий итог в течение нового дня: (предположим, что структура таблицы и данные)

select O.OrderId,
convert(char(10),O.OrderDate,101) as 'Order Date',
O.OrderAmt, 
(select sum(OrderAmt) from Orders 
                      where OrderID <= O.OrderID and 
                           convert(char(10),OrderDate,101)
                         = convert(char(10),O.OrderDate,101))
                               'Running Total' 
from Orders O
order by OrderID

Ниже приведены результаты, возвращенные из запроса, с помощью примера таблицы заказов:

OrderId     Order Date OrderAmt   Running Total                            
----------- ---------- ---------- ---------------
1           10/11/2003 10.50      10.50
2           10/11/2003 11.50      22.00
3           10/11/2003 1.25       23.25
4           10/12/2003 100.57     100.57
5           10/12/2003 19.99      120.56
6           10/13/2003 47.14      47.14
7           10/13/2003 10.08      57.22
8           10/13/2003 7.50       64.72
9           10/13/2003 9.50       74.22

Обратите внимание, что "Running Total" начинается со значения 10.50, а затем становится 22.00 и, наконец, становится 23.25 для OrderID 3, так как все эти записи имеют тот же OrderDate (10/11/2003). Но когда отображается OrderID 4, общее количество операций reset, а общее количество начинается снова. Это связано с тем, что OrderID 4 имеет другую дату для своего OrderDate, затем OrderID 1, 2 и 3. Вычисление этой общей суммы для каждой уникальной даты еще раз выполняется с использованием коррелированного подзапроса, хотя требуется дополнительное условие WHERE, которое что OrderDate в разных записях должен быть в тот же день. Это условие WHERE выполняется с помощью функции CONVERT для усечения OrderDate в формате MM/DD/YYYY.

Ответ 5

В SQL Server 2012 и выше вы можете просто использовать функцию окна Sum непосредственно к исходной таблице:

SELECT
   artranid,
   trandate,
   type,
   checkNumber,
   refNumber,
   custid,
   amount,
   taxAmount,
   Balance = Sum(amount) OVER (ORDER BY trandate ROWS UNBOUNDED PRECEDING),
   postedflag,
   modifieddate
FROM
   dbo.Sales
;

Это будет очень хорошо по сравнению со всеми решениями и не будет иметь возможности для ошибок, найденных в "причудливом обновлении".

Обратите внимание, что вам следует использовать версию ROWS, когда это возможно; версия RANGE может работать менее эффективно.

Ответ 6

Вы можете просто включить коррелированный подзапрос в предложение select. (Это будет плохо работать для очень больших наборов результатов), но

   Select <other stuff>,
       (Select Sum(ColumnVal) From Table
        Where OrderColumn <= T.OrderColumn) As RunningTotal
   From Table T
   Order By OrderColumn

Ответ 7

Вы можете выполнить количество запусков, вот пример, имейте в виду, что на самом деле это не так быстро, поскольку он должен проверять таблицу для каждой строки, если ваша таблица большая, это может быть достаточно трудоемким и дорогостоящим

create table #Test  (id int, Value decimal(16,4))
insert #Test values(1,100)
insert #Test values(2,100)
insert #Test values(3,100)
insert #Test values(4,200)
insert #Test values(5,200)
insert #Test values(6,200)
insert #Test values(7,200)

select *,(select sum(Value) from  #Test t2 where t2.id <=t1.id) as SumValues
 from #test t1

id  Value       SumValues
1   100.0000    100.0000
2   100.0000    200.0000
3   100.0000    300.0000
4   200.0000    500.0000
5   200.0000    700.0000
6   200.0000    900.0000
7   200.0000    1100.0000

Ответ 8

В SQLTeam также содержится article о вычислении текущих итогов. Существует сравнение 3 способов сделать это, наряду с некоторыми измерениями производительности:

• с помощью курсоров
• с использованием подзапроса (по сообщению SQLMenace)
• используя CROSS JOIN

Курсоры превосходят другие решения, но если вы не должны использовать курсоры, там, по крайней мере, есть альтернатива.

Ответ 9

То, что бит SELECT @nvcConcatonated возвращает только конкатенированное значение single. (Хотя он вычисляет промежуточные значения для каждой строки, вы можете только получить окончательное значение).

Итак, я думаю, что ответ отрицательный. Если бы вы хотели получить одно окончательное значение суммы, вы, конечно, просто использовали бы SUM.

Я не говорю, что вы не можете этого сделать, я просто говорю, что вы не можете сделать это, используя этот "трюк".

Ответ 10

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

UPDATE @SalesTbl SET @RunningTotal = RunningTotal = @RunningTotal + Sales FROM @SalesTbl

Использование одной из других опций (курсор, функция окна или вложенные запросы), как правило, будет вашей безопасной ставкой для надежных результатов.

Ответ 11

выберите TransactionDate, сумму, сумму + (сумма x.amount из транзакций x, где x.TransactionDate < транзакции) Выполнение итогов транзакций

где   x.TransactionDate < операции может быть любым условием, которое будет представлять все предыдущие записи, кроме текущего.