Como faço para calcular um total em execução no SQL sem usar um cursor?

Estou deixando de fora toda a configuração do cursor e o SELECT da tabela temporária por brevidade. Basicamente, esse código calcula um saldo em execução para todas as transactions por transação.

WHILE @@fetch_status = 0 BEGIN set @balance = @balance+@amount insert into @tblArTran values ( --from artran table @artranid, @trandate, @type, @checkNumber, @refNumber,@custid, @amount, @taxAmount, @balance, @postedflag, @modifieddate ) FETCH NEXT FROM artranCursor into @artranid, @trandate, @type, @checkNumber, @refNumber, @amount, @taxAmount,@postedFlag,@custid, @modifieddate END 

Inspirado por este código de uma resposta a outra pergunta,

 SELECT @nvcConcatenated = @nvcConcatenated + C.CompanyName + ', ' FROM tblCompany C WHERE C.CompanyID IN (1,2,3) 

Eu queria saber se o SQL tinha a capacidade de sumr números da mesma forma que é concatenar strings, se você entender o meu significado. Ou seja, para criar um “saldo em execução” por linha, sem usar um cursor.

É possível?

Você pode querer dar uma olhada na atualização para a solução variável local aqui: 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 

Supera todos os outros methods, mas tem algumas dúvidas sobre a garantia de ordem de linha. Parece funcionar bem quando a tabela temporária é indexada embora ..

  • Subconsulta aninhada 9300 ms
  • Auto-junit 6100 ms
  • Cursor 400 ms
  • Atualizar para variável local 140 ms

O SQL pode criar totais em execução sem usar cursores, mas é um dos poucos casos em que um cursor tem mais desempenho do que uma solução baseada em conjunto (dados os operadores atualmente disponíveis no SQL Server). Alternativamente, uma function CLR pode às vezes brilhar bem. Itzik Ben-Gan fez uma excelente série na revista SQL Server Magazine sobre agregações em execução. A série foi concluída no mês passado, mas você pode obter access a todos os artigos se tiver uma assinatura on-line.

Edit: aqui está o seu mais recente artigo da série (SQL CLR). Como você pode acessar toda a série comprando um passe mensal on-line por um mês – menos de 6 dólares – vale a pena se você estiver interessado em analisar o problema de todos os ângulos. Itzik é um Microsoft MVP e um codificador TSQL muito shiny.

No Oracle e no PostgreSQL 8.4 você pode usar as funções da janela:

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

No MySQL , você pode usar uma variável de session para o mesmo propósito:

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

No SQL Server , é um exemplo raro de uma tarefa para a qual um cursor é uma solução preferida.

Um exemplo de cálculo de um total em execução para cada registro, mas somente se o OrderDate dos registros estiver na mesma data. Depois que o OrderDate for para um dia diferente, um novo total em execução será iniciado e acumulado para o novo dia: (assuma a estrutura e os dados da tabela)

 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 

Aqui estão os resultados retornados da consulta usando a Tabela de Pedidos de amostra:

 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 

Observe que o "Running Total" começa com um valor de 10,50 e, em seguida, torna-se 22,00 e, finalmente, torna-se 23,25 para OrderID 3, pois todos esses registros têm o mesmo OrderDate (10/11/2003). Mas quando o OrderID 4 é exibido, o total de execução é redefinido e o total de execução recomeça. Isso ocorre porque OrderID 4 tem uma data diferente para seu OrderDate, depois OrderID 1, 2 e 3. O cálculo desse total em execução para cada data exclusiva é realizado novamente usando uma subconsulta correlacionada, embora seja necessária uma condição WHERE extra, que identificou que o OrderDate em registros diferentes precisa ser o mesmo dia. Essa condição WHERE é realizada usando a function CONVERT para truncar o OrderDate em um formato MM / DD / AAAA.

No SQL Server 2012 e superior, você pode usar a function de janelamento Sum diretamente na tabela original:

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

Isso funcionará muito bem em comparação com todas as soluções e não terá o potencial para erros como encontrado na “atualização peculiar”.

Note que você deve usar a versão ROWS quando possível; a versão RANGE pode ter um desempenho menor.

Você pode include apenas uma subconsulta correlacionada na cláusula select. (Isso terá um desempenho ruim para conjuntos de resultados muito grandes), mas

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

Você pode fazer uma contagem em execução , aqui está um exemplo, lembre-se de que isso não é tão rápido, já que tem que varrer a tabela para cada linha, se a sua tabela for grande, isso pode ser demorado e dispendioso.

 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 

No SQLTeam, há também um artigo sobre como calcular totais em execução. Há uma comparação de três maneiras de fazer isso, juntamente com algumas medidas de desempenho:

  • usando cursores
  • usando uma subseleção (de acordo com o post do SQLMenace)
  • usando um CROSS JOIN

Cursores superam de longe as outras soluções, mas se você não deve usar cursores, há pelo menos uma alternativa.

Que esse bit SELECT @nvcConcatonated está retornando apenas um único valor concatenado. (Embora esteja computando os valores intermediários por linha, você só poderá recuperar o valor final).

Então, acho que a resposta é não. Se você quisesse um único valor final, você simplesmente usaria SUM .

Eu não estou dizendo que você não pode fazer isso, só estou dizendo que você não pode fazer isso usando esse ‘truque’.

Observe que o uso de uma variável para realizar isso, como a seguir, pode falhar em um sistema multiprocessador porque linhas separadas podem ser calculadas em processadores diferentes e podem acabar usando o mesmo valor inicial. Meu entendimento é que uma dica de consulta poderia ser usada para forçá-lo a usar um único thread, mas eu não tenho essa informação à mão.

ATUALIZAR @SalesTbl SET @RunningTotal = RunningTotal = @RunningTotal + Vendas FROM @SalesTbl

Usar uma das outras opções (um cursor, uma function de janela ou consultas aninhadas) normalmente será sua aposta mais segura para resultados confiáveis.

selecione TransactionDate, quantidade, valor + (sum x.amount de transactions x onde x.TransactionDate

onde x.TransactionDate